Bug #77128 Regression in dealing with filesystem limits
Submitted: 22 May 2015 7:34 Modified: 29 Oct 2015 17:13
Reporter: Przemyslaw Malkowski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.24 5.7.7, 5.6.25 OS:Any
Assigned to: CPU Architecture:Any
Tags: ibdata

[22 May 2015 7:34] Przemyslaw Malkowski
Description:
When a maximum file size limit, allowed by a given file system, is reached, auto-extend of ibdata file fails in a way that it's impossible to add another ibdata space. 
In version 5.5 it works without problem - once ibdata1 is full, you may add ibdata2 and keep writing. In 5.6 and 5.7 InnoDB won't start due to ibdata1 size mismatch error. It happened originally on EXT3 file system, where the ibdata reached 2TB single file limit. But it's much easier to reproduce on fat32 where the file size limit is 4GB.
Note that I was NOT able to reproduce the same problem when the datadir disk simply got full.

This is how it works on MySQL 5.5, where I cannot experience the problem:
Version: '5.5.43'  socket: '/tmp/mysql_sandbox15543.sock'  port: 15543  MySQL Community Server (GPL)
150521 10:53:53  InnoDB: Error: Write to file ./ibdata1 failed at offset 0 4293918720.
InnoDB: 1048576 bytes should have been written, only 1048575 were written.
InnoDB: Operating system error number 0.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 0 means 'Success'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
150521 10:53:54 [ERROR] /bigdisk/sandbox1/oracle5.5.43/bin/mysqld: The table 'sbtest1' is full

File on the disk:
-rwxr-xr-x 1 przemek przemek 4294967295 May 21 10:54 ibdata1

Stopped mysqld, and added to my.cnf:
innodb_data_file_path=ibdata1:4294967295;ibdata2:50M:autoextend

After restart it went just well, allowing me to continue writing:
150521 10:58:58 mysqld_safe Starting mysqld daemon with databases from /bigdisk/sandbox1/mysql5.5.43/data
150521 10:58:58 [Note] /bigdisk/sandbox1/oracle5.5.43/bin/mysqld (mysqld 5.5.43) starting as process 37243 ...
150521 10:58:58 [Warning] You have forced lower_case_table_names to 0 through a command-line option, even though your file system '/bigdisk/sandbox1/mysql5.5.43/data/' is case insensitive.  This means that you can corrupt a MyISAM table by accessing it with different cases. You should consider changing lower_case_table_names to 1 or 2
150521 10:58:58 [Note] Plugin 'FEDERATED' is disabled.
150521 10:58:58 InnoDB: The InnoDB memory heap is disabled
150521 10:58:58 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150521 10:58:58 InnoDB: Compressed tables use zlib 1.2.3
150521 10:58:58 InnoDB: Using Linux native AIO
150521 10:58:58 InnoDB: Initializing buffer pool, size = 8.0G
150521 10:58:59 InnoDB: Completed initialization of buffer pool
150521 10:58:59  InnoDB: Data file ./ibdata2 did not exist: new to be created
150521 10:58:59  InnoDB: Setting file ./ibdata2 size to 50 MB
InnoDB: Database physically writes the file full: wait...
150521 10:58:59 InnoDB: highest supported file format is Barracuda.
150521 10:59:00  InnoDB: Waiting for the background threads to start
150521 10:59:01 InnoDB: 5.5.43 started; log sequence number 4761156725
150521 10:59:01 [Note] Server hostname (bind-address): '127.0.0.1'; port: 15543
150521 10:59:01 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
150521 10:59:01 [Note] Server socket created on IP: '127.0.0.1'.
150521 10:59:01 [Note] Event Scheduler: Loaded 0 events
150521 10:59:01 [Note] /bigdisk/sandbox1/oracle5.5.43/bin/mysqld: ready for connections.
Version: '5.5.43'  socket: '/tmp/mysql_sandbox15543.sock'  port: 15543  MySQL Community Server (GPL)

How to repeat:
Set up fat32 (vfat) partition for MySQL data and use innodb_file_per_table=0 setting. Then fill in with data until you see "The table 'tablename' is full" error.

Test on 5.6.24:

2015-05-21 11:28:29 7ffdbb580700 InnoDB: Error: Write to file ./ibdata1 failed at offset 4293918720.
InnoDB: 1048576 bytes should have been written, only 1048575 were written.
InnoDB: Operating system error number 11.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 11 means 'Resource temporarily unavailable'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
2015-05-21 11:28:29 39295 [ERROR] /bigdisk/sandbox1/oracle5.6.24/bin/mysqld: The table 'sbtest1' is full

File on disk:
-rwxr-xr-x 1 przemek przemek 4294967295 May 21 11:38 mysql5.6.24/data/ibdata1

Added to my.cnf:
innodb_data_file_path=ibdata1:4294967295;ibdata2:50M:autoextend

Result:
150521 11:39:22 mysqld_safe Starting mysqld daemon with databases from /bigdisk/sandbox1/mysql5.6.24/data
2015-05-21 11:39:22 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-05-21 11:39:22 0 [Note] /bigdisk/sandbox1/oracle5.6.24/bin/mysqld (mysqld 5.6.24) starting as process 40338 ...
2015-05-21 11:39:22 40338 [Warning] You have forced lower_case_table_names to 0 through a command-line option, even though your file system '/bigdisk/sandbox1/mysql5.6.24/data/' is case insensitive.  This means that you can corrupt a MyISAM table by accessing it with different cases. You should consider changing lower_case_table_names to 1 or 2
2015-05-21 11:39:22 40338 [Note] Plugin 'FEDERATED' is disabled.
2015-05-21 11:39:22 40338 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-05-21 11:39:22 40338 [Note] InnoDB: The InnoDB memory heap is disabled
2015-05-21 11:39:22 40338 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-05-21 11:39:22 40338 [Note] InnoDB: Memory barrier is not used
2015-05-21 11:39:22 40338 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-05-21 11:39:22 40338 [Note] InnoDB: Using Linux native AIO
2015-05-21 11:39:22 40338 [Note] InnoDB: Using CPU crc32 instructions
2015-05-21 11:39:22 40338 [Note] InnoDB: Initializing buffer pool, size = 8.0G
2015-05-21 11:39:23 40338 [Note] InnoDB: Completed initialization of buffer pool
2015-05-21 11:39:23 40338 [ERROR] InnoDB: Data file ./ibdata1 is of a different size 262143 pages (rounded down to MB) than specified in the .cnf file 262080 pages!
2015-05-21 11:39:23 40338 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2015-05-21 11:39:23 40338 [ERROR] Plugin 'InnoDB' init function returned error.
2015-05-21 11:39:23 40338 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-05-21 11:39:23 40338 [ERROR] Unknown/unsupported storage engine: InnoDB
2015-05-21 11:39:23 40338 [ERROR] Aborting

Test on 5.7.7:

Version: '5.7.7-rc'  socket: '/tmp/mysql_sandbox11577.sock'  port: 11577  MySQL Community Server (GPL)
2015-05-21T16:01:58.805709Z 4 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./ibdata1, desired size 67108864 bytes. Operating system error number 27. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2015-05-21T16:01:58.928291Z 4 [Warning] InnoDB: 1048576 bytes should have been written. Only 1048575 bytes written. Retrying again to write the remaining bytes.
2015-05-21T16:01:58.928355Z 4 [ERROR] InnoDB: Error in system call pwrite(). The operating system error number is 27.
2015-05-21T16:01:58.928463Z 4 [ERROR] InnoDB: Error number 27 means 'File too large'.
2015-05-21T16:01:58.928486Z 4 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2015-05-21T16:01:58.928496Z 4 [Warning] InnoDB: Retry attempts for writing partial data failed.
2015-05-21T16:01:58.928513Z 4 [ERROR] InnoDB: Write to file ./ibdata1 failed at offset 4293918720. 1048576 bytes should have been written, only 1048575 were written. 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.
2015-05-21T16:01:58.928528Z 4 [Warning] InnoDB: Error while writing 67108864 zeroes to ./ibdata1 starting at offset 4240441344
2015-05-21T16:01:58.960368Z 4 [ERROR] /bigdisk/sandbox1/oracle5.7.7/bin/mysqld: The table 'sbtest1' is full
2015-05-21T16:01:59.049258Z 4 [Note] Aborted connection 4 to db: 'test' user: 'msandbox' host: 'localhost' (Got an error reading communication packets)

$ ls -l mysql5.7.7/data/
...
-rwxr-xr-x 1 przemek przemek 4294967295 May 21 12:03 ibdata1

added: innodb_data_file_path=ibdata1:4294967295;ibdata2:50M:autoextend

150522 02:37:25 mysqld_safe Starting mysqld daemon with databases from /bigdisk/sandbox1/mysql5.7.7/data
2015-05-22T06:37:25.780794Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-05-22T06:37:25.780940Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2015-05-22T06:37:25.781029Z 0 [Note] /bigdisk/sandbox1/oracle5.7.7/bin/mysqld (mysqld 5.7.7-rc) starting as process 37644 ...
2015-05-22T06:37:25.786300Z 0 [Warning] You have forced lower_case_table_names to 0 through a command-line option, even though your file system '/bigdisk/sandbox1/mysql5.7.7/data/' is case insensitive.  This means that you can corrupt a MyISAM table by accessing it with different cases. You should consider changing lower_case_table_names to 1 or 2
2015-05-22T06:37:25.789828Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-05-22T06:37:25.789893Z 0 [Note] InnoDB: Uses event mutexes
2015-05-22T06:37:25.789908Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2015-05-22T06:37:25.789925Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-05-22T06:37:25.789936Z 0 [Note] InnoDB: Using Linux native AIO
2015-05-22T06:37:25.790560Z 0 [Note] InnoDB: Number of pools: 1
2015-05-22T06:37:25.790792Z 0 [Note] InnoDB: Using CPU crc32 instructions
2015-05-22T06:37:25.793072Z 0 [Note] InnoDB: Initializing buffer pool, total size = 8G, instances = 8, chunk size = 128M
2015-05-22T06:37:27.559190Z 0 [Note] InnoDB: Completed initialization of buffer pool
2015-05-22T06:37:27.650816Z 0 [Note] InnoDB: Need to create a new innodb_system data file 'ibdata2'.
2015-05-22T06:37:27.650842Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2015-05-22T06:37:27.650993Z 0 [ERROR] InnoDB: The innodb_system data file './ibdata1' is of a different size 262143 pages (rounded down to MB) than the 262080 pages specified in the .cnf file!
2015-05-22T06:37:28.251633Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2015-05-22T06:37:28.251690Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-05-22T06:37:28.251773Z 0 [ERROR] Failed to initialize plugins.
2015-05-22T06:37:28.251789Z 0 [ERROR] Aborting

Tried also to set different values to innodb_data_file_path=ibdata1, like 4096M:
2015-05-22T06:53:33.213386Z 0 [ERROR] InnoDB: The innodb_system data file './ibdata1' is of a different size 262143 pages (rounded down to MB) than the 262144 pages specified in the .cnf file!

And tried to add +- 16kB blocks to the size, but not able to make it start, always there is mismatch in number of pages.

Suggested fix:
Make it possible to add another ibdata tablespace when max file size is reached for the first one.
[19 Jun 2015 12:50] MySQL Verification Team
Hello Przemyslaw,

Thank you for the report.
Observed this with 5.6.25.

Thanks,
Umesh
[19 Jun 2015 12:51] MySQL Verification Team
test results

Attachment: 77128.results.log (application/octet-stream, text), 25.99 KiB.

[29 Oct 2015 17:13] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.6.28, 5.7.10, 5.8.0 releases, and here's the changelog entry:

The system tablespace data file failed to extend automatically when
reaching the file size limit, and restarting the server failed with a size
mismatch error, preventing the addition of another system tablespace data
file. 

Thank you for the bug report.
[24 Nov 2015 12:51] Daniel Price
Posted by developer:
 
Changelog entry revised as follows:

The system tablespace data file did not extend automatically when reaching
the file size limit, causing startup to fail with a size mismatch error
and preventing the addition of another system tablespace data file.