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:	Database	Assignee:	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
Circulation function:
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 (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