| Bug #82717 | ALTER TABLE allows adding NOT NULL BLOB columns | ||
|---|---|---|---|
| Submitted: | 24 Aug 2016 23:48 | Modified: | 25 Aug 2016 17:38 |
| Reporter: | Chris Giard | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.6.27 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[25 Aug 2016 15:13]
MySQL Verification Team
Hi, Thank you for your bug report. However, I can not find any justification for your report. Our manual does not have a single sentence about non-assumption of the default values in the case of the combo of the --innodb-strict-mode and sql_mode=TRADITIONAL. There are references to syntax errors and some non-permissible options, but nowhere does our manual state that default values must be specified with the above combo !!!!
[25 Aug 2016 15:23]
Chris Giard
Hello Sinisa, TRADITIONAL includes STRICT_ALL_TABLES, which has in the description "Enable strict SQL mode for all storage engines. Invalid data values are rejected. For details, see Strict SQL Mode." http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_strict_all_tables. The "Strict SQL Mode" section additionally says "A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition," and "Strict mode also affects DDL statements such as CREATE TABLE."
[25 Aug 2016 17:38]
MySQL Verification Team
Yes, You are right. When you connect all those dots together, this DDL should return the error, while it does not. Verified as reported. However, a low priority bug.
Description: ALTER TABLE allows adding a NOT NULL BLOB column to a table with existing rows, even when innodb_strict_mode=ON and sql_mode='TRADITIONAL'. With those settings set MySQL shouldn't be making any assumptions about a default value, but does in this case, setting the new column to a value of '' for each row. How to repeat: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.27 Homebrew Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show create table a\G *************************** 1. row *************************** Table: a Create Table: CREATE TABLE `a` ( `id` int(11) NOT NULL AUTO_INCREMENT, `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> select * from a; +----+---------------------+------+ | id | updated_at | b | +----+---------------------+------+ | 1 | 2016-08-10 14:04:53 | 2 | +----+---------------------+------+ 1 row in set (0.00 sec) mysql> set session sql_mode='TRADITIONAL'; Query OK, 0 rows affected (0.01 sec) mysql> set session innodb_strict_mode=ON; Query OK, 0 rows affected (0.00 sec) mysql> select @@innodb_strict_mode,@@sql_mode; +----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | @@innodb_strict_mode | @@sql_mode | +----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table a add column c blob not null; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from a; +----+---------------------+------+---+ | id | updated_at | b | c | +----+---------------------+------+---+ | 1 | 2016-08-10 14:04:53 | 2 | | +----+---------------------+------+---+ 1 row in set (0.00 sec) mysql> select * from a where c=''; +----+---------------------+------+---+ | id | updated_at | b | c | +----+---------------------+------+---+ | 1 | 2016-08-10 14:04:53 | 2 | | +----+---------------------+------+---+ 1 row in set (0.00 sec) mysql> ^DBye Suggested fix: Make the ALTER return an error or make BLOB columns support an explicit default.