'Invalid default value' = database install fail

Running into the same issue when trying to pull my site onto my local computer (Mac OS X El Capitan).

I installed the latest version of MySQL and ran into the same errors.

I’m wondering if it has anything to do with the SQL Modes: docs
mysql> SELECT @@GLOBAL.sql_mode;
Mine look like this:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

********************************************************************************
DATABASE-ROUTINES
********************************************************************************
————————————–
SERVER ENVIROMENT
————————————–
MYSQL VERSION: 5.7.10
TIMEOUT: 5000
MAXPACK: 4194304
————————————–
DATABASE RESULTS
————————————–
**ERROR** database error write ‘Invalid default value for ‘comment_date” – [sql=
CREATE TABLE wp_comments (
comment_ID bigint(20) unsigned NOT NULL A…]
**ERROR** database error write ‘Invalid default value for ‘created” – [sql=
CREATE TABLE wp_duplicator_packages (
id bigint(20) unsigned NOT NUL…]
**ERROR** database error write ‘Invalid default value for ‘link_updated” – [sql=
CREATE TABLE wp_links (
link_id bigint(20) unsigned NOT NULL AUTO_IN…]
**ERROR** database error write ‘Invalid default value for ‘post_date” – [sql=
CREATE TABLE wp_posts (
ID bigint(20) unsigned NOT NULL AUTO_INCREME…]
**ERROR** database error write ‘Invalid default value for ‘user_registered” – [sql=
CREATE TABLE wp_users (
ID bigint(20) unsigned NOT NULL AUTO_INCREME…]
**ERROR** database error write ‘Table ‘shenatest.wp_duplicator_packages’ doesn’t exist’ – [sql=

/* INSERT TABLE DATA: wp_duplicator_packages */
INSERT INTO
`
wp_duplicato…]
**ERROR** database error write ‘Table ‘shenatest.wp_duplicator_packages’ doesn’t exist’ – [sql=INSERT INTO wp_duplicator_packages VALUES(“20”, “20151221_shenatschofen_c…]
**ERROR** database error write ‘Table ‘shenatest.wp_duplicator_packages’ doesn’t exist’ – [sql=INSERT INTO wp_duplicator_packages VALUES(“21”, “20151221_shenatschofen_c…]
**ERROR** database error write ‘Incorrect string value: ‘\xF0\x9F\x91\x8D” …’ for column ‘option_value’ at row 1′ – [sql=INSERT INTO wp_options VALUES(“8080”, “_transient_feed_ac0b00fe65abe10e0c…]
**ERROR** database error write ‘Table ‘shenatest.wp_posts’ doesn’t exist’ – [sql=
/* INSERT TABLE DATA: wp_posts */
INSERT INTO wp_posts VALUES(“49”, “2”,…]
**ERROR** database error write ‘Table ‘shenatest.wp_posts’ doesn’t exist’ – [sql=INSERT INTO wp_posts VALUES(“55”, “2”, “2013-02-05 16:24:35”, “2013-02-05…]

More details:
The script created tables in the database. I tried running the process with the mySql root account with the same errors…so I don’t think it’s a permissions issue.

The mySQL version that HostGator uses is 5.5.42-37.1 which is older than the installed version on my mac. However, I’ve been running a version of 5.7x on another mac and that has been working.

I got it to work again. It was the SQL mode. It was too strict to allow the script to run.
As my install is only local for testing, I just set the mode to:

sql_mode=NO_ENGINE_SUBSTITUTION in the my.cnf file

That did the trick.

THANKS madla!!! You sorted it, now works fine ??

For anyone else with this problem, I opened this file in notepad:

C:\wamp64\bin\mysql\mysql5.7.9\my.ini

and added the final line to the file:

[mysqld]
port = 3306
sql_mode=NO_ENGINE_SUBSTITUTION

All seems well now.

It would be great if this could be addressed in the Duplicator FAQ or by some automatic means, was a huge waste of time and energy fixing manually.

I will not mark it resolved yet in the hope one of the duplicator guys could comment on this, cheers!

Hey Guys,

Thanks for providing your solutions/work-a-rounds. I have added this thread to the todo list of items to look into, it may take some time but if there is a solution in code we will try and automate it for you..

thanks

Thanks a lot David, you help me!!! Thanks ??

Updated on the FAQ as well!

I’m new to DigitalOcean and using ServerPilot to manage my Ubuntu 16.04 droplet. The above workarounds didn’t work for me though I had a ton of similar Deploy errors on two separate site installs.

Thanks to the clues above, I found this ServerPilot article about disabling Strict Mode in MySQL 5.7. Worked like a charm. Hope this helps someone else!

Hi,

Ok for this fix but why Duplicator doesn’t display any errors ?! I lost a lot of time, trying many password, checking folder md5, etc.

Hey Alysko,

Duplicator does display errors, however its in the form of SQL errors in the the installer-log.txt. We are looking for a way to try and display it in the UI as well if its possible to detect the setting from SQL.

Thanks

• This reply was modified 8 years, 1 month ago by Cory Lamle.

There is now a possible fix for this in developer version, if anyone wants to give it a try and provide feedback that will allow us to get it pushed out in the next version. Instructions for using/testing can be found on this thread.

Thanks

• This reply was modified 8 years, 1 month ago by Cory Lamle.

Great, I will try it.

just to let you know : i’ve got the same problem and i’m on a shared server (sql private OVH) so no my.ini, etc…

to resolve the problem, i just :
1/ open the database.sql in the archive
2/ add on the top on the file : SET SESSION sql_mode=’NO_ENGINE_SUBSTITUTION’;
3/ saved the file

then the installation worked like a charm.

great pluging by the way really usefull ??

Hi @najhal,

With version 1.1.20 there is now a new option under advanced settings in the installer called MySQL mode that will allow you to pass in custom sql_modes…

Hope this helps everyone on this thread, any issues please let me know~

Remember to restart server after mode change…