Bug 23022

Summary: Koha is not compatible with MySQL >= 8.0.11 because of NO_AUTO_CREATE_USER mode
Product: Koha Reporter: Samir Shah <solaris.smoke>
Component: DatabaseAssignee: Jonathan Druart <jonathan.druart>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: fridolin.somers, jonathan.druart, lucas
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128
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
Bug 23022: Remove the SQl mode NO_AUTO_CREATE_USER
Bug 23022: Remove the SQl mode NO_AUTO_CREATE_USER

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 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