MySQL Bugs: #112991: mysqlsh checkForServerUpgrade doesn't identify old temporal types correctly

Bug #112991	mysqlsh checkForServerUpgrade doesn't identify old temporal types correctly
Submitted:	7 Nov 2023 13:33	Modified:	1 Mar 2024 12:33
Reporter:	Kedar Vaijanapurkar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell Upgrade Checker	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysqlsh, mysqlshell, upgrade checker

Description:
MySQL Upgrade Checker utility function doesn't correctly identify the old temporal types.

The query it uses is:
          "SELECT table_schema, table_name,column_name,column_type "
          "FROM information_schema.columns WHERE column_type LIKE "
          "'timestamp /* 5.5 binary format */';"},

While it literally ignores the date/datetime/time columns. If we do not manually check it for upgrade, this risks people to believe in they're ready for upgrade.

The error you receive after upgrade would be

```2023-11-07T11:18:57.869756Z 3 [ERROR] [MY-010923] [Server] Table upgrade required. Please do "REPAIR TABLE `xyz`" or dump/reload to fix it!```

The only way after this will be a rollback to MySQL 5.7 and fix the tables listed.

The mysqlshell's checkForServerUpgrade function needs a fixture in the query as 

          "SELECT table_schema, table_name,column_name,column_type "
          "FROM information_schema.columns WHERE column_type LIKE "
          "'%5.5 binary format%';"},

https://github.com/mysql/mysql-shell/blob/0b6abe8b264be694d7491e9c2f1da4ca6fcc35bf/modules...

Note that this doesn't get identified by mysql_upgrade or mysqlcheck.

Until this is fixed one must manually verify and fix this before upgrading:

set show_old_temporals=ON;
SELECT concat('REPAIR TABLE ',table_schema,'.', table_name,';') FROM information_schema.columns WHERE column_type LIKE '%5.5 binary format%';

How to repeat:
Run mysqlsh's util.checkForServerUpgrade against tables having old temporal for datetime column for example.

Suggested fix:
Fix the query
https://github.com/mysql/mysql-shell/blob/0b6abe8b264be694d7491e9c2f1da4ca6fcc35bf/modules...

          "SELECT table_schema, table_name,column_name,column_type "
          "FROM information_schema.columns WHERE column_type LIKE "
          "'%5.5 binary format%';"},

Hi Mr. Kedar,

Thank you for your bug report.

Can you provide us with a full CREATE TABLE statement, on which you get an error.

Also, we hope that you are not upgrading from 5.5 to 8.0. You should upgrade first to 5.6, then to 5.7 and then to 8.0.

Waiting on your feedback.

Hi Mr. Kedar,

This is not a bug.

LIKE is used in this query, just like in any other. LIKE is searching for a string which can include wild cards.

 It is a query like any other. It will NOT return SIMILAR types.

Hence, use something like this:

REATE TABLE t1 (
  `id_col` int NOT NULL DEFAULT '0',
  `tinyint_col` tinyint NOT NULL,
  `date_col` date NOT NULL,
  `datetime_col` datetime(6) NOT NULL,
  `timestamp_col` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `time_col` time  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT table_schema, table_name,column_name,column_type FROM information_schema.columns WHERE table_name='t1' AND column_type LIKE "timestamp%";

SELECT table_schema, table_name,column_name,column_type FROM information_schema.columns WHERE table_name='t1' AND  column_type LIKE  "date%";

SELECT table_schema, table_name,column_name,column_type FROM information_schema.columns WHERE table_name='t1' AND column_type LIKE  "datetime%";

Hello Team,

Thanks for your response. The bug here is that the checkForServerUpgrade function does not correctly identify the tables needing report.

Steps:
- Run checkForServerUpgrade
- Fix issues
- Upgrade to 8.0
- Start mysql
- Face error: 
2023-11-07T11:18:57.869756Z 3 [ERROR] [MY-010923] [Server] Table upgrade required. Please do "REPAIR TABLE `xyz`" or dump/reload to fix it!
- This happens because checkForServerUpgrade doesn't correctly identify the table needs an upgrade.

IMHO this is a bug because the query used by the mysqlsh function is limiting the search to "timestamp" only. Can you confirm if this query here will identify all the columns needing an upgrade?

https://github.com/mysql/mysql-shell/blob/0b6abe8b264be694d7491e9c2f1da4ca6fcc35bf/modules...

(Attaching required information in a separate comment / file.)

Thanks,
K

Required information

Attachment: 112991.txt (text/plain), 9.89 KiB.

Hi,

Yes, we confirm that the query will identify all columns correctly.

Hi Kedar,

Thank you for your report. Verified as described.

Hello Team,

Back from vacation and I still need you to review the following.

About "we confirm that the query will identify all columns correctly", I have this usecase:

...

db8 (none)> select version();
+---------------+
| version()     |
+---------------+
| 5.7.24-27-log |
+---------------+
1 row in set (0.00 sec)

db8 (none)> set show_old_temporals=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

# The query from code returns only 1 table with temporal types
db8 (none)> SELECT count(*) FROM information_schema.columns WHERE column_type LIKE 'timestamp /* 5.5 binary format */';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.92 sec)

# If we extend the LIKE to use wildcard compare (include any column with that comment) then
db8 (none)> SELECT count(*) FROM information_schema.columns WHERE column_type LIKE '%/* 5.5 binary format */';
+----------+
| count(*) |
+----------+
|      269 |
+----------+
1 row in set (0.99 sec)

You wonder why there are 269 counts??? Because it is not only TIMESTAMP.

db8 (none)>  SELECT column_type, count(*) FROM information_schema.columns WHERE column_type LIKE '%/* 5.5 binary format */' group by 1;
+-----------------------------------+----------+
| column_type                       | count(*) |
+-----------------------------------+----------+
| datetime /* 5.5 binary format */  |      267 |
| time /* 5.5 binary format */      |        1 |
| timestamp /* 5.5 binary format */ |        1 |
+-----------------------------------+----------+

We have "DATETIME" and "TIME" columns that also qualifies by the temporal check which is being missed by the get_old_temporal_check()...... and thus when we use util.checkForServerUpgrade, it completely misses temporal types on these dataypes. My bug report is about that part. 

Let me know if I am clear enough.

Thanks,
K

Posted by developer:
 
Added the following note to the MySQL Shell 8.0.37 and 8.4.0 release notes:
	
  The upgrade checker utility did not check for all old temporal types. 
  Under certain circumstances, this could result in an upgrade failure. 

Thank you for the bug report.

Thank you Mr. Gilmore.

Hello Team,

Since we have verified, isn't the fix of this bug is to fix the query to only compare with '%5.5 binary format%'?

Thanks