Bug #95928 InnoDB crashes on compressed and encrypted table when changing FS block size
Submitted: 22 Jun 15:20 Modified: 28 Jun 2:58
Reporter: Sergei Glushchenko (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.x, 8.0.x OS:Linux
Assigned to: CPU Architecture:Any

[22 Jun 15:20] Sergei Glushchenko
Description:
When InnoDB tries to decrypt compressed page uses FS block size to align encrypted data length (it literally using stat to determine block size). If table was created on the FS with block size 4096 and copied to the FS with block size 1024 it leads to crash.

How to repeat:
Create some tables:

sysbench /usr/share/sysbench/oltp_insert.lua --tables=9 --mysql-db=test --mysql-user=root --db-driver=mysql --mysql-socket=/tmp/mysql.sock --table-size=1000000 prepare

Encrypt and compress them:

ALTER TABLE sbtest1 ENCRYPTION='y' COMPRESSION='zlib';
...

Check block size, typically it is 4096:

stat test/sbtest1.ibd
  File: test/sbtest1.ibd
  Size: 247463936       Blocks: 252616     IO Block: 4096   regular file
Device: 17h/23d Inode: 376         Links: 1
Access: (0640/-rw-r-----)  Uid: ( 1000/  sergei)   Gid: ( 1000/  sergei)
Access: 2019-06-22 21:55:57.054893894 +0700
Modify: 2019-06-22 21:49:49.702300000 +0700
Change: 2019-06-22 21:49:49.702300000 +0700
 Birth: -

Create FS with block size 1024:

truncate --size 30G my.img
mkfs.ext4 -b 1024 -F my.img
sudo mount -o loop my.img /mnt/tmp

Stop mysqld and copy datadir:

killall mysqld
cd /mnt/tmp
rsync -rvP --sparse /dev/shm/dat1 .

Check that block size is 1024 now:
stat dat1/test/sbtest1.ibd
  File: dat1/test/sbtest1.ibd
  Size: 247463936       Blocks: 243652     IO Block: 1024   regular file
Device: 70bh/1803d      Inode: 1163549     Links: 1
Access: (0640/-rw-r-----)  Uid: ( 1000/  sergei)   Gid: ( 1000/  sergei)
Access: 2019-06-22 21:54:37.248439044 +0700
Modify: 2019-06-22 21:53:26.658606064 +0700
Change: 2019-06-22 21:53:26.658606064 +0700
 Birth: 2019-06-22 21:53:25.998607527 +0700

You may also check that md5sum reports the same checksum for original and copied file.

Try to start mysqld on new datadir and query the tables. I got crash at startup:

~/dist/p/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld --basedir=${HOME}/dist/p/mysql-8.0.16-linux-glibc2.12-x86_64 --datadir=${PWD}/dat1 --default-authentication-plugin=mysql_native_password --early-plugin-load=keyring_file.so --keyring-file-data=keyring.data
2019-06-22T14:55:11.839132Z 0 [System] [MY-010116] [Server] /home/sergei/dist/p/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.16) starting as process 19790
2019-06-22T14:55:12.255981Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: fil0fil.cc:7505:req_type.is_dblwr_recover() || err == DB_SUCCESS thread 140546065491712
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
14:55:12 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
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 0x46000
/home/sergei/dist/p/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld(my_print_stacktrace(unsigned char*, unsigned long)+0x2e) [0x1d563fe]
/home/sergei/dist/p/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld(handle_fatal_signal+0x323) [0xee86f3]
/usr/lib/libpthread.so.0(+0x124d0) [0x7fd3a168b4d0]
/usr/lib/libc.so.6(gsignal+0x10f) [0x7fd3a0af482f]
/usr/lib/libc.so.6(abort+0x125) [0x7fd3a0adf672]
/home/sergei/dist/p/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x2b6) [0x1fdf586]
/home/sergei/dist/p/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld(Fil_shard::do_io(IORequest const&, bool, page_id_t const&, page_size_t const&, unsigned long, unsigned long, void*, void*)+0xa21) [0x2105da1]
/home/sergei/dist/p/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld(fil_io(IORequest const&, bool, page_id_t const&, page_size_t const&, unsigned long, unsigned long, void*, void*)+0x56) [0x2105e66]
/home/sergei/dist/p/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld() [0x2064fd2]
/home/sergei/dist/p/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld(buf_read_page_background(page_id_t const&, page_size_t const&, bool)+0x26) [0x20653a6]
/home/sergei/dist/p/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld() [0x2050a82]
/home/sergei/dist/p/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld(buf_dump_thread()+0x115) [0x2051475]
/home/sergei/dist/p/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable, void (*)()> > >::_M_run()+0x63) [0x1e0fcf3]
/home/sergei/dist/p/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld() [0x23c4caf]
/usr/lib/libpthread.so.0(+0x7a92) [0x7fd3a1680a92]
/usr/lib/libc.so.6(clone+0x43) [0x7fd3a0bb7cd3]
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.

Suggested fix:
InnoDB could save file block size somewhere and use it later. Alternatively Encryption::decrypt could round length to fixed value, 512 is probably minimum possible block size.
[28 Jun 2:59] Bogdan Kecman
Hi.

Thanks for the report. Verified as reported

Bogdan