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)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.7.5 lab
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[27 Nov 2014 6:52] Yoshinori Matsunobu

Description:
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)
)engine=innodb;

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.
innodb_page_size=65536
innodb_doublewrite=0
innodb_compression_level=1
innodb_compression_algorithm=1
innodb_buffer_pool_size=128M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_log_file_size=2G