Bug #64752 When trying to restore tables or mysql dump: ERROR 1114 table is full
Submitted: 24 Mar 2012 2:58 Modified: 2 Apr 2012 17:56
Reporter: Jared Griffith Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.5.21 OS:Linux (CentOS 5.4)
Assigned to: CPU Architecture:Any

[24 Mar 2012 2:58] Jared Griffith
Description:
When trying to restore either a table or a full dump to a database, I am getting various table is full errors, including error 1114.
I am using innodb_file_per_table=1 in my.cnf
The server is a dual core Intel Xeon (4 core each) 2.4Ghz, 6GB of RAM, and a RAID 10 disk.

How to repeat:
Do a dump of either a table or the full database to a file, then use mysql -D db_name < filename.sql, on one or more tables I get ERROR 1114 table is full.  There is another error that I am getting on other tables, which I will update this with later when the restore is triggered by cron.
[24 Mar 2012 2:59] Jared Griffith
my cnf file from the server.

Attachment: my.cnf (application/octet-stream, text), 1.41 KiB.

[24 Mar 2012 9:43] Valeriy Kravchuk
Please, send the output of SHOW CREATE TABLE and SHOW TABLE STATUS for any of the tables affected.
[27 Mar 2012 16:35] Jared Griffith
| audit_cat_data | CREATE TABLE `audit_cat_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auditID` int(11) NOT NULL,
  `categoryID` int(11) NOT NULL,
  `requiredCompleted` smallint(6) unsigned NOT NULL DEFAULT '0',
  `numRequired` smallint(6) unsigned NOT NULL DEFAULT '0',
  `numAnswered` smallint(6) unsigned NOT NULL DEFAULT '0',
  `numVerified` smallint(6) unsigned NOT NULL DEFAULT '0',
  `applies` tinyint(4) NOT NULL DEFAULT '1',
  `override` tinyint(4) NOT NULL DEFAULT '0',
  `score` decimal(8,3) DEFAULT '0.000',
  `scorePossible` decimal(8,3) DEFAULT '0.000',
  `createdBy` int(11) DEFAULT NULL,
  `updatedBy` mediumint(8) DEFAULT NULL,
  `creationDate` datetime DEFAULT NULL,
  `updateDate` datetime DEFAULT NULL,
  `ruleID` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `auditID` (`auditID`,`categoryID`),
  KEY `categoryID` (`categoryID`),
  CONSTRAINT `FK_audit_cat_data` FOREIGN KEY (`auditID`) REFERENCES `contractor_audit` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_audit_cat_data_category` FOREIGN KEY (`categoryID`) REFERENCES `audit_category` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=30006938 DEFAULT CHARSET=latin1 |

| users | CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) DEFAULT NULL,
  `isGroup` enum('Yes','No') NOT NULL DEFAULT 'No',
  `email` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `isActive` enum('Yes','No') NOT NULL DEFAULT 'Yes',
  `lastLogin` datetime DEFAULT NULL,
  `accountID` int(11) NOT NULL,
  `passwordHistory` varchar(1000) DEFAULT NULL,
  `failedAttempts` tinyint(4) NOT NULL DEFAULT '0',
  `lockUntil` datetime DEFAULT NULL,
  `resetHash` varchar(100) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `fax` varchar(15) DEFAULT NULL,
  `phoneIndex` varchar(11) DEFAULT NULL,
  `passwordChanged` date DEFAULT NULL,
  `createdBy` int(11) DEFAULT NULL,
  `updatedBy` int(11) DEFAULT NULL,
  `creationDate` datetime DEFAULT NULL,
  `updateDate` datetime DEFAULT NULL,
  `emailConfirmedDate` date DEFAULT NULL,
  `timezone` varchar(50) DEFAULT NULL,
  `forcePasswordReset` tinyint(4) NOT NULL DEFAULT '0',
  `needsIndexing` tinyint(4) NOT NULL DEFAULT '1',
  `locale` varchar(5) DEFAULT 'en',
  `department` varchar(100) DEFAULT NULL,
  `inheritReportMenuFrom` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `id` (`id`),
  KEY `accountID` (`accountID`,`name`),
  KEY `email` (`email`),
  KEY `phoneIndex` (`phoneIndex`),
  KEY `isActive` (`isActive`,`name`),
  CONSTRAINT `FK_userAccount` FOREIGN KEY (`accountID`) REFERENCES `accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=62709 DEFAULT CHARSET=latin1 |
[27 Mar 2012 16:36] Jared Griffith
| audit_cat_data                       | InnoDB |      10 | Compact    | 24800085 |             80 |  1995440128 |                0 |   1292894208 |   3145728 |       30006938 | 2012-03-22 10:09:26 | NULL                | NULL                | latin1_swedish_ci |     NULL |  

| users                                | InnoDB |      10 | Compact    |    62398 |            294 |    18366464 |                0 |     18464768 |   5242880 |          62709 | 2012-03-23 12:57:29 | NULL                | NULL                | latin1_swedish_ci |     NULL |
[27 Mar 2012 16:54] Jared Griffith
Also, of interest, this is occurring when I am doing automated restores to a database on this machine.  If I take the table data out of the full dump file, and import that, it works just fine, without issue.  This only occurs when running the restore of the full dump file.
[27 Mar 2012 17:04] Jared Griffith
Machine specs:
Dell R4410 (I believe)
2 x Intel Xeon 2.4 Ghz
6 GB RAM
4 x 15k SAS RAID 10 on Perc 6/i controller
[27 Mar 2012 17:06] Jared Griffith
The audit_cat_data table is reporting full when trying to do a restore from dump, and the users table is reporting full when running the following sql:
update users set email = 'tester@picsauditing.com' WHERE accountID != 1100 AND email > '';
[27 Mar 2012 17:50] Jared Griffith
Disk space is also fine on the machine.
root@cobalt:/usr/local/IT/sql_files(master) $ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      190G   90G   90G  50% /
/dev/sda1              99M   13M   82M  14% /boot
tmpfs                 3.0G     0  3.0G   0% /dev/shm
[27 Mar 2012 17:53] Jared Griffith
I'm sorry, I am getting these errors on 2 separate databases.  In regards to the audit_cat_data, that is on a separate database called pics_yesterday, and the table doesn't exist yet.  This is where the restore is failing on audit_cat_data with the table is full error.
The other database is pics_config, where when running the update statement, I get users table is full error.
[27 Mar 2012 17:59] Jared Griffith
Here is the correct show create table and show table status from pics_yesterday for the audit_cat_data table that is reporting back as full:

| audit_cat_data | CREATE TABLE `audit_cat_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auditID` int(11) NOT NULL,
  `categoryID` int(11) NOT NULL,
  `requiredCompleted` smallint(6) unsigned NOT NULL DEFAULT '0',
  `numRequired` smallint(6) unsigned NOT NULL DEFAULT '0',
  `numAnswered` smallint(6) unsigned NOT NULL DEFAULT '0',
  `numVerified` smallint(6) unsigned NOT NULL DEFAULT '0',
  `applies` tinyint(4) NOT NULL DEFAULT '1',
  `override` tinyint(4) NOT NULL DEFAULT '0',
  `score` decimal(8,3) DEFAULT '0.000',
  `scorePossible` decimal(8,3) DEFAULT '0.000',
  `createdBy` int(11) DEFAULT NULL,
  `updatedBy` mediumint(8) DEFAULT NULL,
  `creationDate` datetime DEFAULT NULL,
  `updateDate` datetime DEFAULT NULL,
  `ruleID` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `auditID` (`auditID`,`categoryID`),
  KEY `categoryID` (`categoryID`),
  CONSTRAINT `FK_audit_cat_data` FOREIGN KEY (`auditID`) REFERENCES `contractor_audit` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_audit_cat_data_category` FOREIGN KEY (`categoryID`) REFERENCES `audit_category` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=30183242 DEFAULT CHARSET=latin1 |

| audit_cat_data                  | InnoDB |      10 | Compact    |       0 |              0 |       16384 |               0 |        32768 | 3243245568 |       30183242 | 2012-03-27 10:28:44 | NULL                | NULL       | latin1_swedish_ci |     NULL |
[27 Mar 2012 18:23] Jared Griffith
Also, I am using Rackspace's 5.5.21 ius rpm
[28 Mar 2012 6:45] Valeriy Kravchuk
Please, send the output of:

show variables like 'innodb_data%';
[28 Mar 2012 17:53] Jared Griffith
mysql> show variables like 'innodb_data%';
+-----------------------+---------------------------------+
| Variable_name         | Value                           |
+-----------------------+---------------------------------+
| innodb_data_file_path | ibdata1:10M:autoextend:max:500M |
| innodb_data_home_dir  |                                 |
+-----------------------+---------------------------------+
2 rows in set (0.00 sec)
[28 Mar 2012 18:07] Jared Griffith
Also, the audit_cat_data table alone is 2.4Gb.  I am wondering if maybe I am just running out of RAM which is causing the error.
Last night with the script, I had a different table on the pics_config database fail with the same error.
At the time of the script running, I am seeing spikes in RAM, Processor, and i/o, which is too be expected.
[28 Mar 2012 19:09] Jared Griffith
We have added additional RAM, the machine is now sitting there with 32 Gb of RAM, and we are still seeing the problem, even with adjusting the my.cnf values to what I have most recently attached.
[28 Mar 2012 19:10] Jared Griffith
New my.cnf

Attachment: my.cnf (application/octet-stream, text), 1.39 KiB.

[28 Mar 2012 23:50] Jared Griffith
I think we may have corrected the problem.  In the dump file, we had the following at the beginning of the file:
SET AUTOCOMMIT = 0;
as well as a set foreign_key_check=0, and 
COMMIT;
SET AUTOCOMMIT = 1;
at the end of the file.  I ran the dump file as a whole without those lines in it and did not get any error messages, and all of the data has been verified as being in the database.
We are disabling the restore tonight as we have a demo going on, so I won't know if this truly has corrected the problem until Friday 3/30/2012.  I will update you then once the script has run.
[29 Mar 2012 7:19] Valeriy Kravchuk
Your recent potential workaround described allows server to not keep REDO information and thus may help to get more free space in ibdata1 for your data. But your real problem is the following:

...max:500M

in the innodb_data_file_path variable. You limit size of data, explicitly, so eventually will not be able to add new data for sure. Check size of the ibdata1 file and if it is 500M already, please, consider removing that limitation above.
[29 Mar 2012 18:38] Jared Griffith
But it was my understanding that was just limiting the size of the ibdata table and the innodb_file_per_table=1 setting was the work around from getting a large disk consuming ibdata file?  Am I mistaken in that?
[30 Mar 2012 19:05] Valeriy Kravchuk
REDO information is always located in shared tablespace, ibdata* (even with innodb_file_per_table=1 and table's data in separate .ibd file). If you need to keep more REDO and ibdata* can not grow, operation fails. This is what you get.
[30 Mar 2012 19:12] Jared Griffith
After removing that commit line as mentioned before, both databases in question were restored perfectly as expected last night.  
I am sure that was the culprit (the additional memory we added I am sure helped), so you can close / resolve this.
[31 Mar 2012 9:03] Valeriy Kravchuk
I think this problem was a result of wrong configuration, not a bug.
[2 Apr 2012 17:56] Jared Griffith
As I mentioned, I believe that this was not an issue with MySQL, but an issue with the dump file that was being generated on the server where it was trying to make the entire dump a large transaction.  This was over sight on my part and didn't even see it / realize it until after creating this bug.