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

Database crashes in the query to the View

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.14, 5.5, 10.0, 10.1, 10.2
    • Fix Version/s: 5.5.54
    • Component/s: Optimizer, Views
    • Labels:
      None
    • Environment:
      CentOS 7
    • Sprint:
      5.5.50

      Description

      Hi- konnichiwa

      Database server to crash by the second time SQL execution.

      CREATE TABLE `table_A` (
      	`key_code` INT(11) NOT NULL,
      	`value_string` VARCHAR(50) NULL DEFAULT NULL,
      	PRIMARY KEY (`key_code`)
      )
      COLLATE='utf8_general_ci'
      ENGINE=InnoDB
      ;
       
      CREATE TABLE `table_B` (
      	`key_code` INT(11) NOT NULL,
      	`target_date` DATE NULL DEFAULT NULL,
      	PRIMARY KEY (`key_code`)
      )
      COLLATE='utf8_general_ci'
      ENGINE=InnoDB
      ;
       
      CREATE TABLE `table_C` (
      	`now_date` DATE NOT NULL,
      	PRIMARY KEY (`now_date`)
      )
      COLLATE='utf8_general_ci'
      ENGINE=InnoDB
      ;
       
      CREATE VIEW view_B
      AS
      SELECT
      	B.key_code,
      	B.target_date
      FROM
      	table_B B INNER JOIN table_C C ON
      	B.target_date = C.now_date
      ;
      

      After creating an environment as described above , and run the next certain SQL statements.

      SET @s = 'SELECT A.* FROM table_A A WHERE A.key_code IN (SELECT key_code FROM view_B)';
      PREPARE stmt FROM @s;
      EXECUTE stmt;                                   #1st time -> success
      EXECUTE stmt;                                   #2nd time -> crash
      DEALLOCATE PREPARE stmt;
      

      a) This SQL is Prepare statement
      b) Structure of view_B
      c) view_B is written in the In statement

      Three of the elements of the above , I think to be related to this problem.

      *** Error Log ***

      May 29 10:40:37 ryokin mysqld: 160529 10:40:37 [ERROR] mysqld got signal 11 ;
      May 29 10:40:37 ryokin mysqld: This could be because you hit a bug. It is also possible that this binary
      May 29 10:40:37 ryokin mysqld: or one of the libraries it was linked against is corrupt, improperly built,
      May 29 10:40:37 ryokin mysqld: or misconfigured. This error can also be caused by malfunctioning hardware.
      May 29 10:40:37 ryokin mysqld: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
      May 29 10:40:37 ryokin mysqld: We will try our best to scrape up some info that will hopefully help
      May 29 10:40:37 ryokin mysqld: diagnose the problem, but since we have already crashed,
      May 29 10:40:37 ryokin mysqld: something is definitely wrong and this may fail.
      May 29 10:40:37 ryokin mysqld: Server version: 10.1.14-MariaDB
      May 29 10:40:37 ryokin mysqld: key_buffer_size=134217728
      May 29 10:40:37 ryokin mysqld: read_buffer_size=131072
      May 29 10:40:37 ryokin mysqld: max_used_connections=1
      May 29 10:40:37 ryokin mysqld: max_threads=153
      May 29 10:40:37 ryokin mysqld: thread_count=1
      May 29 10:40:37 ryokin mysqld: It is possible that mysqld could use up to
      May 29 10:40:37 ryokin mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467113 K  bytes of memory
      May 29 10:40:37 ryokin mysqld: Hope that's ok; if not, decrease some variables in the equation.
      May 29 10:40:37 ryokin mysqld: Thread pointer: 0x0x7f3d61fc2008
      May 29 10:40:37 ryokin mysqld: Attempting backtrace. You can use the following information to find out
      May 29 10:40:37 ryokin mysqld: where mysqld died. If you see no messages after this, something went
      May 29 10:40:37 ryokin mysqld: terribly wrong...
      May 29 10:40:37 ryokin mysqld: stack_bottom = 0x7f3d66bac130 thread_stack 0x48400
      May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x7f3d676bf1be]
      May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(handle_fatal_signal+0x38d)[0x7f3d671eaf0d]
      May 29 10:40:37 ryokin mysqld: /lib64/libpthread.so.0(+0xf100)[0x7f3d66809100]
      May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_ZN13st_select_lex17mark_as_dependentEP3THDPS_P4Item+0xbd)[0x7f3d6705d5fd]
      May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_ZN10Item_field15fix_outer_fieldEP3THDPP5FieldPP4Item+0x83d)[0x7f3d67209ead]
      May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_ZN10Item_field10fix_fieldsEP3THDPP4Item+0x41f)[0x7f3d6720a7ef]
      May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_Z12setup_fieldsP3THDPP4ItemR4ListIS1_E17enum_mark_columnsPS5_b+0x17c)[0x7f3d6702b68c]
      May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_ZN4JOIN7prepareEPPP4ItemP10TABLE_LISTjS1_jP8st_orderbS7_S1_S7_P13st_select_lexP18st_select_lex_unit+0x312)[0x7f3d670b4b12]
      May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_ZN18st_select_lex_unit7prepareEP3THDP13select_resultm+0x953)[0x7f3d67101ca3]
      May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_Z21mysql_derived_prepareP3THDP3LEXP10TABLE_LIST+0x1f5)[0x7f3d67047f85]
      May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0xe4)[0x7f3d67048e34]
      May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_ZN13st_select_lex14handle_derivedEP3LEXj+0x47)[0x7f3d6705fc37]
      May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_ZN4JOIN7prepareEPPP4ItemP10TABLE_LISTjS1_jP8st_orderbS7_S1_S7_P13st_select_lexP18st_select_lex_unit+0xd3)[0x7f3d670b48d3]
      May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x924)[0x7f3d670bff44]
      May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x245)[0x7f3d670c0205]
      May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(+0x428c91)[0x7f3d67062c91]
      May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x5f8f)[0x7f3d6706e89f]
      May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x4b6)[0x7f3d67085816]
      May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(+0x44b983)[0x7f3d67085983]
      May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z22mysql_sql_stmt_executeP3THD+0xbb)[0x7f3d67085f9b]
      May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x17ad)[0x7f3d6706a0bd]
      May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x28e)[0x7f3d6707217e]
      May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x25db)[0x7f3d6707563b]
      May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z10do_commandP3THD+0x169)[0x7f3d67075eb9]
      May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x18a)[0x7f3d6713962a]
      May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(handle_one_connection+0x40)[0x7f3d67139800]
      May 29 10:40:38 ryokin mysqld: /lib64/libpthread.so.0(+0x7dc5)[0x7f3d66801dc5]
      May 29 10:40:38 ryokin mysqld: /lib64/libc.so.6(clone+0x6d)[0x7f3d64c2428d]
      May 29 10:40:38 ryokin mysqld: Trying to get some variables.
      May 29 10:40:38 ryokin mysqld: Some pointers may be invalid and cause the dump to abort.
      May 29 10:40:38 ryokin mysqld: Query (0x7f3d46f6d0e8): SELECT A.* FROM table_A A WHERE A.key_code IN (SELECT key_code FROM view_B)
      May 29 10:40:38 ryokin mysqld: Connection ID (thread ID): 2
      May 29 10:40:38 ryokin mysqld: Status: NOT_KILLED
      May 29 10:40:38 ryokin mysqld: 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
      May 29 10:40:38 ryokin mysqld: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
      May 29 10:40:38 ryokin mysqld: information that should help you find out what is causing the crash.
      May 29 10:40:38 ryokin systemd: mariadb.service: main process exited, code=exited, status=1/FAILURE
      May 29 10:40:38 ryokin systemd: Unit mariadb.service entered failed state.
      May 29 10:40:38 ryokin systemd: mariadb.service failed.
      

      Thanks K.S

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                account@zam.att.ne.jp Ken Sugawara
              • Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: