Search code examples
mysqltimestampmampconcrete5sql-timestamp

Is this MySQL error timezone-related? `value` datetime DEFAULT '0000-00-00 00:00:00',

Being a designer, I don't understand MySQL and have no ability to alter the database. Go easy!

CREATING THE ERROR

  1. Exported a Concrete5 db (modified to run on php 7) from a server running MySQL 5.7.27

  2. Imported the db into MAMP Pro 5.5.1 running MySQL 5.7.26 for local development

  3. Import fails on time-related rows like this one...

Querious Import Error

CREATE TABLE `atDateTime` (
  `avID` int(10) unsigned NOT NULL,
  `value` datetime DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`avID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

ANSWERS FOUND

1. MYSQL 5.7+

Many answers say the error is because MySQL 5.7+ no longer accepts blank dates but the db is being exported from a server already running MySQL 5.7.27?

2. TIMEZONE ISSUE

Other answers suggest it's a Timezone difference but both the server and local Mac are running NZDT. MAMP Pro seems to use the system timezone.

3. QUERIOUS/SEQUAL PRO

Multiple db apps were tried incase issue was application-specific but both show the same errors.

QUESTION

Can anyone explain what the exact issue is (to a designer) and how to fix without altering the db?

Solution

  • MySQL 5.7 doesn't allow blank dates by default.

    1. The db doesn't work in MAMP Pro because MySQL strict mode is enabled by default.

    2. The db works on the live server because MySQL strict mode has been disabled.

    3. The solution is to disable strict mode in MAMP Pro.

    Disable MySQL strict mode and allow blank dates in MAMP Pro:

    1. Shut down the MAMP Pro servers
    2. Go File > Edit Templates> MySQL (my.conf) > MySQL Version you're using
    3. In the first third of the file, look for (with square brackets)...

    [mysqld]

    1. Directly underneath, add sql_mode="" like this...
    [mysqld]
sql_mode=""
    1. This disables strict mode and allows blank dates
    2. Save the file and close
    3. Start the servers
    4. Import your db

    If you don't know the correct search terms; 'disable MySQL strict mode in MAMP Pro', you're left wandering in the wilderness for hours. Hope this helps other designers.

    More reference: https://mampsupportforum.com/forums/latest/mamp-mamp-pro-disable-mysql-strict-mode