Analysis by Marc and Eric indicates that you may be suffering from a rather
arbitrary default time limit that MySQL imposes on locks, which is rather
absurd.
===
>From Marc:
innodb_lock_wait_timeout=50
Timeout in seconds an InnoDB transaction may wait for a lock before being
rolled back. InnoDB automatically detects transaction deadlocks in its own
lock table and rolls back the transaction. If you use LOCK TABLES command, or
other transaction-safe table handlers than InnoDB in the same transaction,
then a deadlock may arise which InnoDB cannot notice. In cases like this the
timeout is useful to resolve the situation.
...
Anyhow, I guess the only solution is to ask the user to raise this value...
It would have been easy to circumvent this problem if we could set this
parameter at session level, ...
===
Regards,
Kern
On Monday 24 September 2007 22:19, Ondrej PLANKA wrote:
> Hi Kern,
>
> Kern Sibbald napsal(a):
> > On Monday 24 September 2007 15:58, Arno Lehmann wrote:
> >> Hi,
> >>
> >> 24.09.2007 14:53,, Kern Sibbald wrote::
> >>> Hello,
> >>>
> >>> On Monday 24 September 2007 13:57, Chris Howells wrote:
> >>>> Kern Sibbald wrote:
> >>>>> 4. The errors you guys are seeing is because batch insert is turned
> >>>>> on
> >>
> >> Again, it may be turned on, but the config.out told me it was off.
> >> This might be a misunderstanding on my part, as I don't know if
> >> FreeBSDs ports mechanism leaves the config.log lying around, so it
> >> might be an artifact of an earlier install.
> >>
> >>>>> but something went wrong in creating the batch table. I suspect that
> >>>>> you have not setup the correct permissions on MySQL that allows the
> >>>>> user you are running with (normally "bacula") to create tables.
> >>>>> However, if that is the case, there *should* be an error message that
> >>>>> is printed -- I don't imagine that error path has been tested though.
> >>>>> Something to be investigated more.
> >>>>
> >>>> I had a problem with bacula backups failing with a similar error. I
> >>>> eventually debugged it to be a problem with MySQL not allowing the
> >>>> creation of temporary tables. So 'CREATE TABLE blah' worked, but
> >>>> 'CREATE TEMPORARY TABLE blah' didn't. Very weird I thought!
> >>>
> >>> Yes, more fine grained than I would have expected.
> >>
> >> In my case - or rather, my customers - I just ensured that I could do
> >> the following:
> >>
> >> lehmann$xxx [~] mysql -u bacula -p bacula
> >> Enter password:
> >> Reading table information for completion of table and column names
> >> You can turn off this feature to get a quicker startup with -A
> >>
> >> Welcome to the MySQL monitor. Commands end with ; or \g.
> >> Your MySQL connection id is 160929
> >> Server version: 5.1.21-beta FreeBSD port: mysql-server-5.1.21
> >>
> >> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> >>
> >> mysql> create temporary table test_xxx(id int unsigned);
> >> Query OK, 0 rows affected (0.00 sec)
> >>
> >> mysql> quit
> >> Bye
> >> lehmann$xxx [~]
> >>
> >>
> >> Which seems a good indication that the bacula user may create
> >> temporary tables.
> >>
> >> ...
> >>
> >>> This pretty much confirms that most of the problems are probably due to
> >>> MySQL (or OS) misconfiguration.
> >>
> >> Well, not in this case :-(
> >>
> >>> I'm still concerned about the lack of decent
> >>> error messages for the create temp table failure, which appears to be a
> >>> Bacula problem ...
> >>
> >> Could it be this function:
> >>
> >> bool my_batch_start(JCR *jcr, B_DB *mdb)
> >> {
> >> bool ok;
> >>
> >> db_lock(mdb);
> >> ok = db_sql_query(mdb,
> >> " CREATE TEMPORARY TABLE batch "
> >> " (fileindex integer, "
> >> " jobid integer, "
> >> " path blob, "
> >> " name blob, "
> >> " lstat tinyblob, "
> >> " md5 tinyblob) ",NULL, NULL);
> >> db_unlock(mdb);
> >> return ok;
> >> }
> >> from cats/sql_create.c
> >>
> >> No explicit error checking here. In the places it's called, I see
> >> error checking and I notice that at debug level 100 the creation of
> >> the batch table should be logged.
> >
> > As far as I can tell, the error condition *is* correctly tested and
> > reported. However, since no one reported an error message from the create
> > temp table, I have asked Eric if he could examine the error reporting
> > more carefully.
> >
> > The place where it is reported is in sql_create.c in the subroutine
> > db_create_file_attributes_record(). If the create did not work, the Job
> > should fail.
> >
> > Ah, one minor point I notice when looking at the create -- in general, it
> > is incorrect for Bacula because it uses all lower case character for the
> > column names, while Bacula internal usage is to use both upper and lower
> > characters. For PostgreSQL, this makes no difference i.e. it is case
> > independent, but for a stock MySQL, it will fail, because in general
> > MySQL is case dependent for column names. Depending on your MySQL setup,
> > and the *exact* code that is used in the batch insert, this *could* be a
> > problem.
> >
> > I.e. *normally* in Bacula code fileindex *must* be written as FileIndex,
> > jobid as JobId, path as Path, ... or the query will fail (or in this case
> > queries or inserts against that table).
> >
> > For MySQL, the above command should be:
> >> ok = db_sql_query(mdb,
> >> " CREATE TEMPORARY TABLE batch "
> >> " (FileIndex integer unsigned, "
> >> " JobId integer unsigned, "
> >> " Path blob, "
> >> " Name blob, "
> >> " LStat tinyblob, "
> >> " MD5 tinyblob) ",NULL, NULL);
> >
> > You might try that and see what results you get.
> >
> > In looking back at previous emails, this is very likely to be Ondrej's
> > problem.
>
> It is not the same proble as I reported - I investigated problem with
>
> 24-Sep 17:38 backup-dir: Backup_Client2.2007-09-24_16.40.35 Fatal error:
> Can't fill File table Query failed: INSERT INTO File (FileIndex, JobId,
> PathId, FilenameId, LStat, MD5) SELECT batch.FileIndex, batch.JobId,
> Path.PathId,Filename.FilenameId,batch.LStat, batch.MD5 FROM batch
> JOIN Path ON (batch.Path = Path.Path)
> JOIN Filename ON (batch.Name = Filename.Name)
>
> : ERR=Lock wait timeout exceeded; try restarting transaction
>
> And I think it is other issue then you describe above.
>
> Regards,
>
> Ondrej.
>
> > Regards,
> >
> > Kern
> >
> >> I would thus suggest to run the DIR with logging at level >= 100 and
> >> tracing enabled, and then see if it tries to create the batch table,
> >> and what it reports about it.
> >>
> >> By the way - the code above is from the current svn repository,
> >> version 5635.
> >>
> >> Let's see if we can analyze this...
> >>
> >> Arno
> >>
> >>> Many thanks.
> >>>
> >>> Kern
> >>>
> >>> -----------------------------------------------------------------------
> >>>-- This SF.net email is sponsored by: Microsoft
> >>> Defy all challenges. Microsoft(R) Visual Studio 2005.
> >>> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> >>> _______________________________________________
> >>> Bacula-devel mailing list
> >>> [email protected]
> >>> https://lists.sourceforge.net/lists/listinfo/bacula-devel
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Bacula-devel mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/bacula-devel
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
Bacula-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/bacula-devel
