Import SQL errors, and it’s always Wordfence

  • Resolved Lazylonewolf

    (@lazylonewolf)


    4 months, 1 week ago

    I’m recently learning web development seriously, so I now have a local development environment using Laragon on my computer.

    So my problem is when I export my website’s SQL database, and then import it into my local dev, WordFence’s tables almost always causes import errors. The latest error is:

    Error SQL query: Copy -- -------------------------------------------------------- -- -- Table structure for table cn_wfauditevents -- CREATE TABLE cn_wfauditevents ( id bigint(20) UNSIGNED NOT NULL, type varchar(255) NOT NULL DEFAULT '', data text NOT NULL, event_time double(14,4) NOT NULL, request_id bigint(20) UNSIGNED NOT NULL, state enum('new','sending','sent') NOT NULL DEFAULT 'new', state_timestamp timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci;

    MySQL said: #1273 - Unknown collation: 'utf8mb3_uca1400_ai_ci'

    I still don’t know much in terms of backend, but as far as I can tell the MySQl 8.4.3 that’s included with Laragon doesn’t have “utf8mb3_uca1400_ai_ci” collation and why it’s causing the error?

    Aside from excluding all the WordFence tables whenever I export the database, can anyone help because I’m not sure how to fix this…

    • Should I be exporting my WordPress’ database with different settings?
    • Should I try to figure out how to add utf8mb3_uca1400_ai_ci into my version of MySQL?
Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Support wfphil

    (@wfphil)

    4 months, 1 week ago

    Hi @lazylonewolf

    This is an issue when moving databases between different MySQL/MariaDB versions.

    Wordfence’s table schemas only set DEFAULT CHARSET=utf8 and does not hard-code a collation, so they inherit whatever utf8 / utf8mb3 collation your production server uses.

    The utf8mb3_uca1400_ai_ci collation is specific to MariaDB 10.6+ on your production server, and you’re running MySQL 8.4.3 on your local sever, which doesn’t recognize it.

    If you use the same version of MariaDB on your local Laragon server then that should fix it.

    Thread Starter Lazylonewolf

    (@lazylonewolf)

    4 months, 1 week ago

    Thanks for replying. I figured out what MariaDB version my webhost was using (11.8.3) and used that for my Laragon. I was starting to think that might’ve fixed it since the import was taking longer, but…

    Table structure for table cn_wfblockediplog
    --

    CREATE TABLE cn_wfblockediplog (
      IP binary(16) NOT NULL DEFAULT x AS 00000000000000000000000000000000,
      countryCode varchar(2) NOT NULL,
      blockCount int(10) UNSIGNED NOT NULL DEFAULT 0,
      unixday int(10) UNSIGNED NOT NULL,
      blockType varchar(50) NOT NULL DEFAULT 'generic'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci;

    MySQL said: Documentation

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS 00000000000000000000000000000000,
      countryCode varchar(2) NOT NULL,
    ...' at line 8

    Plugin Support wfphil

    (@wfphil)

    4 months, 1 week ago

    Hi @lazylonewolf

    Thank you for the update.

    That error is due to a faulty dump of your production database in some way.

Viewing 3 replies - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.