'colValue' can't have a default value
2 posts by 2 authors in: Forums > CMS Builder
Last Post: September 8, 2022 (RSS)
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
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,
Technical Lead
interactivetools.com