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 ross - February 8, 2017

Hi there.

Thanks for posting.

My first thought here is maybe a plugin is interfering with the default values.

Could you try disabling all plugins and then creating the new field?

Also, could you try creating the same kind of field in any of your other sections?

And finally, could you let me know what version of CMS Builder you are using? 

Thanks!

-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/

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  

Attachments:

Screen Shot 2017-02-10 at 13.08.05.png 113K

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.

:-)