Bug #92609 Upgrade to 8.0.12 fails
Submitted: 1 Oct 2018 1:37 Modified: 3 Jan 11:34
Reporter: Frederic Steinfels Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Installing Severity:S2 (Serious)
Version:8.0.12 OS:Fedora (28)
Assigned to: CPU Architecture:x86 (64BIT)
Tags: 8.0, fails, upgrade

[1 Oct 2018 1:37] Frederic Steinfels
Description:
After installing the 8.0.12 rpms and starting the server, I am getting these errors. I had to downgrade to 5.7.

2018-10-01T01:00:43.084100Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.12) starting as process 1596
2018-10-01T01:00:44.356470Z 1 [ERROR] [MY-012592] [InnoDB] InnoDB: Operating system error number 2 in a file operation.
2018-10-01T01:00:44.356521Z 1 [ERROR] [MY-012593] [InnoDB] InnoDB: The error means the system cannot find the path specified.
2018-10-01T01:00:44.356541Z 1 [ERROR] [MY-012216] [InnoDB] InnoDB: Cannot open datafile for read-only: './dvdupgrades/FTS_000000000000003f_BEING_DELETED.ibd' OS error: 71
2018-10-01T01:00:44.356560Z 1 [Warning] [MY-012019] [InnoDB] InnoDB: Ignoring tablespace `dvdupgrades/FTS_000000000000003f_BEING_DELETED` because it could not be opened.
2018-10-01T01:00:44.356605Z 1 [ERROR] [MY-012592] [InnoDB] InnoDB: Operating system error number 2 in a file operation.
2018-10-01T01:00:44.356626Z 1 [ERROR] [MY-012593] [InnoDB] InnoDB: The error means the system cannot find the path specified.
2018-10-01T01:00:44.356640Z 1 [ERROR] [MY-012216] [InnoDB] InnoDB: Cannot open datafile for read-only: './dvdupgrades/FTS_000000000000003f_BEING_DELETED_CACHE.ibd' OS error: 71
2018-10-01T01:00:44.356656Z 1 [Warning] [MY-012019] [InnoDB] InnoDB: Ignoring tablespace `dvdupgrades/FTS_000000000000003f_BEING_DELETED_CACHE` because it could not be opened.
2018-10-01T01:00:44.356693Z 1 [ERROR] [MY-012592] [InnoDB] InnoDB: Operating system error number 2 in a file operation.
2018-10-01T01:00:44.356712Z 1 [ERROR] [MY-012593] [InnoDB] InnoDB: The error means the system cannot find the path specified.
2018-10-01T01:00:44.356725Z 1 [ERROR] [MY-012216] [InnoDB] InnoDB: Cannot open datafile for read-only: './dvdupgrades/FTS_000000000000003f_CONFIG.ibd' OS error: 71
2018-10-01T01:00:44.356744Z 1 [Warning] [MY-012019] [InnoDB] InnoDB: Ignoring tablespace `dvdupgrades/FTS_000000000000003f_CONFIG` because it could not be opened.
2018-10-01T01:00:44.356781Z 1 [ERROR] [MY-012592] [InnoDB] InnoDB: Operating system error number 2 in a file operation.
2018-10-01T01:00:44.356800Z 1 [ERROR] [MY-012593] [InnoDB] InnoDB: The error means the system cannot find the path specified.
2018-10-01T01:00:44.356845Z 1 [ERROR] [MY-012216] [InnoDB] InnoDB: Cannot open datafile for read-only: './dvdupgrades/FTS_000000000000003f_DELETED.ibd' OS error: 71
2018-10-01T01:00:44.356861Z 1 [Warning] [MY-012019] [InnoDB] InnoDB: Ignoring tablespace `dvdupgrades/FTS_000000000000003f_DELETED` because it could not be opened.
2018-10-01T01:00:44.356920Z 1 [ERROR] [MY-012592] [InnoDB] InnoDB: Operating system error number 2 in a file operation.
2018-10-01T01:00:44.356942Z 1 [ERROR] [MY-012593] [InnoDB] InnoDB: The error means the system cannot find the path specified.
2018-10-01T01:00:44.356956Z 1 [ERROR] [MY-012216] [InnoDB] InnoDB: Cannot open datafile for read-only: './dvdupgrades/FTS_000000000000003f_DELETED_CACHE.ibd' OS error: 71
2018-10-01T01:00:44.356974Z 1 [Warning] [MY-012019] [InnoDB] InnoDB: Ignoring tablespace `dvdupgrades/FTS_000000000000003f_DELETED_CACHE` because it could not be opened.
2018-10-01T01:00:48.128419Z 2 [ERROR] [MY-012592] [InnoDB] InnoDB: Operating system error number 2 in a file operation.
2018-10-01T01:00:48.128459Z 2 [ERROR] [MY-012593] [InnoDB] InnoDB: The error means the system cannot find the path specified.
2018-10-01T01:00:48.128471Z 2 [ERROR] [MY-012216] [InnoDB] InnoDB: Cannot open datafile for read-only: './dvdupgrades/FTS_000000000000003f_BEING_DELETED.ibd' OS error: 71
2018-10-01T01:00:48.128547Z 2 [ERROR] [MY-012592] [InnoDB] InnoDB: Operating system error number 14 in a file operation.
2018-10-01T01:00:48.128570Z 2 [ERROR] [MY-012596] [InnoDB] InnoDB: Error number 14 means 'Bad address'
2018-10-01T01:00:48.128584Z 2 [ERROR] [MY-012646] [InnoDB] InnoDB: File (unknown): 'stat' returned OS error 114.
2018-10-01T01:00:48.128598Z 2 [ERROR] [MY-012121] [InnoDB] InnoDB: Cannot find space id 46 in the tablespace memory cache, though the file '(null)' in a rename operation should have that ID.
2018-10-01T01:00:48.128646Z 2 [ERROR] [MY-010767] [Server] Error in fixing SE data for dvdupgrades.category
mysqld: ../../mysql-8.0.12/sql/dd/upgrade/table.cc:1075: bool dd::upgrade_57::add_triggers_to_table(THD*, TABLE*, const String_type&, const String_type&): Assertion `t->get_event() >= t_type && (t->get_event() > t_type || t->get_action_time() >= t_time)' failed.
01:00:48 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=1
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 67846 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x4a45a50
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f20d615ada0 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char*, unsigned long)+0x41) [0x1b5e271]
/usr/sbin/mysqld(handle_fatal_signal+0x453) [0xd85d63]
/lib64/libpthread.so.0(+0x11fb0) [0x7f20e928afb0]
/lib64/libc.so.6(gsignal+0x10b) [0x7f20e7471eab]
/lib64/libc.so.6(abort+0x123) [0x7f20e745c5b9]
/lib64/libc.so.6(+0x21491) [0x7f20e745c491]
/lib64/libc.so.6(+0x2f612) [0x7f20e746a612]
/usr/sbin/mysqld() [0x1b1ba58]
/usr/sbin/mysqld(dd::upgrade_57::migrate_all_frm_to_dd(THD*, char const*, bool)+0x590) [0x1b1cb80]
/usr/sbin/mysqld(dd::upgrade_57::fill_dd_and_finalize(THD*)+0xf2) [0x1af28f2]
/usr/sbin/mysqld() [0xe2ed06]
/usr/sbin/mysqld() [0x1fe4ad5]
/lib64/libpthread.so.0(+0x7594) [0x7f20e9280594]
/lib64/libc.so.6(clone+0x3f) [0x7f20e7534e6f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 2
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2018-10-01T01:00:50.911625Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.12) starting as process 1679
2018-10-01T01:00:52.730660Z 1 [ERROR] [MY-011014] [Server] Found partially upgraded DD. Aborting upgrade and deleting all DD tables. Start the upgrade process again.
2018-10-01T01:00:52.801316Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2018-10-01T01:00:52.801397Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-10-01T01:00:54.830796Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.12)  MySQL Community Server - GPL.

How to repeat:
Install 8.0 binaries
[1 Oct 2018 8:47] Miguel Solorzano
Please describe here step by step how you did the upgrade process. Thanks.
[1 Oct 2018 14:23] Frederic Steinfels
According to this site:

https://www.if-not-true-then-false.com/2010/install-mysql-on-fedora-centos-red-hat-rhel/

dnf install https://dev.mysql.com/get/mysql80-community-release-fc28-1.noarch.rpm

however there was an error message about conflicting packages with already installed version 5.7 so I did an "rpm -e --justdb mysql-community-server"

I was reading the internet about such problems. I am not alone with that. I tried several times restarting mysqld with

systemctl restart mysqld

I was checking out the permissions of the file and directory and except that the mysql directory was symlinked, permissions are ok.

I can copy the db to a virtual machine where you try yourself if you want without the risk of loss of data.
[1 Oct 2018 16:32] Miguel Solorzano
Thank you for the feedback. Sorry but you have not followed the correct upgrade process from 5.7 to 8.0, please read and follow the instructions in our Manual;

https://dev.mysql.com/doc/refman/8.0/en/updating-yum-repo.html

Important

For important information about upgrading from MySQL 5.7 to 8.0, see Upgrading from MySQL 5.7 to 8.0.
[1 Oct 2018 19:37] Frederic Steinfels
There is no relevant difference between those two instructions. Both end up with installing the 8.0 RPM which is the same. The mysql_upgrade will be called anyway when starting the server and that is the time when mysqld fails. Starting mysql_upgrade by hand will not work as the server needs to be running.
[2 Oct 2018 10:11] Frederic Steinfels
Please reopen the bug.
[2 Oct 2018 11:18] Miguel Solorzano
Please read: https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/upgrading-strategies.html
[2 Oct 2018 12:28] Terje Røsten
Seems like contents of database is more important than package format (RPM in this case).

Can you describe any less common feature you have enabled? 

Are you using FULLTEXT search (FTS)? Are tables InnoDB only or mix with MyISAM and InnoDB?
[2 Oct 2018 15:08] Frederic Steinfels
Yes, I am using Innodb and MyIsam and a lot of triggers and stored procedures. While googling this, somebody wrote that he had a newline missing at the end of one of his triggers. Doing a divide and conquer is quite expensive in time due to the amount of tables I have. DB is about 40 Gigabytes.
[2 Oct 2018 19:59] Terje Røsten
Hi again,

excuse my ignorance, but what do you mean by "newline missing at the end of one of his triggers"? 

This information might helps us create a re-producer.
[2 Oct 2018 20:49] Frederic Steinfels
Unless the log given will ring a bell, there is basically no way to reproduce unless you go to my test machine to try yourself. Please PM me.
I read about this newline stuff on google somewhere but I cant find it anymore. As I could not start mysql 8.0, I was unable to edit my stored procedures so I had to downgrade.
[5 Oct 2018 11:35] Frederic Steinfels
I forgot to write: I am using fulltext indexes on some tabeles.
[8 Oct 2018 7:36] Sivert Sørumgård
The error indicates that the triggers listed in the trigger file are in an incorrect order. Editing a trigger file to redefine the order, then trying an upgrade, will lead to the same error.
[8 Oct 2018 8:38] Truong Duong
Might I meet same issue. The work around is delete all .TRG file (or move them to out side of mysql data folder) then update. 
After success we can re-create the trigger.

Other solution if you still running on MySQL 5.7.23 (lasted version) is dump all trigger to sql file, delete it and re-import the dump file to recreate all trigger with MySQL 5.7.23 file format. After that you are free to update to MySQL 8.0.12.

I did both of above solutions on my three database server, all are work well.
[9 Oct 2018 5:17] Umesh Shastry
Bug #92696 marked as duplicate of this one
[10 Oct 2018 7:56] Terje Røsten
Hi Frederic!

Can you try the workaround as suggested by Truong?
[10 Oct 2018 14:56] Frederic Steinfels
It worked! I was able to upgrade this way.

# mysqldump --triggers --no-create-db --no-data --no-create-info --all-databases -uroot -p >triggers.sql
# systemctl stop mysqld
# cd /var/lib/
# cp -rp mysql mysql.bak
# cd mysql
# rm */*.TRG
# dnf install mysql-community-server
# dnf update
# systemctl restart mysqld 
<fixing my my.cnf>
# systemctl start mysqld
# mysql_upgrade -u root -p
# mysql -u root -p <triggers.sql
[24 Nov 2018 18:07] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.14 release, and here's the changelog entry:

Triggers were loaded into memory in an incorrect order when upgrading
from MySQL 5.7 to MySQL 8.0, causing an assertion failure.