“Fatal Error” Adding Keys

Sometimes the Index WP MySQL For Speed plugin for WordPress generates errors when you use it to add keys. These can look like this or similar:

Fatal error: Uncaught ImfsException: [0]: Index for table 'wp_postmeta' is corrupt; try to repair it

What can you do about this?

Don’t panic. This (usually) does not mean your site has been corrupted. It simply means your MariaDB or MySQL server was not able to add the keys to that particular table. Your site will still function ,but you won’t get the benefit of high-performance keys on the particular table.

Very large tables are usually the ones causing this kind of error. Very likely you ran out of temporary disk space on your MariaDB or MySQL database server machine. The database server makes a temporary copy of a table when you add keys to it; that allows it to add the keys without blocking your users.

It’s possible to correct this particular problem by changing your MariaDB or MySQL configuration.

If you have access to a privileged shell on your database server machine and you have system administration skills you can attempt to troubleshoot and attempt to solve the problem. If not you can ask a system administrator to help you.

These instructions are for standard installations on Ubuntu Linux. If you use a different operating system or a non-standard installation you must modify them to match your machine.

Troubleshooting

Let’s find out more about what caused the error. Retry the operation that caused the error, then promptly give this command to look at the database server log file. (Why promptly? So other entries in the log file won’t make it hard to find the ones we want.)

journalctl -u mariadb | tail -100

Look at the log output. You may see some lines that look like this

(timestamp) 9 [ERROR] [MY-012639] [InnoDB] Write to file (merge) failed at offset 625999872, 
   1048576 bytes should have been written, only 696320 were written. 
   Operating system error number 28. 
   Check that your OS and file system support files of this size.
   Check also that the disk is not full or a disk quota exceeded.
(timestamp) 9 [ERROR] [MY-012640] [InnoDB] Error number 28 means ‘No space left on device

Your logfile lines probably will be different. Still, look for complaints about disks being full. If you see them, then you can investigate whether it’s the temporary-storage disk that filled up.

Give this command. It reports the free space on your system’s two temporary-storage directories, /tmp and /var/tmp.

df -h /tmp /var/tmp

The first one, /tmp, is sometimes quite small and can be a fast RAMdisk. The second one is larger in that case. But sometimes the two directories are on the same physical disk drive. If so, they’ll show up as having the exact same amount of space. In that case, this article’s advice won’t help you. If you see less free space on /tmp, you can proceed to the next step.

Next, let’s find out whether your database server is configured to use the /tmp directory for its temporary files. Set your shell’s working directory to the root of your WordPress installation (the directory containing wp-config.php and other WordPress files). Then give this command.

wp db query "SHOW VARIABLES LIKE 'tmpdir';"

Or you can give the SQL statement SHOW VARIABLES LIKE 'tmpdir'; to phpmyadmin or some other MySQL client program. If that tmpdir variable has the value /tmp you can reconfigure your MySQL server.

Reconfiguring MariaDB

Edit the configuration file like this:

sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf

Look for the line starting with tmpdir = and change the value from /tmp to /var/tmp. Save the file.

Then restart the database server using

sudo systemctl restart mariadb

That should correct your problem.

Reconfiguring MySQL

Edit the configuration file (for MySQL 8) like this:

 sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

For older versions you may have to look around for the appropriate .cnf file to edit.

Look for the line starting with tmpdir = and change the value from /tmp to /var/tmp. If the line is not present in your file, add it. When you’re done the line should say this. Spaces don’t matter.

tmpdir = /var/tmp

Save the file. Then restart the database server using this command

sudo systemctl restart mysql

That should correct your problem. Try again to add the indexes to your table.

Leave a Comment