Fix: backup-dir JobId 0: Fatal error: cats/sql_create.cc:96 Create DB Job
record INSERT INTO Job
(Job,Name,Type,Level,JobStatus,SchedTime,JobTDate,ClientId,Comment) VALUES (
...
четверг, 10 июня 2021 г. в 14:23:47 UTC+3, khvalera:
четверг, 10 июня 2021 г. в 14:23:47 UTC+3, khvalera:
> Made a patch for the mysql.sql file:
>
> четверг, 10 июня 2021 г. в 12:05:16 UTC+3, khvalera:
>
>> bareos 20.0.1
>>
>> четверг, 10 июня 2021 г. в 12:04:27 UTC+3, khvalera:
>>
>>> When executing */usr/lib/bareos/scripts/make_bareos_tables --user root
>>> --password*, I get the following error:
>>>
>>>
>>> ERROR 1005 (HY000) at line 31: Can't create table `bareos`.`File`
>>> (errno: 150 "Foreign key constraint is incorrectly formed")
>>> ERROR 1005 (HY000) at line 55: Can't create table
>>> `bareos`.`RestoreObject` (errno: 150 "Foreign key constraint is incorrectly
>>> formed")
>>> ERROR 1005 (HY000) at line 104: Can't create table `bareos`.`Job`
>>> (errno: 150 "Foreign key constraint is incorrectly formed")
>>> ERROR 1005 (HY000) at line 178: Can't create table
>>> `bareos`.`LocationLog` (errno: 150 "Foreign key constraint is incorrectly
>>> formed")
>>> ERROR 1005 (HY000) at line 199: Can't create table `bareos`.`JobMedia`
>>> (errno: 150 "Foreign key constraint is incorrectly formed")
>>> ERROR 1005 (HY000) at line 215: Can't create table `bareos`.`Media`
>>> (errno: 150 "Foreign key constraint is incorrectly formed")
>>> ERROR 1005 (HY000) at line 267: Can't create table `bareos`.`Pool`
>>> (errno: 150 "Foreign key constraint is incorrectly formed")
>>> ERROR 1005 (HY000) at line 310: Can't create table `bareos`.`Log`
>>> (errno: 150 "Foreign key constraint is incorrectly formed")
>>> ERROR 1005 (HY000) at line 319: Can't create table `bareos`.`BaseFiles`
>>> (errno: 150 "Foreign key constraint is incorrectly formed")
>>> ERROR 1146 (42S02) at line 328: Table 'bareos.BaseFiles' doesn't exist
>>> ERROR 1005 (HY000) at line 378: Can't create table `bareos`.`Quota`
>>> (errno: 150 "Foreign key constraint is incorrectly formed")
>>> ERROR 1005 (HY000) at line 385: Can't create table
>>> `bareos`.`NDMPLevelMap` (errno: 150 "Foreign key constraint is incorrectly
>>> formed")
>>> ERROR 1005 (HY000) at line 393: Can't create table
>>> `bareos`.`NDMPJobEnvironment` (errno: 150 "Foreign key constraint is
>>> incorrectly formed")
>>> ERROR 1005 (HY000) at line 401: Can't create table
>>> `bareos`.`DeviceStats` (errno: 150 "Foreign key constraint is incorrectly
>>> formed")
>>> ERROR 1005 (HY000) at line 417: Can't create table `bareos`.`JobStats`
>>> (errno: 150 "Foreign key constraint is incorrectly formed")
>>> Creation of Bareos MySQL tables succeeded.
>>>
>>
--
You received this message because you are subscribed to the Google Groups
"bareos-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to bareos-devel+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/bareos-devel/63bfae92-60d4-4af1-a52d-c81b3eda9d1fn%40googlegroups.com.
--- a/core/src/cats/ddl/creates/mysql.sql 2021-06-10 16:14:05.373605979 +0300
+++ b/core/src/cats/ddl/creates/mysql.sql 2021-06-10 16:33:09.592630760 +0300
@@ -11,63 +11,6 @@
INDEX (Path(255))
);
--- We strongly recommend to avoid the temptation to add new indexes.
--- In general, these will cause very significant performance
--- problems in other areas. A better approach is to carefully check
--- that all your memory configuration parameters are
--- suitable for the size of your installation. If you backup
--- millions of files, you need to adapt the database memory
--- configuration parameters concerning sorting, joining and global
--- memory. By default, sort and join parameters are very small
--- (sometimes 8Kb), and having sufficient memory specified by those
--- parameters is extremely important to run fast.
-
--- In File table
--- FileIndex is 0 for FT_DELETED files
--- Name is '' for directories
--- The index INDEX (PathId, JobId, FileIndex) is
--- important for bvfs performance, especially
--- for .bvfs_lsdirs which is used by bareos-webui.
-CREATE TABLE File (
- FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
- FileIndex INTEGER UNSIGNED DEFAULT 0,
- JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
- PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
- DeltaSeq SMALLINT UNSIGNED DEFAULT 0,
- MarkId INTEGER UNSIGNED DEFAULT 0,
- Fhinfo NUMERIC(20) DEFAULT 0,
- Fhnode NUMERIC(20) DEFAULT 0,
- LStat TINYBLOB NOT NULL,
- MD5 TINYBLOB NOT NULL,
- Name BLOB NOT NULL,
- PRIMARY KEY (FileId),
- INDEX (JobId, PathId, Name(255)),
- INDEX (PathId, JobId, FileIndex)
-);
-
---
--- Possibly add one or more of the following indexes
--- to the above File table if your Verifies are
--- too slow, but they can slow down backups.
---
--- INDEX (PathId),
-
-CREATE TABLE RestoreObject (
- RestoreObjectId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- ObjectName BLOB NOT NULL,
- RestoreObject LONGBLOB NOT NULL,
- PluginName TINYBLOB NOT NULL,
- ObjectLength INTEGER DEFAULT 0,
- ObjectFullLength INTEGER DEFAULT 0,
- ObjectIndex INTEGER DEFAULT 0,
- ObjectType INTEGER DEFAULT 0,
- FileIndex INTEGER UNSIGNED DEFAULT 0,
- JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
- ObjectCompression INTEGER DEFAULT 0,
- PRIMARY KEY(RestoreObjectId),
- INDEX (JobId)
-);
-
CREATE TABLE MediaType (
MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
MediaType TINYBLOB NOT NULL,
@@ -101,13 +44,65 @@
PRIMARY KEY(DeviceId)
);
+CREATE TABLE FileSet (
+ FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+ FileSet TINYBLOB NOT NULL,
+ MD5 TINYBLOB,
+ CreateTime DATETIME DEFAULT NULL,
+ FileSetText BLOB NOT NULL,
+ PRIMARY KEY(FileSetId)
+);
+
+CREATE TABLE Client (
+ ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+ Name TINYBLOB NOT NULL,
+ Uname TINYBLOB NOT NULL, /* full uname -a of client */
+ AutoPrune TINYINT DEFAULT 0,
+ FileRetention BIGINT UNSIGNED DEFAULT 0,
+ JobRetention BIGINT UNSIGNED DEFAULT 0,
+ UNIQUE (Name(128)),
+ PRIMARY KEY(ClientId)
+);
+
+CREATE TABLE Pool (
+ PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+ Name TINYBLOB NOT NULL,
+ NumVols INTEGER UNSIGNED DEFAULT 0,
+ MaxVols INTEGER UNSIGNED DEFAULT 0,
+ UseOnce TINYINT DEFAULT 0,
+ UseCatalog TINYINT DEFAULT 0,
+ AcceptAnyVolume TINYINT DEFAULT 0,
+ VolRetention BIGINT UNSIGNED DEFAULT 0,
+ VolUseDuration BIGINT UNSIGNED DEFAULT 0,
+ MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
+ MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
+ MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
+ AutoPrune TINYINT DEFAULT 0,
+ Recycle TINYINT DEFAULT 0,
+ ActionOnPurge TINYINT DEFAULT 0,
+ PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
+ LabelType TINYINT DEFAULT 0,
+ LabelFormat TINYBLOB,
+ Enabled TINYINT DEFAULT 1,
+ ScratchPoolId INTEGER UNSIGNED DEFAULT 0,
+ RecyclePoolId INTEGER UNSIGNED DEFAULT 0,
+ NextPoolId INTEGER UNSIGNED DEFAULT 0,
+ MinBlockSize INTEGER UNSIGNED DEFAULT 0,
+ MaxBlockSize INTEGER UNSIGNED DEFAULT 0,
+ MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
+ MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
+ MigrationTime BIGINT UNSIGNED DEFAULT 0,
+ UNIQUE (Name(128)),
+ PRIMARY KEY (PoolId)
+);
+
CREATE TABLE Job (
JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
Job TINYBLOB NOT NULL,
Name TINYBLOB NOT NULL,
Type BINARY(1) NOT NULL,
Level BINARY(1) NOT NULL,
- ClientId INTEGER DEFAULT 0 REFERENCES Client(ClientId),
+ ClientId INTEGER UNSIGNED DEFAULT 0 REFERENCES Client(ClientId),
JobStatus BINARY(1) NOT NULL,
SchedTime DATETIME DEFAULT NULL,
StartTime DATETIME DEFAULT NULL,
@@ -121,9 +116,9 @@
ReadBytes BIGINT UNSIGNED DEFAULT 0,
JobErrors INTEGER UNSIGNED DEFAULT 0,
JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
- PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool(PoolId),
- FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet(FileSetId),
- PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job(JobId),
+ 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,
@@ -134,6 +129,63 @@
INDEX (JobTDate)
);
+-- We strongly recommend to avoid the temptation to add new indexes.
+-- In general, these will cause very significant performance
+-- problems in other areas. A better approach is to carefully check
+-- that all your memory configuration parameters are
+-- suitable for the size of your installation. If you backup
+-- millions of files, you need to adapt the database memory
+-- configuration parameters concerning sorting, joining and global
+-- memory. By default, sort and join parameters are very small
+-- (sometimes 8Kb), and having sufficient memory specified by those
+-- parameters is extremely important to run fast.
+
+-- In File table
+-- FileIndex is 0 for FT_DELETED files
+-- Name is '' for directories
+-- The index INDEX (PathId, JobId, FileIndex) is
+-- important for bvfs performance, especially
+-- for .bvfs_lsdirs which is used by bareos-webui.
+CREATE TABLE File (
+ FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ FileIndex INTEGER UNSIGNED DEFAULT 0,
+ JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
+ PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
+ DeltaSeq SMALLINT UNSIGNED DEFAULT 0,
+ MarkId INTEGER UNSIGNED DEFAULT 0,
+ Fhinfo NUMERIC(20) DEFAULT 0,
+ Fhnode NUMERIC(20) DEFAULT 0,
+ LStat TINYBLOB NOT NULL,
+ MD5 TINYBLOB NOT NULL,
+ Name BLOB NOT NULL,
+ PRIMARY KEY (FileId),
+ INDEX (JobId, PathId, Name(255)),
+ INDEX (PathId, JobId, FileIndex)
+);
+
+--
+-- Possibly add one or more of the following indexes
+-- to the above File table if your Verifies are
+-- too slow, but they can slow down backups.
+--
+-- INDEX (PathId),
+
+CREATE TABLE RestoreObject (
+ RestoreObjectId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+ ObjectName BLOB NOT NULL,
+ RestoreObject LONGBLOB NOT NULL,
+ PluginName TINYBLOB NOT NULL,
+ ObjectLength INTEGER DEFAULT 0,
+ ObjectFullLength INTEGER DEFAULT 0,
+ ObjectIndex INTEGER DEFAULT 0,
+ ObjectType INTEGER DEFAULT 0,
+ FileIndex INTEGER UNSIGNED DEFAULT 0,
+ JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
+ ObjectCompression INTEGER DEFAULT 0,
+ PRIMARY KEY(RestoreObjectId),
+ INDEX (JobId)
+);
+
-- Create a table like Job for long term statistics
CREATE TABLE JobHisto (
JobId INTEGER UNSIGNED NOT NULL,
@@ -175,50 +227,13 @@
PRIMARY KEY(LocationId)
);
-CREATE TABLE LocationLog (
- LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- Date DATETIME DEFAULT NULL,
- Comment BLOB NOT NULL,
- 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,
- PRIMARY KEY(LocLogId)
-);
-
-CREATE TABLE FileSet (
- FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- FileSet TINYBLOB NOT NULL,
- MD5 TINYBLOB,
- CreateTime DATETIME DEFAULT NULL,
- FileSetText BLOB NOT NULL,
- PRIMARY KEY(FileSetId)
-);
-
-CREATE TABLE JobMedia (
- JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
- MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
- FirstIndex INTEGER UNSIGNED DEFAULT 0,
- LastIndex INTEGER UNSIGNED DEFAULT 0,
- StartFile INTEGER UNSIGNED DEFAULT 0,
- EndFile INTEGER UNSIGNED DEFAULT 0,
- StartBlock INTEGER UNSIGNED DEFAULT 0,
- EndBlock INTEGER UNSIGNED DEFAULT 0,
- JobBytes NUMERIC(20) DEFAULT 0,
- VolIndex INTEGER UNSIGNED DEFAULT 0,
- PRIMARY KEY(JobMediaId),
- INDEX (JobId, MediaId)
-);
-
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),
MediaType TINYBLOB NOT NULL,
- MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType(MediaTypeId),
+ MediaTypeId INTEGER UNSIGNED DEFAULT 0,
LabelType TINYINT DEFAULT 0,
FirstWritten DATETIME DEFAULT NULL,
LastWritten DATETIME DEFAULT NULL,
@@ -244,19 +259,19 @@
MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
InChanger TINYINT DEFAULT 0,
StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage(StorageId),
- DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device(DeviceId),
+ 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),
+ LocationId INTEGER UNSIGNED DEFAULT 0,
RecycleCount INTEGER UNSIGNED DEFAULT 0,
MinBlockSize INTEGER UNSIGNED DEFAULT 0,
MaxBlockSize INTEGER UNSIGNED DEFAULT 0,
InitialWrite DATETIME DEFAULT NULL,
- ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool(PoolId),
- RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool(PoolId),
+ ScratchPoolId INTEGER UNSIGNED DEFAULT 0,
+ RecyclePoolId INTEGER UNSIGNED DEFAULT 0,
EncryptionKey TINYBLOB,
Comment BLOB,
PRIMARY KEY(MediaId),
@@ -264,47 +279,32 @@
INDEX (PoolId)
);
-CREATE TABLE Pool (
- PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- Name TINYBLOB NOT NULL,
- NumVols INTEGER UNSIGNED DEFAULT 0,
- MaxVols INTEGER UNSIGNED DEFAULT 0,
- UseOnce TINYINT DEFAULT 0,
- UseCatalog TINYINT DEFAULT 0,
- AcceptAnyVolume TINYINT DEFAULT 0,
- VolRetention BIGINT UNSIGNED DEFAULT 0,
- VolUseDuration BIGINT UNSIGNED DEFAULT 0,
- MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
- MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
- MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
- AutoPrune TINYINT DEFAULT 0,
- Recycle TINYINT DEFAULT 0,
- ActionOnPurge TINYINT DEFAULT 0,
- PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
- 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,
- MinBlockSize INTEGER UNSIGNED DEFAULT 0,
- MaxBlockSize INTEGER UNSIGNED DEFAULT 0,
- MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
- MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
- MigrationTime BIGINT UNSIGNED DEFAULT 0,
- UNIQUE (Name(128)),
- PRIMARY KEY (PoolId)
+CREATE TABLE JobMedia (
+ JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+ JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
+ MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
+ FirstIndex INTEGER UNSIGNED DEFAULT 0,
+ LastIndex INTEGER UNSIGNED DEFAULT 0,
+ StartFile INTEGER UNSIGNED DEFAULT 0,
+ EndFile INTEGER UNSIGNED DEFAULT 0,
+ StartBlock INTEGER UNSIGNED DEFAULT 0,
+ EndBlock INTEGER UNSIGNED DEFAULT 0,
+ JobBytes NUMERIC(20) DEFAULT 0,
+ VolIndex INTEGER UNSIGNED DEFAULT 0,
+ PRIMARY KEY(JobMediaId),
+ INDEX (JobId, MediaId)
);
-CREATE TABLE Client (
- ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- Name TINYBLOB NOT NULL,
- Uname TINYBLOB NOT NULL, /* full uname -a of client */
- AutoPrune TINYINT DEFAULT 0,
- FileRetention BIGINT UNSIGNED DEFAULT 0,
- JobRetention BIGINT UNSIGNED DEFAULT 0,
- UNIQUE (Name(128)),
- PRIMARY KEY(ClientId)
+CREATE TABLE LocationLog (
+ LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+ Date DATETIME DEFAULT NULL,
+ Comment BLOB NOT NULL,
+ 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,
+ PRIMARY KEY(LocLogId)
);
CREATE TABLE Log (
@@ -318,9 +318,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 REFERENCES Job(JobId),
+ FileId BIGINT UNSIGNED NOT NULL REFERENCES File(FileId),
FileIndex INTEGER UNSIGNED,
PRIMARY KEY(BaseId)
);
@@ -360,7 +360,7 @@
);
CREATE INDEX pathhierarchy_ppathid
- ON PathHierarchy (PPathId);
+ ON PathHierarchy (PPathId);
CREATE TABLE PathVisibility
(
@@ -376,14 +376,14 @@
);
CREATE TABLE Quota (
- ClientId INTEGER DEFAULT 0 REFERENCES Client,
+ ClientId INTEGER UNSIGNED DEFAULT 0 REFERENCES Client(ClientId),
GraceTime BIGINT DEFAULT 0,
QuotaLimit BIGINT UNSIGNED DEFAULT 0,
PRIMARY KEY (ClientId)
);
CREATE TABLE NDMPLevelMap (
- ClientId INTEGER DEFAULT 0 REFERENCES Client(ClientId),
+ ClientId INTEGER UNSIGNED DEFAULT 0 REFERENCES Client(ClientId),
FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet(FileSetId),
FileSystem TINYBLOB NOT NULL,
DumpLevel INTEGER NOT NULL,
