Incorrect key file for table [How to Solve]

Problem phenomenon:

alter table portal_ app_ xxxx_ XXX add devno varchar (64) not null default ” comment ‘device machine coding’, add serialno varchar (64) not
null default ” comment ‘binding process serial number’, error_ code: 1034

MySQLerrorcode1034(ER_ NOT_ KEYFILE):Incorrectkeyfilefortable’XXXXXX’; trytorepairit

Find solutions:

After searching on the Internet, it is found that most of the solutions are repair, and then MyISAM is used to check and recover

However, we found that this method is only applicable to the table whose storage engine is MyISAM, so we continued to guess the search method

When using the repair table, the InnoDB table is reported directly, and the repair method is not supported

Guess the solution:

Since it is a 39 GB large table alter operation, it is assumed that the temporary table will not be used. Therefore, the temporary space of the slave library is checked to be/tmp, which is only half of the total memory, 16 GB. As a result, the temporary table space is not enough, and the alter process fails, which leads to the failure of modifying the table structure, and finally the failure of master-slave replication

The solution to this problem is as follows:

(1) Increase/tmp directory space

(2) Modify the TMP directory of MySQL database, migrate the TMP corresponding directory to a larger space, and then restart the database

| innodb_tmpdir | /data/mysql/mysql3306/data/inno_tmp_dir | <- This parameter specifies the TMP directory of the alter behavior
| tmpdir | /data/mysql/mysql3306/tmp

 

Similar Posts: