WordPress database error Duplicate entry X for key yarpp_content for query UPDATE wp_posts SET, MySQL Error
Today I was adding a note to a WordPress article I had written a couple of days ago and it appeared to POST without issue after I clicked the Update button in the WordPress admin. I went to the front-end of the site to verify the new content formatted correctly and to my surprise it wasn’t displaying so I figured I must have left a HTML tag or something open since I didn’t get any errors within the WordPress admin when I clicked the Update button for the Post. After further review the content didn’t exist and it appeared that the article had not posted the update at all since there were zero revisions from today. Fixing the problem is easy by using the below information that describes where I located the error, the error itself, and how to resolve it by issuing a single MySQL command.
How Was The WordPress/YARPP MySQL Duplicate Entry Error Located:
I was able to locate the error in the php_errors.log which I have configured in php.ini. If you do not have PHP errors being logged you can easily turn them on by adding the below lines to your php.ini file, creating the new log file, and then setting the proper permissions on the log file so it can be written to.
php.ini Configuration File Changes To Enable Logging:
- ; Log errors into a log file (server-specific log, stderr, or error_log (below))
- ; As stated above, you're strongly advised to use error logging in place of
- ; error displaying on production web sites.
- log_errors = On
- ; Log errors to specified file.
- error_log = /var/log/phperrors.log
So just set log_errors to On and error_log to the location you want the errors to be logged to such as /var/log/phperrors.log.
WordPress/MySQL Error When Attempting To Update A Post:
So the below output is from the error I saw in the PHP errors log. I have trimmed it down since the majority of the output is the actual article content and isn’t necessary to describe the issue in detail.
- [11-Jan-2011 17:50:52] WordPress database error Incorrect key file for table './DATABASE-NAME/wp_posts.MYI';
- try to repair it for query
- UPDATE `wp_posts` SET `post_author` = 99, `post_date` = '2011-01-11 12:50:52',
- `post_date_gmt` = '2011-01-11 17:50:52', `post_content` = 'ARTICLE CONTENT WAS HERE AND HAS BEEN REMOVED',
- `post_excerpt` = '', `post_status` = 'inherit', `post_type` = 'revision', `comment_status` = 'open',
- `ping_status` = 'open', `post_password` = '', `post_name` = '1234567890-autosave', `to_ping` = '', `pinged` = '',
- `post_modified` = '2011-01-11 12:50:52', `post_modified_gmt` = '2011-01-11 17:50:52', `post_parent` = 0987654321,
- `menu_order` = 0, `guid` = 'http://www.example.com/2011/01/07/1234567890-autosave'
- WHERE `ID` = 11111111 made by wp_create_post_autosave, wp_update_post, wp_insert_post
How To Resolve Duplicate Entry X For Key yarpp_content Error:
It took me a moment to realize the relationship between yarpp_content and the wp_posts table however after some looking around I figured out that yarpp_content is an index that has been created on the wp_posts table. Basically there has been an error where two entries are the same and it needs to be fixed before you can modify this article specifically. In my case you could still modify other articles so I would imagine that being similar on your end. To resolve the problem simply repair the table via MySQL and that should reindex the indexes associated with the table as shown in the below example output. Issue the below command once you have connected to the MySQL server and the proper database which by default may be wordpress unless you have changed it.
Repair MySQL Table To Resolve Duplicate MySQL Index Error:
- mysql> repair table wp_posts;
- | Table | Op | Msg_type | Msg_text |
- | wordpress.wp_posts | repair | status | OK |
- 1 row in set (26.07 sec)
It took me a minute to track this down but once the above command was issued it resolved the problem. Please be aware that while the table is being repaired it will be locked which will leave your site down until it unlocks. In my case it took between 60 and 180 seconds to repair and the issue was resolved.