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

use_stat_table + histograms crashing optimizer

    XMLWordPrintable

    Details

      Description

      use_stat_tables='preferably'
      optimizer_use_condition_selectivity=4
      histogram_size=255

      Thread pointer: 0x0x7f187cf92008
      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 = 0x7f1cb5654d10 thread_stack 0x48000
      /usr/sbin/mysqld(my_print_stacktrace+0x2b)[0xb7054b]
      /usr/sbin/mysqld(handle_fatal_signal+0x398)[0x727588]
      /lib64/libpthread.so.0(+0xf710)[0x7f1cb531a710]
      /usr/sbin/mysqld(_ZN9Field_bit5storeEPKcjPK15charset_info_st+0x10f)[0x713caf]
      /usr/sbin/mysqld(_Z28get_column_range_cardinalityP5FieldP12st_key_rangeS2_j+0x135)[0x63f605]
      /usr/sbin/mysqld(_Z36calculate_cond_selectivity_for_tableP3THDP5TABLEP4Item+0x836)[0x80bd76]
      /usr/sbin/mysqld[0x528cce]
      /usr/sbin/mysqld(_ZN4JOIN14optimize_innerEv+0x6de)[0x61c8de]
      /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xd8)[0x61efb8]
      /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x28d)[0x62284d]
      /usr/sbin/mysqld[0x5ca456]
      /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x4c4f)[0x5d51af]
      /usr/sbin/mysqld[0x5d6cc2]
      /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1b20)[0x5d8e80]
      /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x453)[0x696993]
      /usr/sbin/mysqld(handle_one_connection+0x42)[0x696a62]
      /lib64/libpthread.so.0(+0x79d1)[0x7f1cb53129d1]
      /lib64/libc.so.6(clone+0x6d)[0x7f1cb3a2fb5d]

      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x7f1859c20020): SELECT id, reportName, scheduleName, reportId, reportScheduleId, reportType, status, reportFormat, generationStartTime, executionTime, fileUrl, fileSize, retainUntil, users, userGroups, recurrenceType, recurrenceIntervalType, recurrenceIntervalValue, recurrenceParam1, recurrenceParam2, recurrenceParam3, reportSource, userId, isGloballyViewable, retainLast FROM report_schedule_logs WHERE ((`retainUntil` < 20140912114319) AND (`retainLast` != true))
      Connection ID (thread ID): 4
      Status: NOT_KILLED

       CREATE TABLE `report_schedule_logs` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `reportName` varchar(128) DEFAULT NULL,
        `scheduleName` varchar(128) DEFAULT NULL,
        `reportId` varchar(255) DEFAULT NULL,
        `reportScheduleId` varchar(255) DEFAULT NULL,
        `reportType` int(10) unsigned DEFAULT NULL,
        `reportFormat` tinyint(3) unsigned DEFAULT NULL,
        `generationStartTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        `executionTime` varchar(128) DEFAULT NULL,
        `reportSource` tinyint(3) unsigned DEFAULT NULL,
        `status` tinyint(3) unsigned DEFAULT NULL,
        `fileUrl` varchar(1024) DEFAULT NULL,
        `fileSize` bigint(20) DEFAULT NULL,
        `retainUntil` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
        `retainLast` bit(1) DEFAULT b'0',
        `users` longblob,
        `userGroups` longblob,
        `recurrenceType` tinyint(3) unsigned DEFAULT NULL,
        `recurrenceIntervalType` tinyint(3) unsigned DEFAULT NULL,
        `recurrenceIntervalValue` int(11) DEFAULT NULL,
        `recurrenceParam1` varchar(128) DEFAULT NULL,
        `recurrenceParam2` varchar(128) DEFAULT NULL,
        `recurrenceParam3` varchar(128) DEFAULT NULL,
        `userId` varchar(128) DEFAULT NULL,
        `isGloballyViewable` bit(1) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `report_schedule_logs_idx1` (`status`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8

      explain  SELECT id, reportName, scheduleName, reportId, reportScheduleId, reportType, status, reportFormat, generationStartTime, executionTime, fileUrl, fileSize, retainUntil, users, userGroups, recurrenceType, recurrenceIntervalType, recurrenceIntervalValue, recurrenceParam1, recurrenceParam2, recurrenceParam3, reportSource, userId, isGloballyViewable, retainLast FROM report_schedule_logs WHERE ((`retainUntil` < 20140912114319) AND (`retainLast` != true));

      changing the configuration to default instanlty solve the issue .

       show variables like 'optimizer_use_condition_selectivity';
      +-------------------------------------+-------+
      | Variable_name                       | Value |
      +-------------------------------------+-------+
      | optimizer_use_condition_selectivity | 4     |
      +-------------------------------------+-------+
      1 row in set (0.00 sec)
       
      MariaDB [sa]> explain  SELECT id, reportName, scheduleName, reportId, reportScheduleId, reportType, status, reportFormat, generationStartTime, executionTime, fileUrl, fileSize, retainUntil, users, userGroups, recurrenceType, recurrenceIntervalType, recurrenceIntervalValue, recurrenceParam1, recurrenceParam2, recurrenceParam3, reportSource, userId, isGloballyViewable, retainLast FROM report_schedule_logs WHERE ((`retainUntil` < 20140912114319) AND (`retainLast` != true));
      ERROR 2013 (HY000): Lost connection to MySQL server during query

      MariaDB [sa]> show variables like 'optimizer_use_condition_selectivity';ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...
      Connection id:    6
      Current database: sa
       
      +-------------------------------------+-------+
      | Variable_name                       | Value |
      +-------------------------------------+-------+
      | optimizer_use_condition_selectivity | 1     |
      +-------------------------------------+-------+
      1 row in set (0.46 sec)
       
      MariaDB [sa]> explain  SELECT id, reportName, scheduleName, reportId, reportScheduleId, reportType, status, reportFormat, generationStartTime, executionTime, fileUrl, fileSize, retainUntil, users, userGroups, recurrenceType, recurrenceIntervalType, recurrenceIntervalValue, recurrenceParam1, recurrenceParam2, recurrenceParam3, reportSource, userId, isGloballyViewable, retainLast FROM report_schedule_logs WHERE ((`retainUntil` < 20140912114319) AND (`retainLast` != true));
      +------+-------------+----------------------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table                | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+----------------------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | report_schedule_logs | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
      +------+-------------+----------------------+------+---------------+------+---------+------+------+-------------+

      The context is that our client under support benefit from histograms on some query plan. We activated it but after a process of reseting the data . it started to crash on this query run every 5 minutes. not that after the reset we did no run analyze table but the table was readable and and was empty.

      the content of the stats tables because are empty ! due to restart

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              stephane@skysql.com VAROQUI Stephane
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration