Re: innodb data file of different size
Asif, since you did not have any my.cnf, ibdata1 is an auto-extending data file (initially 10 MB) in the datadir of MySQL. And there are two 5 MB ib_logfiles in the datadir. If you specify the last data file with the autoextend option, InnoDB will extend the last data file if it runs out of free space in the tablespace. The increment is 8 MB at a time. An example: innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:100M:autoextend instructs InnoDB to create just a single data file whose initial size is 100 MB and which is extended in 8 MB blocks when space runs out. If the disk becomes full you may want to add another data file to another disk, for example. Then you have to look the size of ibdata1, round the size downward to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the rounded size of ibdata1 explicitly in innodb_data_file_path. After that you can add another data file: innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend 81024 pages = 1266 MB 16384 pages = 256 MB In your case the line innodb_data_file_path = ibdata1:1266M;ibdata2:10M:autoextend might work. But best to make a backup of the MySQL datadir first, just in case something goes wrong. Regards, Heikki . Subject: Re: innodb data file of different size From: Paul DuBois Date: Wed, 30 Jul 2003 00:07:49 -0500 At 22:08 -0400 7/29/03, Asif Iqbal wrote: I just decided to use my.cnf and bumped into this error message 030729 22:04:22 mysqld started InnoDB: Error: data file /usr/local/mysql/data/ibdata1 is of a different size InnoDB: 81024 pages (rounded down to MB) InnoDB: than specified in the .cnf file 16384 pages! InnoDB: Could not open data files 030729 22:04:23 Can't init databases 030729 22:04:23 Aborting 030729 22:04:23 InnoDB: Warning: shutting down a not properly started InnoDB: or created database! 030729 22:04:23 /usr/local/mysql-standard-4.0.13-sun-solaris2.8-sparc/bin/mysqld: Shutdown Complete 030729 22:04:23 mysqld ended I am assuming I need to change values in the following line taken from my.cnf file innodb_data_file_path = ibdata1:10M;ibdata2:10M:autoextend Please help Most likely you were running without any InnoDB-related options in your option file, so it was using its default values. Then you added the option shown above, which explicitly specifies sizes for the data files -- and which does not match the number or size of the default data file. What I'd do if it were me in your situation: - Remove the innodb_data_file_path line from your my.cnf file - Restart the server; it should come up normally now - Perform a complete dump of all your databases (just in case) (use mysqldump) - Perform a complete dump of your InnoDB tables (use mysqldump) - Remove your InnoDB tables - Shut down the server - Remove the default InnoDB data file and log files (these will be the files that begin with ib in your data directory) - Shut down the server - Add the innodb_data_file_path line to your my.cnf file - Start the server - Load the dump file back into the server to recreate your InnoDB tables Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb data file of different size
On Wed, 30 Jul 2003, Paul DuBois wrote: What I'd do if it were me in your situation: - Remove the innodb_data_file_path line from your my.cnf file done - Restart the server; it should come up normally now done - Perform a complete dump of all your databases (just in case) (use mysqldump) done - Perform a complete dump of your InnoDB tables (use mysqldump) how do I do this ? this is where I am stuck - Remove your InnoDB tables - Shut down the server - Remove the default InnoDB data file and log files (these will be the files that begin with ib in your data directory) - Shut down the server - Add the innodb_data_file_path line to your my.cnf file - Start the server - Load the dump file back into the server to recreate your InnoDB tables Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb data file of different size
innodb_data_file_path = ibdata1:1266M;ibdata2:10M:autoextend might work. But best to make a backup of the MySQL datadir first, just in That did it. However I could not put ibdata2. It was saying no access rights to file ibdata2. Once I removed the ibdata2 entry it started fine. Another thing I noticed that it can't create the pid file. However it has no problem creating the err file. Would you know why is it so ? case something goes wrong. Regards, Heikki . Subject: Re: innodb data file of different size From: Paul DuBois Date: Wed, 30 Jul 2003 00:07:49 -0500 At 22:08 -0400 7/29/03, Asif Iqbal wrote: I just decided to use my.cnf and bumped into this error message 030729 22:04:22 mysqld started InnoDB: Error: data file /usr/local/mysql/data/ibdata1 is of a different size InnoDB: 81024 pages (rounded down to MB) InnoDB: than specified in the .cnf file 16384 pages! InnoDB: Could not open data files 030729 22:04:23 Can't init databases 030729 22:04:23 Aborting 030729 22:04:23 InnoDB: Warning: shutting down a not properly started InnoDB: or created database! 030729 22:04:23 /usr/local/mysql-standard-4.0.13-sun-solaris2.8-sparc/bin/mysqld: Shutdown Complete 030729 22:04:23 mysqld ended I am assuming I need to change values in the following line taken from my.cnf file innodb_data_file_path = ibdata1:10M;ibdata2:10M:autoextend Please help Most likely you were running without any InnoDB-related options in your option file, so it was using its default values. Then you added the option shown above, which explicitly specifies sizes for the data files -- and which does not match the number or size of the default data file. What I'd do if it were me in your situation: - Remove the innodb_data_file_path line from your my.cnf file - Restart the server; it should come up normally now - Perform a complete dump of all your databases (just in case) (use mysqldump) - Perform a complete dump of your InnoDB tables (use mysqldump) - Remove your InnoDB tables - Shut down the server - Remove the default InnoDB data file and log files (these will be the files that begin with ib in your data directory) - Shut down the server - Add the innodb_data_file_path line to your my.cnf file - Start the server - Load the dump file back into the server to recreate your InnoDB tables Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb data file of different size
At 22:08 -0400 7/29/03, Asif Iqbal wrote: I just decided to use my.cnf and bumped into this error message 030729 22:04:22 mysqld started InnoDB: Error: data file /usr/local/mysql/data/ibdata1 is of a different size InnoDB: 81024 pages (rounded down to MB) InnoDB: than specified in the .cnf file 16384 pages! InnoDB: Could not open data files 030729 22:04:23 Can't init databases 030729 22:04:23 Aborting 030729 22:04:23 InnoDB: Warning: shutting down a not properly started InnoDB: or created database! 030729 22:04:23 /usr/local/mysql-standard-4.0.13-sun-solaris2.8-sparc/bin/mysqld: Shutdown Complete 030729 22:04:23 mysqld ended I am assuming I need to change values in the following line taken from my.cnf file innodb_data_file_path = ibdata1:10M;ibdata2:10M:autoextend Please help Most likely you were running without any InnoDB-related options in your option file, so it was using its default values. Then you added the option shown above, which explicitly specifies sizes for the data files -- and which does not match the number or size of the default data file. What I'd do if it were me in your situation: - Remove the innodb_data_file_path line from your my.cnf file - Restart the server; it should come up normally now - Perform a complete dump of all your databases (just in case) (use mysqldump) - Perform a complete dump of your InnoDB tables (use mysqldump) - Remove your InnoDB tables - Shut down the server - Remove the default InnoDB data file and log files (these will be the files that begin with ib in your data directory) - Shut down the server - Add the innodb_data_file_path line to your my.cnf file - Start the server - Load the dump file back into the server to recreate your InnoDB tables Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
