> I don't really have an exact explanation but at least the schema seem to
> be created ...
I've been a little bit too rash, database gets created but director was
unable to run a single job ...
```
27-Nov 19:40 xxx JobId 0: Fatal error: sql_create.c:83 Create DB Job
record INSERT INTO Job
(Job,Name,Type,Level,JobStatus,SchedTime,JobTDate,ClientId,Comment)
VALUES
('job_tester.xxx_fs_autotest.2020-11-27_19.40.13_03','job_xxx_fs_autotest','B','F','C','2020-11-27
19:40:13',1606502413,2,'') failed. ERR=Cannot add or update a child row:
a foreign key constraint fails (`bacula`.`Job`, CONSTRAINT `Job_ibfk_2`
FOREIGN KEY (`PoolId`) REFERENCES `Pool` (`PoolId`))
```
... so I consulted sources about the postgresl db structure and found,
that there does not seem to be any FKs in the original/supported schema.
So I went to create another patch and just dropped the REFERENCES from
mysql schema (see the attachment). Director seem to be fully working at
least in our CI with it.
I wonder whether this might be a proper solution which works for everybody:
* it resolved the original issue
* it matches postgresql schema a bit more
* also ClientId types gets synced across all columns in all tables
bodik
--- /usr/share/dbconfig-common/data/bacula-director-mysql/install/mysql 2020-10-01 17:33:19.000000000 +0200
+++ site/profile/files/bacula/server/mysql.sql 2020-11-27 20:44:17.535712338 +0100
@@ -35,9 +35,9 @@
CREATE TABLE File (
FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
FileIndex INTEGER DEFAULT 0,
- JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
- PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
- FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
+ JobId INTEGER UNSIGNED NOT NULL,
+ PathId INTEGER UNSIGNED NOT NULL,
+ FilenameId INTEGER UNSIGNED NOT NULL,
DeltaSeq SMALLINT UNSIGNED DEFAULT 0,
MarkId INTEGER UNSIGNED DEFAULT 0,
LStat TINYBLOB NOT NULL,
@@ -57,7 +57,7 @@
ObjectIndex INTEGER DEFAULT 0,
ObjectType INTEGER DEFAULT 0,
FileIndex INTEGER DEFAULT 0,
- JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
+ JobId INTEGER UNSIGNED NOT NULL,
ObjectCompression INTEGER DEFAULT 0,
PRIMARY KEY(RestoreObjectId),
INDEX (JobId)
@@ -90,8 +90,8 @@
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,
+ StorageId INTEGER UNSIGNED DEFAULT 0,
DevMounts INTEGER UNSIGNED DEFAULT 0,
DevReadBytes BIGINT UNSIGNED DEFAULT 0,
DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
@@ -113,7 +113,7 @@
Name TINYBLOB NOT NULL,
Type BINARY(1) NOT NULL,
Level BINARY(1) NOT NULL,
- ClientId INTEGER DEFAULT 0 REFERENCES Client,
+ ClientId INTEGER UNSIGNED DEFAULT 0,
JobStatus BINARY(1) NOT NULL,
SchedTime DATETIME DEFAULT NULL,
StartTime DATETIME DEFAULT NULL,
@@ -127,9 +127,9 @@
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,
+ FileSetId INTEGER UNSIGNED DEFAULT 0,
+ PriorJobId INTEGER UNSIGNED DEFAULT 0,
PurgedFiles TINYINT DEFAULT 0,
HasBase TINYINT DEFAULT 0,
HasCache TINYINT DEFAULT 0,
@@ -149,7 +149,7 @@
Name TINYBLOB NOT NULL,
Type BINARY(1) NOT NULL,
Level BINARY(1) NOT NULL,
- ClientId INTEGER DEFAULT 0,
+ ClientId INTEGER UNSIGNED DEFAULT 0,
JobStatus BINARY(1) NOT NULL,
SchedTime DATETIME DEFAULT NULL,
StartTime DATETIME DEFAULT NULL,
@@ -189,8 +189,8 @@
LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
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,
+ LocationId INTEGER UNSIGNED DEFAULT 0,
NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
NewEnabled TINYINT,
@@ -209,8 +209,8 @@
CREATE TABLE JobMedia (
JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
- MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
+ JobId INTEGER UNSIGNED NOT NULL,
+ MediaId INTEGER UNSIGNED NOT NULL,
FirstIndex INTEGER UNSIGNED DEFAULT 0,
LastIndex INTEGER UNSIGNED DEFAULT 0,
StartFile INTEGER UNSIGNED DEFAULT 0,
@@ -227,9 +227,9 @@
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,
MediaType TINYBLOB NOT NULL,
- MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
+ MediaTypeId INTEGER UNSIGNED DEFAULT 0,
LabelType TINYINT DEFAULT 0,
FirstWritten DATETIME DEFAULT NULL,
LastWritten DATETIME DEFAULT NULL,
@@ -262,18 +262,18 @@
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,
+ DeviceId INTEGER UNSIGNED DEFAULT 0,
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,
RecycleCount INTEGER UNSIGNED DEFAULT 0,
InitialWrite DATETIME DEFAULT NULL,
- ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
- RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
+ ScratchPoolId INTEGER UNSIGNED DEFAULT 0,
+ RecyclePoolId INTEGER UNSIGNED DEFAULT 0,
Comment BLOB,
PRIMARY KEY(MediaId),
UNIQUE (VolumeName(128)),
@@ -302,9 +302,9 @@
LabelType TINYINT DEFAULT 0,
LabelFormat TINYBLOB,
Enabled TINYINT DEFAULT 1,
- ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
- RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
- NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
+ ScratchPoolId INTEGER UNSIGNED DEFAULT 0,
+ RecyclePoolId INTEGER UNSIGNED DEFAULT 0,
+ NextPoolId INTEGER UNSIGNED DEFAULT 0,
MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
MigrationTime BIGINT UNSIGNED DEFAULT 0,
@@ -326,7 +326,7 @@
CREATE TABLE Log (
LogId INTEGER UNSIGNED AUTO_INCREMENT,
- JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
+ JobId INTEGER UNSIGNED DEFAULT 0,
Time DATETIME DEFAULT NULL,
LogText BLOB NOT NULL,
PRIMARY KEY(LogId),
@@ -336,9 +336,9 @@
CREATE TABLE BaseFiles (
BaseId BIGINT UNSIGNED AUTO_INCREMENT,
- BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
- JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
- FileId BIGINT UNSIGNED NOT NULL REFERENCES File,
+ BaseJobId INTEGER UNSIGNED NOT NULL,
+ JobId INTEGER UNSIGNED NOT NULL,
+ FileId BIGINT UNSIGNED NOT NULL,
FileIndex INTEGER DEFAULT 0,
PRIMARY KEY(BaseId)
);
@@ -347,9 +347,9 @@
CREATE TABLE UnsavedFiles (
UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
- JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
- PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
- FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
+ JobId INTEGER UNSIGNED NOT NULL,
+ PathId INTEGER UNSIGNED NOT NULL,
+ FilenameId INTEGER UNSIGNED NOT NULL,
PRIMARY KEY (UnsavedId)
);
