[#MDEV-16060] InnoDB: Failing assertion: ut_strcmp(index->name, key->name)

[MDEV-16060] InnoDB: Failing assertion: ut_strcmp(index->name, key->name) Created: 2018-04-30 Updated: 2020-08-25 Resolved: 2019-05-09
Status:	Closed
Project:	MariaDB Server
Component/s:	Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s:	10.1, 10.2.10, 10.2, 10.3
Fix Version/s:	10.2.25, 10.1.41, 10.3.15, 10.4.5

Type:

Bug

Priority:

Blocker

Reporter:

ReporterG

Assignee:

Sergei Golubchik

Resolution:

Fixed

Votes:

Labels:

None

Environment:

4.4.0-97-generic #120-Ubuntu SMP Tue Sep 19 17:28:18 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

Attachments:

mariadb_crashes_250618.log

threads_full

Issue Links:

Duplicate
is duplicated by	~~MDEV-15776~~	Failing assertion: table->get_ref_cou...	Closed
is duplicated by	~~MDEV-16460~~	Magento randomly crashes MariaDB 10.2...	Closed
is duplicated by	~~MDEV-18524~~	Invalid (old?) table, unexpected ER_R...	Closed
is duplicated by	~~MDEV-18847~~	Assertion failure in file ha_innodb.c...	Closed
is duplicated by	~~MDEV-18933~~	Hang on shutdown in rec_get_next_offs...	Closed
is duplicated by	~~MDEV-19037~~	Crashing on adding index / index name...	Closed
Relates
relates to	MDEV-6642	Server crashed with assertion failure...	Open
relates to	~~MDEV-16467~~	MariaDB crashes because of "long sema...	Closed

Description

With our current setup (and also on other hardware, but same software versions)
we keep getting this issue. This due a process of running a process to create a index table.

[Note] InnoDB: Index for key no 1 mysql name

{code:java}

CATALOG_PRODUCT_FLAT_1_TYPE_ID InnoDB name CATALOG_PRODUCT_FLAT_8_ATTRIBUTE_SET_ID for table app2/#sql-26be_424c6

For table application1/#sql-26be_424c6

apr 25 13:07:10 db1 mysqld[9918]: 2018-04-25 13:07:10 0x7f8f35189700 InnoDB: Assertion failure in file /home/buildbot/buildbot/build/mariadb-10.2.14/storage/innobase/handler/ha_innodb.cc line 9631

apr 25 13:07:10 db1 mysqld[9918]: InnoDB: Failing assertion: ut_strcmp(index->name, key->name) == 0

apr 25 13:07:10 db1 mysqld[9918]: InnoDB: We intentionally generate a memory trap.

apr 25 13:07:10 db1 mysqld[9918]: InnoDB: Submit a detailed bug report to https://jira.mariadb.org/

apr 25 13:07:10 db1 mysqld[9918]: InnoDB: If you get repeated assertion failures or crashes, even

apr 25 13:07:10 db1 mysqld[9918]: InnoDB: immediately after the mysqld startup, there may be

apr 25 13:07:10 db1 mysqld[9918]: InnoDB: corruption in the InnoDB tablespace. Please refer to

apr 25 13:07:10 db1 mysqld[9918]: InnoDB: https://mariadb.com/kb/en/library/xtradbinnodb-recovery-modes/

apr 25 13:07:10 db1 mysqld[9918]: InnoDB: about forcing recovery.

apr 25 13:07:10 db1 mysqld[9918]: 180425 13:07:10 [ERROR] mysqld got signal 6 ;

apr 25 13:07:10 db1 mysqld[9918]: This could be because you hit a bug. It is also possible that this binary

apr 25 13:07:10 db1 mysqld[9918]: or one of the libraries it was linked against is corrupt, improperly built,

apr 25 13:07:10 db1 mysqld[9918]: or misconfigured. This error can also be caused by malfunctioning hardware.

apr 25 13:07:10 db1 mysqld[9918]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs

apr 25 13:07:10 db1 mysqld[9918]: We will try our best to scrape up some info that will hopefully help

apr 25 13:07:10 db1 mysqld[9918]: diagnose the problem, but since we have already crashed,

apr 25 13:07:10 db1 mysqld[9918]:

Mariadb server dies after this, and we need to recover manually to get things running again.

{code:java}

Maridb my.cnf settings:

{noformat}

# MariaDB database server configuration file.

# You can copy this file to one of:

# - "/etc/mysql/my.cnf" to set global options,

# - "~/.my.cnf" to set user-specific options.

# One can use all long options that the program supports.

# Run program with --help to get a list of available options and with

# --print-defaults to see which it would actually understand and use.

# For explanations see

# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients

# It has been reported that passwords should be enclosed with ticks/quotes

# escpecially if they contain "#" chars...

# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

[client]

port            = 3306

socket          = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs

# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.

[mysqld_safe]

socket          = /var/run/mysqld/mysqld.sock

nice            = 0

[mysqld]

# * Basic Settings

user            = mysql

pid-file        = /var/run/mysqld/mysqld.pid

socket          = /var/run/mysqld/mysqld.sock

port            = 3306

basedir         = /usr

datadir         = /var/lib/mysql

tmpdir          = /tmp

lc_messages_dir = /usr/share/mysql

lc_messages     = en_US

skip-external-locking

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

bind-address            = 0.0.0.0

# * Fine Tuning

max_connections         = 275

connect_timeout         = 5

#wait_timeout           = 300

wait_timeout            = 7200

#max_allowed_packet     = 16M

max_allowed_packet      = 64M

thread_cache_size       = 128

sort_buffer_size        = 4M

bulk_insert_buffer_size = 16M

tmp_table_size          = 32M

max_heap_table_size     = 32M

# * MyISAM

# This replaces the startup script and checks MyISAM tables if needed

# the first time they are touched. On error, make copy and try a repair.

myisam_recover_options = BACKUP

key_buffer_size         = 128M

#open-files-limit       = 2000

table_open_cache        = 600

myisam_sort_buffer_size = 512M

concurrent_insert       = 2

read_buffer_size        = 2M

read_rnd_buffer_size    = 1M

# * Query Cache Configuration

# Cache only tiny result sets, so we can fit more in the query cache.

query_cache_limit               = 128K

query_cache_size                = 64M

# for more write intensive setups, set to DEMAND or OFF

#query_cache_type               = DEMAND

# * Logging and Replication

# Both location gets rotated by the cronjob.

# Be aware that this log type is a performance killer.

# As of 5.1 you can enable the log at runtime!

#general_log_file        = /var/log/mysql/mysql.log

#general_log             = 1

# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.

# we do want to know about network errors and such

log_warnings            = 2

# Enable the slow query log to see queries with especially long duration

#slow_query_log[={0|1}]

slow_query_log_file     = /var/log/mysql/mariadb-slow.log

long_query_time = 10

#log_slow_rate_limit    = 1000

log_slow_verbosity      = query_plan

#log-queries-not-using-indexes

#log_slow_admin_statements

# The following can be used as easy to replay backup logs or for replication.

# note: if you are setting up a replication slave, see README.Debian about

#       other settings you may need to change.

#server-id              = 1

#report_host            = master1

#auto_increment_increment = 2

#auto_increment_offset  = 1

log_bin                 = /var/log/mysql/mariadb-bin

log_bin_index           = /var/log/mysql/mariadb-bin.index

# not fab for performance, but safer

#sync_binlog            = 1

expire_logs_days        = 10

max_binlog_size         = 100M

# slaves

#relay_log              = /var/log/mysql/relay-bin

#relay_log_index        = /var/log/mysql/relay-bin.index

#relay_log_info_file    = /var/log/mysql/relay-bin.info

#log_slave_updates

#read_only

# If applications support it, this stricter sql_mode prevents some

# mistakes like inserting invalid dates etc.

#sql_mode               = NO_ENGINE_SUBSTITUTION,TRADITIONAL

# * InnoDB

# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.

# Read the manual for more InnoDB related options. There are many!

default_storage_engine  = InnoDB

# you can't just change log file size, requires special procedure

#innodb_log_file_size   = 50M

innodb_buffer_pool_size = 24G

innodb_log_buffer_size  = 32M

innodb_file_per_table   = 1

innodb_open_files       = 600

innodb_io_capacity      = 400

innodb_flush_method     = O_DIRECT

# * Security Features

# Read the manual, too, if you want chroot!

# chroot = /var/lib/mysql/

# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".

# ssl-ca=/etc/mysql/cacert.pem

# ssl-cert=/etc/mysql/server-cert.pem

# ssl-key=/etc/mysql/server-key.pem

# * Galera-related settings

[galera]

# Mandatory settings

#wsrep_on=ON

#wsrep_provider=

#wsrep_cluster_address=

#binlog_format=row

#default_storage_engine=InnoDB

#innodb_autoinc_lock_mode=2

# Allow server to accept connections on all interfaces.

#bind-address=0.0.0.0

# Optional setting

#wsrep_slave_threads=1

#innodb_flush_log_at_trx_commit=0

[mysqldump]

quick

quote-names

max_allowed_packet      = 16M

[mysql]

#no-auto-rehash # faster start of mysql but no tab completion

[isamchk]

key_buffer              = 16M

# * IMPORTANT: Additional settings that can override those from this file!

#   The files must end with '.cnf', otherwise they'll be ignored.

!includedir /etc/mysql/conf.d/

 {noformat}

Comments

Comment by Elena Stepanova [ 2018-05-31 ]

Can you identify and provide the query that causes the crash, and tables which it uses?

Comment by ReporterG [ 2018-06-14 ]

jun 14 11:04:00 db2 mysqld[7612]: Query (0x7f962400f130): ALTER TABLE `catalog_product_flat_34` ADD CONSTRAINT `FK_0D911190942826074E456C15A38B26D9` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE

desc catalog_product_flat_34;

+--------------------------------+----------------------+------+-----+---------+-------+

| Field                          | Type                 | Null | Key | Default | Extra |

+--------------------------------+----------------------+------+-----+---------+-------+

| entity_id                      | int(10) unsigned     | NO   | PRI | NULL    |       |

| attribute_set_id               | smallint(5) unsigned | NO   | MUL | 0       |       |

| type_id                        | varchar(32)          | NO   | MUL | simple  |       |

| color                          | int(11)              | YES  |     | NULL    |       |

| color_value                    | varchar(255)         | YES  |     | NULL    |       |

| cost                           | decimal(12,4)        | YES  |     | NULL    |       |

| created_at                     | timestamp            | YES  |     | NULL    |       |

| description                    | text                 | YES  |     | NULL    |       |

| gift_message_available         | smallint(6)          | YES  |     | NULL    |       |

| has_options                    | smallint(6)          | NO   |     | 0       |       |

| image                          | varchar(255)         | YES  |     | NULL    |       |

| image_label                    | varchar(255)         | YES  |     | NULL    |       |

| is_recurring                   | smallint(6)          | YES  |     | NULL    |       |

| links_exist                    | int(11)              | YES  |     | NULL    |       |

| links_purchased_separately     | int(11)              | YES  |     | NULL    |       |

| links_title                    | varchar(255)         | YES  |     | NULL    |       |

| manufacturer                   | int(11)              | YES  |     | NULL    |       |

| manufacturer_value             | varchar(255)         | YES  |     | NULL    |       |

| meta_description               | varchar(255)         | YES  |     | NULL    |       |

| meta_keyword                   | text                 | YES  |     | NULL    |       |

| meta_title                     | varchar(255)         | YES  |     | NULL    |       |

| msrp                           | decimal(12,4)        | YES  |     | NULL    |       |

| msrp_display_actual_price_type | varchar(255)         | YES  |     | NULL    |       |

| msrp_enabled                   | smallint(6)          | YES  |     | NULL    |       |

| name                           | varchar(255)         | YES  | MUL | NULL    |       |

| news_from_date                 | datetime             | YES  |     | NULL    |       |

| news_to_date                   | datetime             | YES  |     | NULL    |       |

| price                          | decimal(12,4)        | YES  | MUL | NULL    |       |

| price_type                     | int(11)              | YES  |     | NULL    |       |

| price_view                     | int(11)              | YES  |     | NULL    |       |

| recurring_profile              | text                 | YES  |     | NULL    |       |

| required_options               | smallint(5) unsigned | NO   |     | 0       |       |

| shipment_type                  | int(11)              | YES  |     | NULL    |       |

| short_description              | text                 | YES  |     | NULL    |       |

| size                           | int(11)              | YES  |     | NULL    |       |

| size_value                     | varchar(255)         | YES  |     | NULL    |       |

| sku                            | varchar(64)          | YES  |     | NULL    |       |

| sku_type                       | int(11)              | YES  |     | NULL    |       |

| small_image                    | varchar(255)         | YES  |     | NULL    |       |

| small_image_label              | varchar(255)         | YES  |     | NULL    |       |

| special_from_date              | datetime             | YES  |     | NULL    |       |

| special_price                  | decimal(12,4)        | YES  |     | NULL    |       |

| special_to_date                | datetime             | YES  |     | NULL    |       |

| status                         | smallint(5) unsigned | YES  | MUL | NULL    |       |

| tax_class_id                   | int(10) unsigned     | YES  |     | NULL    |       |

| thumbnail                      | varchar(255)         | YES  |     | NULL    |       |

| thumbnail_label                | varchar(255)         | YES  |     | NULL    |       |

| updated_at                     | timestamp            | YES  |     | NULL    |       |

| url_key                        | varchar(255)         | YES  |     | NULL    |       |

| url_path                       | varchar(255)         | YES  |     | NULL    |       |

| visibility                     | smallint(5) unsigned | YES  |     | NULL    |       |

| weight                         | decimal(12,4)        | YES  |     | NULL    |       |

| weight_type                    | int(11)              | YES  |     | NULL    |       |

| case                           | varchar(255)         | YES  |     | NULL    |       |

| delivery                       | text                 | YES  |     | NULL    |       |

| giftwrap                       | smallint(6)          | YES  |     | NULL    |       |

| ring_color                     | int(11)              | YES  |     | NULL    |       |

| ring_color_value               | varchar(255)         | YES  |     | NULL    |       |

| material                       | text                 | YES  |     | NULL    |       |

| care                           | varchar(255)         | YES  |     | NULL    |       |

| jewelry_color_text             | varchar(255)         | YES  |     | NULL    |       |

| jewelry_size_text              | varchar(255)         | YES  |     | NULL    |       |

| google_product_category        | varchar(255)         | YES  |     | NULL    |       |

| lifestyle_image                | text                 | YES  |     | NULL    |       |

| gtin_code                      | varchar(255)         | YES  |     | NULL    |       |

| country_of_manufacture         | varchar(255)         | YES  |     | NULL    |       |

| country_of_manufacture_value   | varchar(255)         | YES  |     | NULL    |       |

| customs_description            | varchar(255)         | YES  |     | NULL    |       |

| height                         | int(11)              | YES  |     | NULL    |       |

| length                         | int(11)              | YES  |     | NULL    |       |

| matrix                         | varchar(255)         | YES  |     | NULL    |       |

| packages_per_unit              | int(11)              | YES  |     | NULL    |       |

| processing_days                | int(11)              | YES  |     | NULL    |       |

| volume                         | decimal(12,4)        | YES  |     | NULL    |       |

| width                          | int(11)              | YES  |     | NULL    |       |

| color_jewel_search             | int(11)              | YES  |     | NULL    |       |

| color_jewel_search_value       | varchar(255)         | YES  |     | NULL    |       |

| color_strap_search             | int(11)              | YES  |     | NULL    |       |

| color_strap_search_value       | varchar(255)         | YES  |     | NULL    |       |

| charms_color                   | int(11)              | YES  |     | NULL    |       |

| charms_color_value             | varchar(255)         | YES  |     | NULL    |       |

| charms_color_text              | varchar(255)         | YES  |     | NULL    |       |

| clasp_color_strap_search       | int(11)              | YES  |     | NULL    |       |

| clasp_color_strap_search_value | varchar(255)         | YES  |     | NULL    |       |

+--------------------------------+----------------------+------+-----+---------+-------+

> desc catalog_product_entity;

+------------------+----------------------+------+-----+---------+----------------+

| Field            | Type                 | Null | Key | Default | Extra          |

+------------------+----------------------+------+-----+---------+----------------+

| entity_id        | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |

| entity_type_id   | smallint(5) unsigned | NO   | MUL | 0       |                |

| attribute_set_id | smallint(5) unsigned | NO   | MUL | 0       |                |

| type_id          | varchar(32)          | NO   |     | simple  |                |

| sku              | varchar(64)          | YES  | MUL | NULL    |                |

| has_options      | smallint(6)          | NO   |     | 0       |                |

| required_options | smallint(5) unsigned | NO   |     | 0       |                |

| created_at       | timestamp            | YES  |     | NULL    |                |

| updated_at       | timestamp            | YES  |     | NULL    |                |

+------------------+----------------------+------+-----+---------+----------------+

Comment by Elena Stepanova [ 2018-06-22 ]

Thanks, but could you please provide SHOW CREATE TABLE output? It contains some information which DESCRIBE doesn't provide.
Also, there were some errors/notes in the error log before the crash, could you please paste or attach a bigger portion of the log, preferably from server startup till the end of the crash report.

Comment by ReporterG [ 2018-06-25 ]

I've made a log file of several crashes. One from a few weeks ago and the one from which the error in my previous comment came from.

mariadb_crashes_250618.log

Here are also the `SHOW CREATE` outputs for the same tables as earlier.

MariaDB [app2]> SHOW CREATE TABLE `catalog_product_flat_34`\G

*************************** 1. row ***************************

       Table: catalog_product_flat_34

Create Table: CREATE TABLE `catalog_product_flat_34` (

  `entity_id` int(10) unsigned NOT NULL COMMENT 'Entity Id',

  `attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute Set Id',

  `type_id` varchar(32) NOT NULL DEFAULT 'simple' COMMENT 'Type Id',

  `color` int(11) DEFAULT NULL COMMENT 'Color',

  `color_value` varchar(255) DEFAULT NULL COMMENT 'Color Value',

  `cost` decimal(12,4) DEFAULT NULL COMMENT 'Cost',

  `created_at` timestamp NULL DEFAULT NULL COMMENT 'Created At',

  `description` text DEFAULT NULL COMMENT 'Description',

  `gift_message_available` smallint(6) DEFAULT NULL COMMENT 'Gift Message Available',

  `has_options` smallint(6) NOT NULL DEFAULT 0 COMMENT 'Has Options',

  `image` varchar(255) DEFAULT NULL COMMENT 'Image',

  `image_label` varchar(255) DEFAULT NULL COMMENT 'Image Label',

  `is_recurring` smallint(6) DEFAULT NULL COMMENT 'Is Recurring',

  `links_exist` int(11) DEFAULT NULL COMMENT 'Links Exist',

  `links_purchased_separately` int(11) DEFAULT NULL COMMENT 'Links Purchased Separately',

  `links_title` varchar(255) DEFAULT NULL COMMENT 'Links Title',

  `manufacturer` int(11) DEFAULT NULL COMMENT 'Manufacturer',

  `manufacturer_value` varchar(255) DEFAULT NULL COMMENT 'Manufacturer Value',

  `meta_description` varchar(255) DEFAULT NULL COMMENT 'Meta Description',

  `meta_keyword` text DEFAULT NULL COMMENT 'Meta Keyword',

  `meta_title` varchar(255) DEFAULT NULL COMMENT 'Meta Title',

  `msrp` decimal(12,4) DEFAULT NULL COMMENT 'Msrp',

  `msrp_display_actual_price_type` varchar(255) DEFAULT NULL COMMENT 'Msrp Display Actual Price Type',

  `msrp_enabled` smallint(6) DEFAULT NULL COMMENT 'Msrp Enabled',

  `name` varchar(255) DEFAULT NULL COMMENT 'Name',

  `news_from_date` datetime DEFAULT NULL COMMENT 'News From Date',

  `news_to_date` datetime DEFAULT NULL COMMENT 'News To Date',

  `price` decimal(12,4) DEFAULT NULL COMMENT 'Price',

  `price_type` int(11) DEFAULT NULL COMMENT 'Price Type',

  `price_view` int(11) DEFAULT NULL COMMENT 'Price View',

  `recurring_profile` text DEFAULT NULL COMMENT 'Recurring Profile',

  `required_options` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Required Options',

  `shipment_type` int(11) DEFAULT NULL COMMENT 'Shipment Type',

  `short_description` text DEFAULT NULL COMMENT 'Short Description',

  `size` int(11) DEFAULT NULL COMMENT 'Size',

  `size_value` varchar(255) DEFAULT NULL COMMENT 'Size Value',

  `sku` varchar(64) DEFAULT NULL COMMENT 'Sku',

  `sku_type` int(11) DEFAULT NULL COMMENT 'Sku Type',

  `small_image` varchar(255) DEFAULT NULL COMMENT 'Small Image',

  `small_image_label` varchar(255) DEFAULT NULL COMMENT 'Small Image Label',

  `special_from_date` datetime DEFAULT NULL COMMENT 'Special From Date',

  `special_price` decimal(12,4) DEFAULT NULL COMMENT 'Special Price',

  `special_to_date` datetime DEFAULT NULL COMMENT 'Special To Date',

  `status` smallint(5) unsigned DEFAULT NULL COMMENT 'Status',

  `tax_class_id` int(10) unsigned DEFAULT NULL COMMENT 'Tax Class Id',

  `thumbnail` varchar(255) DEFAULT NULL COMMENT 'Thumbnail',

  `thumbnail_label` varchar(255) DEFAULT NULL COMMENT 'Thumbnail Label',

  `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Updated At',

  `url_key` varchar(255) DEFAULT NULL COMMENT 'Url Key',

  `url_path` varchar(255) DEFAULT NULL COMMENT 'Url Path',

  `visibility` smallint(5) unsigned DEFAULT NULL COMMENT 'Visibility',

  `weight` decimal(12,4) DEFAULT NULL COMMENT 'Weight',

  `weight_type` int(11) DEFAULT NULL COMMENT 'Weight Type',

  `case` varchar(255) DEFAULT NULL COMMENT 'Case',

  `delivery` text DEFAULT NULL COMMENT 'Delivery',

  `giftwrap` smallint(6) DEFAULT NULL COMMENT 'Giftwrap',

  `ring_color` int(11) DEFAULT NULL COMMENT 'Ring Color',

  `ring_color_value` varchar(255) DEFAULT NULL COMMENT 'Ring Color Value',

  `material` text DEFAULT NULL COMMENT 'Material',

  `care` varchar(255) DEFAULT NULL COMMENT 'Care',

  `jewelry_color_text` varchar(255) DEFAULT NULL COMMENT 'Jewelry Color Text',

  `jewelry_size_text` varchar(255) DEFAULT NULL COMMENT 'Jewelry Size Text',

  `google_product_category` varchar(255) DEFAULT NULL COMMENT 'Google Product Category',

  `lifestyle_image` text DEFAULT NULL COMMENT 'Lifestyle Image',

  `gtin_code` varchar(255) DEFAULT NULL COMMENT 'Gtin Code',

  `country_of_manufacture` varchar(255) DEFAULT NULL COMMENT 'Country Of Manufacture',

  `country_of_manufacture_value` varchar(255) DEFAULT NULL COMMENT 'Country Of Manufacture Value',

  `customs_description` varchar(255) DEFAULT NULL COMMENT 'Customs Description',

  `height` int(11) DEFAULT NULL COMMENT 'Height',

  `length` int(11) DEFAULT NULL COMMENT 'Length',

  `matrix` varchar(255) DEFAULT NULL COMMENT 'Matrix',

  `packages_per_unit` int(11) DEFAULT NULL COMMENT 'Packages Per Unit',

  `processing_days` int(11) DEFAULT NULL COMMENT 'Processing Days',

  `volume` decimal(12,4) DEFAULT NULL COMMENT 'Volume',

  `width` int(11) DEFAULT NULL COMMENT 'Width',

  `color_jewel_search` int(11) DEFAULT NULL COMMENT 'Color Jewel Search',

  `color_jewel_search_value` varchar(255) DEFAULT NULL COMMENT 'Color Jewel Search Value',

  `color_strap_search` int(11) DEFAULT NULL COMMENT 'Color Strap Search',

  `color_strap_search_value` varchar(255) DEFAULT NULL COMMENT 'Color Strap Search Value',

  `charms_color` int(11) DEFAULT NULL COMMENT 'Charms Color',

  `charms_color_value` varchar(255) DEFAULT NULL COMMENT 'Charms Color Value',

  `charms_color_text` varchar(255) DEFAULT NULL COMMENT 'Charms Color Text',

  `clasp_color_strap_search` int(11) DEFAULT NULL COMMENT 'Clasp Color Strap Search',

  `clasp_color_strap_search_value` varchar(255) DEFAULT NULL COMMENT 'Clasp Color Strap Search Value',

  PRIMARY KEY (`entity_id`),

  KEY `IDX_CATALOG_PRODUCT_FLAT_34_TYPE_ID` (`type_id`),

  KEY `IDX_CATALOG_PRODUCT_FLAT_34_ATTRIBUTE_SET_ID` (`attribute_set_id`),

  KEY `IDX_CATALOG_PRODUCT_FLAT_34_NAME` (`name`),

  KEY `IDX_CATALOG_PRODUCT_FLAT_34_PRICE` (`price`),

  KEY `IDX_CATALOG_PRODUCT_FLAT_34_STATUS` (`status`),

  CONSTRAINT `FK_0D911190942826074E456C15A38B26D9` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product Flat (Store 34)'

1 row in set (0.00 sec)

MariaDB [app2]> SHOW CREATE TABLE `catalog_product_entity`\G

*************************** 1. row ***************************

       Table: catalog_product_entity

Create Table: CREATE TABLE `catalog_product_entity` (

  `entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',

  `entity_type_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Entity Type ID',

  `attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute Set ID',

  `type_id` varchar(32) NOT NULL DEFAULT 'simple' COMMENT 'Type ID',

  `sku` varchar(64) DEFAULT NULL COMMENT 'SKU',

  `has_options` smallint(6) NOT NULL DEFAULT 0 COMMENT 'Has Options',

  `required_options` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Required Options',

  `created_at` timestamp NULL DEFAULT NULL COMMENT 'Creation Time',

  `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Update Time',

  PRIMARY KEY (`entity_id`),

  KEY `IDX_CATALOG_PRODUCT_ENTITY_ENTITY_TYPE_ID` (`entity_type_id`),

  KEY `IDX_CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` (`attribute_set_id`),

  KEY `IDX_CATALOG_PRODUCT_ENTITY_SKU` (`sku`),

  CONSTRAINT `FK_E26A0FDF92505D35A08BA4FB041E934F` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `FK_CAT_PRD_ENTT_ATTR_SET_ID_EAV_ATTR_SET_ATTR_SET_ID` FOREIGN KEY (`attribute_set_id`) REFERENCES `eav_attribute_set` (`attribute_set_id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=2198 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Table'

1 row in set (0.00 sec)

Comment by Elena Stepanova [ 2018-06-25 ]

Thanks a lot.

Comment by Elena Stepanova [ 2018-06-26 ]

Finally, I've been able to reproduce the problem with a reasonable reliability. It's still a race condition, so the test case is non-deterministic; run with --repeat=N. N=20 seems to be enough on my machine, but it can vary.

--source include/have_innodb.inc

CREATE TABLE t1 (`pk` INT PRIMARY KEY, f1 INT NOT NULL, INDEX ind_f1 (f1)) ENGINE=InnoDB;

CREATE TABLE t2 (`pk` INT PRIMARY KEY, f2 INT NOT NULL, INDEX ind_f2 (f2)) ENGINE=InnoDB;

--connect (con1,localhost,root,,test)

CREATE TABLE t3 (`pk` INT PRIMARY KEY, f3 INT NOT NULL, INDEX ind_f3 (f3)) ENGINE=InnoDB;

CREATE TABLE t4 (`pk` INT PRIMARY KEY, f4 INT NOT NULL, INDEX ind_f4 (f4)) ENGINE=InnoDB;

CREATE TABLE t5 (`pk` INT PRIMARY KEY, f5 INT NOT NULL, INDEX ind_f5 (f5), FOREIGN KEY (`pk`) REFERENCES t4 (`pk`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

--send

  ALTER TABLE t3 ADD CONSTRAINT fk3 FOREIGN KEY (`pk`) REFERENCES t5 (`pk`) ON DELETE CASCADE ON UPDATE CASCADE;

--connection default

UPDATE IGNORE t4 SET f4 = 10 WHERE `pk` = 1;

--connection con1

--reap

ALTER TABLE t1 ADD CONSTRAINT fk1 FOREIGN KEY (f1) REFERENCES t2 (`pk`) ON DELETE CASCADE ON UPDATE CASCADE;

# Cleanup

--disconnect con1

--connection default

DROP TABLE t3, t1, t5, t4, t2;

10.2 ee6ac4d313
2018-06-26 13:31:36 140251290875648 [ERROR] Invalid (old?) table or database name '#sql-1508_50'
2018-06-26 13:31:36 140251290572544 [ERROR] InnoDB: Index for key no 1 mysql name ind_f1 InnoDB name ind_f3 for table test/#sql-1508_50
2018-06-26 13:31:36 140251290572544 [Note] InnoDB: Index for key no 1 mysql name ind_f1 InnoDB name PRIMARY for table test/#sql-1508_50
2018-06-26 13:31:36 140251290572544 [Note] InnoDB: Index for key no 1 mysql name ind_f1 InnoDB name ind_f3 for table test/#sql-1508_50
2018-06-26 13:31:36 0x7f8ecc5a9700 InnoDB: Assertion failure in file /data/src/10.2/storage/innobase/handler/ha_innodb.cc line 9689
InnoDB: Failing assertion: ut_strcmp(index->name, key->name) == 0

#6 0x000055b75b9651cd in ut_dbg_assertion_failed (expr=0x55b75bda80a0 "ut_strcmp(index->name, key->name) == 0", file=0x55b75bda3c48 "/data/src/10.2/storage/innobase/handler/ha_innodb.cc", line=9689) at /data/src/10.2/storage/innobase/ut/ut0dbg.cc:61
#7 0x000055b75b75fc1e in ha_innobase::innobase_get_index (this=0x7f8e880d32a8, keynr=1) at /data/src/10.2/storage/innobase/handler/ha_innodb.cc:9689
#8 0x000055b75b76905c in ha_innobase::info_low (this=0x7f8e880d32a8, flag=26, is_analyze=false) at /data/src/10.2/storage/innobase/handler/ha_innodb.cc:14412
#9 0x000055b75b7695ba in ha_innobase::info (this=0x7f8e880d32a8, flag=26) at /data/src/10.2/storage/innobase/handler/ha_innodb.cc:14549
#10 0x000055b75b7586b9 in ha_innobase::open (this=0x7f8e880d32a8, name=0x7f8e880c7b48 "./test/#sql-1508_50") at /data/src/10.2/storage/innobase/handler/ha_innodb.cc:6643
#11 0x000055b75b449464 in handler::ha_open (this=0x7f8e880d32a8, table_arg=0x7f8e880d26a0, name=0x7f8e880c7b48 "./test/#sql-1508_50", mode=2, test_if_locked=18) at /data/src/10.2/sql/handler.cc:2501
#12 0x000055b75b2d4ef7 in open_table_from_share (thd=0x7f8e88000b00, share=0x7f8e880c7630, alias=0x7f8ecc5a6280 "#sql-1508_50", db_stat=1, prgflag=8, ha_open_flags=18, outparam=0x7f8e880d26a0, is_create_table=false) at /data/src/10.2/sql/table.cc:3334
#13 0x000055b75b3c4abe in THD::open_temporary_table (this=0x7f8e88000b00, share=0x7f8e880c7630, alias=0x7f8ecc5a6280 "#sql-1508_50", open_in_engine=true) at /data/src/10.2/sql/temporary_tables.cc:1105
#14 0x000055b75b3c2ac4 in THD::create_and_open_tmp_table (this=0x7f8e88000b00, hton=0x55b75eeb9b40, frm=0x7f8ecc5a55e0, path=0x7f8ecc5a6aec "./test/#sql-1508_50", db=0x7f8e88011850 "test", table_name=0x7f8ecc5a6280 "#sql-1508_50", open_in_engine=true) at /data/src/10.2/sql/temporary_tables.cc:76
#15 0x000055b75b2a64df in mysql_alter_table (thd=0x7f8e88000b00, new_db=0x7f8e88011850 "test", new_name=0x0, create_info=0x7f8ecc5a6e50, table_list=0x7f8e88011240, alter_info=0x7f8ecc5a6da0, order_num=0, order=0x0, ignore=false) at /data/src/10.2/sql/sql_table.cc:9491
#16 0x000055b75b3205d9 in Sql_cmd_alter_table::execute (this=0x7f8e88011a60, thd=0x7f8e88000b00) at /data/src/10.2/sql/sql_alter.cc:324
#17 0x000055b75b1d8f90 in mysql_execute_command (thd=0x7f8e88000b00) at /data/src/10.2/sql/sql_parse.cc:6222
#18 0x000055b75b1ddd41 in mysql_parse (thd=0x7f8e88000b00, rawbuf=0x7f8e880110b8 "ALTER TABLE t1 ADD CONSTRAINT fk1 FOREIGN KEY (f1) REFERENCES t2 (`pk`) ON DELETE CASCADE ON UPDATE CASCADE", length=107, parser_state=0x7f8ecc5a8200, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7999
#19 0x000055b75b1cb7fc in dispatch_command (command=COM_QUERY, thd=0x7f8e88000b00, packet=0x7f8e8808bc81 "ALTER TABLE t1 ADD CONSTRAINT fk1 FOREIGN KEY (f1) REFERENCES t2 (`pk`) ON DELETE CASCADE ON UPDATE CASCADE", packet_length=107, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1821
#20 0x000055b75b1ca15f in do_command (thd=0x7f8e88000b00) at /data/src/10.2/sql/sql_parse.cc:1375
#21 0x000055b75b31b2c6 in do_handle_one_connection (connect=0x55b75f3c6da0) at /data/src/10.2/sql/sql_connect.cc:1335
#22 0x000055b75b31b053 in handle_one_connection (arg=0x55b75f3c6da0) at /data/src/10.2/sql/sql_connect.cc:1241
#23 0x000055b75b73d0c0 in pfs_spawn_thread (arg=0x55b75f3e9b50) at /data/src/10.2/storage/perfschema/pfs.cc:1862
#24 0x00007f8edcea1494 in start_thread (arg=0x7f8ecc5a9700) at pthread_create.c:333
#25 0x00007f8edb28793f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Full threads are attached: threads_full

Please note that in the test case non-pk columns and secondary indexes for different tables are deliberately given table-specific names (e.g. f2 / ind_f2 for t2, etc.); and in the output before the crash we can see

2018-06-26 13:31:36 140251290572544 [Note] InnoDB:  Index for key no 1 mysql name ind_f1 InnoDB name ind_f3 for table test/#sql-1508_50

they are not just different indexes of the same tables, they are indexes from different tables. Same I've seen in the error log that atlasgert attached to this issue, e.g.

Index for key no 1 mysql name IDX_CATALOG_PRODUCT_FLAT_1_TYPE_ID InnoDB name IDX_CATALOG_PRODUCT_FLAT_8_ATTRIBUTE_SET_ID for table app2/#sql-26be_424c6

where the first index name clearly belongs to catalog_product_flat_1, while the second to catalog_product_flat_8.

Reproducible on 10.2, 10.3. So far I couldn't reproduce it on 10.1.

Comment by ReporterG [ 2018-06-26 ]

That is great to hear. The crash happens when we run the catalog_product_flat indexer (This is a command line tool) on the database. This tool convert the Magento EAV structure to a flat table structure to improve query performance. On its own the DB is stable.
The crash usually happens when we run this indexer during day hours if we run it at night, when the web store is pretty quiet, nothing happens and it completes correctly.

I regrettably won't be able to provide you with any binary logs. As this is a relative busy store mostly serving the EU, and this log most certainly will contain personal data it would present too great of a risk.

Comment by Elena Stepanova [ 2018-06-26 ]

It's okay, with the test case we hopefully shouldn't need the logs. Thanks for your help.

Comment by Marko Mäkelä [ 2018-06-26 ]

Analyzed from a local crash with the test case:

10.2 31c950cca8fa1623d41a8a7784e09fbcd6bc2c71
(gdb) p index->name
$1 = {m_name = 0x7fb6cc080e28 "ind_f3"}
(gdb) p key->name
$2 = 0x7fb6cc092c59 "ind_f1"
(gdb) p table->s->table_name
$3 = {str = 0x7fb6cc09f011 "#sql-4883_1e", length = 12}
(gdb) p index->table->name
$4 = {m_name = 0x7fb6cc0752c0 "test/t3"}

The issue seems to be that during the execution of ALTER TABLE t1, the temporary table name #sql-4883_1e somehow finds the table that was altered as ALTER TABLE t3. That earlier ALTER did not fail. Why was the temporary name not removed from the table definition cache?

Comment by Sergey Vojtovich [ 2019-03-06 ]

Intermediate tables created for ALTER TABLE are never inserted into table definition cache, although they're still available via per-thread temporary tables registry for the duration of ALTER TABLE statement.

Comment by Sergey Vojtovich [ 2019-03-29 ]

serg, please review https://github.com/MariaDB/server/commit/d1307408d717cb25dacd459ca3dc4a6c248452d9

Comment by Sergey Vojtovich [ 2019-04-29 ]

serg, please review new patch with updated test case. It became half a second slower than original one.
Patch at commits@

Comment by Sergei Golubchik [ 2019-04-30 ]

ok to push

Comment by Sergey Vojtovich [ 2019-05-01 ]

serg, please review add-on fixing PS issue: https://github.com/MariaDB/server/commit/3927d41a032277d9b00b257bfa7257f9c308133a

Comment by Sergei Golubchik [ 2019-05-02 ]

ok to push

Comment by Sergey Vojtovich [ 2019-05-02 ]

Yet another PS hiccup: e5d35a618a5c4c971bcb1d4e44a8e072991099a7

Comment by Sergey Vojtovich [ 2019-05-07 ]

Yet another attempt to fix PS issue: https://github.com/MariaDB/server/commit/d67c60146fce9fb353ac3ad575420cdc92e901ad

Comment by Sergei Golubchik [ 2019-05-08 ]

ok to push, as of df1b050603c40e2c3f04ece067f9fd8b04d1681a

Generated at Thu Feb 08 08:26:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-16060] InnoDB: Failing assertion: ut_strcmp(index->name, key->name) Created: 2018-04-30 Updated: 2020-08-25 Resolved: 2019-05-09