Mysql – ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

innodbMySQLmysql-5mysqldump

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".

Best Answer

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

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.

FACTS

You said you are using ext4. File size limit is 16TB. Thus, Sample.ibd should not be full.

You said your innodb_data_file_path is ibdata1:10M:autoextend. Thus, the ibdata1 file itself has no cap to its size except from the OS.

Why is this message coming up at all? Notice the message is "The table ... is full", not "The disk ... is full". This table full condition is from a logical standpoint. Think about InnoDB. What interactions are going on ?

My guess is InnoDB is attempting to load 93GB of data as a single transaction. Where would the Table is Full message emanate from? I would look at the ibdata1, not in terms its physical size (which you already ruled out), but in terms of what transaction limits are being reached.

What is inside ibdata1 when innodb_file_per_table is enabled and you load new data into MySQL?

Data Dictionary
Double Write Buffer
- Safety Net to Prevent Data Corruption
- Helps Bypass OS for Caching
Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs
Click Here to see a Pictorial Representation of ibdata1

My suspicions tell me that the Undo Logs and/or Redo Logs are to blame.

What are these logs? According to the Book

sxs

Chapter 10 : "Storage Engines" Page 203 Paragraphs 3,4 say the following:

The InnoDB engine keeps two types of logs: an undo log and a redo log. The purpose of an undo log is to roll back transactions, as well as to display the older versions of the data for queries running in the transaction isolation level that requires it. The code that handles the undo log can be found in storage/innobase/buf/log/log0log.c.

The purpose of the redo log is to store the information to be used in crash recovery. It permits the recovery process to re-execute the transactions that may or may not have completed before the crash. After re-executing those transactions, the database is brought to a consistent state. The code dealing with the redo log can be found in storage/innobase/log/log0recv.c.

ANALYSIS

There are 1023 Undo Logs inside ibdata1 (See Rollback Segments and Undo Space). Since the undo logs keep copies of data as they appeared before the reload, all 1023 Undo Logs have reached its limit. From another perspective, all 1023 Undo Logs may be dedicated to the one transaction that loads the Sample table.

BUT WAIT...

You are probably saying "I am loading an empty Sample table". How are Undo Logs involved? Before the Sample table was loaded with 93GB of data, it was empty. Representing every row that did not exist must take up some housecleaning space in the Undo Logs. Filling up 1023 Undo Logs seems trivial given the amount of data pouring into ibdata1. I am not the first person to suspect this:

From the MySQL 4.1 Documentation, note Posted by Chris Calender on September 4 2009 4:25pm:

Note that in 5.0 (pre-5.0.85) and in 5.1 (pre-5.1.38), you could receive the "table is full" error for an InnoDB table if InnoDB runs out of undo slots (bug #18828).

Here is the bug report for MySQL 5.0 : http://bugs.mysql.com/bug.php?id=18828

SUGGESTIONS

When you create the mysqldump of the Sample table, please use --no-autocommit

mysqldump --no-autocommit ... mydb Sample > Sample.sql

This will put an explicit COMMIT; after every INSERT. Then, reload the table.

If this does not work (you are not going to like this), do this

mysqldump --no-autocommit --skip-extended-insert ... mydb Sample > Sample.sql

This will make each INSERT have just one row. The mysqldump will be much larger (10+ times bigger) and could take 10 to 100 times longer to reload.

In either case, this will spare the Undo Logs from being inundated.

Give it a Try !!!

UPDATE 2013-06-03 13:05 EDT

ADDITIONAL SUGGESTION

If the InnoDB system table (a.k.a ibdata1) strikes a filesize limit and Undo Logs cannot be used, you could just add another system tablespace (ibdata2).

I just encountered this situation just two days ago. I updated my old post with what I did: See Database Design - Creating Multiple databases to avoid the headache of limit on table size

In essence, you have to change innodb_data_file_path to accommodate a new system tablespace file. Let me explain how:

SCENARIO

On disk (ext3), my client's server had the following:

[root@l*****]# ls -l ibd*
-rw-rw---- 1 s-em7-mysql s-em7-mysql     362807296 Jun  2 00:15 ibdata1
-rw-rw---- 1 s-em7-mysql s-em7-mysql 2196875759616 Jun  2 00:15 ibdata2

The setting was

innodb_data_file_path=ibdata1:346M;ibdata2:500M:autoextend:max:10240000M

Note that ibdata2 grew to 2196875759616 which is 2145386484M.

I had to embed the filesize of ibdata2 into innodb_data_file_path and add ibdata3

innodb_data_file_path=ibdata1:346M;ibdata2:2196875759616;ibdata3:10M:autoextend

When I restarted mysqld, it worked:

[root@l*****]# ls -l ibd*
-rw-rw---- 1 s-em7-mysql s-em7-mysql     362807296 Jun  3 17:02 ibdata1
-rw-rw---- 1 s-em7-mysql s-em7-mysql 2196875759616 Jun  3 17:02 ibdata2
-rw-rw---- 1 s-em7-mysql s-em7-mysql   32315015168 Jun  3 17:02 ibdata3

In 40 hours, ibdata3 grew to 31G. MySQL was once again working.

MySQL Cluster – Using ndb_desc and Partitions

With noofreplicas=2 and two nodes, you will have 2/2 = 1 nodegroup, meaning that you have no sharding at all (or one shard, as you prefer). All your partitions will be in both nodes. Your cluster will provide you high availability and better read throughup, but not write scaling.

What you show are the statistics for the table users, if you have not defined a custom partitioned schema, NDB will manage the partitioning for you using a hash on the primary key, in this case 4 partitions are created. Out of those 4 partitions, probably 2 will be "primary" on one node and "backup" in the other, and vice versa. You can define your own partitioning schema with standard MySQL Syntax (although having less partitions may reduce concurrency -MaxNoOfExecutionThreads- and future scaling -making the adding nodes more painful). You can know which partition each individual row is with EXPLAIN PARTITIONS.

Think of NDB as an implementation of RAID 10, but as you only have 2 nodes, you only get a RAID1. Your "block level" is a partition, each the default partition selection uses the hashing used for KEY() partitioning, which I believe it is based on the PASSWORD() function.

I believe the number of partitions by default, as you say, should be equal to the number of nodes (2), but I believe that it can create it with a larger number if you setup a larger MAX_ROWS, so it is not fixed.

Edit thanks to extra information: The other reason why partitions can be set higher by default is because, to allow for higher concurrency -in case you are using ndbmtd-, the number of partitions get multiplied by the Local Data Manager instances (in a nutshell, how many threads can potential read and write to a data node in concurrency). When you set MaxNoOfExecutionThreads to 4, as in your case, 2 of them are dedicated to that in the multithreaded server.

I must apologize for not thinking about this before, but there are tons of small changes from version to version, and the number of partitions are usually 4 in shard-enabled 4-node setups.

Do not worry too much, as even if you had extra partitions of that size (as you can force manually, to some extent) is not a bad thing at all. Partitions (or fragments) are just the arbitrary unit that is indivisible at node level. As I commented earlier extra partitions can provide additional performance by pruning non-desired rows (assuming you are partitioning correctly and using mostly key-value reads), and make easier the addition of extra nodes.