Hacked. Now My SQL denying INSERT command for wp_users

  • Hi all,

    At the start of the year, my site was hacked which resulted in my losing Admin privileges. I solved this by creating a new user in the database itself.

    It would appear this has now happened again! However, this time when I try to create a new user in the database, following this guide: https://www.wpbeginner.com/wp-tutorials/how-to-add-an-admin-user-to-the-wordpress-database-via-mysql/

    I get this error:
    [IMG]https://i473.photobucket.com/albums/rr95/richardpetersphotography/MySQL_zps43b0dabc.jpg[/IMG]

    One thing I tried before creating a new user in the database, was to create a new user in the dashboard. Although this appeared to NOT work, due to no longer having admin privileges, I did get an email to say a new user was created. So, I tried to login with it ONCE, and it did not work. This was swiftly followed by an email to say there had been TWO attempts to login with that user and my IP was banned for 48 hours (I use the Limit Login Attempts plugin).

    Could my inability to create a new user in the database be because this plugin has locked my IP – but then surely I wouldn’t have been able to get in to the database at all if that were the case?

    Any help appreciated!

Viewing 10 replies - 1 through 10 (of 10 total)
  • Thread Starter richardpeters

    (@richardpeters)

    Sorry attempt to embed screen grab didn’t work, here’s the link to it
    https://i473.photobucket.com/albums/rr95/richardpetersphotography/MySQL_zps43b0dabc.jpg

    Thread Starter richardpeters

    (@richardpeters)

    Hi all, after more investigation, it appears that my hack has coincided with running out of space on the database which could be causing the INSERT problem?

    My hosting allows 150mb database and from within the DB itself it says the size is 149.3 MiB

    So, assuming the access denied issue above is due to the DB being full, can anyone explain how I clear out the old post revisions etc to free up space. The issue being, with no admin rights, I can’t install a plugin to do it so must do it at database level…and I’m not an database expert ??

    Get and download a backup of your database.

    Using phpmyadmin, in the view which has one row per table, select a table. One of the operations to perform upon the selected table(s) is OPTIMIZE. Maybe start on the small tables, and build up to the larger ones, maybe do them in small groups so as to not bust the quota.
    Does this save you any space ?

    If you can login, then install a plugin that does database optimisation, or limits revisions etc. This should let you save more space.
    If this continues then you need to either organise more space or change hosting.

    Thread Starter richardpeters

    (@richardpeters)

    Hi Ross, thank you for the reply.

    I’ll go through and try that. I had already optimized the entire table in one go, which came back with this yellow warning (the same as when trying to create a new user).

    https://i473.photobucket.com/albums/rr95/richardpetersphotography/DB_zps8bf22ce5.jpg

    Also, you can see that wp_commentsmeta is where all the size is being eaten up. Originally it had 6000 or so records. I deleted them but now despite saying ZERO records, it is remains 76mb in size even after optimizing.

    I can’t download any plugins right now as I have lost admin rights – which was why I was trying to create a new user in the DB, when I discovered the bigger problem ??

    I think that

    https://i473.photobucket.com/albums/rr95/richardpetersphotography/DB_zps8bf22ce5.jpg

    May be its way of saying “out of database quota”.

    Just maybe it can be coached into running by optimising the smallest tables first, start with the smallest tables, even one at a time.
    The issue is most likely caused by VARCHAR fields being edited.

    In the most extreme case, drop some tables that are big enough to make a difference. Optimise the remaining tables. Extract from the backup a slice which will just restore the dropped table. Restore that table.

    OR

    Drop all the tables and reload from the backup.

    Then give the plugins a go that will squeeze the WordPress data, like dropping old revisions.

    Thread Starter richardpeters

    (@richardpeters)

    Thanks Ross.

    I’m basically getting the 1142 INSERT error on any table I try to optimize, even when doing them one by one and small ones first.

    I’ll try dropping the wp_commentsmeta table I guess as that’s the one causing the problem and it’s not even got anything in it now anyway! I’ve got a back up of that table specifically that I just did, so I’ll see how it goes!

    I’ll give this a go a little later and report back…fingers crossed!

    Thread Starter richardpeters

    (@richardpeters)

    Morning,

    Ok so after deleting all content form wp_commentsmeta the size was still 76mb. So, I just want to check I’ve got this right before I do the next step:

    1) I backed up the DB
    2) I backed up JUST the wp_commentsmeta table
    3) I dropped the table (it free’d up the much needed 76mn finally)

    Can I know just go and re-import the backup of the wp_commentsmeta table or do I need to do something else to put that table back in?

    Many thanks
    Richard

    Thread Starter richardpeters

    (@richardpeters)

    Also, even with this table deleted and my database only half the limit size, I am STILL getting an 1142 INSERT error when trying to optimize tables or create a new admin user!?

    Thread Starter richardpeters

    (@richardpeters)

    Ok chaps (sorry for all the updates but saves being given advice that is now superseded),

    I’ve spoken to my host and they have now manually unsuspended my DB and the 1142 ERROR is gone it would appear.

    So I’m now left with the query of how to put back the wp_commentsmeta table, as per the post one up from above.

    Many thanks!

    A) You create a database, create a database user and password. These could be the same as your previous installation. Put these values into your wp-config.php file.

    B) Using phpmyadmin, you import the .sql file. You may need to edit the backup for various reasons, such as removing its “create database phrase”, OR to break the loading time down to an acceptable limit, maybe separate out the big tables, maybe split a table into separate loads, if you keep them in order the database wont know the difference.

Viewing 10 replies - 1 through 10 (of 10 total)
  • The topic ‘Hacked. Now My SQL denying INSERT command for wp_users’ is closed to new replies.