Bug #106791 ALTER TABLE MODIFY COLUMN reinitialize default properties

• Submitted: 22 Mar 2022 8:45
• Modified: 22 Mar 2022 9:21
• Reporter: STEINMETZ Catherine
• Status: Not a Bug
• Category: MySQL Server: DDL
• Severity: S2 (Serious)
• Version: 8.0.20
• OS: Any
• CPU Architecture: Any

[22 Mar 2022 8:45] STEINMETZ Catherine

Description:
When we modify a column in a table (extend the length of a varchar for exemple), we expect that only the properties listed in the modify clause will be updated. 

But this is not the case. 

In fact we have to list all the properties of the column (default value, not nullable, collation) because if we omit one of them, it will be reinitialized with : 
- no default value, 
- Nullable, 
- collation of the table

How to repeat:
mysql> create table test (a int primary key auto_increment, b varchar(30) not null default 'DEFAULT VALUE');
Query OK, 0 rows affected (0,11 sec)

mysql> desc test;
+-------+-------------+------+-----+---------------+----------------+
| Field | Type        | Null | Key | Default       | Extra          |
+-------+-------------+------+-----+---------------+----------------+
| a     | int         | NO   | PRI | NULL          | auto_increment |
| b     | varchar(30) | NO   |     | DEFAULT VALUE |                |
+-------+-------------+------+-----+---------------+----------------+
2 rows in set (0,00 sec)

mysql> alter table test modify b varchar(40);
Query OK, 0 rows affected (0,07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| a     | int         | NO   | PRI | NULL    | auto_increment |
| b     | varchar(40) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0,00 sec)

mysql>

Suggested fix:
Only the properties listed in the alter command should be modified.

[22 Mar 2022 9:21] MySQL Verification Team

Hello STEINMETZ Catherine,

Thank you for the report and feedback.
Imho this is expected and documented behavior. Quoting from official documentation page -  For column definition changes using CHANGE or MODIFY, the definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward. Suppose that a column col1 is defined as INT UNSIGNED DEFAULT 1 COMMENT 'my column' and you modify the column as follows, intending to change only INT to BIGINT:

ALTER TABLE t1 MODIFY col1 BIGINT;

That statement changes the data type from INT to BIGINT, but it also drops the UNSIGNED, DEFAULT, and COMMENT attributes. To retain them, the statement must include them explicitly:

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';

For data type changes using CHANGE or MODIFY, MySQL tries to convert existing column values to the new type as well as possible.  Please see https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

--  
create table test (a int primary key auto_increment, b varchar(30) not null default 'DEFAULT VALUE');
show create table test;
===================================
CREATE TABLE `test` (
  `a` int NOT NULL AUTO_INCREMENT,
  `b` varchar(30) NOT NULL DEFAULT 'DEFAULT VALUE',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
	
alter table test modify b varchar(40) not null default 'DEFAULT VALUE';
show create table test;
CREATE TABLE `test` (
  `a` int NOT NULL AUTO_INCREMENT,
  `b` varchar(40) NOT NULL DEFAULT 'DEFAULT VALUE',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

regards,
Umesh