2

There is a SAN mounted 1TB partition for Mysql backups in our server with version 5.0.45-log. From two days, im seeing the log entries ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full. The mysqldump is 3.2GB. I assue that it will create an issue while restoring this DB.

 1. Can we analyse the Dump / backup inconsistancy without restoring (testing), if so how ? I can aware that this error is occurring due to one table named user_analysis.

Even a single table error leads to dump / backup incosistancy which leads to restoration failure. In my case daily backup is completing successfully. This issue might also cause due to insufficient space. But i had aroung 82GB free space in the SAN mounted partition. I could also see innoDB free space in the table.

 blade_server:master_db> show table status like "user_analysis"\G *************************** 1. row *************************** Name: user_analysis Engine: InnoDB Version: 10 Row_format: Compact Rows: 1312878 Avg_row_length: 33 Data_length: 43581440 Max_data_length: 0 Index_length: 19447808 Data_free: 0 Auto_increment: 1312171 Create_time: 2011-11-25 03:00:41 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: InnoDB free: 13190144 kB; (`user_id`) REFER `master_db/users`(`user_id`) 1 row in set (0.01 sec) blade_server:master_db> select (13190144) /1024/1024; +-----------------------+ | (13190144) /1024/1024 | +-----------------------+ | 12.57910156 | +-----------------------+ 1 row in set (0.00 sec) Where we had around 12.57 GB InnoDBfree space. InnoDB is per file : mysql> show variables like "innodb_data_file_path"\G *************************** 1. row *************************** Variable_name: innodb_data_file_path Value:

ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M;ibdata6:1000M;ibdata7:1000M;ibdata8:1000M;ibdata9:1000M;ibdata10:1000M;ibdata11:1000M;ibdata12:1000M

 2. What could be the root cause to this error ? I dont have any diskspace issues. Where can we pin point for this error ? 3. Will table is full" triggering in logs due to too low maximum size for"innodb_data_file_path".
Improve this question

1 Answer 1

Reset to default
1

You have set aside, 12000M for your InnoDB DataFiles (ibdata1...ibdata12). The only possible way for you to have this error is if all 12000M of InnoDB DataFiles have no more room to accommodate new rows into it. How is that possible?

There are four types of information that reside in InnoDB DataFiles

  • Table Data Pages
  • Table Index Pages
  • Table MetaData
  • MVCC Data

MVCC is Multiversion Concurrency Control. This facilitates ACID Compliance and Transaction Isolation for every SQL transaction, whether it is a single SQL statement or a block of SQL Statements. Whenever you run SQL against InnoDB Tables, that will definitely involve transaction control thus introducing new MVCC Data. Even if you do not execute START TRANSACTION...COMMIT/ROLLBACK paradigms in your application, AUTOCOMMIT is on by default. That will cause InnoDB to write MVCC Data around any data you are reading and/or writing. If there is enough MVCC in the InnoDB DataFiles, it could potentially block InnoDB row data of a certain length from being written.

You have three options to make this go away:

OPTION 1 : Add one or more InnoDB DataFiles to innodb_data_file_path

innodb_data_file_path=ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M;ibdata6:1000M;ibdata7:1000M;ibdata8:1000M;ibdata9:1000M;ibdata10:1000M;ibdata11:1000M;ibdata12:1000M;ibdata13:1000M;ibdata14:1000M

OPTION 2 : Add autoextend to the laste InnoDB DataFile in innodb_data_file_path

innodb_data_file_path=ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M;ibdata6:1000M;ibdata7:1000M;ibdata8:1000M;ibdata9:1000M;ibdata10:1000M;ibdata11:1000M;ibdata12:1000M:autoextend

OPTION 3 : Cleanup the InnoDB Infrastructure

This would be the most enduring solution because there is an option to keep Table Data Pages and Table Index Pages from ever entering the InnoDB DataFiles. You would have to set this option in my.cnf

[mysqld] innodb-file-per-table

This creates a separate tablespace (.ibd) file for each InnoDB table create after you restart mysql with this new option. Just putting in the option and restart mysql will not create the tablespace file. The added bonus for doing this is that you can collapse the innodb_data_file_path to the default:

[mysqld] innodb_data_file_path=ibdata1:10M:autoextend

I wrote up instructions on StackOverflow on how to do this.

Improve this answer
1
  • There is scheduled cron job which was prepared for QA, where it will connect to the remote Db using mysqlclient, drop the Database and fetech the last night compressed Db and uncompress it execute the dump script. This error is generating in logs while executing dump script remotely. So how can we restore the dump, in this case which was inconsistant. Commented Nov 27, 2011 at 1:39

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.