Bug 23022 - Koha is not compatible with MySQL >= 8.0.11 because of NO_AUTO_CREATE_USER mode
Summary: Koha is not compatible with MySQL >= 8.0.11 because of NO_AUTO_CREATE_USER mode
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-05-31 05:19 UTC by Samir Shah
Modified: 2020-06-04 20:33 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
19.11.00,19.05.01


Attachments
Bug 23022: Remove the SQl mode NO_AUTO_CREATE_USER (1.61 KB, patch)
2019-06-06 00:14 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 23022: Remove the SQl mode NO_AUTO_CREATE_USER (1.66 KB, patch)
2019-06-11 13:28 UTC, Mark Tompsett
Details | Diff | Splinter Review
Bug 23022: Remove the SQl mode NO_AUTO_CREATE_USER (1.72 KB, patch)
2019-06-14 17:36 UTC, Nick Clemens
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Samir Shah 2019-05-31 05:19:26 UTC
The NO_AUTO_CREATE_USER mode was removed in MySQL 8.0.11 - see https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html

But Koha attempts to set this mode here: https://github.com/Koha-Community/Koha/blob/3a150b5d7519c2969de6d966193af242e1e787e0/Koha/Database.pm#L79-L82

This results in the following exception when using MySQL 8.0.11 or higher:

DBD::mysql::db do failed: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' [for Statement "SET sql_mode = 'IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'"] at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1440.
Comment 1 Samir Shah 2019-05-31 05:22:38 UTC
Here's the relevant bit from the release notes:

> The following features related to account management have been removed:
> Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is removed.
Comment 2 Jonathan Druart 2019-06-06 00:14:38 UTC
Created attachment 90343 [details] [review]
Bug 23022: Remove the SQl mode NO_AUTO_CREATE_USER

To make our sql mode list compatible with MySQL 8.0.11

NO_AUTO_CREATE_USER has been removed in MySQL 8.0.11
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html

I do not think we needed it.
Comment 3 Mark Tompsett 2019-06-11 13:28:02 UTC
Created attachment 90484 [details] [review]
Bug 23022: Remove the SQl mode NO_AUTO_CREATE_USER

To make our sql mode list compatible with MySQL 8.0.11

NO_AUTO_CREATE_USER has been removed in MySQL 8.0.11
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html

I do not think we needed it.

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>
Comment 4 Nick Clemens 2019-06-14 17:36:43 UTC
Created attachment 90607 [details] [review]
Bug 23022: Remove the SQl mode NO_AUTO_CREATE_USER

To make our sql mode list compatible with MySQL 8.0.11

NO_AUTO_CREATE_USER has been removed in MySQL 8.0.11
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html

I do not think we needed it.

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 5 Martin Renvoize 2019-06-15 07:02:58 UTC
Nice work!

Pushed to master for 19.11.00
Comment 6 Fridolin Somers 2019-06-20 12:34:48 UTC
Pushed to 19.05.x for 19.05.01
Comment 7 Lucas Gass 2019-06-26 16:59:27 UTC
backported to 18.11.x for 18.11.07