Should I be worried? MySQL error whilst importing database

Wellwisher

As some of you know, I am in the process of migrating from Simplemachines to Flarum.
On my localhost, I have a successful, fully functional 'Flarum migration'.

When I imported my "localhost" database to my "web-host" (aka production site), I got the error (see below) in the terminal.

Now, the puzzling thing is that... I can see all the discussion topics and users accounted for.

However, because I got this error in the terminal, I am unsure if the migration was 100% successful on my production site.

Flarum information

Flarum 1.3.0 PHP (basic install)
7.4.14 MySQL
8.0.28-0ubuntu 0.20.04.3

Extensions

Old Passwords

SSH Error information (when attempting to import database on a production site)


### ERROR 1452 (23000) at line 158393: Cannot add or update a child row: a foreign key constraint fails (`MyDataBaseName`.`#sql-9a68i1_5a721027`, CONSTRAINT `discussions_last_posted_user_id_foreign` FOREIGN KEY (`last_posted_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL)

Fyi, I saw this error when I run an import database ssh command via terminal.

luceos

Wellwisher it would probably be best to do a clean installation first. Then import only the rows, not the schema structure. There might be an issue here, but I cannot be sure honestly. The error might also have been caused by creating a table that relies on a foreign key constraint that hasn't been created or changed yet...

Wellwisher

Thanks for the reply. I had a brief look online about this error:

Full description of the MySQL Error:

ERROR 1452 (23000) at line 158393: Cannot add or update a child row: a foreign key constraint fails ('MyDataBaseName'.'#sql-9a68i1_5a721027', CONSTRAINT 'discussions_last_posted_user_id_foreign' FOREIGN KEY ('last_posted_user_id') REFERENCES 'users' ('id') ON DELETE SET NULL)

Here's what online resources state about this error type:

The error comes when you are trying to add a row for which no matching row in the other table.

MySQL Error type:

Cannot add or update a child row: a foreign key constraint fails

MySQL contents effected:

CONSTRAINT discussions_last_posted_user_id_foreign
FOREIGN KEY (last_posted_user_id)
REFERENCES users (id) ON DELETE SET NULL)

I think it might be as simple as copying over some tables. By which I mean, I am going to pay someone to resolve it. 😆 The fact that all my migration posts and users are accounted for gives me hope. It's time to take out the rubber 🦆.

Once "I" (cough) resolve it, I will post the solution here to help other SimpleMachine users out.

Wellwisher

Oh dear. Well this is embarrassing... 😊

The OP error was my fault. I wasn't following the "migration process" correctly.

My mistake was when I successfully merged my SMF & Flarum database on my localhost (WAMP install) I then tried to import that database to my web-host which resulted in the OP error.

When I followed the correct process (which involved setting up a fresh install and then importing the migrated database), I got this error:

ERROR at line 24843: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: 'INSERT INTOposts

I solved this error by enabling the binary-mode via the SSH command line when importing the new flarum database. I used the following command line as per my webhost (Dreamhost.com) and it worked!

mysql -h mysql.mywebsite.com -u USERNAME -p --binary-mode=1 DATABASENAME < Flarum.sql