Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MySQL 5.7 - Invalid default timestamp values... #678

Closed
mikesname opened this issue Dec 8, 2015 · 6 comments
Closed

MySQL 5.7 - Invalid default timestamp values... #678

mikesname opened this issue Dec 8, 2015 · 6 comments

Comments

@mikesname
Copy link

@mikesname mikesname commented Dec 8, 2015

I just tried to install Omeka 2.3.1 with MySQL 5.7.9 and received the following error after submitting the install details for at /install:

Schema task failed on table 'omeka_collections' with Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Invalid default value for 'added'

Changing all the timestamp defaults in application/schema from 0000-00-00 00:00:00 to CURRENT_TIMESTAMP let it successfully install, but I'm not sure whether this changes behaviour in any way or is compatible with earlier versions of MySQL (apparently prior to 5.6.5 only one timestamp column per table could be initialised to the current value.)

@zerocrates

This comment has been minimized.

Copy link
Member

@zerocrates zerocrates commented Dec 8, 2015

Hmmm.... okay. Yeah, reliance on features from 5.6 is definitely a no-go.

We may just instead go down the road of removing the defaults entirely. There's already code in Omeka for manually updating the timestamps from our end so the MySQL-side defaults don't really accomplish much for us. It may be best (certainly from a strict correctness standpoint) to have these as DEFAULT NULL.

@zerocrates

This comment has been minimized.

Copy link
Member

@zerocrates zerocrates commented Dec 8, 2015

It looks like the situation here is that from 5.7.4 up, MySQL strict mode now always implies NO_ZERO_IN_DATE and NO_ZERO_DATE.

@zerocrates

This comment has been minimized.

Copy link
Member

@zerocrates zerocrates commented Dec 8, 2015

Interestingly, it actually looks like they reverted that change in 5.7.8... I suppose you must have that mode specifically enabled.

It's still probably the sensible move to make the schema work with those modes on.

@zerocrates

This comment has been minimized.

Copy link
Member

@zerocrates zerocrates commented Dec 8, 2015

Ah actually I see that they basically just made it possible to opt out of the "no zero" date modes in 5.7.8 and up. In between 5.7.4 and 5.7.8 it was impossible to have strict mode on and "no zero" off. The default still includes the "no zero" modes so it doesn't really change much for us.

@zerocrates

This comment has been minimized.

Copy link
Member

@zerocrates zerocrates commented Dec 8, 2015

To avoid potential issues on our wide range of supported MySQL versions, I've gone with the simplest possible change: instead of 0000-00-00 00:00:00, these defaults are now just an arbitrary date that's within the valid timestamp range. (see e81009d)

@zerocrates zerocrates closed this Dec 8, 2015
@mikesname

This comment has been minimized.

Copy link
Author

@mikesname mikesname commented Dec 9, 2015

Thanks. Note that there are also variants of this issue affecting the bundled ExhibitBuilder and SimplePages plugins.

zerocrates added a commit that referenced this issue Dec 9, 2015
zerocrates added a commit that referenced this issue Dec 9, 2015
(#678)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.