[Koha-bugs] [Bug 5366] Koha does not work right with MariaDB

[bugzilla-daemon at bugs.koha-community.org]

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5366

--- Comment #18 from Galen Charlton <gmcharlt at gmail.com> ---
As a data point, I'm currently running MariaDB 5.5.31, using mariadb.org's
Debian Wheezy package.

So far, I am *not* running into any glitches with inserting into tables that
don't set explicit defaults for non-NULL columns.  Here's an example session:

MariaDB [koha]> CREATE TABLE foo (
    ->    id INTEGER auto_increment,
    ->    field1 VARCHAR(50) NOT NULL,
    ->    field2 TEXT NOT NULL,
    ->    PRIMARY KEY (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

MariaDB [koha]> INSERT INTO foo (field1) VALUES ('field1 set, field2 not');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [koha]> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1364 | Field 'field2' doesn't have a default value |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

MariaDB [koha]> INSERT INTO foo (field2) VALUES ('field2 set, field1 not');
Query OK, 1 row affected, 1 warning (0.03 sec)

MariaDB [koha]> show warnings ;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1364 | Field 'field1' doesn't have a default value |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

MariaDB [koha]> select * from foo;
+----+------------------------+------------------------+
| id | field1                 | field2                 |
+----+------------------------+------------------------+
|  1 | field1 set, field2 not |                        |
|  2 |                        | field2 set, field1 not |
+----+------------------------+------------------------+
2 rows in set (0.00 sec)

MariaDB [koha]> select * from foo where field2 is null;
Empty set (0.00 sec)

MariaDB [koha]> select * from foo where field2 = '';
+----+------------------------+--------+
| id | field1                 | field2 |
+----+------------------------+--------+
|  1 | field1 set, field2 not |        |
+----+------------------------+--------+
1 row in set (0.00 sec)


I wonder if, back when this was originally tested, whether MariaDB was shipping
with a default value of SQL_MODE that included 'STRICT_TRANS_TABLES':

ariaDB [koha]> SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

MariaDB [koha]> INSERT INTO foo (field2) VALUES ('field2 set, field1 not');
ERROR 1364 (HY000): Field 'field1' doesn't have a default value

If that's the case, perhaps a solution is to set SQL_MODE explicitly whenever
Koha makes a database connection.  The initial value would almost certainly
have to be '', for compatibility reasons, but it would be a very worthy code to
then start tightening the screws and work our way up to setting SQL_MODE to
TRADITIONAL or the like -- and this would be an improvement regardless of
whether any given Koha site uses MariaDB or MySQL.

-- 
You are receiving this mail because:
You are the QA Contact for the bug.
You are watching all bug changes.