Support » Fixing WordPress » Find duplicates in PhpMyadmin Mysql

  • Hello,

    After a troublesome migration I have discovered that I seem to have a few duplicate entries in my database. For example: there are 2 lines in my wp_options that are perfectly identical, but one is ID 173 and the other is 430.

    I have been comparing and removing duplicates just by sorting on option_name (column name), but this takes ages.

    Isn’t there a SQL query that gives me a list of all the duplicates? So I don’t have to scroll 10 pages before finding the next duplicate?

    To begin with I would like to do this in wp_options and look in the column option_name. But it would be sweet if the query was easily edited to use in different tables and different columns.

Viewing 12 replies - 1 through 12 (of 12 total)
  • Hello,

    I think we can find the duplication via SQL Query. This is what is coming to my mind.

    SELECT 
        column, 
        COUNT(column)
    FROM
        table_name
    GROUP BY column
    HAVING COUNT(column) > 1;

    See if this helps.

    Thank you!

    Thread Starter supervinnie

    (@supervinnie)

    Hi, I edited the query to this:

    SELECT 
        option_name, 
        COUNT(option_name)
    FROM
        wp_options
    GROUP BY option_name
    HAVING COUNT(option_name) > 1;

    And I got the following result:

    asl_debug_data
    2
    asl_options
    2
    charitable_settings
    2
    elementor_log
    30
    fs_accounts
    2

    From what I can see, I indeed have this amount of duplicates.
    But, is there also a way that I end up in the screen where I can make the edits? I’m happy I have a list of the duplicates, but this isn’t a clickable list, is that even possible via the query?

    Dion

    (@diondesigns)

    My first thought is that something is wrong with your wp_options table because the option_name column should have a unique index, and that will prevent duplicates. Please run the following query to display the table structure, and then check what type of index is defined for the option_name column:

    SHOW CREATE TABLE wp_options;

    Thread Starter supervinnie

    (@supervinnie)

    In fact, I’m 100% sure the database is a mess. We asked the hosting company to help us migrate a website and they mess it up bigtime. They simply refused to help in any way because they said they did their job: transfer the files (nobody ever anything about the integrity of the data….).

    We have been able to fix most of the website, only thing left is that visitors can’t log in to their account via the front-end, only via wp-login.php (but this is for another topic probably).

    To answer your query, the results it gave me was:

    CREATE TABLEwp_options` (
    option_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    option_name varchar(191) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ”,
    option_value longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
    autoload varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ‘yes’,
    PRIMARY KEY (option_id),
    KEY option_name (option_name),
    KEY autoload (autoload)
    ) ENGINE=InnoDB AUTO_INCREMENT=9129 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci`

    I have been mannually going through every table in the database, comparing it with the original and trying to copy those settings. At least it helped me to get the website working for 90% again.

    Thread Starter supervinnie

    (@supervinnie)

    I also ran your query in the original database, which never has given any problems.

    CREATE TABLEwp_options` (
    option_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    option_name varchar(191) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ”,
    option_value longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
    autoload varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ‘yes’,
    PRIMARY KEY (option_id),
    UNIQUE KEY option_name (option_name),
    KEY autoload (autoload)
    ) ENGINE=InnoDB AUTO_INCREMENT=46258 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci`

    Dion

    (@diondesigns)

    It looks like whomever migrated your site ALTERed the option_name index from UNIQUE to a basic index. That’s why you are getting duplicate entries in the wp_options table.

    You won’t be able to restore the index to UNIQUE until you remove the duplicate entries. The problem should never return once the index is restored to UNIQUE.

    Thread Starter supervinnie

    (@supervinnie)

    Well, the cleanup has been done. But i haven’t found yet how to set it to unique?

    And to be honest there are many other tables to also check. They all had faults.

    Dion

    (@diondesigns)

    The following query will reset the index to UNIQUE as long as there are no duplicate entries:

    ALTER TABLE wp_options DROP INDEX option_name, ADD UNIQUE option_name (option_name) USING BTREE;
    

    Please give some thought to moving to a new hosting company. Hacking your WordPress database to facilitate a migration is inexcusable.

    Thread Starter supervinnie

    (@supervinnie)

    Thanks for the help.

    I also agree about the bad service. But I am one of 500 employees in a large company and the big boss isn’t gonna listen to my complaint about this small issue.
    So, we just fix it and hope it won’t happen again.

    Checked the table again for duplicates and ran your query. Then ran your previous query with the result:
    (I tried to mark the differences with the output from the original database with ****)

    CREATE TABLEwp_options` (
     option_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
     option_name varchar(191) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ”,
     option_value longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
     autoload varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ‘yes’,
     PRIMARY KEY (option_id),
     UNIQUE KEY option_name (option_name)****USING BTREE****,
     KEY autoload (autoload)
    ) ENGINE=InnoDB AUTO_INCREMENT=****10667**** DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci`

    I tried to Google wether these differences really matter. But from I can read the “auto increment” number is not really important, and the addition of “using btree” should affect the performance of the table.
    Am I correct?

    • This reply was modified 3 months, 2 weeks ago by supervinnie.
    • This reply was modified 3 months, 2 weeks ago by supervinnie.
    • This reply was modified 3 months, 2 weeks ago by supervinnie.
    Thread Starter supervinnie

    (@supervinnie)

    Continueing on the subject, I have found a difference in tables that is too big for me to solve. Would you mind if I put down my question for help here?

    In the original database the wp_posts table returns:

    TransIP MySQL/mysterymountain2022_nl_wordpress/		https://mysql.transip.nl/db_sql.php?db=mysterymountain2022_nl_wordpress
    Uw SQL-query is succesvol uitgevoerd.
    
    SHOW CREATE TABLE wp_posts
    
    wp_posts	CREATE TABLE <code>wp_posts</code> (
      <code>ID</code> bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      <code>post_author</code> bigint(20) unsigned NOT NULL DEFAULT '0',
      <code>post_date</code> datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      <code>post_date_gmt</code> datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      <code>post_content</code> longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
      <code>post_title</code> text COLLATE utf8mb4_unicode_520_ci NOT NULL,
      <code>post_excerpt</code> text COLLATE utf8mb4_unicode_520_ci NOT NULL,
      <code>post_status</code> varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'publish',
      <code>comment_status</code> varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
      <code>ping_status</code> varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
      <code>post_password</code> varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
      <code>post_name</code> varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
      <code>to_ping</code> text COLLATE utf8mb4_unicode_520_ci NOT NULL,
      <code>pinged</code> text COLLATE utf8mb4_unicode_520_ci NOT NULL,
      <code>post_modified</code> datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      <code>post_modified_gmt</code> datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      <code>post_content_filtered</code> longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
      <code>post_parent</code> bigint(20) unsigned NOT NULL DEFAULT '0',
      <code>guid</code> varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
      <code>menu_order</code> int(11) NOT NULL DEFAULT '0',
      <code>post_type</code> varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'post',
      <code>post_mime_type</code> varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
      <code>comment_count</code> bigint(20) NOT NULL DEFAULT '0',
      PRIMARY KEY (<code>ID</code>),
      KEY <code>post_name</code> (<code>post_name</code>(191)),
      KEY <code>type_status_date</code> (<code>post_type</code>,<code>post_status</code>,<code>post_date</code>,<code>ID</code>),
      KEY <code>post_parent</code> (<code>post_parent</code>),
      KEY <code>post_author</code> (<code>post_author</code>)
    ) ENGINE=InnoDB AUTO_INCREMENT=7151 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

    But the “new” database returns this:

    TransIP MySQL/mysterymountain_nl_mysterymountain2022_nl_wordpress/		https://mysql.transip.nl/db_sql.php?db=mysterymountain_nl_mysterymountain2022_nl_wordpress
    Uw SQL-query is succesvol uitgevoerd.
    
    SHOW CREATE TABLE wp_posts
    
    wp_posts	CREATE TABLE <code>wp_posts</code> (
      <code>ID</code> bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      <code>post_author</code> bigint(20) unsigned NOT NULL DEFAULT '0',
      <code>post_date</code> datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      <code>post_date_gmt</code> datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      <code>post_content</code> longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
      <code>post_title</code> text COLLATE utf8mb4_unicode_520_ci NOT NULL,
      <code>post_excerpt</code> text COLLATE utf8mb4_unicode_520_ci NOT NULL,
      <code>post_status</code> varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'publish',
      <code>comment_status</code> varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
      <code>ping_status</code> varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
      <code>post_password</code> varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
      <code>post_name</code> varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
      <code>to_ping</code> text COLLATE utf8mb4_unicode_520_ci NOT NULL,
      <code>pinged</code> text COLLATE utf8mb4_unicode_520_ci NOT NULL,
      <code>post_modified</code> datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      <code>post_modified_gmt</code> datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      <code>post_content_filtered</code> longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
      <code>post_parent</code> bigint(20) unsigned NOT NULL DEFAULT '0',
      <code>guid</code> varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
      <code>menu_order</code> int(11) NOT NULL DEFAULT '0',
      <code>post_type</code> varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'post',
      <code>post_mime_type</code> varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
      <code>comment_count</code> bigint(20) NOT NULL DEFAULT '0',
      PRIMARY KEY (<code>ID</code>),
      KEY <code>ID</code> (<code>ID</code>),
      KEY <code>post_author</code> (<code>post_author</code>),
      KEY <code>post_date</code> (<code>post_date</code>),
      KEY <code>post_status</code> (<code>post_status</code>),
      KEY <code>post_name</code> (<code>post_name</code>(191)),
      KEY <code>post_parent</code> (<code>post_parent</code>),
      KEY <code>post_type</code> (<code>post_type</code>)
    ) ENGINE=InnoDB AUTO_INCREMENT=7402 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

    When you put these results in https://text-compare.com/

    You can see how:
     comment_count bigint(20) NOT NULL DEFAULT ‘0’,
     PRIMARY KEY (ID),
     KEY post_name (post_name(191)),
     KEY type_status_date (post_type,post_status,post_date,ID),
     KEY post_parent (post_parent),
     KEY post_author (post_author)`
    Differs greatly from:
     comment_count bigint(20) NOT NULL DEFAULT ‘0’,
     PRIMARY KEY (ID),
     KEY ID (ID),
     KEY post_author (post_author),
     KEY post_date (post_date),
     KEY post_status (post_status),
     KEY post_name (post_name(191)),
     KEY post_parent (post_parent),
     KEY post_type (post_type)`

    And I have not been able to figure out how to correct this….

    Thread Starter supervinnie

    (@supervinnie)

    Maybe I underestimated myself. I managed to achieve the following.

    Original entry:
    CREATE TABLEwp_posts` (
    ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    post_author bigint(20) unsigned NOT NULL DEFAULT ‘0’,
    post_date datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
    post_date_gmt datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
    post_content longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
    post_title text COLLATE utf8mb4_unicode_520_ci NOT NULL,
    post_excerpt text COLLATE utf8mb4_unicode_520_ci NOT NULL,
    post_status varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ‘publish’,
    comment_status varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ‘open’,
    ping_status varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ‘open’,
    post_password varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ”,
    post_name varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ”,
    to_ping text COLLATE utf8mb4_unicode_520_ci NOT NULL,
    pinged text COLLATE utf8mb4_unicode_520_ci NOT NULL,
    post_modified datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
    post_modified_gmt datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
    post_content_filtered longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
    post_parent bigint(20) unsigned NOT NULL DEFAULT ‘0’,
    guid varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ”,
    menu_order int(11) NOT NULL DEFAULT ‘0’,
    post_type varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ‘post’,
    post_mime_type varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ”,
    comment_count bigint(20) NOT NULL DEFAULT ‘0’,
    PRIMARY KEY (ID),
    KEY post_name (post_name(191)),
    KEY type_status_date (post_type,post_status,post_date,ID),
    KEY post_parent (post_parent),
    KEY post_author (post_author)
    ) ENGINE=InnoDB AUTO_INCREMENT=7151 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci`

    The new entry after some editing:
    CREATE TABLEwp_posts` (
    ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    post_author bigint(20) unsigned NOT NULL DEFAULT ‘0’,
    post_date datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
    post_date_gmt datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
    post_content longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
    post_title text COLLATE utf8mb4_unicode_520_ci NOT NULL,
    post_excerpt text COLLATE utf8mb4_unicode_520_ci NOT NULL,
    post_status varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ‘publish’,
    comment_status varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ‘open’,
    ping_status varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ‘open’,
    post_password varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ”,
    post_name varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ”,
    to_ping text COLLATE utf8mb4_unicode_520_ci NOT NULL,
    pinged text COLLATE utf8mb4_unicode_520_ci NOT NULL,
    post_modified datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
    post_modified_gmt datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
    post_content_filtered longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
    post_parent bigint(20) unsigned NOT NULL DEFAULT ‘0’,
    guid varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ”,
    menu_order int(11) NOT NULL DEFAULT ‘0’,
    post_type varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ‘post’,
    post_mime_type varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ”,
    comment_count bigint(20) NOT NULL DEFAULT ‘0’,
    PRIMARY KEY (ID),
    KEY post_author (post_author),
    KEY post_name (post_name(191)),
    KEY post_parent (post_parent),
    KEY type_status_date (post_type,post_status,post_date,ID) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=7408 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci`

    Everything seems to be present. The only difference is the order of the items. Does that matter?

    Dion

    (@diondesigns)

    The order of the indexes doesn’t matter.

    One thing to keep in mind is that during every core update, WordPress will change the structure of core WP tables to their defaults if it finds any differences. So if your site isn’t using the current version of WordPress, perhaps the easiest way to fix the DB structure is to update to the current version.

Viewing 12 replies - 1 through 12 (of 12 total)
  • You must be logged in to reply to this topic.