Bug #88195 8.0 crash recovery fails for upgraded 5.7 compressed tables with size < 96k
Submitted: 24 Oct 2017 6:04 Modified: 8 Nov 2017 20:40
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:8.0.3 OS:Any
Assigned to: CPU Architecture:Any

[24 Oct 2017 6:04] Shane Bester
Description:
After upgrading to 8.0 server may work fine.  But on first crash recovery, it fails to start with messages like :

[Note] InnoDB: Doing recovery: scanned up to log sequence number 56067062
[Note] InnoDB: Applying a batch of 880 redo log records ...
[Note] InnoDB: 10%
[Note] InnoDB: 20%
[Note] InnoDB: 30%
[Note] InnoDB: 40%
[ERROR] InnoDB: The size of tablespace file '.\test\t.ibd' is only 65536, should be at least 98304!
[Warning] InnoDB: We do not continue the crash recovery, because the table may become corrupt if we cannot apply the log records in the InnoDB log to it. To fix the problem and start mysqld:
[Note] InnoDB: 1) If there is a permission problem in the file and mysqld cannot open the file, you should modify the permissions.
[Note] InnoDB: 2) If the tablespace is not needed, or you can restore an older version from a backup, then you can remove the .ibd file, and use --innodb_force_recovery=1 to force startup without this file.
[Note] InnoDB: 3) If the file system or the disk is broken, and you cannot remove the .ibd file, you can set --innodb_force_recovery.
[Note] InnoDB: 50%
[Note] InnoDB: 60%
[Note] InnoDB: 70%
[Note] InnoDB: 80%
[Note] InnoDB: 90%
[Note] InnoDB: 100%
[Note] InnoDB: Apply batch completed!
[ERROR] InnoDB: Some files associated with the tablespace 23 were not found: '.\test\t.ibd'

How to repeat:
1. On 5.7.20:
----------
drop table if exists t;
create table t(a varchar(255))engine=innodb row_format=compressed;

2. Shutdown and upgrade to 8.0.3, run mysql_upgrade, restart again.

3. Table is working fine and is 64k ibd file, and all is okay.

4. At some later point when 8.0.3 does a crash recovery and refuses to startup.  
   Here is a crash recovery invoker:

drop procedure if exists p;
delimiter $
create procedure p()
begin
  repeat
    insert into t values(uuid());
    delete from t;
  until rand() < 0.0001 end repeat;
end $
delimiter ;

set global innodb_fast_shutdown=2;
call p(); # run this for a while
shutdown; # a 'fast shutdown' to invoke crash recovery on next startup.
[24 Oct 2017 6:21] Shane Bester
Workaround is to find the 64k tables and rebuild them in 8.0 before a crash recovery is needed...

e.g.

mysql> select name,file_size from information_schema.INNODB_TABLESPACES where file_size<96*1024 and file_size>0;
+--------+-----------+
| name   | file_size |
+--------+-----------+
| test/t |     65536 |
+--------+-----------+
1 row in set (0.00 sec)

mysql>
mysql> optimize table test.t;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.t | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.15 sec)

mysql> select name,file_size from information_schema.INNODB_TABLESPACES where name='test/t';
+--------+-----------+
| name   | file_size |
+--------+-----------+
| test/t |     98304 |
+--------+-----------+
1 row in set (0.00 sec)
[8 Nov 2017 20:40] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.4 release, and here's the changelog entry:

 The InnoDB recovery process failed with a tablespace size error for a
compressed table that was previously upgraded from MySQL 5.7 to MySQL 8.0.
[9 Nov 2017 8:52] Naga Satyanarayana Bodapati
Posted by developer:
 
Dan,

can you also update the doc to include the following aspects as well.

Compressed tables will now use its physical page size instead of the database page size for its initial size.
This would mean reduction of size for empty compressed tables that are created in 8.0.

For example, with innodb-page-size=16k, CREATE TABLE t1(a INT) KEY_BLOCK_SIZE=1;

in 5.7  : 65536 (64K)
in 8.0.4: 7168 (7K)
[16 Nov 2017 13:56] Daniel Price
Posted by developer:
 
The changelog entry was revised as follows:

The InnoDB recovery process failed with a tablespace size error for a
compressed table that was previously upgraded from MySQL 5.7 to MySQL 8.0.

The tablespace file for a compressed table is now created
using the physical page size instead of the InnoDB page size, which makes
the initial size of a tablespace file for an empty compressed table
smaller than in previous MySQL releases. 

An update was also made to:
https://dev.mysql.com/doc/refman/8.0/en/innodb-compression-usage.html