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

ANALYZE TABLES crash with table-independent-statistics gathering

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.15
    • Fix Version/s: 10.0.16
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      wheezy x86_64

      Description

      Was running mysqlanalyze --skip-write-binlog --all-databases

      Ran though a lot of tables without error.

      ....
      ...
      ..
       
      oq_dev_eventum.eventum_search_profile              Engine-independent statistics collected
      status   : OK
      oq_dev_eventum.eventum_status                      Engine-independent statistics collected
      status   : OK
      oq_dev_eventum.eventum_subscription                Engine-independent statistics collected
      status   : OK
      oq_dev_eventum.eventum_subscription_type           Engine-independent statistics collected
      status   : OK
      oq_dev_eventum.eventum_support_email               Engine-independent statistics collected
      status   : OK
      mysqlanalyze: Got error: 2013: Lost connection to MySQL server during query when executing 'ANALYZE NO_WRITE_TO_BINLOG TABLE ... '

      Settings where:

      | use_stat_tables | COMPLEMENTARY |

      Dec 23 03:23:25 catinthehat mysqld: Server version: 10.0.15-MariaDB-1~wheezy-log
      Dec 23 03:23:25 catinthehat mysqld: key_buffer_size=8388608
      Dec 23 03:23:25 catinthehat mysqld: read_buffer_size=1048576
      Dec 23 03:23:25 catinthehat mysqld: max_used_connections=4
      Dec 23 03:23:25 catinthehat mysqld: max_threads=102
      Dec 23 03:23:25 catinthehat mysqld: thread_count=4
      Dec 23 03:23:25 catinthehat mysqld: It is possible that mysqld could use up to
      Dec 23 03:23:25 catinthehat mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 140842 K  bytes of memory
      Dec 23 03:23:25 catinthehat mysqld: Hope that's ok; if not, decrease some variables in the equation.
      Dec 23 03:23:25 catinthehat mysqld: 
      Dec 23 03:23:25 catinthehat mysqld: Thread pointer: 0x0x7f53f3936008
      Dec 23 03:23:25 catinthehat mysqld: Attempting backtrace. You can use the following information to find out
      Dec 23 03:23:25 catinthehat mysqld: where mysqld died. If you see no messages after this, something went
      Dec 23 03:23:25 catinthehat mysqld: terribly wrong...
      Dec 23 03:23:25 catinthehat mysqld: stack_bottom = 0x7f5425c73e30 thread_stack 0x48000
      Dec 23 03:23:25 catinthehat mysqld: /usr/sbin/mysqld(my_print_stacktrace+0x2b)[0x7f5428926b9b]
      Dec 23 03:23:25 catinthehat mysqld: /usr/sbin/mysqld(handle_fatal_signal+0x422)[0x7f54284b6e92]
      Dec 23 03:23:25 catinthehat mysqld: /lib/x86_64-linux-gnu/libpthread.so.0(+0xf8d0)[0x7f5427b078d0]
      Dec 23 03:23:25 catinthehat mysqld: /usr/sbin/mysqld(_Z27update_statistics_for_tableP3THDP5TABLE+0x360)[0x7f54283c8850]
      Dec 23 03:23:25 catinthehat mysqld: /usr/sbin/mysqld(+0x4f693e)[0x7f542842e93e]
      Dec 23 03:23:25 catinthehat mysqld: /usr/sbin/mysqld(_ZN21Sql_cmd_analyze_table7executeEP3THD+0xd9)[0x7f5428430079]
      Dec 23 03:23:25 catinthehat mysqld: /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1968)[0x7f542835a7f8]
      Dec 23 03:23:25 catinthehat mysqld: /usr/sbin/mysqld(+0x4275ba)[0x7f542835f5ba]
      Dec 23 03:23:25 catinthehat mysqld: /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x14df)[0x7f5428360b7f]
      Dec 23 03:23:25 catinthehat mysqld: /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x47b)[0x7f542842273b]
      Dec 23 03:23:25 catinthehat mysqld: /usr/sbin/mysqld(handle_one_connection+0x47)[0x7f5428422817]
      Dec 23 03:23:25 catinthehat mysqld: /usr/sbin/mysqld(+0x6f5d5d)[0x7f542862dd5d]
      Dec 23 03:23:25 catinthehat mysqld: /lib/x86_64-linux-gnu/libpthread.so.0(+0x80a4)[0x7f5427b000a4]
      Dec 23 03:23:25 catinthehat mysqld: /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f5426178ccd]
      Dec 23 03:23:25 catinthehat mysqld: 
      Dec 23 03:23:25 catinthehat mysqld: Trying to get some variables.
      Dec 23 03:23:25 catinthehat mysqld: Some pointers may be invalid and cause the dump to abort.
      Dec 23 03:23:25 catinthehat mysqld: Query (0x7f540a4c8020): is an invalid pointer
      Dec 23 03:23:25 catinthehat mysqld: Connection ID (thread ID): 13
      Dec 23 03:23:25 catinthehat mysqld: Status: NOT_KILLED

       mysqlcheck --skip-write-binlog --all-databases 
      ..
      (all OK)

      select * from mysql.table_stats
      | oq_dev_eventum | eventum_round_robin_user          |           0 |
      | oq_dev_eventum | eventum_search_profile            |          27 |
      | oq_dev_eventum | eventum_status                    |          10 |
      | oq_dev_eventum | eventum_subscription              |        1052 |
      | oq_dev_eventum | eventum_subscription_type         |        4003 |
      | oq_dev_eventum | eventum_support_email             |        4535 |
      | oq_dev_eventum | eventum_support_email_body        |        4534 |
      | logsleads      | clflog                            |           0 |
       
       
       
      MariaDB [mysql]> show tables in oq_dev_eventum like 'eventum_%';
      ...
      | eventum_support_email                |
      | eventum_support_email_body           |
      | eventum_time_tracking                |
      | eventum_time_tracking_category       |
      | eventum_user                         |
      +--------------------------------------+
      69 rows in set (0.00 sec)

      After restart with:

      | use_stat_tables                           | NEVER  |
       
      mysqlanalyze --skip-write-binlog --all-databases 
      ...
      oq_eventum.eventum_status                          OK
      oq_eventum.eventum_subscription                    OK
      oq_eventum.eventum_subscription_type               OK
      oq_eventum.eventum_support_email                   OK
      oq_eventum.eventum_support_email_body              Table is already up to date
      oq_eventum.eventum_time_tracking                   Table is already up to date
      oq_eventum.eventum_time_tracking_category          OK
      oq_eventum.eventum_user                            OK
      oq_eventum.oq_customer_contacts                    OK
      oq_eventum.oq_customers                            OK
      oq_ourdelta.wp_QuoteRotator                        OK
      oq_ourdelta.wp_commentmeta                         OK
      oq_ourdelta.wp_comments                            OK
      oq_ourdelta.wp_links                               OK
      ...

      This is repeatable on this table:

      MariaDB [(none)]> set sql_log_bin=0;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [(none)]> analyze local table oq_dev_eventum.eventum_support_email_body;
      +-------------------------------------------+---------+----------+-----------------------------+
      | Table                                     | Op      | Msg_type | Msg_text                    |
      +-------------------------------------------+---------+----------+-----------------------------+
      | oq_dev_eventum.eventum_support_email_body | analyze | status   | Table is already up to date |
      +-------------------------------------------+---------+----------+-----------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [(none)]> set global use_stat_tables=COMPLEMENTARY;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [(none)]> analyze local table oq_dev_eventum.eventum_support_email_body;
      +-------------------------------------------+---------+----------+-----------------------------+
      | Table                                     | Op      | Msg_type | Msg_text                    |
      +-------------------------------------------+---------+----------+-----------------------------+
      | oq_dev_eventum.eventum_support_email_body | analyze | status   | Table is already up to date |
      +-------------------------------------------+---------+----------+-----------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [(none)]> analyze local table oq_dev_eventum.eventum_support_email_body PERSISTENT FOR ALL;
      ERROR 2013 (HY000): Lost connection to MySQL server during query

      | eventum_support_email_body | CREATE TABLE `eventum_support_email_body` (
        `seb_sup_id` int(11) unsigned NOT NULL,
        `seb_body` longtext NOT NULL,
        `seb_full_email` longtext NOT NULL,
        PRIMARY KEY (`seb_sup_id`),
        FULLTEXT KEY `ft_support_email` (`seb_body`)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
       
      MariaDB [(none)]> select count(*) from  oq_dev_eventum.eventum_support_email_body;
      +----------+
      | count(*) |
      +----------+
      |     4534 |
       
       
      MariaDB [(none)]> select length(seb_body) as b from oq_dev_eventum.eventum_support_email_body order by b desc limit 1;
      +-------+
      | b     |
      +-------+
      | 44175 |
       
      MariaDB [(none)]> select length(seb_full_email) as b from oq_dev_eventum.eventum_support_email_body order by b desc limit 1;
      +---------+
      | b       |
      +---------+
      | 8937583 |

      Running with use_stat_tables=NEVER didn't cause a crash.

      If you can repeat this without a copy of oq_dev_eventum.eventum_support_email_body that would be good. If you can't I'll see what I can do.

        Attachments

        1. bt.txt
          7 kB
        2. bt-full.txt
          23 kB
        3. bt-second.txt
          12 kB
        4. catinthehat.cnf
          0.5 kB
        5. mdev-7362.sql
          13 kB
        6. my.cnf
          5 kB

          Issue Links

            Activity

              People

              Assignee:
              cvicentiu Vicențiu Ciorbaru
              Reporter:
              danblack Daniel Black
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration