Bug #77501 Manual mentiones strict_all_tables twice in the same phrase

• Submitted: 26 Jun 2015 9:34
• Modified: 20 Jul 2015 13:45
• Reporter: Valeriy Kravchuk
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 5.6
• OS: Any
• CPU Architecture: Any
• Tags: missing manual, NOT NULL, ONLINE ALTER, strict_all_tables

[26 Jun 2015 9:34] Valeriy Kravchuk

Description:
Manual (https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html) says the following about the case of making column NOT NULL:

"When SQL_MODE includes strict_all_tables or strict_all_tables, the operation fails if the column contains any nulls."

Please, correct this. Maybe strict_trans_tables should be there instead of second strcit_all_tables? 

How to repeat:
Read the manual trying ot find out if Online ALTER works for making table NOT NULL.

Suggested fix:
Fix this text. While we are on the topic, please, clarify also if FOREIGN KEYs referring to the column matter at all, See http://bugs.mysql.com/bug.php?id=71508 etc.

[26 Jun 2015 9:49] MySQL Verification Team

Hello Valeriy,

Thank you for the report.

Thanks,
Umesh

[29 Jun 2015 9:22] MySQL Verification Team

Bug #77534 marked as duplicate of this

[20 Jul 2015 13:45] Daniel Price

Posted by developer:
 
The documentation has been updated to state, "When SQL_MODE includes STRICT_ALL_TABLES or STRICT_TRANS_TABLES, 
the operation fails if the column contains any nulls. As of MySQL 5.6.7, the server prohibits changes to 
foreign key columns that have the potential to cause loss of referential integrity. 
For more information, see ALTER TABLE."

The following content was added to the ALTER TABLE documentation as a result of Bug #46599.

"Before MySQL 5.6.7, using ALTER TABLE to change the definition of a foreign key column could cause a loss of referential integrity. For example, changing a foreign key column that contained NULL values to be NOT NULL caused the NULL values to be the empty string. Similarly, an ALTER TABLE IGNORE that removed rows in a parent table could break referential integrity.

As of 5.6.7, the server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. It also prohibits changes to the data type of such columns that may be unsafe. For example, changing VARCHAR(20) to VARCHAR(30) is permitted, but changing it to VARCHAR(1024) is not because that alters the number of length bytes required to store individual values. A workaround is to use ALTER TABLE ... DROP FOREIGN KEY before changing the column definition and ALTER TABLE ... ADD FOREIGN KEY afterward."

Thank you for the bug report.