The goal of this page is to provide an overview of the situation and the problems we are facing with stricter SQL modes.

How does it affect you?

If you have a production installation you must read the DBMS_configuration wiki page to know if you should edit your configuration.

If you are a developer you should read carefully the whole page to know how it will impact your daily work.

What are the "SQL modes"

From the MySQL documentation:

The MySQL server can operate in different SQL modes.
Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.

Since Ubuntu 16.04 (and other recent distros) the default configuration of the DBMS became more stricter.

Since MySQL 5.7 and MariaDB 10.2.4, the default value for SQL modes includes ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE and NO_ZERO_DATE, which will make some of our SQL statements invalid.

Why should not we ignore them?

Our code base is not ready to let the DBMS use these SQL modes and since bug 20229 we explicitly list the SQL modes we support when the connection to the DBMS is done.

We could be happy with this state, but these SQL modes highlight wrong SQL statements or values that are incorrectly handled.

For instance the most common errors are:

$COLUMN isn't in GROUP BY
Incorrect integer value: for column $COLUMN
Incorrect date value: for column $COLUMN
Incorrect decimal value: for column $COLUMN

They tell us that we tried the insert empty string into a date or integer column. Or that the GROUP BY clause is not correct.

Get rid of them

It will be hard to fix all of them, but we should at least try to not introduce similar issues.

Turn them on for the test suite

The first step would be to have our test suite running and passing with these SQL modes.

After bug 20144 we already had it passing, but then we focused on something else and our integration server did not catch regressions. Few months after we noticed that we introduced a lot of them and we fixed them again, on bug 21597.

To prevent this from happening again we need to make our test suite running with these SQL modes turned on automatically.

Bug 21613 is enabling these modes when the tests are ran, which make them catch the regressions. Which also mean they will be stricter than when you are using the GUI. Potentially you will see errors in the tests that you will not trigger from the interface.

Turn them on for the interface

Be brave and turn them on for the interface!

Since bug 20521 you can have them on for the interface as well.

You just need to add <strict_sql_modes>1</strict_sql_modes> inside the <config> section of your koha-conf.xml file and restart memcached.

What's next?

Catch them all!

Report all the bugs you find, as well as test the existing ones! All the known bugs are linked to the omnibus bug 17328.

Be the bad guy

QAers should set the strict_sql_modes flag to catch new potential issues added by a patch set. Sandboxes and KohaDevBox will not have it set, so QAers will be the step where they will be caught.

Brainstorm

We are going to face issues that will appear all the time: the columns' datatypes when an object is stored will have to be checked before the ->store is called, or in ->store.

The proposition made on bug 21610 (Koha::Object->store needs to handle incorrect values) is to rely on Koha::Object->store to catch wrong values.

If you disagree with that change you can jump into the discussion to give our help and find a consensus.

SQL modes

Contents