Tagged: mysql database
- This topic has 16 replies, 3 voices, and was last updated 4 days, 16 hours ago by Dee Nutbourne.
February 1, 2020 at 3:07 pm #505621Mark SmithParticipant
Even though the database backup was run on MySQL 8, the SQL file is only compatible with version 5.6.
In order to replicate and how I came to these findings was:
* Take a database backup (which pushes the GZ file to S3)
* Download file from S3 and ungzip.
* Place this file in a volume on MySQL 8 docker container mapped to /docker-entrypoint-initdb.d
* Start the Docker container (docker run –name db -p 3306:3306 -v ~/db:/docker-entrypoint-initdb.d -e MYSQL_USER=travis -e MYSQL_PASSWORD=”test123!” -e MYSQL_ROOT_PASSWORD=testing1234! -e MYSQL_DATABASE=myapp_test mysql:8)
The error message is: ERROR 1067 (42000) at line 438: Invalid default value for ‘user_registered’
I get the same error on MySQL 5.7 and MySQL 8 – It works on MySQL 5.6. Running docker run –name db -p 3306:3306 -v ~/db:/docker-entrypoint-initdb.d -e MYSQL_USER=travis -e MYSQL_PASSWORD=”test123!” -e MYSQL_ROOT_PASSWORD=testing1234! -e MYSQL_DATABASE=myapp_test mysql:5.6 works!
Why is Updraft generating database backups which is only MySQL 5.6 compatible?February 3, 2020 at 4:20 pm #506695Dee NutbourneModerator
If possible, please could you send us a copy of the original backup log?
The contents will be too long to post here directly, but you can use an online service such as Dropbox or Pastebin, and post the link here.
DeeFebruary 3, 2020 at 4:31 pm #506700Mark SmithParticipant
The log is here: https://pastebin.com/9rjSRn9N
MarkFebruary 4, 2020 at 6:06 pm #507362Dee NutbourneModerator
The issue is that, due to a change between mySQL 5.6 and 5.7, a ‘zero’ value as a default for datetime fields is no longer allowed.
However, older WordPress tables will still have this default (i.e. if the table was created before mySQL was updated.
When the database is restored directly in mySQL, the table is recreated and falls foul of the new rule.
WordPress (and by extension, UpdraftPlus) bypass this restriction by disabling the incompatible rule (or ‘sql mode’).
You will need to disable the
NO_ZERO_DATEmodes for sql when importing the database. e.g.:
set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Alternatively, restoring via UpdraftPlus should work.
DeeFebruary 6, 2020 at 9:10 pm #508849Mark SmithParticipant
Many thanks for this.
What is the reason for Updraft generating a database export which is not compatible with MySQL 8?
I initially thought that my database had been incorrectly upgraded from 5.6, but I’ve tried the same process on a newer client website which was started on version 8, so there was no chance that this database has ever been 5.6.
For your information I am running Docker containers and placing the .sql file in the /docker-entrypoint-initdb.d directory which upon load will run in the scripts alphabetically.February 14, 2020 at 7:12 am #513565Mark SmithParticipant
Could I get an update on this please?
I have attempted to use your snippet in both a new file at the very top of the existing file to no avail.
I am wondering why I need to adjust a backup file from MySQL version 8 to get it to import back.February 14, 2020 at 4:53 pm #513867Dee NutbourneModerator
Apologies for the delay.
The issue is caused by how WordPress stores dates combined with MySQL8’s strict mode.
The database backups which UpdraftPlus produces output the structure of the WordPress database, including the default values.
Do the database backups restore via UpdraftPlus when running on a MySQL 8 installation (rather than a direct import)?
You may need to disable the
NO_ZERO_DATEmodes in your MySQL server configuration, rather than at runtime.
I will also consult with our lead developer on whether there is any other possible solution.
DeeFebruary 15, 2020 at 5:15 pm #514385udadminKeymaster
MySQL, through its various quirks, is quite capable of having data stored which is incompatible even with the same version its running on. For example, if you have a database running on a particular MySQL version, and then upgrade the MySQL server to a new version with stricter requirements, then the currently stored data can be something that it’d be invalid to attempt to insert as new data. Or, if the table definition or “strict mode” requirements are changed after data entry, then there can be currently stored data that is invalid (and again, won’t be accepted if you try to insert it).
UpdraftPlus aims to reduce the inconvenience and risk of such occurrences, but there’s no “catch-all” way to do that. They have to be dealt with on a case-by-case basis as they are discovered “in the wild”. In such cases, we need to find out: a) what the table definition is (easiest way is to send us a copy of the backup, or to use 7-zip to decompress it and then load it in a text editor and search for that table) b) what the data (the
INSERTstatements) that it’s trying to enter are. Then with those we can run some experiments, try to find the exact problem, and seek to create detection and a work-around when the insertion fails.February 17, 2020 at 10:51 am #515388Mark SmithParticipant
This is my test as I am unable to reply!February 17, 2020 at 10:53 am #515392Mark SmithParticipantThis reply has been marked as private.February 17, 2020 at 10:54 am #515393Mark SmithParticipantThis reply has been marked as private.February 17, 2020 at 10:54 am #515394Mark SmithParticipant
I have done some further experiments and narrowed it down to the CREATE TABLE command.
I have attached both an Updraft backup file (which fails against MySQL 8) and a MySQL 8 export (no data). Both the actual CREATE TABLE commands are identical which makes me think the metadata or comments within the file have an impact.February 17, 2020 at 10:57 am #515400Mark SmithParticipant
To replicate start a new docker instance from mysql:8 mapping the /docker-entrypoint-initdb.d to a standalone directory with one of the files from above in.
You’ll see that the MySQL one works ok, but the Updraft one doesn’t.February 17, 2020 at 4:39 pm #515581Dee NutbourneModerator
Thank you. We will investigate further and get back to you as soon as possible.
DeeFebruary 18, 2020 at 3:08 pm #516177Dee NutbourneModerator
Please could you try adding the following to your updraftplus database backup file, near the top (you should see a block of similar config lines):
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
Does this allow the import to succeed?
- You must be logged in to reply to this topic.