MySQL Can’t create new tempfile
When i try to repair a crashed database table, i get error
1 2 3 4 5 6 7 8 9 10 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[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
1 |
myisamchk -r /var/lib/mysql/centovacastdb/visitorstats_sessions |
When i run, i get following error
1 2 3 4 5 6 7 |
[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.
1 2 3 4 5 6 7 8 |
[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