Hi
All,
I just
migrated from SQLite to MySQL with my Bacula setup. And I thought you might
value some end user feedback. Things to note, the docmentation is a
bit deficient (actually missleading) concerning such migrations. It might be a
good Idea to update the Documentation to avoid leading
people astray. From the Documentation:
<blockquote>
You may begin using
Bacula with SQLite then later find that you want to switch to MySQL for any of a
number of reasons: SQLite tends to use more disk than MySQL, SQLite apparently
does not handle database sizes greater than 2GBytes, ... Several users have done
so by first producing an ASCII "dump" of the SQLite database, then creating the
MySQL tables with the create_mysql_tables script that comes with Bacula,
and finally feeding the SQLite dump into MySQL using the -f command line
option to continue past the errors that are generated by the DDL statements that
SQLite's dump creates. Of course, you could edit the dump and remove the
offending statements. Otherwise, MySQL accepts the SQL produced by
SQLite.
</blockquote>
Following these directions resulted in a MySQL
database in which PoolId's were incorrect and several other inconsistancies. I
then found the following message in the mailing list
archives.
which had the following script attatched to
it:
<shellscript>
#! /bin/sh
#
# Convert a Bacula 1.31a (1.32 should also be ok) Sqlite database to MySQL
#
# The MySQL database MUST be setup with the proper schema first.
#
# Written by Nic Bellamy <[EMAIL PROTECTED]>, Sept/Oct 2003.
#
# Convert a Bacula 1.31a (1.32 should also be ok) Sqlite database to MySQL
#
# The MySQL database MUST be setup with the proper schema first.
#
# Written by Nic Bellamy <[EMAIL PROTECTED]>, Sept/Oct 2003.
echo .dump |
sqlite bacula.db |
awk '/^INSERT INTO / && $3 != "NextId" && $3 != "Version" { print $0 }' |
sed 's/^INSERT INTO Media /INSERT INTO Media (MediaId,VolumeName,Slot,PoolId,MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,VolCapacityBytes,VolStatus,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes) /; /INSERT INTO File VALUES\(.*,NULL\)/d' |
mysql -f bacula
sqlite bacula.db |
awk '/^INSERT INTO / && $3 != "NextId" && $3 != "Version" { print $0 }' |
sed 's/^INSERT INTO Media /INSERT INTO Media (MediaId,VolumeName,Slot,PoolId,MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,VolCapacityBytes,VolStatus,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes) /; /INSERT INTO File VALUES\(.*,NULL\)/d' |
mysql -f bacula
</shellscript>
This script
did a better job, but failed to insert my volumes properly. The SQL statements
produced where:
INSERT INTO
Media ('Bunch of Field Names') VALUES('Bunch of Values');
The problem
was that there were more values in VALUES('Bunch of
Values') then there were Field Names specified. Deleting the Field
specifications and just doing the following fixed that
problem:
INSERT INTO
Media VALUES('Bunch of Values');
Also Restore
Jobs were not inserted because both the PoolId and the FileSetId were 'NULL' and
they needed to be '0' as the MySQL tables disallowed null values. So the SQL
had
to be edited
to replace the 'NULL' Values.
So clearly
the above script needs some work, but it is servicable, and there should be some
reference to guide people in the documentation.
Cheers,
Silas
Bennett
=0)
