Posted by rt-9988 on Thu, May 08, 2025 - 10:16am --- Updated: May 08 '25 - 10:31am

Hi all

I exported the database of my backdrop cms using "Backup-migrate". Everything okay, no error.

Now I import this database to the destination cms, also using the "back-up-migrate" tool. I do not receive any error, everything okay.

Both installations on version 1.30.2 all caches cleared.

 When I log in as an admin I have an error in the status report:

Using the link I have this message:

And pushing the button I get the following error message:

I tried to convert the database with a php/SQL script, imported the database in a newly created utf8mb4 format database - it did NOT help.

My questions:

Why is a conversion mandatory? As I understood the formats are downwards compatible - so no reason to convert.

What is the SQL error and where can I fix it?

What is the best solution / work around to get my site up and running?

Many thanks for your help!

Cheers, Rolf

 

And here the info of the exporting cms:

And the importing CMS:

 

 

Accepted answer

I wonder if what is happening here is the default database settings are for utf8mb4 if the database server supports it.

In settings.php or settings.local.php if that's where you put your database settings you may have settings that look like: 

$database = array(
'database' => 'database_name',
'username' => 'user',
'password' => 'pass',
'host' => 'host_name',
);

if you add in charset and collation then it may stop nagging you to convert. So your new settings would look something like: 

$database = array(
'database' => 'database_name',
'username' => 'user',
'password' => 'pass',
'host' => 'host_name',
'charset' => 'utf8',
'collation' => 'utf8_general_ci',
);

Those are fallback settings it utf8mb4 isn't supported.

To go back to your questions:

With both of those, include as much detail as possible including Backdrop versions, database server versions and other environment information.

There is also a recently added conversion tool within Bee - see https://github.com/backdrop-contrib/bee/wiki/Usage#utf8mb4-convert - though this is based on the core methods so you may not notice any difference.

Comments

Thu, May 08, 2025 - 11:58am by rt-9988

Hi

Thanks for the quick answer. Looks interesting and hope I can verify that this solves the issue. Not yet sure how I can use the bee tool in my MAMP environment.

Cheers, Rolf

Thu, May 08, 2025 - 12:21pm by yorkshirepudding

I think people have used bee in MAMP.  I've posted in our chat channel to see if anyone can jump in here and help:

#Backdrop > Bee @ 💬

Fri, May 09, 2025 - 9:38am by yorkshirepudding

One person said:

I use bee in mamp - but not sure what i can offer. I did a basic setup and it just works...

Thu, May 08, 2025 - 12:24pm by yorkshirepudding

It is possible to remove that line manually from the SQL file and it should then import to MySQL fine.

Fri, May 09, 2025 - 12:44pm by rt-9988

Thank you for all your help.

In the meantime I just edited the export db-file and removed the top line with the sandbox comment. --> Still the same issue.

I exported also with the phpMyAdmin from the hosting provider - there was no "sandbox -line" inserted and it did not work either.

Two things I do not understand: 1) why is Backdrop urging me to convert, and 2) why and where is the bug in the conversion (I guess that it has nothing to do with a top line in the exported file.

I'm new in the forum - do I have to post this issue in another thread e.g. maintainer of backup-migrate tool or core?

Thanks,

BTW, here an interesting report about utf8 vs utf8mb4

Sat, May 10, 2025 - 7:24am by yorkshirepudding

I wonder if what is happening here is the default database settings are for utf8mb4 if the database server supports it.

In settings.php or settings.local.php if that's where you put your database settings you may have settings that look like: 

$database = array(
'database' => 'database_name',
'username' => 'user',
'password' => 'pass',
'host' => 'host_name',
);

if you add in charset and collation then it may stop nagging you to convert. So your new settings would look something like: 

$database = array(
'database' => 'database_name',
'username' => 'user',
'password' => 'pass',
'host' => 'host_name',
'charset' => 'utf8',
'collation' => 'utf8_general_ci',
);

Those are fallback settings it utf8mb4 isn't supported.

To go back to your questions:

With both of those, include as much detail as possible including Backdrop versions, database server versions and other environment information.

There is also a recently added conversion tool within Bee - see https://github.com/backdrop-contrib/bee/wiki/Usage#utf8mb4-convert - though this is based on the core methods so you may not notice any difference.

Sat, May 10, 2025 - 11:14pm by rt-9988

Hi

I added the 2 additional settings in settings.php, did a restart - and the error disappeared!

Thanks for the support!

(but the issues with the conversion still remain .. will open an issue in github queue)