Re: Problem on InnoDB - Tablespace enough but engine said table full
Heiki Thx... it's work :D Sorry, one liner Heikki Tuuri [EMAIL PROTECTED] wrote: Ady, - Alkuperäinen viesti - Lähettäjä: Ady Wicaksono Vastaanottaja: Ady Wicaksono ; Heikki Tuuri Kopio: Lähetetty: Friday, September 03, 2004 10:41 AM Aihe: Re: Problem on InnoDB - Tablespace enough but engine said table full I try to detect using MC (Midnight Commander) and found that after ;/data4/ibdata25:1802M it won't write anymore... I remove these data file and add /ibdata1/ibdata10:1500M and /data1/ibdata11:1500M I believe, All data below is empty but corrupt :( data file defintion -- #/data4/ibdata25.data3:576M;/data1/ibdata10:1500M; #/data1/ibdata11:1500M;/data1/ibdata12:1500M;/data1/ibdata13:1500M;/data2/ib data14:1500M;/data2/ibdata15:1500M;/data2/ibdata16 :1500M;/data2/ibdata17:1500M;/data2/ibdata18:1500M;/data2/ibdata19:1500M;/da ta3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ib data22:1500M;/data3/ibdata23:1500M;/data3/ibdata24:1500M; --- data file defintion -- I change my innodb_data_file_path to: innodb_data_file_path = /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15 00M;/data0/ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ibdata9 :1500M;/data4/ibdata25:1802M;/data1/ibdata10:1500M ;/data1/ibdata11:1500M It's working since i know i have 877184 pages now, later i simply add something like /data1/ibdata12:1500M but, i still have error when starting, here is InnoDB: Error: tablespace size stored in header is 877184 pages, but InnoDB: the sum of data file sizes is 953856 pages How to fix it ? since i found every data in innodb_data_file_path is not empty (953856 - 877184) / 64 = 1198 MB 1) Stop the mysqld server. 2) Add a new 1198M ibdata file at the end of innodb_data_file_path. 3) When you start mysqld, InnoDB will write that new ibdata file full of zeros, and increment the tablespace size stored in the tablespace header by 1198M. 4) Then stop the mysqld server, remove the extra 1198M ibdata file from innodb_data_file_path and delete that extra ibdata file. 5) Start mysqld again. 6) Voila! Now the tablespace size stored in the tablespace header agrees with the combined size of the files in innodb_data_file_path! Be very careful! From now on always follow the instructions at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html when you want to add a new ibdata file. Thx Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now.
Re: Problem on InnoDB - Tablespace enough but engine said table full
I try to detect using MC (Midnight Commander) and found that after ;/data4/ibdata25:1802M it won't write anymore... I remove these data file and add /ibdata1/ibdata10:1500M and /data1/ibdata11:1500M I believe, All data below is empty but corrupt :( data file defintion -- #/data4/ibdata25.data3:576M;/data1/ibdata10:1500M; #/data1/ibdata11:1500M;/data1/ibdata12:1500M;/data1/ibdata13:1500M;/data2/ibdata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16 :1500M;/data2/ibdata17:1500M;/data2/ibdata18:1500M;/data2/ibdata19:1500M;/data3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ib data22:1500M;/data3/ibdata23:1500M;/data3/ibdata24:1500M; --- data file defintion -- I change my innodb_data_file_path to: innodb_data_file_path = /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:1500M;/data0/ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ibdata9:1500M;/data4/ibdata25:1802M;/data1/ibdata10:1500M ;/data1/ibdata11:1500M It's working since i know i have 877184 pages now, later i simply add something like /data1/ibdata12:1500M but, i still have error when starting, here is InnoDB: Error: tablespace size stored in header is 877184 pages, but InnoDB: the sum of data file sizes is 953856 pages How to fix it ? since i found every data in innodb_data_file_path is not empty Thx Ady Wicaksono [EMAIL PROTECTED] wrote: May i know, how could i know which of the data files that InnoDB MySQL engine is not used ? Did i i made a mistake when adding table space ? Heikki Tuuri wrote: Ady, InnoDB thinks that the tablespace size is 10 706 MB. You have specified 36 782 MB of data files in the my.cnf line :(. Now you should figure out what are the data files that InnoDB is using, and remove the end of the innodb_data_file_path line, as well as the unused ibdata files. Remember that InnoDB uses data files starting from the first, in the order that you list them in innodb_data_file_path. Be very careful! Do not remove your valuable data! After that, you can grow the tablespace as instructed at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html . I have MySQL for heavy duty job . here is my InnoDB table space definition innodb_data_file_path = \ /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15 00M;/data0 \ /ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ \ ibdata9:1500M;/data4/ibdata25:1802M;/data3/ibdata25:1802M \ ;/data4/ibdata25.data3:576M;/data1/ibdata10:1500M;/data1/ibdata11:1500M;/dat a1/ibdata1 \ 2:1500M;/data1/ibdata13:1500M;/data2/ibd \ ata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16:1500M;/data2/ibdata17:1500 M;/data2/i \ bdata18:1500M;/data2/ibdata19:1500M;/dat \ a3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ibdata22:1500M;/data3/ibdata2 3:1500M;/d \ ata3/ibdata24:1500M; What i don't understand is : after ibdata1 - ibdata9 full is go to /data4/ibdata25 ... (1802Mbyte) But it's not continues to write to /data4/ibdata25.data3... /data1/ibdata10 and so on but said table is full when i try to write data to table The question is why ? Secondly What is the meaning of InnoDB: Error: tablespace size stored in header is 685184 pages, but InnoDB: the sum of data file sizes is 2354048 pages Thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! - Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard.
Re: Problem on InnoDB - Tablespace enough but engine said table full
Ady, - Alkuperäinen viesti - Lähettäjä: Ady Wicaksono [EMAIL PROTECTED] Vastaanottaja: Ady Wicaksono [EMAIL PROTECTED]; Heikki Tuuri [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Friday, September 03, 2004 10:41 AM Aihe: Re: Problem on InnoDB - Tablespace enough but engine said table full I try to detect using MC (Midnight Commander) and found that after ;/data4/ibdata25:1802M it won't write anymore... I remove these data file and add /ibdata1/ibdata10:1500M and /data1/ibdata11:1500M I believe, All data below is empty but corrupt :( data file defintion -- #/data4/ibdata25.data3:576M;/data1/ibdata10:1500M; #/data1/ibdata11:1500M;/data1/ibdata12:1500M;/data1/ibdata13:1500M;/data2/ib data14:1500M;/data2/ibdata15:1500M;/data2/ibdata16 :1500M;/data2/ibdata17:1500M;/data2/ibdata18:1500M;/data2/ibdata19:1500M;/da ta3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ib data22:1500M;/data3/ibdata23:1500M;/data3/ibdata24:1500M; --- data file defintion -- I change my innodb_data_file_path to: innodb_data_file_path = /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15 00M;/data0/ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ibdata9 :1500M;/data4/ibdata25:1802M;/data1/ibdata10:1500M ;/data1/ibdata11:1500M It's working since i know i have 877184 pages now, later i simply add something like /data1/ibdata12:1500M but, i still have error when starting, here is InnoDB: Error: tablespace size stored in header is 877184 pages, but InnoDB: the sum of data file sizes is 953856 pages How to fix it ? since i found every data in innodb_data_file_path is not empty (953856 - 877184) / 64 = 1198 MB 1) Stop the mysqld server. 2) Add a new 1198M ibdata file at the end of innodb_data_file_path. 3) When you start mysqld, InnoDB will write that new ibdata file full of zeros, and increment the tablespace size stored in the tablespace header by 1198M. 4) Then stop the mysqld server, remove the extra 1198M ibdata file from innodb_data_file_path and delete that extra ibdata file. 5) Start mysqld again. 6) Voila! Now the tablespace size stored in the tablespace header agrees with the combined size of the files in innodb_data_file_path! Be very careful! From now on always follow the instructions at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html when you want to add a new ibdata file. Thx Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem on InnoDB - Tablespace enough but engine said table full
Ady, InnoDB thinks that the tablespace size is 10 706 MB. You have specified 36 782 MB of data files in the my.cnf line :(. Now you should figure out what are the data files that InnoDB is using, and remove the end of the innodb_data_file_path line, as well as the unused ibdata files. Remember that InnoDB uses data files starting from the first, in the order that you list them in innodb_data_file_path. Be very careful! Do not remove your valuable data! After that, you can grow the tablespace as instructed at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html . I have MySQL for heavy duty job . here is my InnoDB table space definition innodb_data_file_path = \ /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15 00M;/data0 \ /ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ \ ibdata9:1500M;/data4/ibdata25:1802M;/data3/ibdata25:1802M \ ;/data4/ibdata25.data3:576M;/data1/ibdata10:1500M;/data1/ibdata11:1500M;/dat a1/ibdata1 \ 2:1500M;/data1/ibdata13:1500M;/data2/ibd \ ata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16:1500M;/data2/ibdata17:1500 M;/data2/i \ bdata18:1500M;/data2/ibdata19:1500M;/dat \ a3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ibdata22:1500M;/data3/ibdata2 3:1500M;/d \ ata3/ibdata24:1500M; What i don't understand is : after ibdata1 - ibdata9 full is go to /data4/ibdata25 ... (1802Mbyte) But it's not continues to write to /data4/ibdata25.data3... /data1/ibdata10 and so on but said table is full when i try to write data to table The question is why ? Secondly What is the meaning of InnoDB: Error: tablespace size stored in header is 685184 pages, but InnoDB: the sum of data file sizes is 2354048 pages Thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem on InnoDB - Tablespace enough but engine said table full
May i know, how could i know which of the data files that InnoDB MySQL engine is not used ? Did i i made a mistake when adding table space ? Heikki Tuuri [EMAIL PROTECTED] wrote: Ady, InnoDB thinks that the tablespace size is 10 706 MB. You have specified 36 782 MB of data files in the my.cnf line :(. Now you should figure out what are the data files that InnoDB is using, and remove the end of the innodb_data_file_path line, as well as the unused ibdata files. Remember that InnoDB uses data files starting from the first, in the order that you list them in innodb_data_file_path. Be very careful! Do not remove your valuable data! After that, you can grow the tablespace as instructed at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html . I have MySQL for heavy duty job . here is my InnoDB table space definition innodb_data_file_path = \ /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15 00M;/data0 \ /ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ \ ibdata9:1500M;/data4/ibdata25:1802M;/data3/ibdata25:1802M \ ;/data4/ibdata25.data3:576M;/data1/ibdata10:1500M;/data1/ibdata11:1500M;/dat a1/ibdata1 \ 2:1500M;/data1/ibdata13:1500M;/data2/ibd \ ata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16:1500M;/data2/ibdata17:1500 M;/data2/i \ bdata18:1500M;/data2/ibdata19:1500M;/dat \ a3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ibdata22:1500M;/data3/ibdata2 3:1500M;/d \ ata3/ibdata24:1500M; What i don't understand is : after ibdata1 - ibdata9 full is go to /data4/ibdata25 ... (1802Mbyte) But it's not continues to write to /data4/ibdata25.data3... /data1/ibdata10 and so on but said table is full when i try to write data to table The question is why ? Secondly What is the meaning of InnoDB: Error: tablespace size stored in header is 685184 pages, but InnoDB: the sum of data file sizes is 2354048 pages Thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers!
