INSERT into wp_wfHits causes error on MariaDB replication

  • Resolved gshafer

    (@gshafer)


    2 years, 5 months ago

    I have 2 Ubuntu Servers (20.04.1) running mariadb. I have set them up to replicate. I have a main server online and its database is being replicated to my secondary server. I am able to get replication up and running fine. I have tested changes made to the main server database are replicated to the secondary server. Both are on a private network. The server is running several virtual hosts. All are WordPress sites and those files, as well as apache files for the virtual host are being kept up to date using rsync.

    To test that my secondary server is serving up sites correctly, I have pointed a browser to the IP of my secondary server. I get a listing of the sites that are being hosted. When I select a site, morrowgallery.com, it comes up fine.

    When I check the status of replication, it has stopped with this error:

    Error ‘Duplicate entry ‘76358’ for key ‘PRIMARY” on query. Default database: ‘morrowgallery’. Query: ‘INSERT INTO wp_wfhits (ctime, statusCode, isGoogle, IP, userID, URL, referer, UA, jsRun) VALUES (‘1654871899.272762’, ‘200’, ‘0’, ‘\0\0\0\0\0\0\0\0\0\0?????[‘, ‘0’, ‘https://morrowgallery.com/’, ‘https://morrowgallery.com’, ‘Mozilla/5.0+(compatible; UptimeRobot/2.0; https://www.uptimerobot.com/)’, ”)’

    It appears that Wordfence is trying to INSERT data into the secondary database.

    Is there a way to stop Wordfence from doing this?

    I have tried to set the database to read only (SET GLOBAL read_only = 1;) but it seems like that may not be working.

    The page I need help with: [log in to see the link]

Viewing 1 replies (of 1 total)
  • Plugin Support wfpeter

    (@wfpeter)

    Hi @gshafer,

    We don’t see replication come up a great deal but we think this may be a replication issue and not Wordfence itself trying to INSERT data into the secondary database. If you haven’t configured the live WordPress site to use two databases, Wordfence can’t run queries on the second database because it won’t know it exists.

    The primary key for the wfhits table in the error message is the id column, which is auto-incremented, so each time Wordfence INSERTS data into the table, mysql/mariadb generates a new ID.

    What you’re seeing could be similar to this StackExchange question: https://dba.stackexchange.com/questions/139257/mysql-replication-duplicate-entry-for-primary-key

    Note how row-based replication seemed to work in the case above, so this may also work for you if you’re not using it already. It might not be the best solution, but it’s worth trying. Binlog formats and options are described in the mariadb docs here, but read the “be careful” sections in detail: https://mariadb.com/kb/en/binary-log-formats/

    Thanks,

    Peter.

Viewing 1 replies (of 1 total)
  • The topic ‘INSERT into wp_wfHits causes error on MariaDB replication’ is closed to new replies.