MySQL Can’t create new tempfile
When i try to repair a crashed database table, i get error
mysql> repair table visitorstats_sessions; +-------------------------------------+--------+----------+------------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------------------------+--------+----------+------------------------------------------------------------------------+ | centovacastdb.visitorstats_sessions | repair | error | Can't create new tempfile: './centovacastdb/visitorstats_sessions.TMD' | | centovacastdb.visitorstats_sessions | repair | status | Operation failed | +-------------------------------------+--------+----------+------------------------------------------------------------------------+ 2 rows in set (0.01 sec) mysql>
Check the table with myisamcheck
[root@centos7 ~]# myisamchk -a /var/lib/mysql/centovacastdb/visitorstats_sessions Checking MyISAM file: /var/lib/mysql/centovacastdb/visitorstats_sessions Data records: 704545 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed and last repair failed - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check data record references index: 3 - check data record references index: 4 - check record links MyISAM-table '/var/lib/mysql/centovacastdb/visitorstats_sessions' is usable but should be fixed [root@centos7 ~]#
It reported some errros. To fix, run
myisamchk -r /var/lib/mysql/centovacastdb/visitorstats_sessions
When i run, i get following error
[root@centos7 ~]# myisamchk -r /var/lib/mysql/centovacastdb/visitorstats_sessions - recovering (with sort) MyISAM-table '/var/lib/mysql/centovacastdb/visitorstats_sessions' Data records: 704545 myisamchk: error: Can't create new tempfile: '/var/lib/mysql/centovacastdb/visitorstats_sessions.TMD' MyISAM-table '/var/lib/mysql/centovacastdb/visitorstats_sessions' is not fixed because of errors Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag [root@centos7 ~]#
Since the error said “Can’t create new tempfile”, i checked disk usage to make sure disk is not full. Since disk usage is not fill, i run with -f option and it worked.
[root@centos7 ~]# myisamchk -rf /var/lib/mysql/centovacastdb/visitorstats_sessions - recovering (with sort) MyISAM-table '/var/lib/mysql/centovacastdb/visitorstats_sessions' Data records: 704545 - Fixing index 1 - Fixing index 2 - Fixing index 3 - Fixing index 4 [root@centos7 ~]#
See MySQL Repair