MySQL Bugs: #94431: Can't upgrade from 5.7 to 8.0 if any database have a hyphen in their name

Bug #94431	Can't upgrade from 5.7 to 8.0 if any database have a hyphen in their name
Submitted:	21 Feb 2019 21:32	Modified:	11 Mar 2019 14:17
Reporter:	Phil Murray	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S2 (Serious)
Version:	8.0.15	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When you try to upgrade an existing server from version 5.7 to version 8.0 and you have any databases (using InnoDB) with a hyphen in their name, the server will fail to start with the following log output:

2018-04-05T00:46:16.352752Z 0 [System] [MY-010116] [Server] /usr/local/libexec/mysqld (mysqld 8.0.14) starting as process 11438
2018-04-05T00:46:23.015757Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2018-04-05T00:46:23.015766Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2018-04-05T00:46:23.015775Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './open2view-nz/FTS_00000000000000da_BEING_DELETED.ibd' OS error: 71
2018-04-05T00:46:23.015790Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `open2view-nz/FTS_00000000000000da_BEING_DELETED` because it could not be opened.
2018-04-05T00:46:23.015810Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2018-04-05T00:46:23.015817Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2018-04-05T00:46:23.015839Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './open2view-nz/FTS_00000000000000da_BEING_DELETED_CACHE.ibd' OS error: 71
2018-04-05T00:46:23.015849Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `open2view-nz/FTS_00000000000000da_BEING_DELETED_CACHE` because it could not be opened.
2018-04-05T00:46:23.015881Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2018-04-05T00:46:23.015893Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2018-04-05T00:46:23.015903Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './open2view-nz/FTS_00000000000000da_CONFIG.ibd' OS error: 71
2018-04-05T00:46:23.015915Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `open2view-nz/FTS_00000000000000da_CONFIG` because it could not be opened.
2018-04-05T00:46:23.015935Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2018-04-05T00:46:23.015957Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2018-04-05T00:46:23.015968Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './open2view-nz/FTS_00000000000000da_DELETED.ibd' OS error: 71
2018-04-05T00:46:23.015976Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `open2view-nz/FTS_00000000000000da_DELETED` because it could not be opened.
2018-04-05T00:46:23.015997Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2018-04-05T00:46:23.016006Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2018-04-05T00:46:23.016015Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './open2view-nz/FTS_00000000000000da_DELETED_CACHE.ibd' OS error: 71
2018-04-05T00:46:23.016025Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `open2view-nz/FTS_00000000000000da_DELETED_CACHE` because it could not be opened.
2018-04-05T00:46:26.374873Z 2 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2018-04-05T00:46:26.374881Z 2 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2018-04-05T00:46:26.374892Z 2 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './open2view-nz/FTS_00000000000000da_BEING_DELETED.ibd' OS error: 71
2018-04-05T00:46:26.374997Z 2 [ERROR] [MY-012592] [InnoDB] Operating system error number 14 in a file operation.
2018-04-05T00:46:26.375013Z 2 [ERROR] [MY-012596] [InnoDB] Error number 14 means 'Bad address'
2018-04-05T00:46:26.375032Z 2 [ERROR] [MY-012646] [InnoDB] File (unknown): 'stat' returned OS error 114.
2018-04-05T00:46:26.375052Z 2 [ERROR] [MY-012121] [InnoDB] Cannot find space id 207 in the tablespace memory cache, though the file '(null)' in a rename operation should have that ID.
2018-04-05T00:46:26.375075Z 2 [ERROR] [MY-010767] [Server] Error in fixing SE data for open2view-nz.properties
2018-04-05T00:46:27.764887Z 2 [Warning] [MY-010772] [Server] db.opt file not found for test database. Using default Character set.
2018-04-05T00:46:28.262882Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2018-04-05T00:46:28.274513Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-04-05T00:46:30.053709Z 0 [System] [MY-010910] [Server] /usr/local/libexec/mysqld: Shutdown complete (mysqld 8.0.14)  Source distribution.
2018-04-05T00:46:30.6NZ mysqld_safe mysqld from pid file /var/db/mysql/primary-lb.pid ended

How to repeat:
Have a database from version 5.7 named with a hyphen, ie "my-database" and then upgrade the server in-place to mysql 8.0.14.

Suggested fix:
I worked around it by renaming all the tables into a new database (without a hyphen), a.la: 

RENAME TABLE `database-with-hypen`.some_table TO databasewithouthyphen.some_table

The server then started normally, and allowed me to run 'mysql_upgrade' etc.

Sorry, I should say - I downgraded back to 5.7, then renamed the tables/database, and then upgraded to 8.0

Create database my-database with 5.7.25

Attachment: 94431.png (image/png, text), 49.12 KiB.

Process to install 8.0.15

Attachment: 94431-install-8.txt (text/plain), 11.05 KiB.

Running mysql_upgrade

Attachment: 94431_2.png (image/png, text), 48.02 KiB.

Stop/Start 8.0.15 and check my-database.

Attachment: 94431_3.png (image/png, text), 32.34 KiB.

Thank you for the bug report. I couldn't repeat on Ubuntu 18.04 (please check screenshots prior attached).

It looks like it's because the database comes from MySQL 5.6 with a FTS index - see attached log using a completely fresh install/database

Upgrade Process (5.6, 5.7, 8.0)

Attachment: mysql_fts_fail_to_start.txt (text/plain), 9.11 KiB.

Thank you for the feedback. Verified upgrading 5.6->5.7->8.0 with InnoDB table fts.

Posted by developer:
 
This may have already been fixed.  I tried upgrading a 5.7 installation with a `my-db` database and two tablespaces;

mysql> show create table t1;
Current database: my-db
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` varchar(200) DEFAULT NULL,
  `b` text,
  FULLTEXT KEY `a` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.12 sec)
mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` text,
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

When I started the 8.0.16 server the output contains this successful upgrade info:

2019-02-27T23:58:30.819024Z 2 [Note] [MY-010006] [Server] Using data dictionary with version '80016'.
2019-02-27T23:58:39.534508Z 5 [System] [MY-013381] [Server] Server upgrade from 50700 to 80017 has started.
2019-02-27T23:58:39.539604Z 5 [Note] [MY-013386] [Server] Running queries to upgrade MySQL server.
2019-02-27T23:59:16.169467Z 5 [Note] [MY-013387] [Server] Upgrading system table data.
2019-02-27T23:59:27.898366Z 5 [Note] [MY-013391] [Server] Found outdated sys schema version 1.5.1.
2019-02-27T23:59:27.898390Z 5 [Note] [MY-013385] [Server] Upgrading the sys schema.
2019-02-27T23:59:37.100085Z 5 [Note] [MY-013400] [Server] Upgrade of help tables started.
2019-02-27T23:59:41.350050Z 5 [Note] [MY-013400] [Server] Upgrade of help tables completed.
2019-02-27T23:59:41.351817Z 5 [Note] [MY-013394] [Server] Checking 'mysql' schema.
2019-02-27T23:59:43.115440Z 5 [Note] [MY-013394] [Server] Checking 'mtr' schema.
2019-02-27T23:59:43.198570Z 5 [Note] [MY-013394] [Server] Checking 'my-db' schema.
2019-02-27T23:59:43.343487Z 5 [Note] [MY-013394] [Server] Checking 'sys' schema.
2019-02-27T23:59:44.170665Z 5 [Note] [MY-013394] [Server] Checking 'test' schema.
2019-02-27T23:59:44.566088Z 5 [System] [MY-013381] [Server] Server upgrade from 50700 to 80017 has completed.

Note that with 8.0.16 it is not necessary to use mysql_upgrade utility.  In fact, when you do, this is printed;

C:\Work\Repo\mysql-8.0\kl-vs2017-64\runtime_output_directory\Debug>mysql_upgrade
The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.
To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade.
The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand.
It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.

Posted by developer:
 
Reproduces for me in 8.0.16 - but requires the table was created in 5.6 (as per the provided reproduce steps):

1. In 5.6.42:

mysql> CREATE DATABASE `hyphenated-table`;
Query OK, 1 row affected (0.00 sec)

mysql> use `hyphenated-table`;
Database changed
mysql> CREATE TABLE fts_table(id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, text_field TEXT, FULLTEXT idx (text_field)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO fts_table(text_field) VALUES('lorem ipsum dolor sit amet');
Query OK, 1 row affected (0.01 sec)

2. Upgrade to 5.7.25 (execute mysql_upgrade followed by restart)

3. Upgrade to 8.0.16 - MySQL fails to start:

2019-02-28T02:11:52.348087Z mysqld_safe Logging to '/mysql/data/work.err'.
2019-02-28T02:11:52.374261Z mysqld_safe Starting mysqld daemon with databases from /mysql/data
2019-02-28T02:11:52.626712Z 0 [System] [MY-010116] [Server] /mysql/mysql/bin/mysqld (mysqld 8.0.16-commercial) starting as process 14568
2019-02-28T02:11:53.737178Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2019-02-28T02:11:53.737211Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2019-02-28T02:11:53.737240Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './hyphenated-table/FTS_0000000000000018_BEING_DELETED.ibd' OS error: 71
2019-02-28T02:11:53.737280Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `hyphenated-table/FTS_0000000000000018_BEING_DELETED` because it could not be opened.
2019-02-28T02:11:53.737412Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2019-02-28T02:11:53.737439Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2019-02-28T02:11:53.737467Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './hyphenated-table/FTS_0000000000000018_BEING_DELETED_CACHE.ibd' OS error: 71
2019-02-28T02:11:53.737507Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `hyphenated-table/FTS_0000000000000018_BEING_DELETED_CACHE` because it could not be opened.
2019-02-28T02:11:53.737567Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2019-02-28T02:11:53.737594Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2019-02-28T02:11:53.737620Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './hyphenated-table/FTS_0000000000000018_CONFIG.ibd' OS error: 71
2019-02-28T02:11:53.737656Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `hyphenated-table/FTS_0000000000000018_CONFIG` because it could not be opened.
2019-02-28T02:11:53.737842Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2019-02-28T02:11:53.737874Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2019-02-28T02:11:53.737901Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './hyphenated-table/FTS_0000000000000018_DELETED.ibd' OS error: 71
2019-02-28T02:11:53.737938Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `hyphenated-table/FTS_0000000000000018_DELETED` because it could not be opened.
2019-02-28T02:11:53.738008Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2019-02-28T02:11:53.738034Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2019-02-28T02:11:53.738060Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './hyphenated-table/FTS_0000000000000018_DELETED_CACHE.ibd' OS error: 71
2019-02-28T02:11:53.738098Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `hyphenated-table/FTS_0000000000000018_DELETED_CACHE` because it could not be opened.
2019-02-28T02:11:54.400851Z 2 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2019-02-28T02:11:54.400867Z 2 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2019-02-28T02:11:54.400885Z 2 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './hyphenated-table/FTS_0000000000000018_BEING_DELETED.ibd' OS error: 71
2019-02-28T02:11:54.400980Z 2 [ERROR] [MY-012592] [InnoDB] Operating system error number 14 in a file operation.
2019-02-28T02:11:54.401002Z 2 [ERROR] [MY-012596] [InnoDB] Error number 14 means 'Bad address'
2019-02-28T02:11:54.401028Z 2 [ERROR] [MY-012646] [InnoDB] File (unknown): 'stat' returned OS error 114.
2019-02-28T02:11:54.401082Z 2 [ERROR] [MY-012121] [InnoDB] Cannot find space id 13 in the tablespace memory cache, though the file '(null)' in a rename operation should have that ID.
2019-02-28T02:11:54.401112Z 2 [ERROR] [MY-010767] [Server] Error in fixing SE data for hyphenated-table.fts_table
2019-02-28T02:11:55.250073Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2019-02-28T02:11:55.250678Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-02-28T02:11:57.469506Z 0 [System] [MY-010910] [Server] /mysql/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.16-commercial)  MySQL Enterprise Server - Commercial.
2019-02-28T02:11:57.502511Z mysqld_safe mysqld from pid file /mysql/run/mysql.pid ended

Posted by developer:
 
The problem was that there was an entry in the dd:tablespaces but not in dd:tablespace_files. This caused the file to not be found because the filepath generated used the hyphen instead of "@002" which is what the server changed the hyphen to in the identifier.

The fix is to modify dict_check_sys_tables() so that it checks if the filepath is actually found in the DD and if not, makes it correctly.

Put the patch onto RB#21650

Posted by developer:
 
Fixed as of the upcoming 8.0.17 release, and here's the changelog entry:

A FULLTEXT index table created in MySQL 5.6 in a database with a hyphen
in its name caused a startup failure after upgrading from MySQL 5.7 to
MySQL 8.0. Tablespace file paths for FULLTEXT auxiliary tables were not
found in the data dictionary, and the hyphen in the database name was not
handled correctly in subsequently generated file paths.