'colValue' can't have a default value

2 posts by 2 authors in: Forums > CMS Builder
Last Post: September 8, 2022   (RSS)

By petrogus - September 6, 2022

Hello All !

I am trying to transfer some websites from on server to other

I am using cpanel Backup Wizard or manual transfer but both of them I have the same error

Error: The system failed to execute the database script with the following errors: ERROR 1101 (42000) at line 125: BLOB, TEXT, GEOMETRY or JSON column 'colValue' can't have a default value

I have notice that the older host account work with 

Database Server MariaDB Server v5.5.5-10.3.36-MariaDB (Max Connections: 151)

and the new one 

MySQL Community Server - GPL v8.0.30 (Max Connections: 151)

I can't sold the problem, could you suggest any solution 

Thank you in advance

Petrogus

PetroGus

By daniel - September 8, 2022

Hi petrogus,

It can be difficult to tell the exact issue without seeing the backup file itself, but something that we've found in the past is that sometimes fields will end up with a default value of the string "NULL" rather than the NULL value. This is acceptable in MariaDB but causes an error when moving to MySQL, because MySQL doesn't support default values in mediumtext fields.

If you open your backup file in a text editor and search for "colValues" you should see something like this:

`colValues` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT 'NULL',

If there are quotations around 'NULL'  like the above (or any other value other than just NULL) it needs to be modified to:

`colValues` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,

It's likely that this isn't the only field where this is an issue, and they will all need to be corrected, though using a simple search/replace can take care of this relatively quickly.

Let me know if this helps, or if you have any other questions!

Thanks,

Daniel
Technical Lead
interactivetools.com