XF 2.3 Error importing SQL dump from Cloud: Duplicate entry for primary key

[JimH]

I'm in the process of migrating from Cloud to self-host and encountering some unexpected headwinds. As a test, I'm trying to reconstruct my database using one of the auto-generated Cloud backups, and I'm getting a 'duplicate entry for primary key' error in the xf_session_activity table. From what I gather, this table is especially volatile, and because the site remains active while these Cloud backups are taken, I'm wondering if they might be capturing a torn/broken state. Thoughts? I'm tempted to simply delete all the entries in this table to unblock myself, but I feel like I shouldn't have to do something like that with an official backup.

 

[briansol]

don't even import the session table rows, it's not needed.

 

[Sim]

Are you importing into a Windows based MySQL server by any chance?

I ran into this error when creating a local dev copy of my production server - prod is running on Linux, local dev on Windows.

The cause was an issue translating binary fields between platforms (doesn't happen on Linux <-> Linux exports/imports) and the solution was to do the database dump using the --hex-blob flag.

See: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_hex-blob

 

[JimH]

don't even import the session table rows, it's not needed.

Well, that's somewhat reassuring. I'll drop those rows for now. If the database dump has consistency issues though, I might just be seeing the tip of the iceberg, no?

Are you importing into a Windows based MySQL server by any chance?

Thanks Slim! Nope. This is MariaDB running on a shared Linux web host. For better or worse, I have no control over how the dumps are done. This is XenForo's Cloud hosting doing the backups.

 

[Rene_V]

I'm trying to reconstruct my database using one of the auto-generated Cloud backups, and I'm getting a 'duplicate entry for primary key' error in the xf_session_activity table.

This type of errors happens because the backup is trying to insert or update a record with a value that already exists in a field that has a unique constraint. The unique constraint could be a primary key, unique index, or unique key. Because primary keys are and should be unique, MySQL will refuse to insert the duplicate and throw the error that you got. Can you post the full error message?

 

[briansol]

you are on the site/new db.
you were on the old site/old db while performing the dump.

there's a conflict trying to bring that session over.


Just don't bring it over, start empty, XF will re-start new sessions for everyone.