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
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
Circulation function:

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 (kidclamp) 		Details | Diff | Splinter Review
Show Obsolete (2) View All Add an attachment (proposed patch, testcase, etc.)

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 (kidclamp) 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 (ashimema) 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 (lukeg) 2019-06-26 16:59:27 UTC 
backported to 18.11.x for 18.11.07