Insert Statement on large MySQL table does not terminate

Question

I have a MySQL database with several tables. One table (InnoDB) is quite large (about 490 million rows). The table consists of 4 columns:

• id: int(11), auto_increment
• term: varchar(255)
• docId: int(11)
• count: int(11)

The table has several indices that involve up to three columns (BTREE).

The table has been filled with data for months and everything worked well. Today, all of a sudden, insert statements don't work any more. There is no error message from the DB. The insert statement just does not terminate.

I thouoght of a disk space or memory limitation. But even if I delete a number of rows (delete works) I can't insert new rows.

What could cause this problem?

Thanks in advance.

• RolandoMySQLDBA: Please run SELECT MAX(id) FROM yourtable;. What is its value ???
• Philipp: MAX(id) is 493732400.

Answer 1

from my knowledge:

1. Check if there is enough disk space available for the database to perform the insert operation - which you said thats ok!
2. Check the transaction log to see if there is anything locking the table.
3. As @Ergest Basha said, try changing the int type to BIGINT.
4. is there any chance of having full the log file?

Kind regards, Bill

• Philipp: Finally I moved the database to another machine with twice the disk space (this was planned anyway). The problem has disappeared. So it seems that it was indeed a disk space issue, although it's not quite clear why only that particular table was affected.
• Rick James: @Philipp - Well, if the new machine has only twice the disk space, you are likely to run out of space again soon. Provi8de SHOW CREATE TABLE; we may have suggestions.