Description:
If you add 2 extra ibdata files to innodb_data_file_path, but the 2nd has permissions issue, the 1st new ibdata file is created, but not the second.
The problem comes in because the 1st is created fine, but the tablespace size stored in the header is not updated.
So when you fix the permissions problem for the 2nd ibdata file, then mysqld starts up properly, but you will see such error messages as below:
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
InnoDB: Error: tablespace size stored in header is 23040000 pages, but
InnoDB: the sum of data file sizes is 26240000 pages
How to repeat:
Have working instance with following options:
innodb_data_dir_home =
innodb_data_file_path =/data/mysql/ibdata1:10M:autoextend
Stop MySQL.
Create new directory (and subdirectory) /data1/mysql/ that is only owned/accessible by the root user.
Now add the 2 new ibdata files to the config. Point the first one to the working directory. Point the second to the non-working directory.
innodb_data_file_path =/data/mysql/ibdata1:10M;/data/mysql/ibdata2:10M;/data1/mysql/ibdata3:10M;:autoextend
Start mysqld.
Will fail to start, OS error 13 when trying to create ibdata3. But note that ibdata2 is created.
Now, change the path for ibdata3 to match that of ibdata2:
innodb_data_file_path =/data/mysql/ibdata1:10M;/data/mysql/ibdata2:10M;/data/mysql/ibdata3:10M;:autoextend
Now start mysqld again. It should start up fine, and create ibdata3.
However, you will now see errors such as these:
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
And you may even see as error such as this (below snippet was from an instance where the size of the 1st created ibdata file was 50000M, as opposed to 10M):
InnoDB: Error: tablespace size stored in header is 23040000 pages, but
InnoDB: the sum of data file sizes is 26240000 pages
Suggested fix:
If multiple tablespace files are added, update the tablespace size in the header after each new tablespace file is added instead of at the end. Otherwise, the LSNs will not match if there is a problem such as described here.
