Remove duplicate rows from usermeta table

I don't know what is the problem in my installation (wp mu + buddypress) but I often see (not always), user activity in triplicate. The message

user xyz registered 5 minutes ago
would appear thrice

sometimes new groups created would list thrice.

And many users also list thrice on the screen in the wordpress when I delete a user and assign its posts to some other user.

I have seen that this occurs because of multiple identical meta entries at some point. Cleaning the group meta table is easy because of limited groups. I can manually delete identical rows. But I would like to clean my database of the duplicate rows, esp usermeta table. I need some sql for that because that table shows more than 80000 records.

Can the pros here help me write an sql query to check the usermeta table for duplicate rows and then delete the extra rows while keeping one copy with the lowest id?

  • aecnu

    Greetings Hpathy,

    Thank you for being a WPMU Dev member!

    I would imagine that some of those entries you see are temporary entries - though I cannot readily confirm this.

    I am also curious to know if a regular MySQL optimization and repair command would not clean this up?

    Executed from root --> ssh:

    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

    Then enter MySQL password when prompted. This password if you do not know it can in many cases dependent upon server configuration be found in my.cnf file.

    The above code does all databases.

    Another that does individual databases and the password is the database user specifically:
    mysqlcheck -op -u database_user database_name

    This gets around the need to know the master MySQL database password as required for the all databases command.

    I would love to hear how it goes if you try it out?

    Cheers, Joe :slight_smile:

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.