MySQL Bugs: #36477: adding a primary key changes null field types and data without a warning

Bug #36477	adding a primary key changes null field types and data without a warning
Submitted:	2 May 2008 19:30	Modified:	4 Oct 2013 19:28
Reporter:	Marques Johansson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.0.51a	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Primary keys can't contain null valued fields.  This is documented and understood.

When a primary key is added using fields that are null-able the table format and data is altered to support the new key, rather than giving an error.

How to repeat:
mysql> show create table t;
+-------+-------------------------------------------------------------+
| Table | Create Table                                                |
+-------+-------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `i` int(11) NOT NULL default '0',
  `b` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8                                  | 
+-------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t;
+---+------+
| i | b    |
+---+------+
| 1 | NULL | 
+---+------+

mysql> alter table t add primary key (i,b);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t;
+-------+-------------------------------------------------------------+
| Table | Create Table                                                |
+-------+-------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `i` int(11) NOT NULL default '0',
  `b` int(11) NOT NULL default '0',
  PRIMARY KEY  (`i`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8                                  | 
+-------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t;
+---+---+
| i | b |
+---+---+
| 1 | 0 | 
+---+---+

Suggested fix:
If this behavior is really acceptable a warning should at least be produced, otherwise an error should occur.  I haven't checked what the SQL standards have to say about this.

Thank you for the bug report. Could you please upgrade your server version and
enable the relevant sql_mode. Thanks in advance.

c:\dbs>5.0\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.60-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set sql_mode=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "sql_mode"\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_C
REATE_USER
1 row in set (0.00 sec)

mysql>  CREATE TABLE `t` (
    ->   `i` int(11) NOT NULL default '0',
    ->   `b` int(11) default NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t values (1,null);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t;
+---+------+
| i | b    |
+---+------+
| 1 | NULL |
+---+------+
1 row in set (0.00 sec)

mysql> alter table t add primary key (i,b);
ERROR 1265 (01000): Data truncated for column 'b' at row 1
mysql>

I actually reported this under the wrong version number.  My original report should have said 5.0.51a (Ubuntu Hardy).  Per your suggestion, after executing "set sql_mode=TRADITIONAL", I did get a warning.

In Miguel's 5.0.60 example an error was given, so the response to this situation has been a little inconsistent over time, but is perhaps better now.

I would think that MySQL when used without "set sql_mode=TRADITIONAL", could at least warn when this sort of implicit, non-intuitive, data manipulation is going to occur (see my original example).

mysql> set sql_mode=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)

mysql>  show variables like "sql_mode" \G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
1 row in set (0.00 sec)

mysql> delete from t;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (1,null),(1,1),(2,null),(2,1);
Query OK, 4 rows affected (0.10 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table t add primary key (i,b);Query OK, 4 rows affected, 2 warnings (0.12 sec)
Records: 4  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'b' at row 1 | 
| Warning | 1265 | Data truncated for column 'b' at row 3 | 
+---------+------+----------------------------------------+
2 rows in set (0.00 sec)

Thank you for the feedback.

You should get warning even with empty SQL mode:

mysql> \W
Show warnings enabled.
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            | 
+------------+
1 row in set (0.00 sec)

mysql> 
mysql> CREATE TABLE `t` (
    ->   `i` int(11) NOT NULL default '0',
    ->   `b` int(11) default NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8    ;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> insert into t values(1, null);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> alter table t add primary key (i,b);
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

Warning (Code 1265): Data truncated for column 'b' at row 1

Do you use our binaries? If not, please, try with our binaries.

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".

Note that even with sql_mode set to traditional, a warning or error is given only if the table actually had data with primary key values that were changed from null by the addition of the primary key.

If the primary key columns were previously nullable but there were no nulls in those columns, then MySQL will silently alter the columns to be NOT NULL, regardless of sql_mode.

Also note that the same applies to a CREATE TABLE with a column defined as nullable and then including a PRIMARY KEY specification with that column.  Like the ALTER TABLE, no warning or error is output to indicate that the created table was modified to make the primary key columns NOT NULL.

Thank you for the feedback.

But this behavior is by design: MySQL reports such warnings when modifies data. If there is not data or all rows contain nont-NULL fields, you won't get the warning. This is not a bug.