0

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.

Improve this question
2
  • Please run SELECT MAX(id) FROM yourtable;. What is its value ???
    – RolandoMySQLDBA
    Mar 15 at 17:29
  • MAX(id) is 493732400.
    – Philipp
    Mar 15 at 19:08

1 Answer 1

Reset to default
0

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

Improve this answer
2
  • 1
    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.
    – Philipp
    Mar 16 at 14:26
  • @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.
    – Rick James
    yesterday

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.