RE: [Bacula-users] Migration from SQLite to MySQL.

[Bennett, Silas (GE Indust, Security)]

Hi Kern,

Idon't 
have enough knowledge of the intricacies of SQLite or MySQL to propose a proper 
migration solution. But the docs should be 
amended so as not to imply that it is trivial. 
The following (Inline  Attached)should work for people running 
1.36.2. It should, if not just work, give people running other versions a point 
in the right direction.

Cheers,
Silas 
Bennett

=0)

From: http://www.bacula.org/rel-manual/Catalog_Maintenance.html#SECTION000239000

ORIGINAL:

H2A 
NAME="SECTION000239000"
Migrating from SQLite to 
MySQL/A
/H2
A 
NAME="13020"/A
A 
NAME="13021"/A
A 
NAME="13024"/A
P
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 Bcreate_mysql_tables/B script that comes with 
Bacula, and finally feeding the
SQLite dump into MySQL using the 
B-f/B 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. 
P

MODIFIED:

H2A 
NAME="SECTION000239000"Migrating from SQLite to 
MySQL/A/H2A NAME="13020"/AA 
NAME="13021"/AA 
NAME="13024"/APYou may begin using Bacula with 
SQLite then later find that you want to switchto MySQL for any of a number 
of reasons: SQLite tends to use more disk thanMySQL, SQLite apparently does 
not handle database sizes greater than 2GBytes,... Several users have done 
so by the following proceduer:br/br/nbsp 
strong1.)/strong First Use the scripts distributed with bacula 
to create the MySQL database, and tables. br/nbsp 
strong2.)/strong Next, produce an ASCII "dump" of the SQLite 
database using the following: 
br/blockquotestrongecho ".dump" | 
/usr/bin/sqlite bacula.db  bacula-sqlite.sql 
/strongbr//blockquotenbsp 
strong3.)/strong SQLite will produce some SQL that MySQL will 
not interpret correctly. The followinga 
href="" will fix 
mostof these issues, but it might need to be modified slightly for use with 
other versions of bacula: 
br/br/strong#60code#62blockquote#! 
/bin/sh br/# br/# bacula-sqlite_2_mysqldump.sh 
br/# br/# Convert a Bacula 1.36.2 Sqlite database to 
MySQL br/# Originally Written by Nic Bellamy [EMAIL PROTECTED], Sept/Oct 2003. 
br/# Modified by Silas Bennett silas.bennett_AT_ge.com, 
April 2006 for use with Bacula 1.36.2 br/# 
br/br/if [ $1 == '-h' ] || [ $1 == '--help' ] ; then 
br/nbspnbspnbspnbsp echo `basename "$0"`" 
Usage:" br/nbspnbspnbspnbsp echo " "`basename 
$0`" takes a ASCII bacula sqlite database dump as an argument," 
br/nbspnbspnbspnbsp echo " and writes an SQL 
dump suitable for use with MySQL to STDOUT." 
br/nbspnbspnbspnbsp echo 
br/nbspnbspnbspnbsp echo " Example Use: 
"`basename $0`" bacula.sqlite.sql  bacula.mysql.sql" 
br/nbspnbspnbspnbsp echo " Example Use: "cat 
bacula.sqlite.sql | `basename $0`" - | mysql -p -u user baculadb" 
br/nbspnbspnbspnbsp exit br/fi 
br/br/br/# If $1 is '-' then cat will read 
/dev/stdin br/cat $1 | br/awk '/^INSERT INTO / 
 $3 != "NextId"  $3 != "Version" { print $0 }' | 
br/sed '/^INSERT INTO [a-zA-Z]* VALUES(/s/(NULL)/(0)/g ; /^INSERT 
INTO [a-zA-Z]* VALUES(/s/(NULL,/(0,/g ; /^INSERT INTO [a-zA-Z]* 
VALUES(/s/,NULL,/,0,/g ; /^INSERT INTO [a-zA-Z]* VALUES(/s/,NULL,/,0,/g ; 
/^INSERT INTO [a-zA-Z]* VALUES(/s/,NULL)/,0)/g' 
br//blockquote#60/code#62/strongbr/br/nbsp 
strong4.)/strong Next feed the SQL dump to MySQL: 
br/blockquotestrongmysql -p -u root baculadb 
bacula.mysql.sql br//strong/blockquoteNote you 
could do steps 2-4 in one shot using pipes. But if you are not using bacula 
1.36.2 it would be a good idea to do this in steps storing each step to 
file.The reason for this is that if your database is large the dumps, and 
conversions will take quite a while, and if you need to make modifications to 
the conversion scriptthen not having to do the sqlite dump repeatedly is 
conveniant. If you would like to do it all in one shot use the following 
oneliner:br/blockquotestrongecho ".dump" | 
/usr/bin/sqlite bacula.db | bacula-sqlite_2_mysqldump.sh - | mysql -p -u 
user baculadb br//strong/blockquote 
br/br/P




  -Original Message-----From: 
  [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]On Behalf Of Bennett, 
  Silas (GE Indust, Security)Sent: Thursday, April 13, 2006 7:03 
  PMTo: bacula-users@lists.sourceforge.netSubject: 
  [Bacula-users] Migration from SQLite to MySQL.
  Hi 
  All,
  
  I 
  just migrated 

[Bacula-users] Migration from SQLite to MySQL.

[Bennett, Silas (GE Indust, Security)]

Hi 
All,

I just 
migrated from SQLite to MySQL with my Bacula setup. And I thought you might 
valuesome 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 
peopleastray.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.

http://article.gmane.org/gmane.comp.bacula.user/2142/match=bacula+sqlite+mysql+script

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.

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


/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 thatthere weremore 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)