MySQL Bugs: #97295: Ubuntu 19.10 upgrade from 5.7.27 to 8.0.17 failed

Bug #97295	Ubuntu 19.10 upgrade from 5.7.27 to 8.0.17 failed
Submitted:	19 Oct 2019 1:54	Modified:	13 Nov 2019 8:55
Reporter:	Cory Cohen	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Data Dictionary	Severity:	S3 (Non-critical)
Version:	8.0.17, 8.0.18	OS:	Ubuntu (Ubuntu 19.10)
Assigned to:		CPU Architecture:	x86
Tags:	19.04, 19.10, 5.7.27, 8.0.17, mediawiki, ubuntu, upgrade

Description:
Following a standard Ubuntu 19.10 package upgrade, mysqld generates the following errors:

... [MY-011012] [Server] Starting upgrade of data directory.
... [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
... [MY-012593] [InnoDB] The error means the system cannot find the path specified.
... [MY-012216] [InnoDB] Cannot open datafile for read-only: './xxx/links.ibd' OS error: 71
... [MY-012019] [InnoDB] Ignoring tablespace `xxx/links` because it could not be opened.
... [MY-011006] [Server] Got error 197 from SE while migrating tablespaces.
... [MY-010020] [Server] Data Dictionary initialization failed.

Based on what I've been able to find online, it appears that the Ubuntu package upgrade did not properly check whether I had any "abandoned FRM files".   There are links.frm, links.MYD, and links.MYI files in the xxx directory.   These is apparently a mediawiki table that was abandoned a long time ago and replaced with a different table but apparently not cleaned up properly during some other Ubuntu upgrade.

The bug (if there is one) is that I'm now in a situation where I can't start the server because I'm in middle of the upgrade, and yet the recommended fix for the tablespace problem is to start the server and drop the table.  The upgrade also refuses to accept innodb_force_recovery=1.  I don't need to migrate the table -- I just need the server to start.

Is there a better solution than installing Ubuntu 19.04 and an MySQL 5.7.27 to DROP the table, before moving the data back to MySQL 8.0.17 to complete the upgrade?  I realize that this probably doesn't count as a bug in the traditional sense, but it would have been nice if there was a way to finish the upgrade anyway --upgrade=MINIMAL doesn't seem to work.  I didn't actually initiate the upgrade, so I'm not certain what Ubuntu did to "check" the upgrade.

How to repeat:
Probably just upgrade from 19.04 to 19.10 with the mediawiki package installed, although the system has been upgraded several times, so the fault might lie in a combination of earlier upgrade as well.

Hello Cory Cohen,

Thank you for the report.
Observed this issue on OL7 with binary tarball packages and when upgrading from 5.7.27->8.0.17/8.0.18 with one of the schema dir was missing.

regards,
Umesh

Upgrading from 5.7.27->8.0.17/8.0.18

Attachment: 97295.results (application/octet-stream, text), 24.53 KiB.

I eventually recovered my configuration by:

* building 5.7.27 from source
* starting the 5.7.27 server
* dumping the database in question
* shutting down the 5.7.27 server
* restarting 8.0.17 (initializing a completely new data directory)
* restoring the 5.7.27 dump

In the end this solution wasn't that hard, but for a MySQL novice who has mostly just been applying apt package updates until now, the upgrade went a little less smoothly than I had hoped.  ;-)  I presume that your attachment is a cleaner demonstration of the bug, but I didn't completely understand it.  

Does this mean that that there's no easier way to recover in the meantime?  My solution would have been more difficult if I'd been unable or unwilling to discard my entire data directory.  I also encountered some authentication issues related to improved security in 8.0.17 that I'm not 100% certain I handled appropriately.  

Again, I think I'm ok now, but this might be a problem for people about to upgrade to Ubuntu 19.10 soon.  I jumped the gun by a few days when I accepted the "development" upgrade, but my understanding is that this process is near to "release"...

Posted by developer:
 
Files have been manipulated directly in the data directory (copied/renamed/removed); and hence, the necessary meta data changes have not been done. The server itself is not able to correct this situation. We will consider updating the 'Upgrade checker' utility with a check to catch situations like this before attempting upgrade.