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.
I have good news and bad news on this one.
GOOD NEWS
You could use the general log as a table you can query
Step 01) Add this to /etc/my.cnf
[mysqld]
log
log-output=TABLE
Step 02) service mysql restart
OK mysqld is not recording every query in the table mysql.general_log
. Problem: look at the initial layout of mysql.general_log
:
mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
Table: general_log
Create Table: CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`thread_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`command_type` varchar(64) NOT NULL,
`argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.09 sec)
What good is a the general log as a CSV table
Step 03) Make mysql.general_log
a MyISAM table and index it
SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);
SET GLOBAL general_log = @old_log_state;
Now it looks like this:
mysql> show create table general_log\G
*************************** 1. row ***************************
Table: general_log
Create Table: CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`thread_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`command_type` varchar(64) NOT NULL,
`argument` mediumtext NOT NULL,
KEY `event_time` (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.00 sec)
mysql>
THe user and host values are appended together in the user_host fields.
How do you rotate out the general log?
Here is an example of how to blank out mysql.general_log
:
SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
CREATE TABLE mysql.general_log_new LIKE mysql.general_log;
DROP TABLE mysql.general_log;
ALTER TABLE mysql.general_log_new RENAME mysql.general_log;
SET GLOBAL general_log = @old_log_state;
Here is an example of how to keep the last 3 days of entries:
SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
CREATE TABLE mysql.general_log_new LIKE mysql.general_log;
INSERT INTO mysql.general_log_new
SELECT * FROM mysql.general_log WHERE event_time > NOW() - INTERVAL 3 DAY;
DROP TABLE mysql.general_log;
ALTER TABLE mysql.general_log_new RENAME mysql.general_log;
SET GLOBAL general_log = @old_log_state;
BAD NEWS
Anything you have collected in the text file version of the general log will not come for the ride. You can collect new entries going forward.
Best Answer
All statements that hit the server are stored inside the general log. So general log is duplicating your dump. The general log should be used only for debugging only, and it should be activated for the time required to spot a problem. Try to disactivate general log.