| Bug #63537 | on delete cascade on parent table + optimize child cause MySQL crash | ||
|---|---|---|---|
| Submitted: | 2 Dec 2011 8:43 | Modified: | 21 Jan 2012 16:00 |
| Reporter: | Antoine Bonamour | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.5.17 | OS: | Linux (CentOS 5 64 Kernel 2.6.18-194.26.1.el5) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | foreign key on delete cascade optimize table crash | ||
[2 Dec 2011 8:43]
Antoine Bonamour
[12 Dec 2011 19:24]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior. Please try with current version 5.5.19 and if problem still exists send us exact output of SHOW CREATE TABLE for both tables, your configuration file and MySQL package name you use (file name you downloaded).
[14 Dec 2011 8:25]
Antoine Bonamour
Hello, Thanks for your answer. I will upgrade to 5.5.19 when this version will be available in the IUS repo we use in production. I've updated my script in production to skip "optimize table" for "queue_exporter_us" This is the only change I made, about 10 days ago. I don't reach any crash in production since I made this change, whereas I reach ~1 crash per day with the last configuration ( same DB usage, about 2000 queries per seconds ) The exact table description are: CREATE TABLE `repository_us` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'private id', `offerId` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'offer id', `status` tinyint(3) unsigned NOT NULL, `batchId` varchar(255) DEFAULT NULL, `comId` int(10) unsigned DEFAULT NULL, `catId` int(10) unsigned DEFAULT NULL, `pid` int(10) unsigned DEFAULT NULL, `title` varchar(255) DEFAULT NULL, `features` text, `metas` text, `creationTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `lastUpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `idx_offerId` (`offerId`) ) ENGINE=InnoDB AUTO_INCREMENT=1660 DEFAULT CHARSET=utf8 MAX_ROWS=100000000 AVG_ROW_LENGTH=4096 CREATE TABLE `queue_exporter_us` ( `id` int(10) unsigned NOT NULL, `process` smallint(5) unsigned DEFAULT NULL, `processTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `priority` tinyint(3) unsigned DEFAULT NULL, `catId` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_order` (`process`,`catId`), CONSTRAINT `queue_exporter_us_ibfk_1` FOREIGN KEY (`id`) REFERENCES `repository_us` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 MAX_ROWS=1000000 AVG_ROW_LENGTH=20 The my.cnf I use is: [mysqld] datadir="/opt/mysql/instances/default/data" socket="/opt/mysql/instances/default/mysql-3306.sock" log-error="/opt/mysql/instances/default/logs/mysqld.log" pid-file="/opt/mysql/instances/default/mysqld-3306.pid" port=3306 server-id=1 user=mysql group=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # Default configuration # Specific configuration for this instance of mysql character_set_server=utf8 collation_server=utf8_general_ci innodb_adaptive_flushing=1 innodb_additional_mem_pool_size=1G innodb_autoinc_lock_mode=2 innodb_buffer_pool_size=16G innodb_change_buffering=all innodb_checksums=1 innodb_commit_concurrency=0 innodb_data_file_path=oq_data01:10M;oq_data02:1M:autoextend:max:50G innodb_doublewrite=1 innodb_file_format=Barracuda innodb_file_per_table=1 innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT innodb_io_capacity=1200 innodb_lock_wait_timeout=10 innodb_locks_unsafe_for_binlog=0 innodb_log_buffer_size=16M innodb_log_file_size=200M innodb_log_files_in_group=3 innodb_max_dirty_pages_pct=16 innodb_max_purge_lag=4294967295 innodb_open_files=1000 innodb_purge_batch_size=5 innodb_purge_threads=1 innodb_support_xa=0 innodb_thread_concurrency=0 innodb_thread_sleep_delay=0 interactive_timeout=600 max_allowed_packet=16M max_connect_errors=20000 max_connections=2000 max_heap_table_size=100M query_cache_size=16M query_cache_type=2 sort_buffer_size=512K table_cache=2048 thread_cache_size=0 wait_timeout=300 When the 5.5.19 will be available in the IUS repo, I'll use it in production. Then, I'll try to reproduce the problem under control. Regards, Antoine.
[19 Dec 2011 12:34]
Valeriy Kravchuk
5.5.19 should be available already...
[19 Dec 2011 12:50]
Antoine Bonamour
Hello, Yes, available in the official IUS repo, but not in our IUS mirror repo ( the one we should use in production ) I'll check with the Infra team to update our mirror repo. I'll keep you inform about this problem. Regards
[20 Dec 2011 9:19]
Antoine Bonamour
Hello, Just one update : our private production yum repo is a mirror of the public http://mirrors.ircam.fr/pub/ius/stable/Redhat/6/x86_64/ This one is a mirror of http://dl.iuscommunity.org/pub/ius/stable/Redhat/6/x86_64/ I'll upgrade MySQL in production when 5.5.19 will be available in those repo. Then I'll try to reproduce the behavior described above. Regards, Antoine.
[22 Jan 2012 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
