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:	22 Apr 15:26
Reporter:	Jinyou Ma (OCA)	Email Updates:
Status:	Closed	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.

Posted by developer:
 
Added the following note to the MySQL Server 9.7.0 release notes:
		
When altering a table's character set, the date and time data
type columns are converted in both the data dictionary and SDI.
However, the new collation_id in SDI did not match that of the
Field object.

As of this release, ALTER TABLE CONVERT TO CHARACTER SET does
not alter the character set for temporal types stored in data
dictionary or SDI. The server always uses my_charset_latin1 
for temporal types in data dictionary and SDI.

Our thanks to Venkatesh Prasad Venugopal and the team at Percona 
for the contribution.