Errors creating tables with bareos
Christopher Lamitie
I'm doing a new, first time install of Bareos on CentOS7 with MySQL community server 5.7.9. I have been following the tutorial at http://doc.bareos.org/master/html/bareos-manual-main-reference.html#x1-590005
I've run into an issue that's not covered in the tutorial
When I execute the script at /usr/lib/bareos/scripts/make_bareos_tables I get the following output
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.
However I can move on and execute the /usr/lib/bareos/scripts/grant_bareos_privileges script.
I can then start dir, fd and sd daemons and connect to the bconsole. when I run the default job as the tutorial says, it fails. The tutorial says it will fail but as I was looking at the messages I saw many entries such as these:
17-Nov 15:14 bareos-dir-test-dir JobId 0: Fatal error: sql_find.c:375 sql_find.c:375 query SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,EndFile,EndBlock,LabelType,LabelDate,StorageId,Enabled,LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge,EncryptionKey,MinBlocksize,MaxBlocksize FROM Media WHERE PoolId=3 AND MediaType='File' AND Enabled=1 AND VolStatus='Purged' AND Recycle=1 ORDER BY LastWritten ASC,MediaId LIMIT 1 failed:
Table 'bareos.Media' doesn't exist
17-Nov 15:14 bareos-dir-test-dir JobId 0: Fatal error: sql_create.c:100 Create DB Job record INSERT INTO Job (Job,Name,Type,Level,JobStatus,SchedTime,JobTDate,ClientId,Comment) VALUES ('BackupClient1.2015-11-17_15.14.50_04','BackupClient1','B','I','C','2015-11-17 15:14:46',1447794886,1,'') failed. ERR=Table 'bareos.Job' doesn't exist
Did the make table script fail?
Logging into MySQL and showing all the tables in the bareos DB, I have:
mysql> show tables;
+--------------------+
| Tables_in_bareos |
+--------------------+
| BaseFiles |
| Client |
| Counters |
| DeviceStats |
| File |
| Filename |
| JobMedia |
| JobStats |
| Location |
| MediaType |
| NDMPJobEnvironment |
| NDMPLevelMap |
| Path |
| PathHierarchy |
| PathVisibility |
| Pool |
| RestoreObject |
| Status |
| Storage |
| TapeAlerts |
| UnsavedFiles |
| Version |
+--------------------+
Clearly the Media and Jobs tables are missing and I have no idea what other tables may be missing.
What can I do to fix up the MySQL tables? I followed the MySQL instructions at http://doc.bareos.org/master/html/bareos-manual-main-reference.html#x1-400002.4.2 including supplying bareos with a password. All of which worked except for the above falures when running the make_bareos_table script.
Thanks for your help!
Christopher Lamitie
Hmmm. It does seem to be a malformed sql script used in the make_bareos_tables script.
I looked at /usr/lib/bareos/scripts/ddl/creates/mysql.sql file
It does have a 'Job' and 'Media' tables but I found the following in the 'Job' creation section:
"SchedTime DATETIME DEFAULT 0,"
I decided to run the CREATE TABLE Job statement in MySQL in the bareos db and it did kick a invalid default value error for SchedTime.
The mysql.sql file says:
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,
JobStatus BINARY(1) NOT NULL,
SchedTime DATETIME DEFAULT 0,
StartTime DATETIME DEFAULT 0,
EndTime DATETIME DEFAULT 0,
RealEndTime DATETIME DEFAULT 0,
JobTDate BIGINT UNSIGNED DEFAULT 0,
VolSessionId INTEGER UNSIGNED DEFAULT 0,
VolSessionTime INTEGER UNSIGNED DEFAULT 0,
JobFiles INTEGER UNSIGNED DEFAULT 0,
JobBytes BIGINT UNSIGNED DEFAULT 0,
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,
PurgedFiles TINYINT DEFAULT 0,
HasBase TINYINT DEFAULT 0,
HasCache TINYINT DEFAULT 0,
Reviewed TINYINT DEFAULT 0,
Comment BLOB,
PRIMARY KEY(JobId),
INDEX (Name(128))
);
I changed all the datetime references to: DATETIME DEFAULT NOW(),
And that cut out most of the errors and got me most of the tables when I ran the make_tables script again. However there still is another error to resolve:
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
Looking into that issue now.
Stephan Dühr
RHEL7/CentOS7 comes with MariaDB instead of MySQL, this detail is not mentioned
in the Bareos documentation. Using the mariadb packages from CentOS 7 is known
to work fine with Bareos. Looks like the latest MySQL version has changed in a
way that is not compatible with older versions.
Regards,
Stephan
Stephan Dühr stepha...@bareos.com
Bareos GmbH & Co. KG Phone: +49 221-630693-90
http://www.bareos.com
Sitz der Gesellschaft: Köln | Amtsgericht Köln: HRA 29646
Komplementär: Bareos Verwaltungs-GmbH
Geschäftsführer: S. Dühr, M. Außendorf,
J. Steffens, Philipp Storz, M. v. Wieringen
Christopher Lamitie
It was the Quota table creation statement. The mysql.sql script has:
CREATE TABLE Quota (
ClientId INT UNSIGNED DEFAULT NULL,
GraceTime BIGINT DEFAULT 0,
QuotaLimit BIGINT UNSIGNED DEFAULT 0,
PRIMARY KEY (ClientId)
);
But in the version of MySQL I'm running, it doesn't like null primary keys so I changed the default value to 0 and now the make_bareos_tables script runs without complaint.
Christopher Lamitie
> Hello,
>
> RHEL7/CentOS7 comes with MariaDB instead of MySQL, this detail is not mentioned
> in the Bareos documentation. Using the mariadb packages from CentOS 7 is known
> to work fine with Bareos. Looks like the latest MySQL version has changed in a
> way that is not compatible with older versions.
>
> Regards,
>
> Stephan
>
Ah, that's a great point. I was afraid if I used mariadb the set up might not work correctly as the HOWTO refers to MySQL and POSTGRESQL, but does not mention mariadb, so I went and put the MySQL-community repo on my server and installed MySQL that way.
This is a test server I'm working with so perhaps I'll blow the DB away and try again with mariadb. But I think I'll continue testing with MySQL 5.7.9 and see how it runs with the table creation statement changes I made.
Will the documentation update to include mariadb?
Do we know if Bareos will support all three DBs? It stands to reason that as the mariadb fork continues it will eventually differ enough from MySQL that it might become an issue.
Anyway thanks for responding Stephan!
-Chris
Marco van Wieringen
the default behavior of MySQL in 5.7 which mostly renders a lot of
applications useless. I wouldn't be surprised you will run into much
more subtle problems even when you now can create a database now.
When searching for the problem with DATETIME DEFAULT 0 you find that
in 5.7 they changed the so called default session parameters of
a database session to some strange strict value that makes a lot
of stuff all of a sudden not acceptable anymore. It even seems they
changed things multiple times in the 5.7 series so its now such an
enormous mess that most people just disable the strict session
settings to be able to use it (That really shows what a great Job
they have done if people have to disable your great new settings.)
Your change to the Quota table might work but I would propose the
following change:
ClientId INTEGER DEFAULT 0 REFERENCES Client,
On a closing note, from a performance standpoint PostgreSQL always won
hands down so I wonder if MySQL will ever catchup and is also not a
database we do a lot of performance tuning for. So for bigger installs
we always advice to use PostgreSQL in combination with Bareos.
--
Marco van Wieringen marco.van...@bareos.com
Bareos GmbH & Co. KG Phone: +49-221-63069389
Sitz der Gesellschaft: Köln | Amtsgericht Köln: HRA 29646
Komplementär: Bareos Verwaltungs-GmbH
P. Storz, M. v. Wieringen
Christopher Lamitie
Thank you all for helping me.
-Chris
Hosted Power
I wonder, will mysql 5.7 be supported? It has great improvements, especially performance, in my experience!
I tried the changes to the create tables script and it seems to be working (for now).
Kind regards
Jo