Bug #72327 ALTER TABLE ... DDL not working

• Submitted: 12 Apr 2014 20:48
• Modified: 12 Sep 2014 18:21
• Reporter: Mikk Tenderamann
• Status: No Feedback
• Category: MySQL Server: DDL
• Severity: S5 (Performance)
• Version: 5.6.17
• OS: Linux (Ubuntu 12.04)
• CPU Architecture: Any
• Tags: ALTER TABLE, DDL, innodb

[12 Apr 2014 20:48] Mikk Tenderamann

Description:
It was advertised, that MySQL 5.6.17 will have DDL support for ALTER TABLE.
I kind of need it(adding hundreds of rows in table per second, and deleting too is quite slow). I bought it, and made ALTER TABLE ... FORCE, and what happened: Table lock

How to repeat:
1. generate millions rows of data
2. delete them in kind of random order
3. optimize table in DDL(works like wonders)
4. ALTER TABLE ... FORCE

Table lock

[12 Apr 2014 20:51] Mikk Tenderamann

changed severity to Performance

[13 Apr 2014 8:20] Mikk Tenderamann

added innodb

[13 Apr 2014 12:47] MySQL Verification Team

Hi Mikk,

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

What is the table structure of the table?

[13 Apr 2014 14:31] Mikk Tenderamann

CREATE TABLE IF NOT EXISTS `frames` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `file` text COLLATE utf8_estonian_ci NOT NULL,
  `cam_id` int(10) unsigned NOT NULL,
  `time` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4),
  `size` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user` (`cam_id`),
  KEY `time` (`time`),
  KEY `CamTimeIndex` (`cam_id`,`time`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_estonian_ci AUTO_INCREMENT=51796962 ;

ALTER TABLE `frames`
  ADD CONSTRAINT `frames_ibfk_1` FOREIGN KEY (`cam_id`) REFERENCES `cameras` (`id`);

[12 Aug 2014 18:21] Sveta Smirnova

Thank you for the report.

According to http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html "Creating a foreign key online requires foreign_key_checks to be disabled. " Have you disabled foreign_key_checks prior adding the constraint?

[13 Sep 2014 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".