MySQL Bugs: #101677: Add primary key error: error 1265 (01000): Data truncated for column

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)

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

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.

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

Thank you very much for your reply.

You are truly welcome ......