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

histogram crash server wen analyzing table (after upgrade from 10.0.1)

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.0
    • Fix Version/s: 10.0
    • Component/s: Scripts & Clients
    • Labels:
      None
    • Environment:
      linux

      Description

      Hi guys, i got a crash, could someone help? (i updated from mariadb 10.0.15 to 10.0.20 and executed the mysql_upgrade tool)

      queries:

      CREATE TABLE t (i INT);
      set histogram_size=255,histogram_type=SINGLE_PREC_HB;
      ANALYZE TABLE t PERSISTENT FOR ALL;
      /* Erro SQL (2013): Lost connection to MySQL server during query */
      

      log:

      150623 12:31:47 [ERROR] mysqld got signal 11 ;
      This could be because you hit a bug. It is also possible that this binary
      or one of the libraries it was linked against is corrupt, improperly built,
      or misconfigured. This error can also be caused by malfunctioning hardware.
      
      To report this bug, see http://kb.askmonty.org/en/reporting-bugs
      
      We will try our best to scrape up some info that will hopefully help
      diagnose the problem, but since we have already crashed,
      something is definitely wrong and this may fail.
      
      Server version: 10.0.20-MariaDB-log
      key_buffer_size=2097152000
      read_buffer_size=6291456
      max_used_connections=22
      max_threads=92
      thread_count=9
      It is possible that mysqld could use up to
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads =
      3180295 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
      
      Thread pointer: 0x0x7f523b81c008
      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 = 0x4b1410a0 thread_stack 0x48000
      mysys/stacktrace.c:247(my_print_stacktrace)[0xbcb2ee]
      sql/signal_handler.cc:153(handle_fatal_signal)[0x71fa8c]
      /lib/libpthread.so.0[0x7f52ccbe98f0]
      sql/field.h:703(Field::set_notnull(long long))[0x610c70]
      sql/sql_statistics.cc:533(Stat_table::update_stat())[0x60dd68]
      sql/sql_admin.cc:759(mysql_admin_table)[0x67adc1]
      sql/sql_admin.cc:1183(Sql_cmd_analyze_table::execute(THD*))[0x67c66c]
      sql/sql_parse.cc:5095(mysql_execute_command(THD*))[0x5986c2]
      sql/sql_parse.cc:6529(mysql_parse(THD*, char*, unsigned int,
      Parser_state*))[0x59fc86]
      sql/sql_parse.cc:1310(dispatch_command(enum_server_command, THD*,
      char*, unsigned int))[0x5a1bb7]
      sql/sql_parse.cc:999(do_command(THD*))[0x5a22f9]
      sql/sql_connect.cc:1378(do_handle_one_connection(THD*))[0x66c7d4]
      sql/sql_connect.cc:1295(handle_one_connection)[0x66c912]
      /lib/libpthread.so.0[0x7f52ccbe1fe7]
      /lib/libc.so.6(clone+0x6d)[0x7f52cbb822bd]
      
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x7f5190884020): is an invalid pointer
      Connection ID (thread ID): 304
      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
      
      The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
      information that should help you find out what is causing the crash.
      

      my.cnf:

      [mysqld]
      skip-name-resolve
      open_files_limit =4096
      default-storage-engine=MYISAM
      # it's mariadb 10.0.20, /opt/mariadb directory = tar -zxf mariadb
      ledir=/opt/mariadb/bin/
      tmpdir=/tmp/mysql
      mysqld                  = /opt/mariadb/bin/mysqld_safe
      socket                  = /tmp/mysql.sock
      port                    = 3307
      pid-file                = /home/mysql/pid/local.pid
      datadir                 = /home/mysql/data/local/
      language                = /opt/mariadb/share/portuguese
      slow_query_log          = 1
      slow_query_log_file     = /home/mysql/log/local.slow_query.log
      log-error               = /home/mysql/log/local.error.log
      #log-output             = FILE
      innodb_use_native_aio=0
      
      long_query_time         = 2
      max_connections         = 90
      key_buffer              = 2000M
      aria_pagecache_buffer_size = 2500M
      table_cache             = 350
      #table_definition_cache = 350
      sort_buffer_size        = 6M
      read_buffer_size        = 6M
      read_rnd_buffer_size    = 16M
      myisam_sort_buffer_size = 128M
      tmp_table_size          = 8M
      max_allowed_packet      = 16M
      net_buffer_length       = 16K
      wait_timeout            = 10
      sql_mode                = ALLOW_INVALID_DATES,PIPES_AS_CONCAT
      concurrent_insert       = 2
      div_precision_increment = 8
      query_cache_size        = 128M
      query_cache_limit       = 8M
      query_cache_type        = 1
      query_cache_min_res_unit= 256
      thread_cache_size       = 20
      myisam-recover          = BACKUP,FORCE,QUICK
      


      more info about mysql database:

      Tables_in_mysql (%stat%)
      column_stats
      index_stats
      innodb_index_stats
      innodb_table_stats
      table_stats
      

      (maybe i'm with a old table? i updated from 10.0.15 to 10.0.20 and used mysql_upgrade tool without errors)

      CREATE TABLE `column_stats` (
        `db_name` varchar(64) COLLATE utf8_bin NOT NULL,
        `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
        `column_name` varchar(64) COLLATE utf8_bin NOT NULL,
        `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL,
        `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL,
        `nulls_ratio` decimal(12,4) DEFAULT NULL,
        `avg_length` decimal(12,4) DEFAULT NULL,
        `avg_frequency` decimal(12,4) DEFAULT NULL,
        PRIMARY KEY (`db_name`,`table_name`,`column_name`)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Columns';
      CREATE TABLE `index_stats` (
        `db_name` varchar(64) COLLATE utf8_bin NOT NULL,
        `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
        `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
        `prefix_arity` int(11) unsigned NOT NULL,
        `avg_frequency` decimal(12,4) DEFAULT NULL,
        PRIMARY KEY (`db_name`,`table_name`,`index_name`,`prefix_arity`)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Indexes';
      CREATE TABLE `table_stats` (
        `db_name` varchar(64) COLLATE utf8_bin NOT NULL,
        `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
        `cardinality` bigint(21) unsigned DEFAULT NULL,
        PRIMARY KEY (`db_name`,`table_name`)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Tables';
      

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            elenst Elena Stepanova added a comment -

            Roberto,

            You know the drill, it's pretty standard.

            • Is it reproducible?
            • Can you upload the data dump?
            • Please attach your cnf file(s).
            Show
            elenst Elena Stepanova added a comment - Roberto, You know the drill, it's pretty standard. Is it reproducible? Can you upload the data dump? Please attach your cnf file(s).
            Hide
            rspadim roberto spadim added a comment - - edited

            hi elena!
            Roberto,
            You know the drill, it's pretty standard.
            Is it reproducible? > YES, even with a new empty table it occur
            Can you upload the data dump? > it occurs with CREATE TABLE ... ANALYZE ... crash
            Please attach your cnf file(s). > included at mdev body

            Show
            rspadim roberto spadim added a comment - - edited hi elena! Roberto, You know the drill, it's pretty standard. Is it reproducible? > YES, even with a new empty table it occur Can you upload the data dump? > it occurs with CREATE TABLE ... ANALYZE ... crash Please attach your cnf file(s). > included at mdev body
            Hide
            rspadim roberto spadim added a comment -

            server have 16gb, i think it's not a hardware memory problem (it's a ecc memory works nice for more than 1 year) or out of memory, hard disk are ok (smatctl don't report error, dmesg don't report too), linux is ok (running withou shutdown for more than 180days without problem)

            Show
            rspadim roberto spadim added a comment - server have 16gb, i think it's not a hardware memory problem (it's a ecc memory works nice for more than 1 year) or out of memory, hard disk are ok (smatctl don't report error, dmesg don't report too), linux is ok (running withou shutdown for more than 180days without problem)
            Hide
            elenst Elena Stepanova added a comment -

            these are very old tables, like 10.0.1 version old. Here is how they look now

            MariaDB [test]> show create table mysql.column_stats \G
            *************************** 1. row ***************************
                   Table: column_stats
            Create Table: CREATE TABLE `column_stats` (
              `db_name` varchar(64) COLLATE utf8_bin NOT NULL,
              `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
              `column_name` varchar(64) COLLATE utf8_bin NOT NULL,
              `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL,
              `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL,
              `nulls_ratio` decimal(12,4) DEFAULT NULL,
              `avg_length` decimal(12,4) DEFAULT NULL,
              `avg_frequency` decimal(12,4) DEFAULT NULL,
              `hist_size` tinyint(3) unsigned DEFAULT NULL,
              `hist_type` enum('SINGLE_PREC_HB','DOUBLE_PREC_HB') COLLATE utf8_bin DEFAULT NULL,
              `histogram` varbinary(255) DEFAULT NULL,
              PRIMARY KEY (`db_name`,`table_name`,`column_name`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Columns'
            1 row in set (0.00 sec)
            

            Most likely the upgrade does not bother fixing the hist* columns conditionally, because they were added while it was still alpha.

            Show
            elenst Elena Stepanova added a comment - these are very old tables, like 10.0.1 version old. Here is how they look now MariaDB [test]> show create table mysql.column_stats \G *************************** 1. row *************************** Table: column_stats Create Table: CREATE TABLE `column_stats` ( `db_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `column_name` varchar(64) COLLATE utf8_bin NOT NULL, `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, `nulls_ratio` decimal(12,4) DEFAULT NULL, `avg_length` decimal(12,4) DEFAULT NULL, `avg_frequency` decimal(12,4) DEFAULT NULL, `hist_size` tinyint(3) unsigned DEFAULT NULL, `hist_type` enum('SINGLE_PREC_HB','DOUBLE_PREC_HB') COLLATE utf8_bin DEFAULT NULL, `histogram` varbinary(255) DEFAULT NULL, PRIMARY KEY (`db_name`,`table_name`,`column_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Columns' 1 row in set (0.00 sec) Most likely the upgrade does not bother fixing the hist* columns conditionally, because they were added while it was still alpha.
            Hide
            rspadim roberto spadim added a comment -

            ok i will try to recrete tables and check what happen

            could the server check if table is ok while starting? a crash isn't something nice to have without a warning

            Show
            rspadim roberto spadim added a comment - ok i will try to recrete tables and check what happen could the server check if table is ok while starting? a crash isn't something nice to have without a warning
            Hide
            rspadim roberto spadim added a comment -

            problem solved with new table format

            Show
            rspadim roberto spadim added a comment - problem solved with new table format
            Hide
            elenst Elena Stepanova added a comment -

            could the server check if table is ok while starting? a crash isn't something nice to have without a warning

            On one hand, it's an easy fix; on the other hand, I don't quite like the idea of polluting the upgrade script with logic which only applies to upgrade from an early alpha – nobody should expect the structures there to be final anyway.
            I don't have a strong opinion on this, will assign it to Sergei Golubchik for a final decision.

            Show
            elenst Elena Stepanova added a comment - could the server check if table is ok while starting? a crash isn't something nice to have without a warning On one hand, it's an easy fix; on the other hand, I don't quite like the idea of polluting the upgrade script with logic which only applies to upgrade from an early alpha – nobody should expect the structures there to be final anyway. I don't have a strong opinion on this, will assign it to Sergei Golubchik for a final decision.
            Hide
            rspadim roberto spadim added a comment -

            ok, but i was thinking about check table strutucat at mysqld start, not mysql_upgrade, or when mysqld set histogram to any value != 'never' or before executing the analyze, just to avoid crashs

            Show
            rspadim roberto spadim added a comment - ok, but i was thinking about check table strutucat at mysqld start, not mysql_upgrade, or when mysqld set histogram to any value != 'never' or before executing the analyze, just to avoid crashs

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                rspadim roberto spadim
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: