Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10147

server crashes sporadically in create_view_field

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.1.14, 10.1.31, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
    • 5.5.61, 10.0.36, 10.1.34, 10.2.16, 10.3.8
    • Views
    • None
    • Linux Mint 17.3

    Description

      Hi-

      I'm experiencing occasionally crashes in a query that joins a few tables, including a join to a view. From what I can tell from the stack trace, the problem may have something to do with the view. This query normally works fine, and when I run it in the command line client it is very fast.

      However, under relatively heavy load, especially when I configure my connection pool to contain only a single connection, I get a crash - always on the same query. The stack trace is below, please let me know if there is any additional information I can provide.

      Server version: 10.1.14-MariaDB-1~trusty
      key_buffer_size=134217728
      read_buffer_size=2097152
      max_used_connections=22
      max_threads=102
      thread_count=2
      It is possible that mysqld could use up to 
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 759828 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
       
      Thread pointer: 0x0x7ff9153a2008
      Attempting backtrace. You can use the following information to find out
      where mysqld died. If you see no messages after this, something went
      terribly wrong...
      stack_bottom = 0x7ff9482d51f0 thread_stack 0x48400
      

      /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x7ff951040b5e]
      /usr/sbin/mysqld(handle_fatal_signal+0x38d)[0x7ff950b6cabd]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x10330)[0x7ff94f0c2330]
      /usr/sbin/mysqld(_Z17create_view_fieldP3THDP10TABLE_LISTPP4ItemPKc+0x1bc)[0x7ff950a971ac]
      /usr/sbin/mysqld(_Z23find_field_in_table_refP3THDP10TABLE_LISTPKcjS4_S4_S4_PP4ItembbPjbPS2_+0x13f)[0x7ff9509abe1f]
      /usr/sbin/mysqld(_Z20find_field_in_tablesP3THDP10Item_identP10TABLE_LISTS4_PP4Item27find_item_error_report_typebb+0x143)[0x7ff9509ac903]
      /usr/sbin/mysqld(_ZN10Item_field10fix_fieldsEP3THDPP4Item+0x332)[0x7ff950b8c1c2]
      /usr/sbin/mysqld(_ZN9Item_func10fix_fieldsEP3THDPP4Item+0x19f)[0x7ff950bc714f]
      /usr/sbin/mysqld(_ZN9Item_cond10fix_fieldsEP3THDPP4Item+0xb9)[0x7ff950b9ce59]
      /usr/sbin/mysqld(_Z11setup_condsP3THDP10TABLE_LISTR4ListIS1_EPP4Item+0x18b)[0x7ff9509aee3b]
      /usr/sbin/mysqld(_ZN4JOIN7prepareEPPP4ItemP10TABLE_LISTjS1_jP8st_orderbS7_S1_S7_P13st_select_lexP18st_select_lex_unit+0x3c4)[0x7ff950a367f4]
      /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x924)[0x7ff950a41b54]
      /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x245)[0x7ff950a41e15]
      /usr/sbin/mysqld(+0x428331)[0x7ff9509e4331]
      /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x61b9)[0x7ff9509f0a19]
      /usr/sbin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x4b9)[0x7ff950a074e9]
      /usr/sbin/mysqld(+0x44b633)[0x7ff950a07633]
      /usr/sbin/mysqld(_Z19mysqld_stmt_executeP3THDPcj+0x17e)[0x7ff950a07b2e]
      /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xfbf)[0x7ff9509f5f2f]
      /usr/sbin/mysqld(_Z10do_commandP3THD+0x169)[0x7ff9509f7b89]
      /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x18a)[0x7ff950abb24a]
      /usr/sbin/mysqld(handle_one_connection+0x40)[0x7ff950abb420]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x8184)[0x7ff94f0ba184]
      /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7ff94e5d937d]
       
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x7ff91798b020): is an invalid pointer
      Connection ID (thread ID): 2544
      Status: NOT_KILLED
       
      Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on
      

      Thanks,
      Kevin

      Attachments

        Activity

          schmeic,

          Can you provide the query since you have already located it, and

          SHOW CREATE TABLE ... 
          SHOW INDEX IN ...
          

          or

          SHOW CREATE VIEW ...
          

          for all involved tables and views?
          If you don't want to share it publicly, you can upload the output to ftp.askmonty.org/private.
          If you can provide the database dump, it would be even better.

          Please also attach your cnf file(s).

          Regarding reproducing it locally on your machine, if you can afford experimenting, please try to execute the query via a prepared statement, and if it does not crash at the first attempt, execute it twice more. That is, do the following:

          PREPARE stmt FROM "<query in question>";
          EXECUTE stmt;
          EXECUTE stmt;
          EXECUTE stmt;
          

          elenst Elena Stepanova added a comment - schmeic , Can you provide the query since you have already located it, and SHOW CREATE TABLE ... SHOW INDEX IN ... or SHOW CREATE VIEW ... for all involved tables and views? If you don't want to share it publicly, you can upload the output to ftp.askmonty.org/private. If you can provide the database dump, it would be even better. Please also attach your cnf file(s). Regarding reproducing it locally on your machine, if you can afford experimenting, please try to execute the query via a prepared statement, and if it does not crash at the first attempt, execute it twice more. That is, do the following: PREPARE stmt FROM "<query in question>" ; EXECUTE stmt; EXECUTE stmt; EXECUTE stmt;

          schmeic, hi,
          Do you have any information for us?

          elenst Elena Stepanova added a comment - schmeic , hi, Do you have any information for us?

          Sorry I was unable to provide further info, got really busy at work. Just wanted to add that this keeps happening with different queries, but all have in common the fact that I'm joining to a view. At this point, I've decided to just change all queries that joined view to just include the view joins inline. This seems to always work. Here is a stack trace, crashing in the same place, but a bit different stack:

          stack_bottom = 0x7f576a3fd0b8 thread_stack 0x48400
          /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x55e24b6c253e]
          /usr/sbin/mysqld(handle_fatal_signal+0x2d5)[0x55e24b2112e5]
          /lib/x86_64-linux-gnu/libpthread.so.0(+0x113d0)[0x7f579cf223d0]
          /usr/sbin/mysqld(_Z17create_view_fieldP3THDP10TABLE_LISTPP4ItemPKc+0x1fe)[0x55e24b133bde]
          /usr/sbin/mysqld(Z23find_field_in_table_refP3THDP10TABLE_LISTPKcjS4_S4_S4_PP4ItembbPjbPS2+0x134)[0x55e24b042634]
          /usr/sbin/mysqld(_Z20find_field_in_tablesP3THDP10Item_identP10TABLE_LISTS4_PP4Item27find_item_error_report_typebb+0x126)[0x55
          e24b043116]
          /usr/sbin/mysqld(_ZN10Item_field10fix_fieldsEP3THDPP4Item+0x252)[0x55e24b231a92]
          /usr/sbin/mysqld(_Z12setup_fieldsP3THDPP4ItemR4ListIS1_E17enum_mark_columnsPS5_b+0x117)[0x55e24b043d57]
          /usr/sbin/mysqld(_ZN4JOIN7prepareEPPP4ItemP10TABLE_LISTjS1_jP8st_orderbS7_S1_S7_P13st_select_lexP18st_select_lex_unit+0x331)[
          0x55e24b0d01e1]
          /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_l
          ex_unitP13st_select_lex+0x8c4)[0x55e24b0de174]
          /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x139)[0x55e24b0de359]
          /usr/sbin/mysqld(+0x3fe641)[0x55e24b07b641]
          /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x6637)[0x55e24b088767]
          /usr/sbin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x4c5)[0x55e24b09f8b5]
          /usr/sbin/mysqld(+0x422a55)[0x55e24b09fa55]
          /usr/sbin/mysqld(_Z19mysqld_stmt_executeP3THDPcj+0x169)[0x55e24b09ff49]
          /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1674)[0x55e24b08d8a4]
          /usr/sbin/mysqld(_Z10do_commandP3THD+0x16d)[0x55e24b08ec8d]
          /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x182)[0x55e24b158092]
          /usr/sbin/mysqld(handle_one_connection+0x40)[0x55e24b158250]
          /usr/sbin/mysqld(handle_one_connection+0x40)[0x55e24b158250]
          /lib/x86_64-linux-gnu/libpthread.so.0(+0x76fa)[0x7f579cf186fa]
          /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f579c5c3b5d]

          Trying to get some variables.
          Some pointers may be invalid and cause the dump to abort.
          Query (0x7f5769cd2020): SELECT r.*, acv.full_name AS associate_full_name, ea.email_address, pn.phone_number FROM `reference` r INNER JOIN associate_contact_view acv USING (associate_id) LEFT JOIN email_address ea ON ea.email_address_id = r.email_address_id LEFT JOIN phone_number pn USING (phone_number_id) WHERE r.reference_id = '158' ORDER BY r.updated_at DESC LIMIT 0,300
          Connection ID (thread ID): 1170
          Status: NOT_KILLED

          Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=off

          schmeic Kevin Schmeichel added a comment - Sorry I was unable to provide further info, got really busy at work. Just wanted to add that this keeps happening with different queries, but all have in common the fact that I'm joining to a view. At this point, I've decided to just change all queries that joined view to just include the view joins inline. This seems to always work. Here is a stack trace, crashing in the same place, but a bit different stack: stack_bottom = 0x7f576a3fd0b8 thread_stack 0x48400 /usr/sbin/mysqld(my_print_stacktrace+0x2e) [0x55e24b6c253e] /usr/sbin/mysqld(handle_fatal_signal+0x2d5) [0x55e24b2112e5] /lib/x86_64-linux-gnu/libpthread.so.0(+0x113d0) [0x7f579cf223d0] /usr/sbin/mysqld(_Z17create_view_fieldP3THDP10TABLE_LISTPP4ItemPKc+0x1fe) [0x55e24b133bde] /usr/sbin/mysqld( Z23find_field_in_table_refP3THDP10TABLE_LISTPKcjS4_S4_S4_PP4ItembbPjbPS2 +0x134) [0x55e24b042634] /usr/sbin/mysqld(_Z20find_field_in_tablesP3THDP10Item_identP10TABLE_LISTS4_PP4Item27find_item_error_report_typebb+0x126)[0x55 e24b043116] /usr/sbin/mysqld(_ZN10Item_field10fix_fieldsEP3THDPP4Item+0x252) [0x55e24b231a92] /usr/sbin/mysqld(_Z12setup_fieldsP3THDPP4ItemR4ListIS1_E17enum_mark_columnsPS5_b+0x117) [0x55e24b043d57] /usr/sbin/mysqld(_ZN4JOIN7prepareEPPP4ItemP10TABLE_LISTjS1_jP8st_orderbS7_S1_S7_P13st_select_lexP18st_select_lex_unit+0x331)[ 0x55e24b0d01e1] /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_l ex_unitP13st_select_lex+0x8c4) [0x55e24b0de174] /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x139) [0x55e24b0de359] /usr/sbin/mysqld(+0x3fe641) [0x55e24b07b641] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x6637) [0x55e24b088767] /usr/sbin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x4c5) [0x55e24b09f8b5] /usr/sbin/mysqld(+0x422a55) [0x55e24b09fa55] /usr/sbin/mysqld(_Z19mysqld_stmt_executeP3THDPcj+0x169) [0x55e24b09ff49] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1674) [0x55e24b08d8a4] /usr/sbin/mysqld(_Z10do_commandP3THD+0x16d) [0x55e24b08ec8d] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x182) [0x55e24b158092] /usr/sbin/mysqld(handle_one_connection+0x40) [0x55e24b158250] /usr/sbin/mysqld(handle_one_connection+0x40) [0x55e24b158250] /lib/x86_64-linux-gnu/libpthread.so.0(+0x76fa) [0x7f579cf186fa] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7f579c5c3b5d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f5769cd2020): SELECT r.*, acv.full_name AS associate_full_name, ea.email_address, pn.phone_number FROM `reference` r INNER JOIN associate_contact_view acv USING (associate_id) LEFT JOIN email_address ea ON ea.email_address_id = r.email_address_id LEFT JOIN phone_number pn USING (phone_number_id) WHERE r.reference_id = '158' ORDER BY r.updated_at DESC LIMIT 0,300 Connection ID (thread ID): 1170 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=off

          By the way, I did try running this as prepared statement as describe above, and was unable to make it crash. It seems pretty random, but happens about once or twice a day.

          schmeic Kevin Schmeichel added a comment - By the way, I did try running this as prepared statement as describe above, and was unable to make it crash. It seems pretty random, but happens about once or twice a day.

          While the stack traces differ, they are similar in some places, thus we wondered if this might be similar/related to:

          https://jira.mariadb.org/browse/MDEV-15492

          ccalender Chris Calender (Inactive) added a comment - While the stack traces differ, they are similar in some places, thus we wondered if this might be similar/related to: https://jira.mariadb.org/browse/MDEV-15492

          The fix pushed into 5.5:
          revision-id: 75b4eb5cc969a1f5fc221d03c62987b8e57d6332 (mariadb-5.5.60-17-g75b4eb5cc96)
          parent(s): 72b6d01848e56a75349d663bc61bbe71f97a280b
          author: Oleksandr Byelkin
          committer: Oleksandr Byelkin
          timestamp: 2018-06-06 15:27:57 +0200
          message:

          Catch of OOM situation.

          sanja Oleksandr Byelkin added a comment - The fix pushed into 5.5: revision-id: 75b4eb5cc969a1f5fc221d03c62987b8e57d6332 (mariadb-5.5.60-17-g75b4eb5cc96) parent(s): 72b6d01848e56a75349d663bc61bbe71f97a280b author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-06-06 15:27:57 +0200 message: Catch of OOM situation.

          People

            sanja Oleksandr Byelkin
            schmeic Kevin Schmeichel
            Votes:
            3 Vote for this issue
            Watchers:
            8 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.