Bug #81002 Concurrent DML via LOCK=NONE does not error on tables with foreign keys.
Submitted: 8 Apr 2016 3:00 Modified: 19 Apr 2016 19:02
Reporter: Derek Downey Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6.29 OS:Any
Assigned to: CPU Architecture:Any
Tags: foreign keys

[8 Apr 2016 3:00] Derek Downey
Description:
Using LOCK=NONE on a child table (table that contains a foreign key constraint) should return an error. Instead it allows the statement but blocks until transactions are committed.

From the documentation regarding FK limitations of Online DDL:

"Concurrent DML is disallowed during online DDL operations on such child tables. (This restriction is being evaluated as a bug and might be lifted.)"

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

This means that LOCK=NONE should not be allowed, if explicitly called. If LOCK=NONE is not allowed, there is an error called:

"LOCK = NONE
If supported, permit concurrent reads and writes. Otherwise, return an error message.
"
src: https://dev.mysql.com/doc/refman/5.6/en/alter-table.html

How to repeat:
Create schema:

CREATE TABLE foo (
  id int unsigned PRIMARY KEY auto_increment
);

CREATE TABLE bar (
  id int unsigned PRIMARY KEY auto_increment,
  foo_id int unsigned,
  FOREIGN KEY (foo_id) 
    REFERENCES foo(id)
    ON DELETE CASCADE
);

INSERT INTO bar (foo_id) VALUES (1);

===

In second session, open a transaction to read from table:
  SET autocommit=0;
  SELECT * FROM bar;

In first session, attempt to alter the table with online DDL:

  ALTER TABLE `bar`
  ADD COLUMN `foobar` tinyint,
  ALGORITHM=INPLACE, LOCK=NONE;

This will block until second transaction is committed or rolled back.

Suggested fix:
Return an error if LOCK=NONE is not supported on foreign key tables (either parent or child)
[8 Apr 2016 12:32] Derek Downey
Actually, after some sleep and some coffee, the 'how to repeat section' only shows that MDL locks are holding up the operation, not that concurrent DML is being blocked or disallowed.

Reduced severity to S3. 

Further tests show that I am able to do concurrent inserts during the operation to add a column, so it appears concurrent DML is allowed on an online DDL to add a column to the table. 

If this is the case, the documentation regarding concurrent DML on tables containing FKs is unclear (or outdated).
[11 Apr 2016 13:39] MySQL Verification Team
Hi Derek,

I have done some tests and I agree with you. This is not properly documented.

Verified as a documentation bug.
[19 Apr 2016 19:02] Daniel Price
Posted by developer:
 
Verified support for concurrent DML in this scenario back to an earlier version of 5.6. The problematic information in the Online DDL documentation was removed. The change should appear online soon.
Thank you for the bug report.