Incorrect datetime value

9 posts by 5 authors in: Forums > CMS Builder
Last Post: February 16, 2017   (RSS)

By Ryan - February 8, 2017

When I try to create a new date field anywhere in the CMS I keep getting the following error message.

Incorrect datetime value '0000-00-00 00:00:00' for column 'myField' at row 1

I'm using MySQL 5.7 and have tried setting SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES' to no avail.

Anyone any ideas?

By Dave - February 9, 2017 - edited: February 9, 2017

Hi Ryandoc, 

It should just work automatically without having to make any mysql config adjustments.  Do you get that error when you try to create a new field under "Admin > Section Editors" or when you try and save a record in a section with a date field? 

I'm wondering if it's related to this MySQL feature in 5.7 "A warning occurs if NO_ZERO_IN_DATE is enabled without also enabling strict mode or vice versa."  From: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_in_date

What's the full MySQL version? 5.7.x? 

Note: A temp fix might be to remove "NO_ZERO_IN_DATE" in /lib/mysql_functions.php (occurs twice in function mysqlStrictMode()). But we'd also like to patch this for the next version if it's a new issue related to MySQL 5.7.x.

Dave Edis - Senior Developer
interactivetools.com

By Damon - February 10, 2017

Hi,

We are working on a patch for this and well release it ASAP.

Will post an update when it is available.

Thanks!

Cheers,
Damon Edis - interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By Ryan - February 11, 2017

Hi Dave,

Sorry for the late response, I'm using version 5.7.7. The issue seemed to effect all sections when you try and add a date field. I was able to work around it by creating the field as a text field first and entering the date manually I.e. 2017-02-11 00:00:01 and then changing it to a date field. Seems like newer versions of MySQL don't like 0000-00-00 00:00:00 date values. I was out of the office the last couple of days but will try out your suggestion on Monday.

Thanks!

Ryan

By zaba - February 14, 2017 - edited: February 14, 2017

Hi,

I am having the same issue (see post http://www.interactivetools.com/forum/forum-posts.php?postNum=2240198#post2240198 )

I can create a publishDate field without issue,

When I create removeDate field I get the issue, i've posted the grab again here.

 v3.06 (Build 2101)

Operating System Linux 3.2.83 (x86_64) (release) Web Server Apache
PHP Version PHP v5.6.22 - phpinfo >>

  • PHP disabled functions: none 
  • open_basedir restrictions: none 
  • Security Modules: None detected

Database Server MySQL v5.7.16  

By Ryan - February 14, 2017

Hi Dave,

Removing NO_ZERO_IN_DATE didn't seem to work for me. However when I removed STRICT_ALL_TABLES on line 67 it seemed to do the trick.

# set MySQL strict mode - http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
  if ($strictMode) {
    $query = "SET SESSION sql_mode = 'NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'";
    mysql_query($query) or die("MySQL Error: " .mysql_error(). "\n");
  }

Thanks for your help.

By Dave - February 15, 2017

Hi Guys, 

I think we've tracked this down.  It looks like MySQL 5.7.x has a new requirement to set a default value when we're creating date fields.

Here's the fix: 

  • Create a backup of /lib/database_functions.php
  • Open /lib/database_functions.php
  • Replace the 3 instances of this code: 'datetime NOT NULL';
  • ...with this code: 'datetime NOT NULL DEFAULT "0000-00-00 00:00:00"';

Let me know if that works for you and we'll release an update with it.

Thanks for the bug report!

Dave Edis - Senior Developer
interactivetools.com

By zaba - February 16, 2017

Many thanks Dave,

This has fixed the problem.

:-)