On Thursday 06 September 2007 20:20, Dan Langille wrote: > On 6 Sep 2007 at 20:16, Kern Sibbald wrote: > > On Thursday 06 September 2007 19:38, Dan Langille wrote: > > > The latest versions of Bacula (>= 2.2.0, I think) require a newer > > > version of MySQL. We are now using nested selects, which older > > > versions of MySQL do not have. I think the minimum requirement is > > > MySQL 4.1 > > > > > > This is the second instances of this problem that I have noticed. > > > > > > Things we should do: > > > > > > 1 - Put out an announcement notifying people of this requirement. > > > 2 - Update ReleaseNotes > > > > > > Things we could also do: > > > > > > 3 - Put checks in bacula-dir to check for database version >= that > > > required. > > > > > > > > > I can do #2 right away. > > I'll be doing that on the Branch-2.2... right?
I think it is better to do it on the trunk where we can test it before releasing. When it is well tested, either we will release the trunk or we can merge it back into the Branch. I'm planning on making a release of 3.0.0 very soon. > > > I assume you mean check for MySQL version. The term database version > > usually refers to Bacula's internal table format. > > Yes. > > > > Kern: can you do #1? > > > > Yes > > > > > #3 would be a nice to have, and would help reduce the number of > > > support calls. I don't think it's a big priority. > > > > Can you tell me where the nested selects are, and are you sure that > > MySQL 4.1 is the first version that supports them? > > No, I'm not sure where they are, but the queries below will help > locate them. >From seeing the failed insert below, I would guess it is in the batch insert code, so another thing you might want to try for older MySQL versions is to build Bacula with batch insert disabled. Most likely (with the possible exception of some bat code), it should then work correctly. I'll leave it to you to test, but it would be very interesting to know before making any announcement. Some people may have problems upgrading MySQL so this can be a workaround. Regards, Kern > > > I have no problem with requiring 4.1 as the any 3.x is *very* old. > > I concluded 4.1 after talking with some folks on IRC in #mysql on the > FreeNode network. Should be accurate, but no promises. > > > > ------- Forwarded message follows ------- > > > From: "Dan Langille" <[EMAIL PROTECTED]> > > > To: "Jody McDonnell" <[EMAIL PROTECTED]> > > > Date sent: Thu, 06 Sep 2007 13:27:30 -0400 > > > Priority: normal > > > Copies to: [EMAIL PROTECTED] > > > Subject: Re: [Bacula-users] Post-upgrade database issues, > > > Bacula 1.38.11 to 2.2.1 on Solaris > > > > > > [ Double-click this line for list subscription options ] > > > > > > 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 > > > [EMAIL PROTECTED] > > > https://lists.sourceforge.net/lists/listinfo/bacula-users > > > > > > > > > ------- End of forwarded message ------- ------------------------------------------------------------------------- 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-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/bacula-devel
