Bug #13110 Improve innodb_data_file_path so that more than one file can be auto-extending
Submitted: 11 Sep 2005 6:32 Modified: 16 Mar 2006 13:01
Reporter: Brett Liotta Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:4.1.8-nt-max-log OS:Windows (Windows 2003 Server)
Assigned to: Heikki Tuuri CPU Architecture:Any

[11 Sep 2005 6:32] Brett Liotta
Description:
When changing the my.ini file from this:
#*** INNODB Specific options ***
innodb_data_home_dir=C:/MySQL InnoDB Datafiles/
innodb_autoextend_increment=8M

to this:
#*** INNODB Specific options ***
innodb_data_home_dir=C:/MySQL InnoDB Datafiles/
innodb_autoextend_increment=8M
innodb_data_file_path=ibdata1:10M:autoextend:max:1020M;ibdata2:10M:autoextend

I can't restart the Mysql server.

When I take out this line:
innodb_data_file_path=ibdata1:10M:autoextend:max:1020M;ibdata2:10M:autoextend

It works fine.

There is already a 1018 Meg ibdata1 file in the innodb_data_home_directory. 

How to repeat:
Add this line to the my.ini file:

innodb_data_file_path=ibdata1:10M:autoextend:max:1020M;ibdata2:10M:autoextend

There is already a 1018 Meg ibdata1 file in the innodb_data_home_directory.

Suggested fix:
It should put a max on the my ibdata1 file of 1020M.  And create a new file called ibdata2 of 10M that is autoextending.

And, Mysql server should restart as normal - instead of giving me this error message.
*******
Could not re-connect to the MySQL Server.
Server could not be started.
[11 Sep 2005 8:32] Heikki Tuuri
Hi!

You have presented a good idea for the future syntax of innodb_data_file_path. But currently, in the correct syntax, only the last data file can be specified as auto-extending.

Regards,

Heikki
[11 Sep 2005 16:24] Brett Liotta
I figured out my problem.  Although Heikki came up with a good idea, my initial problem was that the syntax for the my.ini file was a little tricky.

Since my ibdata1 file was 1018M, and all I really wanted to do was add another file called ibdata2, I had to do it like this:

innodb_data_file_path=ibdata1:1018M;ibdata2:10M:autoextend

The solution was to round the current ibdata1 file to the nearest Megabyte and put that number in the my.ini file (like the above line).  So, now I know how to add innodb datafiles when the current one gets big.