Login | Sign up | Toll-Free: 1-800-752-0455
 
 

Forum

 

9 posts by 5 authors in: Forums > CMS Builder
Last Post: February 16, 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?

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 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

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 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  

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.

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

Many thanks Dave,

This has fixed the problem.

:-)