Bug 17258

Summary: [OMNIBUS] MySQL 5.7
Product: Koha Reporter: Jonathan Druart <jonathan.druart>
Component: Architecture, internals, and plumbingAssignee: Jonathan Druart <jonathan.druart>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: major    
Priority: P5 - low CC: gmcharlt, marjorie.barry-vila, mtj, ngson2000, nick, philippe.blouin, tomascohen
Version: master   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Who signed the patch off: Text to go in the release notes:
Version(s) released in:
Bug Depends on: 17320, 17328, 21941, 22431, 23064, 23242, 23309, 23430, 24001, 6993, 13669, 14637, 15819, 16094, 16402, 16554, 16573, 16583, 16858, 17234, 17257, 17260, 17323, 17324, 17860, 18836, 19248, 19492, 20144, 20175, 20176, 20177, 20179, 20180, 20182, 20229, 21601, 21603, 21604, 21605, 21606, 21610, 21612, 21621, 21622, 21635, 21714, 21718, 21725, 22812, 22878, 22907, 22919, 23709    
Bug Blocks:    

Description Jonathan Druart 2016-09-06 09:05:11 UTC
This is an omnibus report to group the different issues under MySQL 5.7
Comment 1 Jonathan Druart 2016-09-06 09:11:53 UTC
Bug 16402 - DB structure cannot be loaded in MySQL 5.7

Bug 13669 - Web installer fails to load sample data on MySQL 5.6+

Bug 16573 - Web installer fails to load structure and sample data on MySQL 5.7

Bug 16554 - Web installer fails to load i18n sample data on MySQL 5.6+

Bug 14637 - Add patron category fails with MySQL 5.6.26

Bug 15819 - Error when edit borrowers "There are no patron categories defined" but categories exists

Bug 16094 - error editing (borrowers, reports) with mysql enterprise

Bug 16583 - Strangeness in the staff client on ubuntu 16.04 and MySQL 5.7

Bug 17234 - ALTER IGNORE TABLE is invalid in mysql 5.7. This breaks updatedatabase.pl

Bug 17257 - Cannot create a patron under MySQL 5.7
Comment 2 Blou 2016-09-22 14:46:54 UTC
Bug 17328 - Koha fixes for Mysql 5.7

A mini omnibus as I go, with no other intent than report.  Sorry, didn't know about this one.
Comment 3 Jonathan Druart 2016-09-27 08:48:41 UTC
Bug 17320 - MySQL 5.7 doesn't work well with SQL using '0000-00-00'
Bug 17324 - branchcode is NULL in letter triggers red upgrade message
Bug 17260 - updatedatabase.pl fails on invalid entries in ENUM and BOOLEAN columns
Comment 4 Katrin Fischer 2017-01-07 23:52:13 UTC
*** Bug 16584 has been marked as a duplicate of this bug. ***
Comment 6 Jonathan Druart 2017-01-09 09:57:58 UTC
(In reply to Jonathan Druart from comment #5)
> See
> https://wiki.koha-community.org/wiki/Koha_on_ubuntu_-_packages#Ubuntu_16.
> 04_and_MySQL_5.7

On Ubuntu 16.04 (and others recent distro) now (Nov. 2016) is present MySQL 5.7. All version of Koha (3.x, 16.05, 16.11) are not compatible with default config of MySQL 5.7. To bypasss the problem you can:

    Install MariaDB instead of MySQL [ sudo apt-get install mariadb-server]
    OR Insert in your /etc/mysql/my.conf: 

[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

After save the changes and restart mysql.
Comment 7 Jonathan Druart 2018-02-06 19:11:34 UTC
Since MySQL 5.7 and MariaDB 10.2.4, the default value for SQL modes is

MySQL: 
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

MariaDB:
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO
Comment 8 Jonathan Druart 2018-04-09 19:19:44 UTC
*** Bug 10732 has been marked as a duplicate of this bug. ***