Welcome! » Log In » Create A New Profile

Bacula 9.0.0 - StartTime doesnt have a default value

Posted by 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.


------------------------------------------------------------------------------
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
This message was imported via the External PhorumMail Module
Re: Bacula 9.0.0 - StartTime doesnt have a default value
July 16, 2017 02:01AM
------------------------------------------------------------------------------
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
This message was imported via the External PhorumMail Module
Re: Bacula 9.0.0 - StartTime doesnt have a default value
July 16, 2017 03:03AM
------------------------------------------------------------------------------
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
This message was imported via the External PhorumMail Module
Phil Stracchino
Re: Bacula 9.0.0 - StartTime doesnt have a default value
July 16, 2017 12:00PM
On 07/16/17 04:52, Kern Sibbald wrote:
> 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.


--
Phil Stracchino
Babylon Communications
phils@caerllewys.net
phil@co.ordinate.org
Landline: +1.603.293.8485
Mobile: +1.603.998.6958

------------------------------------------------------------------------------
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
This message was imported via the External PhorumMail Module
Re: Bacula 9.0.0 - StartTime doesnt have a default value
July 16, 2017 01:00PM
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


On 07/16/2017 08:27 PM, Phil Stracchino wrote:
> On 07/16/17 04:52, Kern Sibbald wrote:
>> 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.
>
>


------------------------------------------------------------------------------
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
This message was imported via the External PhorumMail Module
Phil Stracchino
Re: Bacula 9.0.0 - StartTime doesnt have a default value
July 16, 2017 04:00PM
On 07/16/17 15:11, Kern Sibbald wrote:
> 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.



--
Phil Stracchino
Babylon Communications
phils@caerllewys.net
phil@co.ordinate.org
Landline: +1.603.293.8485
Mobile: +1.603.998.6958

------------------------------------------------------------------------------
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
This message was imported via the External PhorumMail Module
Daniel Heitepriem
Re: Bacula 9.0.0 - StartTime doesnt have a default value
July 19, 2017 08:00AM
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

Am 17.07.17 um 00:15 schrieb Phil Stracchino:
> On 07/16/17 15:11, Kern Sibbald wrote:
>> 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.
>
>
>

------------------------------------------------------------------------------
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
This message was imported via the External PhorumMail Module
Sorry, only registered users may post in this forum.

Click here to login