View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000705bareos-coredirectorpublic2016-10-07 12:242017-12-18 12:11
Reporterjoergs Assigned To 
PrioritylowSeverityfeatureReproducibilityalways
Status closedResolutionfixed 
Fixed in Version17.2.4 
Summary0000705: create_bareos_database fails on MySQL 5.7
DescriptionCalling the script against a MySQL 5.7 database results in following error messages:

Making mysql tables
ERROR 1067 (42000) at line 88: Invalid default value for 'CleaningDate'
ERROR 1067 (42000) at line 107: Invalid default value for 'SchedTime'
ERROR 1067 (42000) at line 140: Invalid default value for 'SchedTime'
ERROR 1067 (42000) at line 180: Invalid default value for 'Date'
ERROR 1067 (42000) at line 192: Invalid default value for 'CreateTime'
ERROR 1067 (42000) at line 215: Invalid default value for 'FirstWritten'
ERROR 1067 (42000) at line 310: Invalid default value for 'Time'
ERROR 1171 (42000) at line 379: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
Creation of Bareos MySQL tables succeeded.
Additional InformationThe Bareos database creation script set DATETIME columns by default to 0, which is no longer allowed in MySQL 5.7:

e.g. "SchedTime DATETIME DEFAULT 0,"

The other problem is at

CREATE TABLE Quota (
   ClientId INT UNSIGNED DEFAULT NULL,
   GraceTime BIGINT DEFAULT 0,
   QuotaLimit BIGINT UNSIGNED DEFAULT 0,
   PRIMARY KEY (ClientId)
);

This results into the error message:
All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
TagsNo tags attached.

Relationships

has duplicate 0000660 closed MySQL DDL files use wrong syntax 
has duplicate 0000809 closed MySQL errors on Installation 
has duplicate 0000842 closedjoergs Cannot create database anymore on MySQL 5.17 and higher 
child of 0000861 closedjoergs Release bareos-17.2.4 

Activities

joergs

joergs

2016-10-07 12:27

developer   ~0002375

It is planed to fix this someday in the future. For now, we recommend to use MariaDB instead (which is also the default on most Linux distributions).
bareos_user

bareos_user

2017-01-23 22:33

reporter   ~0002526

Is there any workaround for those of us who are stuck with MySql >=5.7 ?

When I run a job. I get this error:
JobId 0: Fatal error: sql_create.c:97 Create DB Job record INSERT INTO Job (Job,Name,Type,Level,JobStatus,SchedTime,JobTDate,ClientId,Comment) VALUES ('MyBackup.2017-01-23_15.27.15_46','MyBackup','B','F','C','2017-01-23 15:27:11',1485203231,3,'') failed. ERR=Incorrect datetime value: '0000-00-00 00:00:00' for column 'StartTime' at row 1

It's been 4 months since this was last reported. I guess someone would have come up with a workaround.
Andreas.vdL

Andreas.vdL

2017-01-30 11:46

reporter   ~0002540

I fixed it under Ubuntu 16.04.1 with MySQL 5.7.17 by manuipulating the create-File under "/usr/lib/bareos/scripts/ddl/creates/mysql.sql"

The date-problem could be fixed by
"sed -i 's/DATETIME DEFAULT 0/DATETIME DEFAULT CURRENT_TIMESTAMP/g' mysql.sql"
I think that an older date at creation time ist not necessary.

There are two other options (NDMP...) with an wrong value of 256 and should be 255.
"CONSTRAINT NDMPLevelMap_pkey PRIMARY KEY (ClientId, FilesetId, FileSystem(255))"
"CONSTRAINT NDMPJobEnvironment_pkey PRIMARY KEY (JobId, FileIndex, EnvName(255))"

Hope this helps.
bareos_user

bareos_user

2017-02-10 16:22

reporter   ~0002558

Thanks Andreas.vdL.

I used the following workaround -

1. vim /etc/mysql/mysql.conf.d/mysqld.cnf

2. add/update
sql_mode=NO_ENGINE_SUBSTITUTION
(remove STRICT_TRANS_TABLES)
jacknob

jacknob

2017-02-14 10:40

reporter   ~0002561

Moving to MySQL 5.7 and PHP7.0, I experienced the same for sql statements throughout our systems. Once we upgraded bareos from 15.x to 16.x
I noticed the problems here.
I highly suggest to integrate a solution as suggested by @Andreas.vdL

MariaDB might be nice, but many people out there (like us) are using MySQL. It is well known and handles large amounts of bareos clients (like in our companies).
joergs

joergs

2017-02-14 10:44

developer   ~0002562

So maybe somebody can provide a patch/github pull request?
Keksdose

Keksdose

2017-05-03 15:48

reporter  

0001-updated-MySQL-creation-schema-to-current-standards.patch (7,801 bytes)    
From b04e8bf2781c35d4f4e2835c1ee26a36519e278a Mon Sep 17 00:00:00 2001
From: Jan Huisink <huisink@mpie.de>
Date: Wed, 3 May 2017 15:35:09 +0200
Subject: [PATCH] updated MySQL creation schema to current standards

---
 src/cats/ddl/creates/mysql.sql | 74 +++++++++++++++++++++---------------------
 1 file changed, 37 insertions(+), 37 deletions(-)

diff --git a/src/cats/ddl/creates/mysql.sql b/src/cats/ddl/creates/mysql.sql
index 74ed62e7..755b50ba 100644
--- a/src/cats/ddl/creates/mysql.sql
+++ b/src/cats/ddl/creates/mysql.sql
@@ -89,8 +89,8 @@ CREATE TABLE Storage (
 CREATE TABLE Device (
    DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    Name TINYBLOB NOT NULL,
-   MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
-   StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
+   MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType(MediaTypeId),
+   StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage(StorageId),
    DevMounts INTEGER UNSIGNED DEFAULT 0,
    DevReadBytes BIGINT UNSIGNED DEFAULT 0,
    DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
@@ -100,7 +100,7 @@ CREATE TABLE Device (
    DevWriteTime BIGINT UNSIGNED DEFAULT 0,
    DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
    DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
-   CleaningDate DATETIME DEFAULT 0,
+   CleaningDate DATETIME DEFAULT NULL,
    CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
    PRIMARY KEY(DeviceId)
 );
@@ -111,12 +111,12 @@ CREATE TABLE Job (
    Name TINYBLOB NOT NULL,
    Type BINARY(1) NOT NULL,
    Level BINARY(1) NOT NULL,
-   ClientId INTEGER DEFAULT 0 REFERENCES Client,
+   ClientId INTEGER DEFAULT 0 REFERENCES Client(ClientId),
    JobStatus BINARY(1) NOT NULL,
-   SchedTime DATETIME DEFAULT 0,
-   StartTime DATETIME DEFAULT 0,
-   EndTime DATETIME DEFAULT 0,
-   RealEndTime DATETIME DEFAULT 0,
+   SchedTime DATETIME DEFAULT NULL,
+   StartTime DATETIME DEFAULT NULL,
+   EndTime DATETIME DEFAULT NULL,
+   RealEndTime DATETIME DEFAULT NULL,
    JobTDate BIGINT UNSIGNED DEFAULT 0,
    VolSessionId INTEGER UNSIGNED DEFAULT 0,
    VolSessionTime INTEGER UNSIGNED DEFAULT 0,
@@ -125,9 +125,9 @@ CREATE TABLE Job (
    ReadBytes BIGINT UNSIGNED DEFAULT 0,
    JobErrors INTEGER UNSIGNED DEFAULT 0,
    JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
-   PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
-   FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet,
-   PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
+   PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool(PoolId),
+   FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet(FileSetId),
+   PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job(JobId),
    PurgedFiles TINYINT DEFAULT 0,
    HasBase TINYINT DEFAULT 0,
    HasCache TINYINT DEFAULT 0,
@@ -146,10 +146,10 @@ CREATE TABLE JobHisto (
    Level BINARY(1) NOT NULL,
    ClientId INTEGER DEFAULT 0,
    JobStatus BINARY(1) NOT NULL,
-   SchedTime DATETIME DEFAULT 0,
-   StartTime DATETIME DEFAULT 0,
-   EndTime DATETIME DEFAULT 0,
-   RealEndTime DATETIME DEFAULT 0,
+   SchedTime DATETIME DEFAULT NULL,
+   StartTime DATETIME DEFAULT NULL,
+   EndTime DATETIME DEFAULT NULL,
+   RealEndTime DATETIME DEFAULT NULL,
    JobTDate BIGINT UNSIGNED DEFAULT 0,
    VolSessionId INTEGER UNSIGNED DEFAULT 0,
    VolSessionTime INTEGER UNSIGNED DEFAULT 0,
@@ -180,10 +180,10 @@ CREATE TABLE Location (
 
 CREATE TABLE LocationLog (
    LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-   Date DATETIME DEFAULT 0,
+   Date DATETIME DEFAULT NULL,
    Comment BLOB NOT NULL,
-   MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
-   LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
+   MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media(MediaId),
+   LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location(LocationId),
    NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
     'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
    NewEnabled TINYINT,
@@ -194,7 +194,7 @@ CREATE TABLE FileSet (
    FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    FileSet TINYBLOB NOT NULL,
    MD5 TINYBLOB,
-   CreateTime DATETIME DEFAULT 0,
+   CreateTime DATETIME DEFAULT NULL,
    FileSetText BLOB NOT NULL,
    PRIMARY KEY(FileSetId)
 );
@@ -218,13 +218,13 @@ CREATE TABLE Media (
    MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    VolumeName TINYBLOB NOT NULL,
    Slot INTEGER DEFAULT 0,
-   PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
+   PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool(PoolId),
    MediaType TINYBLOB NOT NULL,
-   MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
+   MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType(MediaTypeId),
    LabelType TINYINT DEFAULT 0,
-   FirstWritten DATETIME DEFAULT 0,
-   LastWritten DATETIME DEFAULT 0,
-   LabelDate DATETIME DEFAULT 0,
+   FirstWritten DATETIME DEFAULT NULL,
+   LastWritten DATETIME DEFAULT NULL,
+   LabelDate DATETIME DEFAULT NULL,
    VolJobs INTEGER UNSIGNED DEFAULT 0,
    VolFiles INTEGER UNSIGNED DEFAULT 0,
    VolBlocks INTEGER UNSIGNED DEFAULT 0,
@@ -245,20 +245,20 @@ CREATE TABLE Media (
    MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
    MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
    InChanger TINYINT DEFAULT 0,
-   StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
-   DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
+   StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage(StorageId),
+   DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device(DeviceId),
    MediaAddressing TINYINT DEFAULT 0,
    VolReadTime BIGINT UNSIGNED DEFAULT 0,
    VolWriteTime BIGINT UNSIGNED DEFAULT 0,
    EndFile INTEGER UNSIGNED DEFAULT 0,
    EndBlock INTEGER UNSIGNED DEFAULT 0,
-   LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
+   LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location(LocationId),
    RecycleCount INTEGER UNSIGNED DEFAULT 0,
    MinBlockSize INTEGER UNSIGNED DEFAULT 0,
    MaxBlockSize INTEGER UNSIGNED DEFAULT 0,
-   InitialWrite DATETIME DEFAULT 0,
-   ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
-   RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
+   InitialWrite DATETIME DEFAULT NULL,
+   ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool(PoolId),
+   RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool(PoolId),
    EncryptionKey TINYBLOB,
    Comment BLOB,
    PRIMARY KEY(MediaId),
@@ -311,8 +311,8 @@ CREATE TABLE Client (
 
 CREATE TABLE Log (
    LogId INTEGER UNSIGNED AUTO_INCREMENT,
-   JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
-   Time DATETIME DEFAULT 0,
+   JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job(JobId),
+   Time DATETIME DEFAULT NULL,
    LogText BLOB NOT NULL,
    PRIMARY KEY(LogId),
    INDEX (JobId)
@@ -386,19 +386,19 @@ CREATE TABLE Quota (
 );
 
 CREATE TABLE NDMPLevelMap (
-   ClientId INTEGER DEFAULT 0 REFERENCES Client,
-   FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet,
+   ClientId INTEGER DEFAULT 0 REFERENCES Client(ClientId),
+   FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet(FileSetId),
    FileSystem TINYBLOB NOT NULL,
    DumpLevel INTEGER NOT NULL,
-   CONSTRAINT NDMPLevelMap_pkey PRIMARY KEY (ClientId, FilesetId, FileSystem(256))
+   CONSTRAINT NDMPLevelMap_pkey PRIMARY KEY (ClientId, FilesetId, FileSystem(255))
 );
 
 CREATE TABLE NDMPJobEnvironment (
-   JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
+   JobId INTEGER UNSIGNED NOT NULL REFERENCES Job(JobId),
    FileIndex INTEGER UNSIGNED NOT NULL,
    EnvName TINYBLOB NOT NULL,
    EnvValue TINYBLOB NOT NULL,
-   CONSTRAINT NDMPJobEnvironment_pkey PRIMARY KEY (JobId, FileIndex, EnvName(256))
+   CONSTRAINT NDMPJobEnvironment_pkey PRIMARY KEY (JobId, FileIndex, EnvName(255))
 );
 
 CREATE TABLE DeviceStats (
-- 
2.11.0 (Apple Git-81)
0001-updated-MySQL-creation-schema-to-current-standards.patch (7,801 bytes)   
Keksdose

Keksdose

2017-05-03 15:49

reporter   ~0002633

I just attached a patch which will fix the issue with MySQL >= 5.7.
joergs

joergs

2017-12-14 12:43

developer   ~0002839

That you for the patch. We included it in bareos-17.2.
joergs

joergs

2017-12-18 12:11

developer   ~0002842

Patch integrated and tested against bareos-regress against, MariaDB, MySQL 5.5 and MySQL 5.7.

Related Changesets

bareos: bareos-17.2 964ef032

2017-05-03 17:35

mpiejh


Committer: joergs

Ported: N/A

Details Diff		 updated MySQL creation schema to current standards Affected Issues
0000705
mod - src/cats/ddl/creates/mysql.sql Diff File

Issue History

Date Modified Username Field Change
2016-10-07 12:24 joergs New Issue
2016-10-07 12:25 joergs Relationship added related to 0000660
2016-10-07 12:27 joergs Note Added: 0002375
2017-01-23 22:33 bareos_user Note Added: 0002526
2017-01-30 11:46 Andreas.vdL Note Added: 0002540
2017-02-10 16:22 bareos_user Note Added: 0002558
2017-02-14 10:40 jacknob Note Added: 0002561
2017-02-14 10:44 joergs Note Added: 0002562
2017-05-03 15:48 Keksdose File Added: 0001-updated-MySQL-creation-schema-to-current-standards.patch
2017-05-03 15:49 Keksdose Note Added: 0002633
2017-11-28 12:31 joergs Assigned To => joergs
2017-11-28 12:31 joergs Status new => assigned
2017-11-28 12:32 joergs Relationship added has duplicate 0000809
2017-11-28 12:33 joergs Relationship replaced has duplicate 0000660
2017-11-28 12:35 joergs Relationship added has duplicate 0000842
2017-12-14 12:41 joergs Changeset attached => bareos bareos-17.2 964ef032
2017-12-14 12:43 joergs Note Added: 0002839
2017-12-14 12:43 joergs Status assigned => resolved
2017-12-14 12:43 joergs Fixed in Version => 17.2.4
2017-12-14 12:43 joergs Resolution open => fixed
2017-12-18 12:05 joergs Relationship added child of 0000861
2017-12-18 12:11 joergs Note Added: 0002842
2017-12-18 12:11 joergs Status resolved => closed
2017-12-18 12:11 joergs Assigned To joergs =>