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

CREATE FULLTEXT INDEX with a token longer than 127 bytes crashes server

    XMLWordPrintable

Details

    • 10.1.20

    Description

      Adding fulltext index on bigger table crash server

      2016-11-04 07:41:26 7f7149bfe700  InnoDB: Assertion failure in thread 140124545345280 in file row0merge.cc line 890
      InnoDB: Failing assertion: b == &block[0] + buf->total_size + ROW_MERGE_RESERVE_SIZE
      InnoDB: We intentionally generate a memory trap.
      InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
      InnoDB: If you get repeated assertion failures or crashes, even
      InnoDB: immediately after the mysqld startup, there may be
      InnoDB: corruption in the InnoDB tablespace. Please refer to
      InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
      InnoDB: about forcing recovery.
      161104  7:41:26 [ERROR] mysqld got signal 6 ;
      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 https://mariadb.com/kb/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.1.17-MariaDB
      key_buffer_size=134217728
      read_buffer_size=131072
      max_used_connections=54
      max_threads=902
      thread_count=8
      It is possible that mysqld could use up to 
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2112329 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
       
      Thread pointer: 0x0x0
      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 = 0x0 thread_stack 0x48400
      2016-11-04 07:41:27 7f714a3ff700  InnoDB: Assertion failure in thread 140124553737984 in file row0merge.cc line 890
      InnoDB: Failing assertion: b == &block[0] + buf->total_size + ROW_MERGE_RESERVE_SIZE
      InnoDB: We intentionally generate a memory trap.
      InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
      InnoDB: If you get repeated assertion failures or crashes, even
      InnoDB: immediately after the mysqld startup, there may be
      InnoDB: corruption in the InnoDB tablespace. Please refer to
      InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
      InnoDB: about forcing recovery.
      161104 07:41:29 mysqld_safe Number of processes running now: 0
      161104 07:41:29 mysqld_safe mysqld restarted
      2016-11-04  7:41:29 140601780914208 [Note] /usr/sbin/mysqld (mysqld 10.1.17-MariaDB) starting as process 25262 ...
      2016-11-04  7:41:29 140601780914208 [Warning] Although a path was specified for the --log-slow-queries option, log tables are used. To enable logging to files use the --log-output=file option.
      2016-11-04  7:41:29 140601780914208 [Warning] option 'innodb-ft-cache-size': unsigned value 83886080 adjusted to 80000000
      2016-11-04  7:41:29 140601780914208 [Note] InnoDB: Using mutexes to ref count buffer pool pages
      2016-11-04  7:41:29 140601780914208 [Note] InnoDB: The InnoDB memory heap is disabled
      2016-11-04  7:41:29 140601780914208 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
      2016-11-04  7:41:29 140601780914208 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
      2016-11-04  7:41:29 140601780914208 [Note] InnoDB: Compressed tables use zlib 1.2.3
      2016-11-04  7:41:29 140601780914208 [Note] InnoDB: Using Linux native AIO
      2016-11-04  7:41:29 140601780914208 [Note] InnoDB: Using SSE crc32 instructions
      2016-11-04  7:41:29 140601780914208 [Note] InnoDB: Initializing buffer pool, size = 14.0G
      2016-11-04  7:41:30 140601780914208 [Note] InnoDB: Completed initialization of buffer pool
      2016-11-04  7:41:30 140601780914208 [Note] InnoDB: Highest supported file format is Barracuda.
      2016-11-04  7:41:30 140601780914208 [Note] InnoDB: Log scan progressed past the checkpoint lsn 5089194166241
      2016-11-04  7:41:30 140601780914208 [Note] InnoDB: Database was not shutdown normally!
      2016-11-04  7:41:30 140601780914208 [Note] InnoDB: Starting crash recovery.
      2016-11-04  7:41:30 140601780914208 [Note] InnoDB: Reading tablespace information from the .ibd files...
      2016-11-04  7:41:46 140601780914208 [Note] InnoDB: Processed 81433 .ibd/.isl files
      2016-11-04  7:42:02 140601780914208 [Note] InnoDB: Processed 165583 .ibd/.isl files
      2016-11-04  7:42:18 140601780914208 [Note] InnoDB: Processed 248732 .ibd/.isl files
      2016-11-04  7:42:34 140601780914208 [Note] InnoDB: Processed 331595 .ibd/.isl files
      2016-11-04  7:42:50 140601780914208 [Note] InnoDB: Processed 410190 .ibd/.isl files
      2016-11-04  7:43:06 140601780914208 [Note] InnoDB: Processed 490369 .ibd/.isl files
      2016-11-04  7:43:22 140601780914208 [Note] InnoDB: Processed 571032 .ibd/.isl files
      2016-11-04  7:43:38 140601780914208 [Note] InnoDB: Processed 653103 .ibd/.isl files
      2016-11-04  7:43:54 140601780914208 [Note] InnoDB: Processed 733414 .ibd/.isl files
      2016-11-04  7:44:10 140601780914208 [Note] InnoDB: Processed 811624 .ibd/.isl files
      2016-11-04  7:44:26 140601780914208 [Note] InnoDB: Processed 891341 .ibd/.isl files
      2016-11-04  7:44:42 140601780914208 [Note] InnoDB: Processed 969320 .ibd/.isl files
      2016-11-04  7:44:58 140601780914208 [Note] InnoDB: Processed 1047838 .ibd/.isl files
      2016-11-04  7:45:14 140601780914208 [Note] InnoDB: Processed 1123078 .ibd/.isl files
      2016-11-04  7:45:25 140601780914208 [Note] InnoDB: Restoring possible half-written data pages 
      2016-11-04  7:45:25 140601780914208 [Note] InnoDB: from the doublewrite buffer...
      InnoDB: Doing recovery: scanned up to log sequence number 5089199408640
      InnoDB: Doing recovery: scanned up to log sequence number 5089204651520
      InnoDB: Doing recovery: scanned up to log sequence number 5089209894400
      InnoDB: Doing recovery: scanned up to log sequence number 5089215137280
      InnoDB: Doing recovery: scanned up to log sequence number 5089220380160
      InnoDB: Doing recovery: scanned up to log sequence number 5089225623040
      InnoDB: Doing recovery: scanned up to log sequence number 5089230865920
      .... etc
      

      Problem was started by SQL:
      ALTER TABLE `qu_la_contacts` ADD FULLTEXT `qu_la_contacts_ftidx` (`firstname` ,`lastname` ,`emails` ,`system_name`)

      on table, which has about 200k rows

      CREATE TABLE IF NOT EXISTS `qu_la_contacts` (
        `contactid` CHAR(8) NOT NULL,
        `parent_contactid` CHAR(8) NULL,
        `company` CHAR(1) NOT NULL DEFAULT 'N',
        `job_position` VARCHAR(255) NULL,
        `emails` TEXT NULL,
        `firstname` VARCHAR(100) NULL,
        `lastname` VARCHAR(100) NULL,
        `system_name` VARCHAR(100) NULL,
        `description` VARCHAR(255) NULL,
        `rtype` CHAR(1) NOT NULL,
        `status` CHAR(1) NOT NULL DEFAULT 'A' COMMENT 'A=Active, X=Deleted',
        `datecreated` DATETIME NULL,
        `avatar_url` TEXT NULL,
        `city` VARCHAR(255) NULL,
        `countrycode` VARCHAR(2) NULL,
        `note` TEXT NULL,
        `time_offset` INT NULL,
        `language` VARCHAR(10) NULL,
        `gender` CHAR(1) NOT NULL DEFAULT 'M',
        `levelid` CHAR(8) NULL,
        `ldap_id` VARCHAR(255) NULL,
        `groups` VARCHAR(255) NULL,
        `ip` VARCHAR(39) NULL,
        `useragent` TEXT NULL,
        `screen` VARCHAR(12) NULL,
        `latitude` FLOAT NULL,
        `longitude` FLOAT NULL,
        `last_action_type` CHAR(1) NULL,
        `last_action_date` DATETIME NULL,
        `last_action_meta` VARCHAR(80) NULL,
        PRIMARY KEY (`contactid`),
        INDEX `fk_qu_la_contacts_qu_la_levels1_idx` (`levelid` ASC),
        INDEX `fk_parent_contactid` (`parent_contactid` ASC),
        INDEX `qu_la_contacts_datecreated` (`datecreated` ASC),
        INDEX `qu_la_contacts_firstname` (`firstname` ASC),
        INDEX `qu_la_contacts_lastname` (`lastname` ASC),
        INDEX `qu_la_contacts_system_name` (`system_name` ASC),
        INDEX `qu_la_contacts_company` (`company`)
        )
      ENGINE = InnoDB;
      

      Server settings:

      [mysqld]
      server-id=14
      datadir=/opt/mysql/
      socket=/var/lib/mysql/mysql.sock
      tmpdir=/tmp/
       
      log_bin=mysql-bin
      expire_logs_days=6
       
      wait_timeout=60
       
      long_query_time=1
      slow_query_log=1
      slow_query_log_file=/opt/log/slow.log
      log_output=TABLE
       
      userstat = 1
       
      user=root
      symbolic-links=0
      binlog_format=STATEMENT
       
      default_storage_engine=InnoDB
       
      slave_skip_errors=1062,1396,1690
       
      innodb_autoinc_lock_mode=2
       
      innodb_buffer_pool_size=14G
      innodb_buffer_pool_instances=10
      innodb_log_file_size=1G
      innodb_log_buffer_size=196M
      innodb_flush_log_at_trx_commit=1
      innodb_thread_concurrency=24
      innodb_file_per_table
      innodb_write_io_threads=24
      innodb_read_io_threads=24
      innodb_sched_priority_cleaner=39
      innodb_adaptive_flushing=1
      innodb_purge_threads=5
      #transaction-isolation=READ-COMMITTED
      innodb_adaptive_hash_index_partitions=64
      innodb_flush_neighbors=0
      innodb_flush_method=O_DIRECT
      innodb_io_capacity=5000
      innodb_io_capacity_max=8000
      innodb_lru_scan_depth=1024
      innodb_sort_buffer_size=32M
      innodb_ft_cache_size=80M
      innodb_ft_total_cache_size=1G
       
       
       
      slave_parallel_threads=10
      log_slave_updates=on
       
      performance_schema=off
       
      skip-name-resolve
       
      max_allowed_packet = 512M
       
      query_cache_type=1
      query_cache_size = 0
      query_cache_limit = 1M
      query_cache_min_res_unit=1K
      max_connections = 900
       
      table_open_cache=64K
      innodb_open_files=64K
      open_files_limit=1020000
      collation-server = utf8_general_ci
      character-set-server = utf8
       
      log-error=/opt/log/error.log
       
      [mysqld_safe]
      log-error=/opt/log/error.log
      pid-file=/var/run/mysqld/mysqld.pid
      malloc-lib=/usr/lib64/libjemalloc.so.1
      
      

      Exactly the same problem happen on another table when adding fulltext index (table size about 150MB) (reported in additional comments here: https://jira.mariadb.org/browse/MDEV-9129):

      ALTER TABLE qu_g_mails ADD FULLTEXT ft_qu_g_mails1 (subject, body_text, body_html);
      

      CREATE TABLE `qu_g_mails` (
        `mailid` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `hdr_message_id` varchar(255) DEFAULT NULL,
        `unique_message_id` varchar(255) NOT NULL,
        `subject` text,
        `headers` text,
        `body_text` longtext,
        `body_html` longtext,
        `created` datetime NOT NULL,
        `delivered` datetime DEFAULT NULL,
        `from_mail` text,
        `to_recipients` text,
        `cc_recipients` text,
        `bcc_recipients` text,
        `accountuserid` char(8) DEFAULT NULL,
        `reply_to` text,
        PRIMARY KEY (`mailid`),
        KEY `IDX_qu_g_mails_1` (`accountuserid`)
      ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;
      

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              unitminer Viktor Zeman
              Votes:
              0 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.