Hi, First: thanks to everyone who stepped in and shared thoughts and info.
Another person who replied to me directly (not through the mailing list) helped very much with this by sharing what he was also helped with previously. Here is the info: ████ BACKGROUND > This is correct, it is not a MariaDB vs. MySQL > issue, it is a MariaDB/MySQL *version* issue. MySQL > 5.6 and older allowed DATETIME and TIMESTAMP fields > to have the value 0, which is technically not valid > for those datatypes. MySQL 5.7 and MariaDB 10.2, as > configured out of the box, enforce validity of > DATETIME and TIMESTAMP types, and do not allow them > to be 0 or to contain zero year, month or day fields > (since there is no year 0, month 0, or day 0).[1] > > There is a simple configuration fix for this that > does not require any changes to Bacula or your > Catalog: > > 1. Log into your database. > 2. SELECT @@GLOBAL.SQL_MODE; > 3. SET GLOBAL SQL_MODE='new value'; For me step 2 gave: +--------------------------------------------+ | @@GLOBAL.SQL_MODE | +--------------------------------------------+ | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +--------------------------------------------+ 1 row in set (0.00 sec) and I also had /etc/my.cnf showing: sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ████ THE WORKAROUND After removing ",STRICT_TRANS_TABLES" and restarting MariaDB Bacula 9.2.0 works fine. ████ CONS (IMO) This is a global setting which affects all MariaDB. I have been looking for a solution which would allow setting sql_mode just for Bacula. And I found this: https://dba.stackexchange.com/questions/31447/how-can-i-set-a-default-session-sql-mode-for-a-given-user i.e. using an init_connect which would supposedly affect only a particular user. Then following the example I tried this: ------------------- SET @@sql_mode = CASE CURRENT_USER() WHEN 'bacula@%' THEN 'NO_ENGINE_SUBSTITUTION' WHEN 'bacula@127.0.0.1' THEN 'NO_ENGINE_SUBSTITUTION' WHEN 'bacula@localhost' THEN 'NO_ENGINE_SUBSTITUTION' ELSE @@sql_mode END; ------------------- Unfortunately after logging in as user bacula I am still getting the old sql_mode: ------------------- $ mysql -u bacula -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 26 Server version: 10.2.15-MariaDB openSUSE package Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SELECT @@SQL_MODE; +--------------------------------------------+ | @@SQL_MODE | +--------------------------------------------+ | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +--------------------------------------------+ 1 row in set (0.00 sec) ------------------- ████ What I suppose could be a possible stable solution 1) [Perhaps fast and easy for a DB expert] If anyone could figure out the proper value for init_connect which one can use in my.cnf, affecting only DB user 'bacula' 2) [Perhaps a little risky] Have a RunBeforeJob and RunAfterJob short scripts which set/unset the proper SQL_MODE just for the time of the backup jobs. But that might be somewhat "dangerous" if other programs are using MariaDB during backup and may be affected by this change. 3) Perhaps have a script which does what Phil suggests. -- George ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
