MySQL syntax issues (somewhat related to JCB)

[Chris Paschen]

I'm not sure if this is an issue within JCB or just within the Joomla code base related to MySQL (or just me doing something completely wrong).

BACKGROUND

I'm developing in WAMP with MySQL 5.7.x.

I'm installing into at least one web site with MySQL 5.5.x

PROBLEM

Creating a date/time field that defaults to the current date/time causes problems when installing into MyXQL 5.5.x site (works fine on 5.7.x site).

CONFIGURATION

Here's the configuration for the field.

Type - Calendar

Data Type - DATETIME

Data Length/Values - None Set

Data Default - CURRENT_TIMESTAMP

Indexes Type - None

Null Switch - NOT NULL

XML definition:

type="calendar" 

name="edition_release_date" 

label="Edition Date" 

default="NOW" 

description="The date this edition was released" 

readonly="" 

disabled="" 

class="" 

format="%Y-%m-%d" 

filter="" 

size="" 

required="true" 

showon="" 

translateformat="" 

showtime="" 

timeformat="" 

singleheader="" 

todaybutton="" 

weeknumbers="" 

filltable="" 

minyear="-2" 

maxyear="1" 

GENERATED CODE (in the CREATE TABLE IF NOT EXISTS)

`edition_release_date` DATETIME CURRENT_TIMESTAMP NOT NULL,

ERROR NOTICE

JInstaller: :Install: Error SQL You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_TIMESTAMP NOT NULL, `has_cover` INT(1) NOT NULL DEFAULT 0, `has_epub' at line 15

Extension Install: SQL error processing query: DB function failed with error number 1064 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_TIMESTAMP NOT NULL, `has_cover` INT(1) NOT NULL DEFAULT 0, `has_epub' at line 15

I've 'made' it work by changing the default to "0000-00-00 00:00:00"; however, that causes other problems.

I've tried to find out what the proper default and NOT NULL settings should be, but can't seem to make this work. (And strange that it works fine in 5.7.x).

Any tips/help would be appreciated.

[Dave Webber]

Not sure if I helps but when I have set up date/time I have adjusted the format to show the time format as well 

format="%d-%m-%Y %H:%M" 

The Default Data to Other

Other Default to 0000-00-00 00:00:00

If I am setting up a date only then I can leave the date format alone.

[Chris Paschen]

Dave,

THANKS! That solved it.

 I must have somehow missed that combination. I think I've been staring at code too much this week and need some weekend!

Thanks again!

[Marco Dings]

As a sidenote regarding datetimes and mixing up mysql 5.5 and 5.7 installations
The NULL date has changed in MySQL 5.7.
In MySQL 5.6 it accepts 0000-00-00 00:00:00 whareas in 5.7 it must be 1001-01-01 00:00:00.