MySQL Bugs: #72187: Crash with 5.5 datadir

Bug #72187	Crash with 5.5 datadir
Submitted:	1 Apr 2014 13:34	Modified:	4 Apr 2014 19:41
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S3 (Non-critical)
Version:	5.6.16	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	crash

Description:
After a backup/restore (meb 3.9) of a 5.5 datadir the 5.6.16 server crashed on startup.
This is really not a big issue as it can probably be fixed by first running a 5.5 mysqld against this directory and then doing a clean shudown... but on the other hand, it should not crash but it should at least give a helpfull error instead.

2014-04-01 15:16:46 27690 [Note] InnoDB: Database was not shutdown normally!
2014-04-01 15:16:46 27690 [Note] InnoDB: Starting crash recovery.
2014-04-01 15:16:46 27690 [Note] InnoDB: Reading tablespace information from the .ibd files...
2014-04-01 15:16:51 27690 [Note] InnoDB: Restoring possible half-written data pages
2014-04-01 15:16:51 27690 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Transaction 3261135530 was in the XA prepared state.
InnoDB: Transaction 3261135532 was in the XA prepared state.
InnoDB: Transaction 3261135533 was in the XA prepared state.
InnoDB: Transaction 3261135533 was in the XA prepared state.
InnoDB: 3 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 0 row operations to undo
InnoDB: Trx id counter is 3261135872
InnoDB: Last MySQL binlog file position 0 672291321, file name /mysql/binlog/mysql-bin.000283
13:16:54 UTC - mysqld got signal 11 ;
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.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

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

Thread pointer: 0x0
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 = 0 thread_stack 0x80000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x908a15]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x694934]
/lib64/libpthread.so.0[0x3b9860f710]
/usr/sbin/mysqld[0xaa7a97]
/usr/sbin/mysqld[0xaacf49]
/usr/sbin/mysqld[0xa2b239]
/usr/sbin/mysqld[0x97273d]
/usr/sbin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x48)[0x5d9f78]
/usr/sbin/mysqld[0x71c681]
/usr/sbin/mysqld(_Z11plugin_initPiPPci+0xb76)[0x720566]
/usr/sbin/mysqld[0x5cd248]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x455)[0x5d1d55]
/lib64/libc.so.6(__libc_start_main+0xfd)[0x366fa1ed1d]
/usr/sbin/mysqld[0x5c3a71]

How to repeat:
Backup a 5.5 instance, restore on 5.6.

fyi, it is documented here (wasn't in the beginning):

http://dev.mysql.com/doc/mysql-enterprise-backup/3.10/en/restore-upgrade.html#upgrade-5.5-...

I will admit the steps in the above url seem funny to me. I would rather do:

o) backup the existing 5.5 instance on server A
o) restore onto identical version of 5.5 on server B
o) follow normal 5.5->5.6 upgrade on B.

Why would anybody use MEB to do an upgrade on the very same machine? That is what the docs seems to imply?

I knew about that piece of documentation. I was able to use a 5.5 mysqld to do a crash recovery and then use the datadir with 5.6 (inc. mysql_upgrade etc).

The procedure in the documentation will not work as RPM/YUM doesn't allow for multiple versions to be installed at the same time (you could use the relocate option, but that's not a best practice).

In my case I had a 5.5 master and a 5.6 slave, then the replication broke badly, so I had to restore the data from the 5.5 master on the 5.6 slave. 
I used a generic tarball 5.5 install besides the 5.6 RPM install to do the crash recovery w/o uninstalling 5.6.

Thank you for the feedback.

I agree the steps can look weird, but you actually should not have both servers installed, rather do following actions:

1. Back up data on the MySQL 5.5 server.

2. Restore data on the directory you plan to use for MySQL 5.6 server's data by simply running an apply-log and then a copy-back operation on the backup.

3. Restart the old MySQL 5.5 server, using the data directory of the new MySQL 5.6 server as its own data directory.
Note

This is an extra step beyond the normal restore and upgrade procedures, special to the restoration of MySQL 5.5 data to MySQL 5.6 server; with it, the MySQL 5.5 server prepares the data for an upgrade to MySQL 5.6 by performing some clean-up steps on the data, similar to what the server would do during a crash recovery.

4. Stop the MySQL 5.5 server.

5. Install MySQL 5.6

6. Start the new MySQL 5.6 server.

7. Run upgrade steps as documented in the MySQL reference manual on the restored data. Make sure the mysql_upgrade that comes with MySQL 5.6 is applied.

I will open MEB docs bug though.

Bug #72199

13:16:54 UTC - mysqld got signal 11 ;
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.

1 - This could be because you hit a bug.

It is also possible that
2 - this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. 

3 - This error can also be caused by malfunctioning hardware.

So if this is not a bug option it's not option 1.
The binaries and libraries are okay and are compiled by Oracle so it's probably not option 2 either. And I don't think it's a hardware malfunction either.

So the message is not complete if it is supposed to crash if I use a 5.6 mysqld with a 5.5 datadir.
This can be fixed by:
a - updating the message 
b - making sure it doesn't crash and just works.
c - making sure it doesn't crash, but just stops and generates a nice error.

Thank you for the feedback.

You are correct - message is not very clear and can contain option 4: "You used software in not appropriate way" or something similar with better wording.