[MDEV-6922] mysqld signal 11 crash when running many ALTER TABLE statements Created: 2014-10-24  Updated: 2014-11-22  Resolved: 2014-11-22

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.0.14
Fix Version/s: 10.0.15

Type: Bug Priority: Major
Reporter: Woody Gilk Assignee: Elena Stepanova
Resolution: Fixed Votes: 0
Labels: alter, crash, upstream
Environment:

Mac OSX 10.9, Homebrew MariaDB

Attachments: File ushahidi.sql    
Issue Links:
Blocks
is blocked by MDEV-7091 10.0.15 merge Closed
Duplicate
is duplicated by MDEV-7135 MySQL restarted after failed creating FK Closed
Relates
relates to MDEV-6759 innodb valgrind failures Closed

 Description   

I have a migration script that creates several tables, then uses ALTER TABLE to create a bunch of foreign key constraints on those tables. MariaDB 10.0.14 will consistently crash after running between 11 and 14 of ALTER TABLE statements, resulting in err 2006 MySQL server has gone away.

The same crash appears in MySQL 5.5, which prompted me to try MariaDB, which was working perfectly well in 10.0.13 and then immediately starting exhibiting the same broken behavior when I upgraded to 10.0.14 two days ago.

After the crash, the following will appear in the error log:

141023 14:47:57 [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.14-MariaDB
 
key_buffer_size=134217728
 
read_buffer_size=131072
 
max_used_connections=2
 
max_threads=153
 
thread_count=2
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467142 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x0x7fbcca845808
 
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 = 0x122c50e90 thread_stack 0x48000
 
0   mysqld                              0x00000001063a7d9d my_print_stacktrace + 60
 
0   mysqld                              0x0000000105e9191d handle_fatal_signal + 602
 
0   libsystem_platform.dylib            0x00007fff9415f5aa _sigtramp + 26
 
0   ???                                 0x000000010733da00 0x0 + 4415805952
 
0   mysqld                              0x000000010625e359 _ZNSt3__16__treeIP14dict_foreign_t20dict_foreign_compareNS_9allocatorIS2_EEE12__find_equalIS2_EER$
 
NS_16__tree_node_baseIPvEESC_RKT_ + 53
 
0   mysqld                              0x000000010625e2db _ZNSt3__16__treeIP14dict_foreign_t20dict_foreign_compareNS_9allocatorIS2_EEE15__insert_uniqueERKS$
 
_ + 33
 
0   mysqld                              0x000000010625e55d _ZNK36dict_foreign_add_to_referenced_tableclEP14dict_foreign_t + 37
 
0   mysqld                              0x0000000106259515 _Z31dict_create_foreign_constraintsP5trx_tPKcmS2_m + 4599
 
0   mysqld                              0x0000000106337d7c _Z33row_table_add_foreign_constraintsP5trx_tPKcmS2_m + 97
 
0   mysqld                              0x00000001062b06eb _ZN11ha_innobase6createEPKcP5TABLEP14HA_CREATE_INFO + 2915
 
0   mysqld                              0x0000000105e97ac6 _ZN7handler9ha_createEPKcP5TABLEP14HA_CREATE_INFO + 136
 
0   mysqld                              0x0000000105e9858d _Z15ha_create_tableP3THDPKcS2_S2_P14HA_CREATE_INFOP34st_mysql_const_unsigned_lex_string + 548
 
0   mysqld                              0x00000001060724b0 _Z17mysql_alter_tableP3THDPcS1_P14HA_CREATE_INFOP10TABLE_LISTP10Alter_infojP8st_orderb + 15119
 
0   mysqld                              0x00000001060bba93 _ZN19Sql_cmd_alter_table7executeEP3THD + 789
 
0   mysqld                              0x0000000105ff2979 _Z21mysql_execute_commandP3THD + 1109
 
0   mysqld                              0x0000000105ff1b25 _Z11mysql_parseP3THDPcjP12Parser_state + 516
 
0   mysqld                              0x0000000105fef3a7 _Z16dispatch_command19enum_server_commandP3THDPcj + 1003
 
0   mysqld                              0x0000000105ff167c _Z10do_commandP3THD + 231
 
0   mysqld                              0x00000001060b7911 _Z24do_handle_one_connectionP3THD + 383
 
0   mysqld                              0x00000001060b7785 handle_one_connection + 59
 
0   libsystem_pthread.dylib             0x00007fff8e035899 _pthread_body + 138
 
0   libsystem_pthread.dylib             0x00007fff8e03572a _pthread_struct_init + 0
 
0   libsystem_pthread.dylib             0x00007fff8e039fc9 thread_start + 13
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fbcca83d820): is an invalid pointer
 
Connection ID (thread ID): 3
 
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_conditio
 
n_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,pa
 
rtial_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.

It's not really particularly easy for me to upload the exact series of CREATE and ALTER statements, because the migrations are generated programatically. But if required, I could probably find a way to log them out.

Here is a log of all the statements that run before the crash happens, captured by using SET GLOBAL general_log=1:

/usr/local/Cellar/mariadb/10.0.14/bin/mysqld, Version: 10.0.14-MariaDB (Homebrew). started with:
 
Tcp port: 3306  Unix socket: /tmp/mysql.sock
 
Time                 Id Command    Argument
 
141024 10:03:50	   19 Quit	
		   18 Quit	
141024 10:03:56	   20 Connect	ushahidi@localhost as anonymous on ushahidi
 
		   20 Query	SET NAMES utf8
 
		   20 Query	SHOW TABLES LIKE 'config'
 
		   21 Connect	ushahidi@localhost as anonymous on ushahidi
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'phinxlog'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'phinxlog'
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `phinxlog` (`version` BIGINT(14) NOT NULL, `start_time` TIMESTAMP NOT NULL, `end_time` TIMESTAMP NOT NULL) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT * FROM phinxlog ORDER BY version ASC
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT * FROM phinxlog ORDER BY version ASC
 
		   21 Query	SELECT 1
 
		   21 Query	START TRANSACTION
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `config` (`id` INT(11) NOT NULL AUTO_INCREMENT, `group_name` VARCHAR(50) NOT NULL, `config_key` VARCHAR(50) NOT NULL, `config_value` VARCHAR(255) NOT NULL, `updated` TIMESTAMP NOT NULL, PRIMARY KEY (`id`),  UNIQUE KEY (`group_name`,`config_key`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `contacts` (`id` INT(11) NOT NULL AUTO_INCREMENT, `user_id` INT(11) NULL, `data_provider` VARCHAR(150) NULL, `type` VARCHAR(20) NULL COMMENT 'email, phone, twitter', `contact` VARCHAR(255) NOT NULL, `created` INT(11) NOT NULL DEFAULT 0, `updated` INT(11) NULL, PRIMARY KEY (`id`),  KEY (`data_provider`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `forms` (`id` INT(11) NOT NULL AUTO_INCREMENT, `parent_id` INT(11) NULL, `name` VARCHAR(255) NOT NULL, `description` TEXT NOT NULL, `type` VARCHAR(30) NOT NULL DEFAULT 'report' COMMENT 'report, comment, stream', `created` INT(11) NOT NULL DEFAULT 0, `updated` INT(11) NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `form_attributes` (`id` INT(11) NOT NULL AUTO_INCREMENT, `key` VARCHAR(150) NOT NULL, `label` VARCHAR(150) NOT NULL, `input` VARCHAR(30) NOT NULL DEFAULT 'text' COMMENT 'text, textarea, select, radio, checkbox, file, date, location', `type` VARCHAR(30) NOT NULL DEFAULT 'varchar' COMMENT 'decimal, int, geometry, text, varchar, point', `required` TINYINT(1) NOT NULL DEFAULT 0, `default` VARCHAR(150) NULL, `priority` INT(11) NOT NULL DEFAULT 99, `options` VARCHAR(255) NULL, `cardinality` INT(11) NOT NULL DEFAULT 1 COMMENT 'maximum number of values, 0 for unlimited', PRIMARY KEY (`id`),  UNIQUE KEY (`key`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `form_groups` (`id` INT(11) NOT NULL AUTO_INCREMENT, `form_id` INT(11) NOT NULL, `label` VARCHAR(150) NOT NULL, `priority` INT(11) NOT NULL DEFAULT 99, `icon` VARCHAR(100) NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `form_groups_form_attributes` (`form_group_id` INT(11) NOT NULL, `form_attribute_id` INT(11) NOT NULL, PRIMARY KEY (`form_group_id`,`form_attribute_id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `media` (`id` INT(11) NOT NULL AUTO_INCREMENT, `user_id` INT(11) NULL, `mime` VARCHAR(50) NOT NULL, `caption` VARCHAR(255) NOT NULL, `o_filename` VARCHAR(255) NOT NULL, `o_size` INT(11) NOT NULL, `o_width` INT(11) NULL, `o_height` INT(11) NULL, `created` INT(11) NOT NULL DEFAULT 0, `updated` INT(11) NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `messages` (`id` INT(11) NOT NULL AUTO_INCREMENT, `parent_id` INT(11) NULL COMMENT 'marks messages being replied to', `contact_id` INT(11) NULL, `post_id` INT(11) NULL, `data_provider` VARCHAR(150) NULL, `data_provider_message_id` VARCHAR(255) NULL, `title` VARCHAR(255) NULL, `message` TEXT NOT NULL, `datetime` DATETIME NULL, `type` VARCHAR(20) NULL COMMENT 'email, phone, twitter', `status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, received, expired, cancelled, failed, sent', `direction` VARCHAR(20) NOT NULL DEFAULT 'incoming' COMMENT 'incoming, outgoing', `created` INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`),  KEY (`data_provider`),  KEY (`type`),  KEY (`status`),  KEY (`direction`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `posts` (`id` INT(11) NOT NULL AUTO_INCREMENT, `parent_id` INT(11) NULL, `form_id` INT(11) NULL, `user_id` INT(11) NULL, `type` VARCHAR(20) NOT NULL DEFAULT 'report' COMMENT 'report, update, revision', `title` VARCHAR(150) NOT NULL, `slug` VARCHAR(150) NULL, `content` TEXT NULL, `status` VARCHAR(20) NOT NULL DEFAULT 'draft' COMMENT 'draft, published, pending', `locale` VARCHAR(5) NOT NULL DEFAULT 'en_US', `created` INT(11) NOT NULL DEFAULT 0, `updated` INT(11) NULL, PRIMARY KEY (`id`),  KEY (`type`),  KEY (`status`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `posts_media` (`post_id` INT(11) NOT NULL, `media_id` INT(11) NOT NULL, PRIMARY KEY (`post_id`,`media_id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `posts_sets` (`post_id` INT(11) NOT NULL, `set_id` INT(11) NOT NULL, PRIMARY KEY (`post_id`,`set_id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `posts_tags` (`post_id` INT(11) NOT NULL, `tag_id` INT(11) NOT NULL, PRIMARY KEY (`post_id`,`tag_id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `post_comments` (`id` INT(11) NOT NULL AUTO_INCREMENT, `parent_id` INT(11) NULL, `post_id` INT(11) NOT NULL, `user_id` INT(11) NULL, `content` TEXT NOT NULL, `status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, published', `created` INT(11) NOT NULL DEFAULT 0, `updated` INT(11) NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `post_datetime` (`id` INT(11) NOT NULL AUTO_INCREMENT, `post_id` INT(11) NOT NULL, `form_attribute_id` INT(11) NOT NULL, `value` DATETIME NULL, `created` INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `post_decimal` (`id` INT(11) NOT NULL AUTO_INCREMENT, `post_id` INT(11) NOT NULL, `form_attribute_id` INT(11) NOT NULL, `value` DECIMAL(12,4) NULL, `created` INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `post_geometry` (`id` INT(11) NOT NULL AUTO_INCREMENT, `post_id` INT(11) NOT NULL, `form_attribute_id` INT(11) NOT NULL, `value` GEOMETRY NULL, `created` INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `post_int` (`id` INT(11) NOT NULL AUTO_INCREMENT, `post_id` INT(11) NOT NULL, `form_attribute_id` INT(11) NOT NULL, `value` INT(11) NULL, `created` INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `post_point` (`id` INT(11) NOT NULL AUTO_INCREMENT, `post_id` INT(11) NOT NULL, `form_attribute_id` INT(11) NOT NULL, `value` POINT NULL, `created` INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `post_text` (`id` INT(11) NOT NULL AUTO_INCREMENT, `post_id` INT(11) NOT NULL, `form_attribute_id` INT(11) NOT NULL, `value` TEXT NULL, `created` INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `post_varchar` (`id` INT(11) NOT NULL AUTO_INCREMENT, `post_id` INT(11) NOT NULL, `form_attribute_id` INT(11) NOT NULL, `value` VARCHAR(255) NULL, `created` INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
141024 10:03:57	   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `roles` (`name` VARCHAR(50) NOT NULL, `display_name` VARCHAR(50) NOT NULL, `description` VARCHAR(255) NULL, `permissions` VARCHAR(255) NULL, PRIMARY KEY (`name`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `sets` (`id` INT(11) NOT NULL AUTO_INCREMENT, `user_id` INT(11) NULL, `name` VARCHAR(255) NOT NULL, `filter` TEXT NULL, `created` INT(11) NOT NULL DEFAULT 0, `updated` INT(11) NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `tags` (`id` INT(11) NOT NULL AUTO_INCREMENT, `parent_id` INT(11) NULL, `tag` VARCHAR(50) NOT NULL, `slug` VARCHAR(50) NOT NULL, `type` VARCHAR(20) NOT NULL DEFAULT 'category' COMMENT 'category, status', `color` VARCHAR(6) NULL, `icon` VARCHAR(20) NOT NULL DEFAULT 'tag', `description` TEXT NULL, `role` VARCHAR(255) NULL, `priority` INT(11) NOT NULL DEFAULT 99, `created` INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `tasks` (`id` INT(11) NOT NULL AUTO_INCREMENT, `parent_id` INT(11) NULL, `post_id` INT(11) NULL, `assignee` INT(11) NULL, `assignor` INT(11) NULL, `description` VARCHAR(255) NOT NULL, `status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, complete, later', `due` INT(11) NULL, `created` INT(11) NOT NULL DEFAULT 0, `updated` INT(11) NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	CREATE TABLE `users` (`id` INT(11) NOT NULL AUTO_INCREMENT, `email` VARCHAR(150) NULL, `realname` VARCHAR(150) NULL, `username` VARCHAR(50) NULL, `password` VARCHAR(255) NULL, `role` VARCHAR(50) NOT NULL DEFAULT 'user', `logins` INT(11) NOT NULL DEFAULT 0, `failed_attempts` INT(11) NOT NULL DEFAULT 0, `last_login` INT(11) NULL, `last_attempt` INT(11) NULL, `created` INT(11) NOT NULL DEFAULT 0, `updated` INT(11) NULL, PRIMARY KEY (`id`),  UNIQUE KEY (`email`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
 
		   21 Query	SELECT 1
 
		   21 Query	COMMIT
 
		   21 Query	SELECT 1
 
		   21 Query	INSERT INTO phinxlog (version, start_time, end_time) VALUES ("20140716082651","2014-10-24 15:03:56","2014-10-24 15:03:57")
 
		   21 Query	SELECT 1
 
		   21 Query	START TRANSACTION
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'contacts'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'contacts'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `contacts` ADD  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'forms'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'forms'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `forms` ADD  FOREIGN KEY (`parent_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'form_groups'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'form_groups'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `form_groups` ADD  FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'form_groups_form_attributes'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'form_groups_form_attributes'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `form_groups_form_attributes` ADD  FOREIGN KEY (`form_group_id`) REFERENCES `form_groups` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'form_groups_form_attributes'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'form_groups_form_attributes'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `form_groups_form_attributes` ADD  FOREIGN KEY (`form_attribute_id`) REFERENCES `form_attributes` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'messages'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'messages'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `messages` ADD  FOREIGN KEY (`parent_id`) REFERENCES `messages` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'posts'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'posts'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `posts` ADD  FOREIGN KEY (`parent_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'posts'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'posts'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `posts` ADD  FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'posts'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'posts'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `posts` ADD  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'posts_media'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'posts_media'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `posts_media` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'posts_media'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'posts_media'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `posts_media` ADD  FOREIGN KEY (`media_id`) REFERENCES `media` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'posts_sets'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'posts_sets'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `posts_sets` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'posts_sets'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'posts_sets'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `posts_sets` ADD  FOREIGN KEY (`set_id`) REFERENCES `sets` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_comments'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_comments'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `post_comments` ADD  FOREIGN KEY (`parent_id`) REFERENCES `post_comments` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_comments'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_comments'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `post_comments` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_datetime'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_datetime'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `post_datetime` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_decimal'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_decimal'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `post_decimal` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_geometry'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_geometry'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `post_geometry` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_int'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_int'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `post_int` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_point'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_point'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `post_point` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_text'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_text'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `post_text` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_varchar'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_varchar'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `post_varchar` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'sets'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'sets'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `sets` ADD  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'tags'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'tags'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `tags` ADD  FOREIGN KEY (`parent_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'tasks'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'tasks'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `tasks` ADD  FOREIGN KEY (`parent_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'media'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'media'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `media` ADD  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'messages'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'messages'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `messages` ADD  FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'messages'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'messages'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `messages` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_comments'
 
		   21 Query	SELECT 1
 
		   21 Query	SELECT TABLE_NAME
 
            FROM INFORMATION_SCHEMA.TABLES
 
            WHERE TABLE_SCHEMA = 'ushahidi' AND TABLE_NAME = 'post_comments'
 
		   21 Query	SELECT 1
 
		   21 Query	ALTER TABLE `post_comments` ADD  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE



 Comments   
Comment by Elena Stepanova [ 2014-10-24 ]

Hi,

If it's easily reproducible, it's very simple to log the exact sequence.
Add general_log=1 in your cnf file, or run SET GLOBAL general_log=1 before running the statements. (Optionally, also run SET GLOBAL general_log_file=<path to file> if you want to specify the exact location. Then run your migration script. After the server crashes, please attach the general log (it will be tiny if there are only several statements), and also your cnf file(s) or output of SHOW VARIABLES from the server.

You'll probably want to set general_log back to 0 afterwards, since on a busy server it can affect performance and use a lot of disk space.

Thanks.

Comment by Woody Gilk [ 2014-10-24 ]

Elena, thanks for the tip. I've edited my original description to include the general log. The crash almost always happens with this final post_comments table statement, though sometimes it will make it a query or two further before crashing on a post_sets table statement.

Comment by Elena Stepanova [ 2014-10-24 ]

Thanks.

Could you please attach your cnf file(s) or output of SHOW VARIABLES? If you can, please also upload the schema dump to our FTP server (ftp.askmonty.org/private – only MariaDB developers will have access to it). If it's too confidential to be uploaded, please at least paste output of SHOW CREATE TABLE and SHOW INDEX IN for post_comments, users, post_sets and whatever post_sets references to.

Does the crash happen if you execute the single ALTER statement?

Comment by Woody Gilk [ 2014-10-24 ]

Export of database immediately after signal 11 crash, while adding foreign key for posts_sets.post_id -> posts.id.

Comment by Woody Gilk [ 2014-10-24 ]

Complete export of database at time of crash attached. I am not using any custom config files, the entirety of my.cnf is:

#
 
# This group is read both both by the client and the server
 
# use it for options that affect everything
 
#
 
[client-server]
 
 
 
#
 
# include all files from the config directory
 
#
 
!includedir /usr/local/etc/my.cnf.d

(There are no files in /usr/local/etc/my.cnf.d/)

Running the same ALTER statement directly after the failure does not crash, but the very next query (anything except SELECT) will sometimes crash... but this is intermittent and I can't reliably reproduce it.

Comment by Woody Gilk [ 2014-10-24 ]

Based on a suggestion by someone in the #maria IRC channel, I tried adding a native-innodb.cnf configuration (to my.cnf.d/):

[mysqld]
 
ignore_builtin_innodb
 
plugin_load=innodb=ha_innodb.so

Then I restarted mysql, dropped the database, created a fresh db, and ran the same migration script. It crashed in exactly the same way, so it does not appear to be an issue with xtradb vs innodb.

Comment by Elena Stepanova [ 2014-10-26 ]

Thanks a lot, I could reproduce it with the provided data.

Comment by Elena Stepanova [ 2014-10-26 ]

My further digging has shown that we inherited the problem with the InnoDB 5.6.20 merge:

revno: 4390 [merge]
 
revision-id: sergii@pisem.net-20140911144254-e3pisue7khj2bykf
 
parent: jplindst@mariadb.org-20140911124130-pdrgh7b3kznknv34
 
parent: sergii@pisem.net-20140911081335-wl7b54v8snasxuvh
 
committer: Sergei Golubchik <sergii@pisem.net>
 
branch nick: 10.0
 
timestamp: Thu 2014-09-11 16:42:54 +0200
 
message:
 
  InnoDB 5.6.20
 
    ------------------------------------------------------------
 
    revno: 0.49.6
 
    revision-id: sergii@pisem.net-20140911081335-wl7b54v8snasxuvh
 
    parent: sergii@pisem.net-20140609161600-0slb9phos972eilh
 
    committer: Sergei Golubchik <sergii@pisem.net>
 
    branch nick: innodb-5.6
 
    timestamp: Thu 2014-09-11 10:13:35 +0200
 
    message:
 
      5.6.20

It was further mainly fixed in MySQL 5.6.21 by this laconically commented commit:

revno: 6129
 
revision-id: mysql-build@oss.oracle.com-20140908103348-estkb1rqjxrn2clm
 
parent: mysql-builder@oracle.com-20140904054516-614wj4sigi85oazn
 
committer: MySQL Build Team <mysql-build@oss.oracle.com>
 
branch nick: mysql-5.6.21-release
 
timestamp: Mon 2014-09-08 12:33:48 +0200
 
message:
 
  bug19471516 for mysql-5.6

So it should be fixed by the next InnoDB merge.
However, there is still a valgrind warning with this and similar scenarios on MySQL 5.6.21, although maybe unrelated to the problem at hand. I filed it as http://bugs.mysql.com/bug.php?id=74570

It is also important to note that, if I'm right about the revision in mysql-5.6 that fixes it, the test that comes with it does not cause a failure on 10.0 (even with valgrind), so here is one that does:

--source include/have_innodb.inc
 
 
 
CREATE TABLE `posts` (
 
  `id` int(11) NOT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB;
 
 
 
CREATE TABLE `posts_media` (
 
  `post_id` int(11) NOT NULL,
 
  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
 
) ENGINE=InnoDB;
 
 
 
CREATE TABLE `posts_sets` (
 
  `post_id` int(11) NOT NULL,
 
  `set_id` int(11) NOT NULL,
 
  PRIMARY KEY (`post_id`,`set_id`)
 
) ENGINE=InnoDB;
 
 
 
CREATE TABLE `post_comments` (
 
  `id` int(11) NOT NULL,
 
  `parent_id` int(11) DEFAULT NULL
 
) ENGINE=InnoDB;
 
 
 
CREATE TABLE `post_point` (
 
  `post_id` int(11) NOT NULL
 
) ENGINE=InnoDB;
 
 
 
CREATE TABLE `sets` (
 
  `id` int(11) NOT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB;
 
 
 
ALTER TABLE `posts_sets` ADD FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
 
ALTER TABLE `posts_sets` ADD FOREIGN KEY (`set_id`) REFERENCES `sets` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
 
--error ER_CANT_CREATE_TABLE,ER_CANNOT_ADD_FOREIGN
 
ALTER TABLE `post_comments` ADD  FOREIGN KEY (`parent_id`) REFERENCES `post_comments` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
 
ALTER TABLE `post_point` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

Assertion failure on 10.0 revno 4451

 
InnoDB: Failing assertion: ret.second
 
InnoDB: We intentionally generate a memory trap.
 
 
 
#5  0x00007fdcbbcee7c0 in *__GI_abort () at abort.c:92
 
#6  0x00007fdcb42e38ab in dict_foreign_add_to_referenced_table::operator() (this=0x7fdcbdc5e0d0, foreign=0x7fdca90b5678) at 10.0/storage/innobase/include/dict0mem.h:1147
 
#7  0x00007fdcb42e40a1 in std::for_each<std::_Rb_tree_const_iterator<dict_foreign_t*>, dict_foreign_add_to_referenced_table> (__first=..., __last=..., __f=...) at /usr/include/c++/4.7/bits/stl_algo.h:4442
 
#8  0x00007fdcb42dde4f in dict_create_foreign_constraints_low (trx=0x7fdca9154c78, heap=0x7fdca933a000, cs=0x17814c0, sql_string=0x7fdca9290e78 "ALTER TABLE `post_point` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT", name=0x7fdcbdc60280 "test/#sql-7d66_3", reject_fks=0) at 10.0/storage/innobase/dict/dict0dict.cc:4314
 
#9  0x00007fdcb42df1fb in dict_create_foreign_constraints (trx=0x7fdca9154c78, sql_string=0x7fdca921d088 "ALTER TABLE `post_point` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT", sql_length=116, name=0x7fdcbdc60280 "test/#sql-7d66_3", reject_fks=0) at 10.0/storage/innobase/dict/dict0dict.cc:4814
 
#10 0x00007fdcb444725e in row_table_add_foreign_constraints (trx=0x7fdca9154c78, sql_string=0x7fdca921d088 "ALTER TABLE `post_point` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT", sql_length=116, name=0x7fdcbdc60280 "test/#sql-7d66_3", reject_fks=0) at 10.0/storage/innobase/row/row0mysql.cc:2527
 
#11 0x00007fdcb435c4ab in ha_innodb::create (this=0x7fdca9337088, name=0x7fdcbdc6302c "./test/#sql-7d66_3", form=0x7fdcbdc61190, create_info=0x7fdcbdc63420) at 10.0/storage/innobase/handler/ha_innodb.cc:10073
 
#12 0x0000000000870ed0 in handler::ha_create (this=0x7fdca9337088, name=0x7fdcbdc6302c "./test/#sql-7d66_3", form=0x7fdcbdc61190, info=0x7fdcbdc63420) at 10.0/sql/handler.cc:4285
 
#13 0x0000000000871dad in ha_create_table (thd=0x7fdcb59f0070, path=0x7fdcbdc6302c "./test/#sql-7d66_3", db=0x7fdca921d828 "test", table_name=0x7fdcbdc627c0 "#sql-7d66_3", create_info=0x7fdcbdc63420, frm=0x7fdcbdc63240) at 10.0/sql/handler.cc:4654
 
#14 0x0000000000731123 in mysql_alter_table (thd=0x7fdcb59f0070, new_db=0x7fdca921d828 "test", new_name=0x0, create_info=0x7fdcbdc63420, table_list=0x7fdca921d250, alter_info=0x7fdcbdc63390, order_num=0, order=0x0, ignore=false) at 10.0/sql/sql_table.cc:8859
 
#15 0x0000000000798bdc in Sql_cmd_alter_table::execute (this=0x7fdca921da58, thd=0x7fdcb59f0070) at 10.0/sql/sql_alter.cc:312
 
#16 0x0000000000682797 in mysql_execute_command (thd=0x7fdcb59f0070) at 10.0/sql/sql_parse.cc:5077
 
#17 0x000000000068583d in mysql_parse (thd=0x7fdcb59f0070, rawbuf=0x7fdca921d088 "ALTER TABLE `post_point` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT", length=116, parser_state=0x7fdcbdc64630) at 10.0/sql/sql_parse.cc:6406
 
#18 0x0000000000678652 in dispatch_command (command=COM_QUERY, thd=0x7fdcb59f0070, packet=0x7fdcb06aa071 "ALTER TABLE `post_point` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT", packet_length=116) at 10.0/sql/sql_parse.cc:1299
 
#19 0x00000000006779f7 in do_command (thd=0x7fdcb59f0070) at 10.0/sql/sql_parse.cc:996
 
#20 0x0000000000794312 in do_handle_one_connection (thd_arg=0x7fdcb59f0070) at 10.0/sql/sql_connect.cc:1379
 
#21 0x0000000000794065 in handle_one_connection (arg=0x7fdcb59f0070) at 10.0/sql/sql_connect.cc:1293
 
#22 0x0000000000cc9e56 in pfs_spawn_thread (arg=0x7fdcb06a20f0) at 10.0/storage/perfschema/pfs.cc:1860
 
#23 0x00007fdcbd89db50 in start_thread (arg=<optimized out>) at pthread_create.c:304

Stack trace from 10.0.14

 
#2  <signal handler called>
 
#3  __strcmp_ssse3 () at ../sysdeps/x86_64/multiarch/../strcmp.S:214
 
#4  0x00007fd56fc4be84 in ut_strcmp (str2=0x736f706020454c42 <Address 0x736f706020454c42 out of bounds>, str1=0x7fd5655aa868 "test/#sql-7dbb_3_ibfk_1") at 10.0.14/storage/innobase/include/ut0mem.ic:98
 
#5  operator() (this=<optimized out>, lhs=<optimized out>, rhs=<optimized out>) at 10.0.14/storage/innobase/include/dict0mem.h:723
 
#6  std::_Rb_tree<dict_foreign_t*, dict_foreign_t*, std::_Identity<dict_foreign_t*>, dict_foreign_compare, std::allocator<dict_foreign_t*> >::_M_insert_unique (this=0x7fd5655aa688, __v=@0x7fd5798be5b8: 0x7fd56540d668) at /usr/include/c++/4.7/bits/stl_tree.h:1285
 
#7  0x00007fd56fc4a9bd in insert (__x=@0x7fd5798be5b8: 0x7fd56540d668, this=<optimized out>) at /usr/include/c++/4.7/bits/stl_set.h:415
 
#8  operator() (foreign=0x7fd56540d668, this=<optimized out>) at 10.0.14/storage/innobase/include/dict0mem.h:1146
 
#9  for_each<std::_Rb_tree_const_iterator<dict_foreign_t*>, dict_foreign_add_to_referenced_table> (__f=..., __last=..., __first=...) at /usr/include/c++/4.7/bits/stl_algo.h:4442
 
#10 dict_create_foreign_constraints_low (trx=trx@entry=0x7fd565425868, heap=heap@entry=0x7fd565657000, cs=<optimized out>, sql_string=sql_string@entry=0x7fd56540d2e8 "ALTER TABLE `post_point` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT", name=name@entry=0x7fd5798c0810 "test/#sql-7dbb_3", reject_fks=reject_fks@entry=0) at 10.0.14/storage/innobase/dict/dict0dict.cc:4314
 
#11 0x00007fd56fc4ba9a in dict_create_foreign_constraints (trx=0x7fd565425868, sql_string=<optimized out>, sql_length=<optimized out>, name=0x7fd5798c0810 "test/#sql-7dbb_3", reject_fks=0) at 10.0.14/storage/innobase/dict/dict0dict.cc:4814
 
#12 0x00007fd56fd2ccf3 in row_table_add_foreign_constraints (trx=0x7fd565425868, sql_string=0x7fd5655a7020 "ALTER TABLE `post_point` ADD  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT", sql_length=116, name=0x7fd5798c0810 "test/#sql-7dbb_3", reject_fks=<optimized out>) at 10.0.14/storage/innobase/row/row0mysql.cc:2527
 
#13 0x00007fd56fca7cc8 in ha_innodb::create (this=<optimized out>, name=<optimized out>, form=0x7fd5798c1770, create_info=<optimized out>) at 10.0.14/storage/innobase/handler/ha_innodb.cc:10073
 
#14 0x00000000007095d3 in handler::ha_create (this=0x7fd565663020, name=0x7fd5798c3a1c "./test/#sql-7dbb_3", form=form@entry=0x7fd5798c1770, info=info@entry=0x7fd5798c3d20) at 10.0.14/sql/handler.cc:4284
 
#15 0x0000000000709f3b in ha_create_table (thd=0x7fd572bcc008, path=<optimized out>, db=0x7fd5655a77c0 "test", table_name=0x7fd5798c31b0 "#sql-7dbb_3", create_info=0x7fd5798c3d20, frm=<optimized out>) at 10.0.14/sql/handler.cc:4653
 
#16 0x000000000064a3e4 in mysql_alter_table (thd=0x7fd572bcc008, new_db=<optimized out>, new_name=<optimized out>, create_info=0x7fd5798c3d20, table_list=0x7fd5655a71e8, alter_info=0x7fd5798c3c90, order_num=0, order=0x0, ignore=false) at 10.0.14/sql/sql_table.cc:8858
 
#17 0x00000000006856f3 in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x7fd572bcc008) at 10.0.14/sql/sql_alter.cc:312
 
#18 0x00000000005d457f in mysql_execute_command (thd=thd@entry=0x7fd572bcc008) at 10.0.14/sql/sql_parse.cc:5086
 
#19 0x00000000005da53c in mysql_parse (parser_state=0x7fd5798c6160, thd=0x7fd572bcc008, rawbuf=<optimized out>, length=<optimized out>) at 10.0.14/sql/sql_parse.cc:6415
 
#20 mysql_parse (thd=0x7fd572bcc008, rawbuf=<optimized out>, length=116, parser_state=0x7fd5798c6160) at 10.0.14/sql/sql_parse.cc:6336
 
#21 0x00000000005dbc05 in dispatch_command (command=COM_QUERY, thd=0x7fd572bcc008, packet=<optimized out>, packet_length=<optimized out>) at 10.0.14/sql/sql_parse.cc:1307
 
#22 0x0000000000682b7d in do_handle_one_connection (thd_arg=thd_arg@entry=0x7fd572bcc008) at 10.0.14/sql/sql_connect.cc:1379
 
#23 0x0000000000682c40 in handle_one_connection (arg=arg@entry=0x7fd572bcc008) at 10.0.14/sql/sql_connect.cc:1293
 
#24 0x00000000009d0e74 in pfs_spawn_thread (arg=0x7fd576c2d808) at 10.0.14/storage/perfschema/pfs.cc:1860
 
#25 0x00007fd579500b50 in start_thread (arg=<optimized out>) at pthread_create.c:304

Assigning to serg to wait for the merge, check that it fixes the problem and maybe add the test case.

Comment by Elena Stepanova [ 2014-11-22 ]

As already said in a comment to MDEV-7135, the problem disappeared with this revision:

revno: 4464
 
revision-id: jplindst@mariadb.org-20141027145816-cuwsbq6d5yber97u
 
parent: jplindst@mariadb.org-20141027090317-e30s66to4mjj4jig
 
committer: Jan Lindström <jplindst@mariadb.org>
 
branch nick: 10.0
 
timestamp: Mon 2014-10-27 16:58:16 +0200
 
message:
 
  MDEV-6759: innodb valgrind failures
 
  
  Fix failure seen on dict_foreign_remove_partial().

and it didn't reappear after the InnoDB merge, so I'm closing it as fixed.
Although, it would be good to add a test case for that

Generated at Thu Feb 08 07:15:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.