As many of you know that this blog runs on WordPress and under the hood of WordPress there is a MySQL database. MySQL database is quite good and is able to hold massive traffic this blog receives every day. However, just like any database MySQL needs tuning as well as proper management of the same. In this blog post we will discuss about how I received a very weird error WordPress Database Error and how I resolved it.
Last week, suddenly I got call from a friend that our blog is loading very slow. Well, when a new blog post is published or newsletter is sent out, it is very common to see a spike in traffic and momentarily slowness in the website performance. However, in this case, the website was consistently running slow. After a while we found a couple of new problems on the site. Due to the slowness of the performance, we also found out that WordPress scheduler was not publishing new blog posts as well as was not taking routine backup of the system.
After careful diagnostic I figured out that the issue was with MySQL Database. When I checked the error log, I found the following error in the log.
[Fri Sep 09 04:58:03 2016] [error] [client] WordPress database error Duplicate entry ‘3354142’ for key ‘PRIMARY’ for query INSERT INTO wp_options (option_name, option_value,autoload) VALUES (…)
It was very clear that there was a primary key violation in the options table. However, the problem was not easy to solve as I had personally not done any transactions with this table or there was no new update or plugin changes in the recent time. My first attempt was to restore this particular table from older database backup (I take frequent backup my site and its database). Even this particular problem failed and I was not able to get rid of the error.
Finally, I searched the internet but alas, there was no real help. At that time, I decided to do various trial and error. Trust me, I spend over 4 hours and various different tricks to get rid of this error. It was very clear to me that it was logical integrity error on the database, I had to spend time with lots of tables and logic. Well, after 4 hours, I finally found a solution and it was a very simple solution. I wish I had known this earlier and would have not spent over 4 hours on various trials and errors.
Solution / Fix:
I just ran following command it my issue was resolved.
REPAIR TABLE wp_options
That’s it! It was done.
The reality was that my table was corrupted and due to the same reason I was getting error related to Duplicate Key for my database table. Once I fixed the corruption of the table, everything worked just fine. Remember, in my case it was wp_options table which was corrupted, you must replace it with your table name and script will just work fine.
Additionally, if you want to just repair all the tables in your database, you can execute following script and it will generate scripts for every single table in your MySQL database. Once you execute the script, you will repair every single table of your database.
SELECT CONCAT('repair table ', table_name, ';')
FROM information_schema.tables
WHERE table_schema='YourDatabaseName';I hope you find this blog post useful. If you ever have any problem with the WordPress MySQL database, do reach out to me, I will be happy to help you to resolve any error related to the same.
Reference: Pinal Dave (https://blog.sqlauthority.com)
22 Comments. Leave new
why the restore from the backup didnt work?
Hi Pinal Dave
it is maybe possible, that you have a small issue in your script for repairing the tables for the database “xxxx”
In my opinion it should be “CONCAT” instead of the wrong value -CONCAT–>E<– ??
SELECT CONCAT('repair table ', table_name, ';') FROM information_schema.tables WHERE table_schema='database' ;
Agreed I think it s a typo.
Thanks. I’ll fix it.
Hi Antho,
thanks for your response. Could you fix this in the post above? (Mostly for other users. I know the right way for now :-) )
kind regards,
Axel
Hi thank you very much for pointing me in the right direction
Today, I faced similar problem – my woocommerce orders didn’t worked
My error_log was full of records like this :
[22-Oct-2017 18:04:47 UTC] Error in WordPress database Duplicate entry ‘31488’ for key ‘PRIMARY’ za upit INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES (30693, ‘_order_stock_reduced’, ‘1’) (‘wp-blog-header.php’), require_once(‘wp-includes/template-loader.php’), do_action(‘template_redirect’), WP_Hook->do_action, WP_Hook->apply_filters, call_user_func_array, WC_AJAX::do_wc_ajax, do_action(‘wc_ajax_checkout’), WP_Hook->do_action, WP_Hook->apply_filters, call_user_func_array, WC_AJAX::checkout, WC_Checkout->process_checkout, WC_Gateway_BACS->process_payment, WC_Abstract_Order->reduce_order_stock, add_post_meta, add_metadata
I have logged into phpmyadmin, selected all tables, and selected in dropdown to repair all tables :)
After that, my woocomerce orders worked like a charm
Thanks again,
Nikola
“The storage engine for the table doesn’t support repair” -> Azure DB for MySQL. Are there alternatives to REPAIR?
Hi we have a problem. The problem is that when we migrated we never changed home1(shared host) to home (dedicated host) and now we have problems in the cache plugin and other plugins because they want the home1 path but now is home. Any ideas of what we must do? By the way the site works ok but we cant use CDN and others
I was able to fix this problem otherwise; I ran myphpmysql and clicked wp_options table then on the search tab I searched “active_plugins” under field ‘option_name’ then i renamed “active_plugins” to “active_plugins.old”; instantly fixed the error.
I took time to write this and hopefully someone will find it useful.
1 – REPAIR TABLE wp_options
Where did you place this command ? wp-config file or mysql database ?
In MySQL query execution Window.
Thanks for responfing @Pinal Dave. Ran the MySQL query and received this error:
repair
Error
Table ‘xxx_xxx.wp_options’ doesn’t exist
xxx_xxx.wp_options
repair
status
Operation failed
OOOPS !
Used wrong db prefix. (mine is not wp_)
Ran command again.
Let’s see if it fixed my problem !
Got no love. Never had a problem importing sql backups before.
All kinds of wordpress issues after sql backup import threw this error:
— Indexes for table `xxx_options`
—
ALTER TABLE `xxx_options`
ADD PRIMARY KEY (`option_id`),
ADD UNIQUE KEY `option_name` (`option_name`)
MySQL said: Documentation
#1062 – Duplicate entry ‘0’ for key ‘PRIMARY’
WordPress database error: [Duplicate entry ‘0’ for key ‘PRIMARY’]
ALTER TABLE xxx_options ADD PRIMARY KEY (`option_id`)
Never had a problem importing sql backups before.
Thank you! This is what I needed. A quick, simple fix.
Dear all wordpress users,
Im new supporting wordpress. Recently we have launch our company official website and since then this error trigger:
>> insert into wpum_options failed.>> we have grant all required privilege and only this table will give error for insert. Update/Select/Delete works fine.
Can anyone advise if you may have encounter any similar issue like this?
Thank you, this error has been driving me crazy.
dear Pinal, Thank you. it was useful for me.
Thank you! Repair table worked for me.
Thank you so much, I imported into sequel pro which did not have an error on importing, then repaired the table and re-exported.
What can be done if I receive the error after trying `REPAIR TABLE wp_userdata;`
“The storage engine for the table doesn’t support repair”
Ask your webhost about that.