MySQL – Fix Error – WordPress Database Error Duplicate Entry for key PRIMARY for Query INSERT INTO wp_options

SQL
17 Comments

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.

MySQL - Fix Error - WordPress Database Error Duplicate Entry for key PRIMARY for Query INSERT INTO wp_options mysqlerror-800x430

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.

Solarwinds

[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)

Solarwinds
, , ,
Previous Post
SQL SERVER – Setup Screen Not Launching While Updating a Patch
Next Post
SQL SERVER – How to Downgrade SQL Server Edition?

Related Posts

17 Comments. Leave new

  • why the restore from the backup didnt work?

    Reply
  • 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' ;

    Reply
  • 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

    Reply
  • Nikola Papratović
    October 22, 2017 11:47 pm

    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

    Reply
  • “The storage engine for the table doesn’t support repair” -> Azure DB for MySQL. Are there alternatives to REPAIR?

    Reply
  • The War of Destiny
    July 29, 2018 11:56 pm

    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

    Reply
  • 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.

    Reply
  • Patrick Yellen
    January 5, 2019 3:05 am

    1 – REPAIR TABLE wp_options

    Where did you place this command ? wp-config file or mysql database ?

    Reply
  • Patrick Yellen
    January 5, 2019 8:49 pm

    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

    Reply
  • Patrick Yellen
    January 5, 2019 8:55 pm

    OOOPS !

    Used wrong db prefix. (mine is not wp_)

    Ran command again.

    Let’s see if it fixed my problem !

    Reply
  • Patrick Yellen
    January 5, 2019 9:03 pm

    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.

    Reply
  • Thank you! This is what I needed. A quick, simple fix.

    Reply
  • 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?

    Reply
  • Thank you, this error has been driving me crazy.

    Reply

Leave a Reply

Menu