On 10/03/16 10:52, Kern Sibbald wrote: > On 10/03/2016 02:52 PM, Phil Stracchino wrote: >> On 10/03/16 05:19, Ralph Bolton wrote: >>> Yeah, Oracle are making changes alright - not all of them are good :-( >>> >>> It's a relatively easy to fix, and actually, it's probably something >>> that should be changed in the "make_mysql_tables" file supplied with >>> Bacula (as it's backwards compatible with older MySQL and MariaDBs). The >>> change is a search and replace for: >>> >>> DATETIME DEFAULT 0, >>> >>> ...to... >>> >>> DATETIME DEFAULT '0000-00-00 00:00:00', > > Hmm. It doesn't surprise me, but I have not run into the problem you > mention below on any of the MySQL versions that > are currently in the most popular distros. What MySQL version has that > change?
MySQL 5.6 started the stricter enforcement of fully compliant SQL DDL. However, whether it is enforced or not depends upon a number of MySQL settings, including SQL_MODE. In 5.5 and older MySQL releases, SQL_MODE is empty by default, and installations that have been updated in place will continue to have whatever SQL_MODE was set in the old configuration, if set. If not set by the configuration files, MySQL 5.6.6 and later default to NO_ENGINE_SUBSTITUTION. MySQL 5.7.5 added ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES to the default SQL_MODE; MySQL 5.7.7 added NO_AUTO_CREATE_USER; MySQL 5.7.8 added ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE. So a clean new install of a current MySQL 5.6 release (or an updated one with no explicitly set SQL_MODE) will use SQL_MODE = NO_ENGINE_SUBSTITUTION, while a current MySQL 5.7 clean installation (or, again, an updated one with no explicitly set SQL_MODE) will use NO_ENGINE_SUBSTITUTION, NO_AUTO_CREATE_USER, ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO. ONLY_FULL_GROUP_BY is not part of the default SQL mode prior to 5.7.5 because versions prior to 5.7.5 do not detect functional dependency, which is required to properly implement it. It's not really relevant here and causes no issues with Bacula. NO_ZERO_DATE controls whether '0000-00-00' is considered a valid date. NO_ZERO_IN_DATE controls whether a DATETIME value is considered valid if the YEAR is specified but either MONTH or DAY is zero. (For example, 2016-00-01 or 2016-01-00 are invalid with NO_ZERO_IN_DATE.) Both of these and ERROR_FOR_DIVISION_BY_ZERO are oddly inconsistent in their pattern of support. All three were deprecated in 5.7.4, then supported in 5.7.5 through 5.7.7 but did nothing, supported in 5.7.8 and up but only do anything in combination with STRICT, and are scheduled to be deprecated again and removed in some future version when their functionality is fully rolled into STRICT. I'm currently using: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES but NO_AUTO_CREATE USER is also scheduled for future removal (and will then be on all the time). -- Phil Stracchino Babylon Communications ph...@caerllewys.net p...@co.ordinate.org Landline: 603.293.8485 ------------------------------------------------------------------------------ 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
