Bug #75019 Assertion error on punch hole + 64k page size + low buffer pool size
Submitted: 27 Nov 2014 6:52 Modified: 28 Nov 2014 2:27
Reporter: Yoshinori Matsunobu (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.5 lab OS:Any
Assigned to: Sunny Bains CPU Architecture:Any

[27 Nov 2014 6:52] Yoshinori Matsunobu
When I tested LinkBench in MySQL 5.7.5 lab, mysqld aborted as below.
2014-11-27T06:11:34.321449Z 2367 [Warning] InnoDB: Retry attempts for writing partial data failed.
2014-11-27T06:11:34.321502Z 2367 [ERROR] InnoDB: Write to file ./linkdb/linktable.ibd failed at offset 4348313600. 65536 bytes should have been written, only 0 were written. Operating system error number 22. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2014-11-27T06:11:34.321532Z 2367 [ERROR] InnoDB: Error number 22 means 'Invalid argument'.
2014-11-27T06:11:34.321540Z 2367 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2014-11-26 22:11:34 0x7fbd083e5700  InnoDB: Assertion failure in thread 140449863849728 in file fil0fil.cc line 5443
InnoDB: Failing assertion: err == DB_SUCCESS
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/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
06:11:34 UTC - mysqld got signal 6 ;

How to repeat:
mysqld crashed on the following conditions.

* InnoDB: PUNCH HOLE support available (I used 5.7.5 lab)
* Low innodb buffer pool size (128MB)
* 64KB page size (innodb_page_size=65536)
* Running LinkBench (using maxid=10000001)
[28 Nov 2014 1:40] Sunny Bains
Yoshinori, thanks for testing :-)
[28 Nov 2014 2:27] Yoshinori Matsunobu
I could repeat the assertion error with below simple test case.

1. Create the following table
create table t1 (
 id int primary key,
 value int,
 value2 varchar(255),
 index(value, value2)

2. Generate 5Mil rows by the following perl script (so that working set size of primary key and secondary key exceeds buffer poll size)

for(my $i=1; $i <= 5000000; $i++) {
  my $txt = 'x' x 150;
  print "$i,$i,$txt\n";

3. Load the data into table
load data local infile '...' into table t1 fields terminated by ',';

4. Run mysqlslap, and see if mysqld crashes.
mysqlslap --host=X --port=Y --number-of-queries=1000000 --concurrency=40 --query="set @a= floor(5000000*rand()); update test.t1 set value = value+1 where id=@a"

- I used 5.7.5 lab src and built with -DHAVE_FALLOC_PUNCH_HOLE_AND_KEEP_SIZE=1. When starting mysqld, make sure that PUNCH HOLE support is available like this:
2014-11-28T02:20:00.397903Z 0 [Note] InnoDB: PUNCH HOLE support available

- InnoDB setting was as follows. Low innodb_buffer_size was intentional.