[Bacula-users] Bacula 9.0.0 - 'StartTime' doesn't have a default value

[Mick]

Hi,

I've managed to compile and install 9.0.0 (with much appreciated help)
however now the jobs fail.

I have run the update_bacula_tables script


--------------------------------------

Error Message:

JobId 0: Fatal error: sql_create.c:84 Create DB Job record

INSERT INTO Job
(Job,Name,Type,Level,JobStatus,SchedTime,JobTDate,ClientId,Comment)

VALUES
('Client1.2017-07-16_00.05.00_04','Client1','B','D','C','2017-07-16
00:05:00',1500159900,3,'')

failed. ERR=Field 'StartTime' doesn't have a default value

--------------------------------------

OS: openSUSE Leap 42.1 (x86_64)

-----------------------------------------

MySQL version info


innodb_version 5.6.34-79.1
protocol_version 10
slave_type_conversions
version 10.0.29-MariaDB
version_comment SLE 12 SP1 package
version_compile_machine x86_64
version_compile_os Linux
version_malloc_library system


Any help appreciated.

[Kern Sibbald]

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot

[Kern Sibbald]

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot

[Kern Sibbald]

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot

[Phil Stracchino]

Hello,
It sounds like this is another change in MySQL in a newer version.
Previously they prohibited a DEFAULT 0 for the start time, because zero
is not a permitted value, even though the field is a Unix Time stamp,
where 0 is a perfectly valid time. Consequently we removed the
DEFAULT. Apparently, they now require a start time. The problem is
that Bacula counts on the default start time to be zero in some of the
database records (the Media record I think). Note: in the Job record,
there is no start time when the Job is created. The start time is known
only when the Job begins running, so the default value must be zero.

The actual status here:

With default SQL_MODE, MySQL 5.6 and later prohibit NOT NULL without
DEFAULT, and 5.7 and later prohibit zero date or dates with zero parts.
You can have zeroes in the TIME part of a DATETIME or TIMESTAMP column,
but zero fields in the DATE part are prohibited because there is no
year, month or day 0.

The canonical "correct default" for DATETIME fields is the Unix epoch.
However is your code is dependent upon a 0 default, you can manually set
a SQL_MODE that does *NOT* include NO_ZERO_DATE or NO_ZERO_IN_DATE.

[Kern Sibbald]

Hello Phil,

According to the documentation in both MySQL and MariaDB, I can use
"0000-00-00 00:00:00". I might have missed something though. It seems
to work fine with MariaDB 10.0. I will test it with MariaDB 10.2 and
with the current Ubuntu 16.04 version of MySQL, and if it works, I will
probably use that, unless someone can see some major downside. In the
mean time, providing you do not change a default MariaDB/MySQL, Bacula
7.4.x and 9.0.x seem to work fine.

Best regards,

Kern

Post by Phil Stracchino

Hello,
It sounds like this is another change in MySQL in a newer version.
Previously they prohibited a DEFAULT 0 for the start time, because zero
is not a permitted value, even though the field is a Unix Time stamp,
where 0 is a perfectly valid time. Consequently we removed the
DEFAULT. Apparently, they now require a start time. The problem is
that Bacula counts on the default start time to be zero in some of the
database records (the Media record I think). Note: in the Job record,
there is no start time when the Job is created. The start time is known
only when the Job begins running, so the default value must be zero.

With default SQL_MODE, MySQL 5.6 and later prohibit NOT NULL without
DEFAULT, and 5.7 and later prohibit zero date or dates with zero parts.
You can have zeroes in the TIME part of a DATETIME or TIMESTAMP column,
but zero fields in the DATE part are prohibited because there is no
year, month or day 0.
The canonical "correct default" for DATETIME fields is the Unix epoch.
However is your code is dependent upon a 0 default, you can manually set
a SQL_MODE that does *NOT* include NO_ZERO_DATE or NO_ZERO_IN_DATE.

[Phil Stracchino]

Post by Kern Sibbald
Hello Phil,
According to the documentation in both MySQL and MariaDB, I can use
"0000-00-00 00:00:00".

Yes - prior to MySQL 5.7, or if you change MySQL 5.7's default SQL_MODE.
The default SQL_MODE in 5.7 includes NO_ZERO_DATE,NO_ZERO_IN_DATE.
Remove those, and it'll work just fine in MySQL 5.7.

[Daniel Heitepriem]

Hi everyone,

after upgrading from Bacula 7.5.7 to 9.0.1 in my test environment, I
encountered the same issue after running the "update_mysql_tables"
script. Beside removing NO_ZERO_IN_DATE and NO_ZERO_DATE I also had to
remove STRICT_TRANS_TABLES from the SQL mode.

The MySQL version I use is 5.7.13
mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.13 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.13-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | sparc |
| version_compile_os | solaris11 |
+-------------------------+------------------------------+

with these settings:
[mysqld]
basedir=/opt/mysql
datadir=/data/mysql
bind_address=Ip.Address.Of.Zone
collation_server=utf8_general_ci
character_set_server=utf8
slow_query_log=1
slow_query_log_file=/logs/mysql/slow_queries
log_error=/logs/mysql/error
explicit_defaults_for_timestamp=TRUE
skip-external-locking
skip-innodb_doublewrite

Regards,
Daniel

Post by Phil Stracchino

Post by Kern Sibbald
Hello Phil,
According to the documentation in both MySQL and MariaDB, I can use
"0000-00-00 00:00:00".

Yes - prior to MySQL 5.7, or if you change MySQL 5.7's default SQL_MODE.
The default SQL_MODE in 5.7 includes NO_ZERO_DATE,NO_ZERO_IN_DATE.
Remove those, and it'll work just fine in MySQL 5.7.