Bug 16583

Summary: Strangeness in the staff client on ubuntu 16.04 and MySQL 5.7
Product: Koha Reporter: Liz Rea <liz>
Component: Staff interfaceAssignee: Bugs List <koha-bugs>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: major    
Priority: P2 CC: bgkriegel, gmcharlt, jonathan.druart, library, lovenabila, mtj, mtompset, ngson2000, vanoudt, ztajoli
Version: unspecified   
Hardware: All   
OS: Linux   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16402
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16664
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16573
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17257
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on:    
Bug Blocks: 17258    

Description Liz Rea 2016-05-25 03:36:14 UTC
Have noticed some strangeness with a clean install of Koha on MySQL 5.7:

When adding a rule at /cgi-bin/koha/admin/smart-rules.pl, if any box is left empty the first empty field pops an error similar to this:

DBIx::Class::Storage::DBI::_dbh_execute(): Incorrect integer value: '' for column 'chargeperiod' at row 1 at /usr/share/koha/intranet/cgi-bin/admin/smart-rules.pl line 170

When attempting to add a borrower category, it thinks a duplicate has been added with the resultant error message

"An error occurred when inserting this patron category. The patron category might already exist."

The code for the category did not already exist.

I am reporting this bug on behalf of Son Nguyen - he may choose to add any other information he has to this report.
Comment 1 Bernardo Gonzalez Kriegel 2016-05-30 00:58:36 UTC
Hi Liz,
we'll have plenty of trouble with (In reply to Liz Rea from comment #0)
> Have noticed some strangeness with a clean install of Koha on MySQL 5.7:

we'll have plenty of trouble with 5.7

> 
> When adding a rule at /cgi-bin/koha/admin/smart-rules.pl, if any box is left
> empty the first empty field pops an error similar to this:
> 
> DBIx::Class::Storage::DBI::_dbh_execute(): Incorrect integer value: '' for
> column 'chargeperiod' at row 1 at
> /usr/share/koha/intranet/cgi-bin/admin/smart-rules.pl line 170

In this case the problem is that we depended on a type conversion done by MySQL, empty '' string -> 0. In smart-rules.pl for example, every empty box is sent as an empty string to the database, which used to transform that to 0.
With 5.7+ version this is not the default behavior :(

> 
> When attempting to add a borrower category, it thinks a duplicate has been
> added with the resultant error message
> 
> "An error occurred when inserting this patron category. The patron category
> might already exist."
> 

There are some cases where the strange use of 'GROUP BY' not really needed triggers the error. This is one of them.

Thoughts: 
a) try to support the 'strict' mode of MySQL 5.7+
b) suggest that anyone interested reconfigure server to a less 'strict' mode
c) ignore the problem because is not in Debian, yet.

I've patches for these problems, and others not yet reported.
Just need to know what are we going to do.
Comment 2 Ian Palko 2016-06-15 15:25:42 UTC
Have we any consensus on exactly what is to be done with the Koha/MySQL 5.7 difficulties?

The source is that while MySQL since 5.1 has had a strict mode, it was only with 5.7 that "strict mode" became default. We're not the only ones with issues.

It seems to me that the long-term solution is to cleanup the Koha SQL usage to be more strict, thus avoiding the need of tweaking the config files to make Koha work.

An alternate would be to modify the Koha code to always pass SQL through some translator script which would make it conform to strict mode, but that seems a bit sloppy.

Perhaps the best immediate solution is to change MySQL config files to make 5.7 it behave like 5.6.

In any case, I like Bernardo, would like to know what the wide-spread plan is to make this happen.
Comment 3 Nicholas van Oudtshoorn 2016-06-29 02:44:14 UTC
Are there any plans for debian to migrate from MySQL to MariaDB? Latest mariadb works fine with Koha under fedora...
Comment 4 Ian Palko 2016-07-01 14:44:16 UTC
(In reply to Nicholas van Oudtshoorn from comment #3)
> Are there any plans for debian to migrate from MySQL to MariaDB? Latest
> mariadb works fine with Koha under fedora...

This is because MariaDB still is configured to accept the less-than-strict way things have been implemented in Koha.

Since MySQL 5.1 there has been a strict mode. I would have caused the same problems back then, but "strict mode" was not the default. It became default in 5.7.

MySQL 5.7 will work fine with Koha if it is put in the less-strict mode. It also would work fine if we translate the less-than-strict calls into strict calls. It also would work fine if the Koha code is cleaned-up to conform to strict behaviour. The latter seems to me the most preferable long-term solution, since it guarantees future compatibility without hacky fixes. That's a lot of work to do that, however, the first two options would make for a more short-term solution to get Koha running on MySQL 5.7.

I don't know that there are plans to move to MariaDB or officially support it. Still, to be clear, this is a MySQL problem, it's a Koha problem. MariaDB simply allows for our less-than-strict implementation, but if it too eventually decides to follow MySQL's lead, we would have the same issues.
Comment 5 Nabila 2016-08-31 04:19:43 UTC
The Ubuntu 16.04 LTS is not supporting a fresh installation of Koha. I had to revert back to 14.04 LTS and then install a copy which worked for me. I have not tried yet to upgrade the existing Koha Installation of Ubuntu 14.04 LTS to upgrade to 16.04.

The error not comes in Acquisition Module but also the following:

-- Cannot create New Vendor in Acquisition (Doesn't save the record instead searches the vendor)
-- Cannot do the Batch Patron Import (Shows error in DBI.pm file)
-- Cannot create any entry that requires Auto Increment of Primary Key.
-- Does not load default framework of Cataloging.
Comment 6 Mark Tompsett 2017-01-28 02:38:31 UTC
(In reply to Nabila from comment #5)
> The Ubuntu 16.04 LTS is not supporting a fresh installation of Koha.

With the application of 17260, I was able to get a fresh installation working on the xenial kohadevbox.


> The error not comes in Acquisition Module but also the following:
> 
> -- Cannot create New Vendor in Acquisition (Doesn't save the record instead
> searches the vendor)
> -- Cannot do the Batch Patron Import (Shows error in DBI.pm file)
> -- Cannot create any entry that requires Auto Increment of Primary Key.
> -- Does not load default framework of Cataloging.

I propose making this bug one of these things, and creating other bugs for the others. Smaller bugs get pushed through faster. They are also easier to test.
Comment 7 Zeno Tajoli 2017-01-28 11:52:37 UTC
Hi,
for who want an immediate solution:

>Perhaps the best immediate solution is to change MySQL config files to make 5.7
> it behave like 5.6.

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

Source:
http://askubuntu.com/questions/811831/whats-the-correct-way-to-revert-mysql-5-7-strict-mode-back-to-how-it-was-in-5-6

But is not a long term solution.
Comment 8 Mark Tompsett 2017-01-28 21:15:00 UTC
(In reply to Zeno Tajoli from comment #7)
> Insert in your /etc/mysql/my.conf:

This does not fix the software side of the issues, and will not solve the problems long term. And I had to stick it somewhere else to get it to work for me. :)
Comment 9 Owen Leonard 2018-05-30 17:11:34 UTC
Is this bug still valid?
Comment 10 Jonathan Druart 2018-05-30 17:21:07 UTC
We can consider it has been fixed by bug 20229