MySQL Bugs: #93101: Failed to Populate DD tables when table has OLD-Style DATETIME column (need Doc)

Bug #93101	Failed to Populate DD tables when table has OLD-Style DATETIME column (need Doc)
Submitted:	7 Nov 2018 6:21	Modified:	11 Mar 2019 22:01
Reporter:	Tsubasa Tanaka (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0.13	OS:	CentOS (6.9)
Assigned to:		CPU Architecture:	x86
Tags:	--avoid-temporal-upgrade, --upgrade-system-tables, mysql_upgrade

Description:
1. Create DATETIME column in 5.5 or earlier.
2. Inplace upgrade MySQL to 5.6.
3. Inplace upgrade to 5.7, without rebuilding OLD-Style DATETIME column
  - `mysqld --avoid-temporal-upgrade` or `mysql_upgrade --upgrade-system-tables`
4. Try to inplace upgrade to 8.0, mysqld fails to start with MY-010022 "Failed to Populate DD tables"

I can't decide this is expected behavior or not(See suggested fix too)

How to repeat:
### Setup MySQL repo
yum install -y https://dev.mysql.com/get/mysql80-community-release-el6-1.noarch.rpm

### Installing 5.5 and CREATE TABLE
yum install -y --disablerepo="mysql*" --enablerepo="mysql55-community" mysql-community-server
/etc/init.d/mysqld start
mysql -e "CREATE DATABASE d1; CREATE TABLE d1.t1 (num int, val varchar(32), dt DATETIME); INSERT INTO d1.t1 VALUES (1, 'one', NOW())"
mysql -e "SHOW CREATE TABLE d1.t1\G SELECT * FROM d1.t1"
/etc/init.d/mysqld stop

### Upgrade to 5.6
yum install -y --disablerepo="mysql*" --enablerepo="mysql56-community" mysql-community-server
/etc/init.d/mysqld start
mysql_upgrade
mysql -e "SHOW CREATE TABLE d1.t1\G SELECT * FROM d1.t1"
/etc/init.d/mysqld stop

### Upgrade 5.7 (without user-tables by `--upgrade-system-tables` )
yum install -y --disablerepo="mysql*" --enablerepo="mysql57-community" mysql-community-server
/etc/init.d/mysqld start
mysql_upgrade --upgrade-system-tables
mysql -e "SET show_old_temporals= 1; SHOW CREATE TABLE d1.t1\G SELECT * FROM d1.t1"
/etc/init.d/mysqld stop

### Upgrade 8.0 fails
yum install -y --disablerepo="mysql*" --enablerepo="mysql80-community" mysql-community-server
/etc/init.d/mysqld start
tail /var/log/mysqld.log

Suggested fix:
If this is expected, document should describe these options' risk, such as "If you upgrade MySQL with this option, your environment have a risk of unsupported in future release"
  - https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_avoid_temporal...
  - https://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html#option_mysql_upgrade_upgrade-sy...

If this is unexpected behavior, please change "Category" of this bug report.

Hello Tanaka-San,

Thank you for the report and feedback.

regards,
Umesh

test results

Attachment: 93101.results (application/octet-stream, text), 71.54 KiB.

Posted by developer:
 
The MySQL 8.0 upgrade instructions were updated. 

https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html

"In-place upgrade to MySQL 8.0 is not supported if tables contain old
temporal columns in pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP
columns without support for fractional seconds precision). If your tables
still use the old temporal column format, upgrade them before attempting
an in-place upgrade to MySQL 8.0. For more information, see Changes in MySQL 5.7."

Changes should appear online soon.

Thank you for the bug report.