Task #13313

Reconsider the inclusion of STRICT_TRANS_TABLES in the sql_mode

Added by José Raddaoui Marín 3 months ago. Updated 25 days ago.

Status:In progressStart date:05/11/2020
Priority:MediumDue date:
Assignee:David Hume% Done:

0%

Category:Data model / ORM
Target version:Release 2.6.0
Google Code Legacy ID: Tested version:2.5, 2.6
Sponsored:No Requires documentation:

Description

MySQL 5.7 added STRICT_TRANS_TABLES by default to the sql_mode and we tried to support that mode in #9051. However, what we did in there has proven to not be enough:

https://projects.artefactual.com/issues/13095
https://projects.artefactual.com/issues/13144
https://projects.artefactual.com/issues/13174
https://projects.artefactual.com/issues/13214

We have had to remove it from some production instances and there are a few reports in the user forum about the issue. Moreover, NO_ZERO_DATE and NO_ZERO_IN_DATE (needed for the event dates) are deprecated and they will be merged with STRICT_TRANS_TABLES in a future MySQL release. Considering the current status and the possible future issues, I'd suggest that we remove this mode from the documentation and the Ansible playbooks for production until AtoM is really compatible with it.


Related issues

Related to Access to Memory (AtoM) - Feature #13331: Support the default sql_mode from MySQL 8 New 05/25/2020

History

#1 Updated by José Raddaoui Marín 3 months ago

  • Subject changed from Reconsider the inclusion of STRICT_TRANS_TABLES in the @sql_mode@ to Reconsider the inclusion of STRICT_TRANS_TABLES in the sql_mode

#2 Updated by David Hume 3 months ago

  • Status changed from New to Feedback

#4 Updated by José Raddaoui Marín 2 months ago

  • Status changed from Feedback to Code Review
  • Assignee set to Steve Breker
  • Target version set to Release 2.6.0

Another consequence of this issue came out in the upgrade process, in this case due to a mismatch between the updated models in the code and the database schema while it's being migrated, something that could be normal and could be avoided sometimes disabling STRICT_TRANS_TABLES.

While I consider we should remove STRICT_TRANS_TABLES until we fully support it, and this ticket is mostly to discuss that, the changes included in https://github.com/artefactual/atom/pull/1095 programmatically disable that mode in the tools:upgrade-sql task execution, just for that DB connection/session.

#5 Updated by Hector Akamine 2 months ago

I have a couple of questions:
  • for servers currently running AtoM 2.5.x (or earlier) which have STRICT_TRANS_TABLES enabled, is it safe to just disable it? (I mean something like: stopping AtoM, stopping the mysql server, changing the sql mode in the config, restart mysql server, restarting AtoM) ( or currently are there parts in the AtoM code that require having STRICT_TRANS_TABLES enabled ? )
  • Which would the recommended settings be? For example in: https://serverpilot.io/docs/how-to-disable-strict-mode-in-mysql-5-7/ they recommend: sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Would this be good?

#6 Updated by José Raddaoui Marín 2 months ago

Hi Hector,

  • Yes, it's "safe" to disable it, the failures caused by that mode will be reported as warnings instead of errors. Which has always been the case until MySQL 5.7, when it was added by default.
  • I'm not sure about IGNORE_SPACE but we can't have both zero dates and, in MySQL 8.0 (AtoM 2.6), NO_AUTO_CREATE_USER has been removed.

#7 Updated by José Raddaoui Marín 2 months ago

  • Status changed from Code Review to In progress
  • Assignee deleted (Steve Breker)

The fix for the upgrade task has been merged but this ticket is still open for discussion.

My suggestion would be to remove STRICT_TRANS_TABLES from the documentation and the deploy playbooks and include it in the Vagrant and Docker environments used for development and testing, adding a big warning in the documentation of those environments about it, telling that it's enabled in there because we are aiming for it in future versions. I've created #13331 for that aim (LOL at the issue number).

#8 Updated by José Raddaoui Marín 2 months ago

  • Related to Feature #13331: Support the default sql_mode from MySQL 8 added

#9 Updated by José Raddaoui Marín 29 days ago

  • Status changed from In progress to Code Review
  • Assignee set to Dan Gillean

#11 Updated by José Raddaoui Marín 28 days ago

  • Status changed from Code Review to In progress
  • Assignee changed from Dan Gillean to David Hume

Hi Dave (Hector and Amaya),

Documentation has been changed for 2.5.x and 2.6.x. I'd suggest that you update the required playbooks for the next upgrades if it has not been done already.

#12 Updated by José Raddaoui Marín 27 days ago

  • Description updated (diff)

#13 Updated by José Raddaoui Marín 27 days ago

Also, make sure NO_AUTO_CREATE_USER is removed for the MySQL 8.0 upgrade. Final sql_mode for AtoM 2.6 with MySQL 8 (until #13331 is addressed):

sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

#15 Updated by José Raddaoui Marín 25 days ago

Now that we are "all" here, please remember to change the encoding and collation for the database creation and the config files in the 2.6 vars:

https://github.com/artefactual/deploy-pub/blob/master/playbooks/atom-bionic/vars-singlenode-qa.yml#L17
https://github.com/artefactual/deploy-pub/blob/master/playbooks/atom-bionic/vars-singlenode-qa.yml#L99-L110

Also available in: Atom PDF