On 6 Sep 2007 at 13:18, Jody McDonnell wrote: > Hi all, > > I spent the day yesterday upgrading the Bacula install on our Solaris > servers from 1.38.11 to 2.2.1. Things initially seemed to work, but > the overnight incremental backup failed with some database issues. > > I should note that I am using MySQL 4.0.18 with Bacula. I've been > using this version since I started using Bacula three years ago with > version 1.34.2. I haven't upgraded our version of MySQL, mainly > because I wasn't exactly sure of the steps required to upgrade MySQL > without risking data loss. > > The only database-related maintenance I performed before the upgrade > was to compact the MySQL database via the mysqldump utility, as per > section 21.2 of the Bacula manual. > > After building and installing Bacula 2.2.1, I ran the > "update_mysql_tables_9_to_10" script to update my database tables from > 1.38-era to 2.0-era. > > I seem to have encountered two issues. The first seems to be related > to the fact that my initial configuration script included the > "--enable-batch-insert" directive. The nightly incremental backup > failed with the following error messages: > > ============================== > 06-Sep 09:39 kodos-dir: BackupKang.2007-09-06_01.05.00 Fatal error: > sql_create.c:730 sql_create.c:730 insert INSERT INTO batch VALUES > (5432,2083,'/repo/cvsroot/est/build/','est.tar.gz,v','gAAW C2IJ IFt B > Bw B A 3ezOL CAA G8Ew BG3vmb BG3uk8 BG3uk9 A A > C','oqzs5tC+gpBJ0EESyZoa7A') failed: > Table 'bacula.batch' doesn't exist > 06-Sep 09:39 kodos-dir: sql_create.c:730 INSERT INTO batch VALUES > (5432,2083,'/repo/cvsroot/est/build/','est.tar.gz,v','gAAW C2IJ IFt B > Bw B A 3ezOL CAA G8Ew BG3vmb BG3uk8 BG3uk9 A A > C','oqzs5tC+gpBJ0EESyZoa7A') > 06-Sep 09:39 kodos-dir: BackupKang.2007-09-06_01.05.00 Fatal error: > catreq.c:478 Attribute create error. sql_find.c:333 Request for Volume > item 1 greater than max 0 or less than 1 > [...etc...] > ============================== > > There was a long pause in the backup while Bacula requested that a new > tape be mounted. From searching through past posts to this list, it > sounds like this is due to a timeout by MySQL, and I think that it > might be possible to get around the issue by changing the wait_timeout > value in my.cnf to some really large value. > > Since I only have a single-tape DAT drive, backups can sometimes be > stalled over an entire weekend waiting for a new tape to be mounted. > Since I didn't really want to change the wait_timeout to be some huge > number like 4 days, I decided to recompile Bacula 2.2.1 without the > "--enable-batch-insert" directive. I hadn't previously noticed any > database performance issues, anyway. > > My second attempt at an incremental backup failed with the following > error message: > > ============================== > 06-Sep 12:15 kodos-dir: BackupKang.2007-09-06_11.50.55 Fatal error: > Can't fill Path table Query failed: INSERT INTO Path (Path) > SELECT a.Path FROM (SELECT DISTINCT Path FROM > batch) AS a WHERE NOT EXISTS (SELECT Path > FROM Path AS p WHERE p.Path = a.Path) > : ERR=You have an error in your SQL syntax. Check the manual that > corresponds to your MySQL server version for the right syntax to use > near 'SELECT DISTINCT Path FROM batch) AS a WHE > ============================== > > This error, I believe, might be due to my rather old version of MySQL.
I suspect this may be nested selects, which may not be available in your older MySQL. > My current plan is to try the following: > - dump the Bacula data via mysqldump > - upgrade MySQL to something more recent (MySQL 5, I guess) > - run drop_bacula_tables and make_bacula_tables > - read the Bacula data file back in via mysql > > If anyone has any advice, feedback or suggestions, I would appreciate it. That sounds OK to me. -- Dan Langille - http://www.langille.org/ Available for hire: http://www.freebsddiary.org/dan_langille.php ------------------------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
