Database backup file won’t import into MySQL 8

UpdraftPlus Home › Forums › Paid support forum – UpdraftPlus backup plugin › Database backup file won’t import into MySQL 8

Tagged: 

Viewing 15 posts - 1 through 15 (of 25 total)
1 2
  • Author
    Posts
  • February 1, 2020 at 3:07 pm #505621
    Mark Smith
    Participant

    Hello,

    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 #506695
    Dee Nutbourne
    Moderator

    Hi Mark,

    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.

    Best Wishes,
    Dee

    February 3, 2020 at 4:31 pm #506700
    Mark Smith
    Participant

    Hi,

    The log is here: https://pastebin.com/9rjSRn9N

    Many thanks

    Mark

    February 4, 2020 at 6:06 pm #507362
    Dee Nutbourne
    Moderator

    Hi Mark,

    Thank you.

    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_IN_DATE and NO_ZERO_DATE modes 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.

    Best Wishes,
    Dee

    February 6, 2020 at 9:10 pm #508849
    Mark Smith
    Participant

    Hi Dee,

    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 #513565
    Mark Smith
    Participant

    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 #513867
    Dee Nutbourne
    Moderator

    Hi,

    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_IN_DATE and NO_ZERO_DATE modes 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.

    Best Wishes,
    Dee

    February 15, 2020 at 5:15 pm #514385
    udadmin
    Keymaster

    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 INSERT statements) 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 #515388
    Mark Smith
    Participant

    This is my test as I am unable to reply!

    February 17, 2020 at 10:53 am #515392
    Mark Smith
    Participant
    This reply has been marked as private.
    February 17, 2020 at 10:54 am #515393
    Mark Smith
    Participant
    This reply has been marked as private.
    February 17, 2020 at 10:54 am #515394
    Mark Smith
    Participant

    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 #515400
    Mark Smith
    Participant

    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 #515581
    Dee Nutbourne
    Moderator

    Hi,

    Thank you. We will investigate further and get back to you as soon as possible.

    Best Wishes,
    Dee

    February 18, 2020 at 3:08 pm #516177
    Dee Nutbourne
    Moderator

    Hi,

    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 @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

    Does this allow the import to succeed?

    Best Wishes,
    Dee

  • Author
    Posts
Viewing 15 posts - 1 through 15 (of 25 total)
1 2
  • The topic ‘Database backup file won’t import into MySQL 8’ is closed to new replies.