MySQL Community Server 8.0.33
through MySQL guide(https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-primary-key-operations) , it is possible to add primary key using the ALGORITHM=COPY
clause, MySQL converts NULL
values in the associated columns to default values.
But, when i do this operation, it is not working. I forgot something?
> show create table test_pk;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------+
| test_pk | CREATE TABLE `test_pk` (
`p` bigint DEFAULT NULL,
`a` **char**(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------+
1
row in set (0.00
sec)
> **select**
* **from**
test_pk;
+------+---+
| p | a |
+------+---+
| 1 | a |
| NULL
| b |
| NULL
| c |
| NULL
| d |
| NULL
| e |
+------+---+
> alter table test_pk add primary key (a,p), algorithm=copy;
ERROR 1265
(01000): Data truncated **for**
column 'p'
at row 2
> show variables like 'sql_mode';
+---------------+--------------------------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------------------------+
1
row in set (0.01
sec)
THANKS.