Bug 17258 - [OMNIBUS] MySQL 5.7
Summary: [OMNIBUS] MySQL 5.7
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: master
Hardware: All All
: P5 - low major (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
: 10732 16584 (view as bug list)
Depends on: 17306 17328 21941 23360 24001 27498 27968 28012 28152 6993 13669 14637 15819 16094 16402 16554 16573 16583 16858 17234 17257 17260 17320 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 22431 22812 22878 22907 22919 23064 23242 23309 23430 23709 24137 24328 24742 24940 25335 26090 27276 28207
Blocks:
  Show dependency treegraph
 
Reported: 2016-09-06 09:05 UTC by Jonathan Druart
Modified: 2021-09-08 21:00 UTC (History)
9 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
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. ***
Comment 9 Victor Grousset/tuxayo 2020-05-21 22:40:30 UTC
Should this bug be renamed to "MariaDB 10.2 and MySQL 5.7" ?
IIUC most of the related bugs are for these two versions.

Until finding this and comment 7. I had the impression that only MySQL 5.7 had these big breaking changes.
Comment 10 David Cook 2020-05-21 23:22:44 UTC
(In reply to Victor Grousset/tuxayo from comment #9)
> Should this bug be renamed to "MariaDB 10.2 and MySQL 5.7" ?
> IIUC most of the related bugs are for these two versions.
> 
> Until finding this and comment 7. I had the impression that only MySQL 5.7
> had these big breaking changes.

Good question!

It looks like both MariaDB 10.1 and 10.2 are based off MySQL 5.7. 

It might make sense to leave it as MySQL 5.7 with the onus being on users of MariaDB to figure out their MySQL compatibility.
Comment 11 Victor Grousset/tuxayo 2020-11-14 14:35:44 UTC
(In reply to David Cook from comment #10)
> It looks like both MariaDB 10.1 and 10.2 are based off MySQL 5.7. 
> 
> It might make sense to leave it as MySQL 5.7 with the onus being on users of
> MariaDB to figure out their MySQL compatibility.

I can't find much info about equivalence of versions that tell this [1], it seems a bit hard to figure that out. It's likely to also be interpreted as MySQL only compat issues, since the two DBMSs tend to fork more and more.

Regardless of the name of this ticket that is only about one or two versions, does anyone has resources about version equivalence in general? That will come handy to watch in the future.


[1] https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/#drop-in-compatibility-of-specific-mariadb-versions
    Ok for 10.2, as for 10.1 maybe we can deduct from the fact that 10.1 was release during the dev cycle of MySQL 5.7. Use of confirmation bias needed ^^"