Bug #101677 Add primary key error: error 1265 (01000): Data truncated for column
Submitted: 19 Nov 2020 10:34 Modified: 20 Nov 2020 7:09
Reporter: yang li Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: Data truncated for column, error 1265 (01000)

[19 Nov 2020 10:34] yang li
Description:
I want to add primary key , but report errors:error 1265 (01000): Data truncated for column

my  sql_mod:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

How to repeat:
CREATE TABLE `c` (
  `id` int DEFAULT NULL,
  `d` int(11) NOT NULL,
  `f` int(11) NOT NULL,
  `h` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

insert into c select null,1,1,1;
insert into c select null,2,2,2;
insert into c select null,3,3,3;
mysql> alter table c modify id int primary key;
ERROR 1265 (01000): Data truncated for column 'id' at row 1
mysql> alter table c add primary key(id),ALGORITHM=COPY;
ERROR 1265 (01000): Data truncated for column 'id' at row 1
[19 Nov 2020 10:40] yang li
Description of official documents:
When you add a primary key using the ALGORITHM=COPY clause, MySQL converts NULL values in the associated columns to default values: 0 for numbers, an empty string for character-based columns and BLOBs, and 0000-00-00 00:00:00 for DATETIME. This is a non-standard behavior that Oracle recommends you not rely on. Adding a primary key using ALGORITHM=INPLACE is only permitted when the SQL_MODE setting includes the strict_trans_tables or strict_all_tables flags; when the SQL_MODE setting is strict, ALGORITHM=INPLACE is permitted, but the statement can still fail if the requested primary key columns contain NULL values. The ALGORITHM=INPLACE behavior is more standard-compliant.
[19 Nov 2020 13:38] MySQL Verification Team
Hi Mr. li,

Thank you for your bug report.

However, this is not a bug.

The documentation that you have quoted is correct, but your DDL operation fails because 0 (zero) is not a valid value for the auto_increment primary index of the integer type. Hence, evidently NULL columns can not be converted into the auto_increment ones, with any of the two algorithms.

Your report could be used to make a new feature request. The new feature would use a column containing only  NULLs or zeros to generate a sequence of auto_increment values for the newly created primary key. New features can not be added to older GA versions.

Let us know whether this would be acceptable to you .......
[20 Nov 2020 7:09] yang li
Thank you very much for your reply.
[20 Nov 2020 12:49] MySQL Verification Team
You are truly welcome ......