MySQL Bugs: #114830: MySQL converts collection of date data type in ibd but data dictionary

Bug #114830	MySQL converts collection of date data type in ibd but data dictionary
Submitted:	1 May 2024 0:51	Modified:	2 May 2024 8:34
Reporter:	Jinyou Ma	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:

When MySQL converts the charset on a table, It converts the date and time data types columns in ibd file. The collation_id in the ibd does not match that of data dicitonary. 

- the table structure is below

mysql> CREATE TABLE test.a (
  a datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_ci;

mysql> select CHARACTER_SET_NAME,COLLATION_NAME from information_schema.columns where TABLE_NAME = 'a';
+--------------------+----------------+
| CHARACTER_SET_NAME | COLLATION_NAME |
+--------------------+----------------+
| NULL               | NULL           |
+--------------------+----------------+

- the collation_id is 8 (latin1_swedish_ci)
shell> ibd2sdi /var/lib/mysql/test/a.ibd | jq '.[1].object.dd_object.columns[0]' | grep collation_id
  "collation_id": 8,

mysql> ALTER TABLE test.a CONVERT TO CHARACTER SET utf8mb4 collate utf8mb4_unicode_ci;

- the collation_id becomes 224 (utf8mb4_unicode_ci)
shell> ibd2sdi /var/lib/mysql/test/a.ibd | jq '.[1].object.dd_object.columns[0]' | grep collation_id
  "collation_id": 224,

mysql> select CHARACTER_SET_NAME,COLLATION_NAME from information_schema.columns where TABLE_NAME = 'a';
+--------------------+----------------+
| CHARACTER_SET_NAME | COLLATION_NAME |
+--------------------+----------------+
| NULL               | NULL           |
+--------------------+----------------+

- after altering table, the collation_id becomes 8 (latin1_swedish_ci) again.
mysql> ALTER TABLE test.a ENGINE = INNODB;

shell> ibd2sdi /var/lib/mysql/test/a.ibd | jq '.[1].object.dd_object.columns[0]' | grep collation_id
  "collation_id": 8,

How to repeat:

create database test;

CREATE TABLE test.a (
  a int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE test.a CONVERT TO CHARACTER SET utf8mb4 collate utf8mb4_unicode_ci;

Hello jin ma,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

Contribution submitted via Github - Bug#114830 MySQL converts collation of date data type in ibd but data dictionary (*) Contribution by Venkatesh Prasad Venugopal (Github venkatesh-prasad-v, mysql-server/pull/547#issuecomment-2151732141): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_1906737391.txt (text/plain), 12.47 KiB.