Bacula 9.0.0 - 'StartTime' doesn't have a default value

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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.


------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Bacula 9.0.0 - 'StartTime' doesn't have a default value

Kern Sibbald

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.

So, I can see a few possible solutions:

1. Modify the make_mysql_tables that creates the Job record to include DEFAULT '0000-00-00 00:00:00'.

2. Fall back to an older version of MySQL.

3. Research if they permit some other way of setting a default value of zero.

4. Use MariaDB but not version 10.2.x

5. Use PostgreSQL (this is my preferred solution).

Best regards,

Kern



On 07/16/2017 09:28 AM, Mick wrote:
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users



------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Bacula 9.0.0 - 'StartTime' doesn't have a default value

Kern Sibbald

Hello,

After a more careful look, I see that my first two paragraphs are not correct.  After looking at your output more carefully, I see that you are running MariaDB, and at the moment Bacula does not officially support MariaDB.  My suggestions for correcting the problem other than 2 and 4 are probably still valid.

Best regards,

Kern


On 07/16/2017 10:52 AM, 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.

So, I can see a few possible solutions:

1. Modify the make_mysql_tables that creates the Job record to include DEFAULT '0000-00-00 00:00:00'.

2. Fall back to an older version of MySQL.

3. Research if they permit some other way of setting a default value of zero.

4. Use MariaDB but not version 10.2.x

5. Use PostgreSQL (this is my preferred solution).

Best regards,

Kern



On 07/16/2017 09:28 AM, Mick wrote:
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users




------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users


------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Bacula 9.0.0 - 'StartTime' doesn't have a default value

Kern Sibbald

Hello once more,

I have now removed MySQL from my system and installed MariaDB.  Info:


MariaDB [regress]> show variables like "%version%";
+-------------------------+----------------------------------+
| Variable_name           | Value                            |
+-------------------------+----------------------------------+
| innodb_version          | 5.6.34-79.1                      |
| protocol_version        | 10                               |
| slave_type_conversions  |                                  |
| version                 | 10.0.29-MariaDB-0ubuntu0.16.04.1 |
| version_comment         | Ubuntu 16.04                     |
| version_compile_machine | x86_64                           |
| version_compile_os      | debian-linux-gnu                 |
| version_malloc_library  | bundled jemalloc                 |
+-------------------------+----------------------------------+
8 rows in set (0.00 sec)

All my jobs run fine.  I would say that something is not correct with your MariaDB installation.

Best regards,

Kern


On 07/16/2017 11:19 AM, Kern Sibbald wrote:

Hello,

After a more careful look, I see that my first two paragraphs are not correct.  After looking at your output more carefully, I see that you are running MariaDB, and at the moment Bacula does not officially support MariaDB.  My suggestions for correcting the problem other than 2 and 4 are probably still valid.

Best regards,

Kern


On 07/16/2017 10:52 AM, 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.

So, I can see a few possible solutions:

1. Modify the make_mysql_tables that creates the Job record to include DEFAULT '0000-00-00 00:00:00'.

2. Fall back to an older version of MySQL.

3. Research if they permit some other way of setting a default value of zero.

4. Use MariaDB but not version 10.2.x

5. Use PostgreSQL (this is my preferred solution).

Best regards,

Kern



On 07/16/2017 09:28 AM, Mick wrote:
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users




------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users



------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users


------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Bacula 9.0.0 - 'StartTime' doesn't have a default value

Phil Stracchino-2
In reply to this post by Kern Sibbald
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
  [hidden email]
  [hidden email]
  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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Bacula 9.0.0 - 'StartTime' doesn't have a default value

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


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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Bacula 9.0.0 - 'StartTime' doesn't have a default value

Phil Stracchino-2
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
  [hidden email]
  [hidden email]
  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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Bacula 9.0.0 - 'StartTime' doesn't have a default value

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

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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Loading...