Re: [Bacula-devel] MySQL table 'batch' goes missing

[Eric B. <er...@eb...>]

Hi,

On Sunday 06 January 2008 18:33:58 Kern Sibbald wrote:
> On Sunday 06 January 2008 13:31, Michel Meyers wrote:
> > Hello,
> >
> > I just had a job bail out with this message:
> >
> > 06-Jan 13:09 server-dir JobId 84: Fatal error: sql_create.c:732
> > sql_create.c:732 insert INSERT INTO batch VALUES
> > (578407,84,'/path/','filename','P4A ORB4 IGk B h h A Byp BAA Q BHf8G2
> > BHQexl BHfb1A A A E','oJKoU70RjrXaw
> > UdZeOLFQ') failed:
> > Table 'bacula.batch' doesn't exist
> >
> > A short investigation shows that this happens due to the MySQL
> > connection timing out and the table subsequently getting dropped.
> >
> > My question is: Wouldn't it be better to create a permanent table and
> > just drop it when done? If multiple of these are needed concurrently,
> > they could be created with a unique name, like bacula.batch-$jobid and
> > then maybe add some cleanup code that purges any bacula.batch* tables
> > upon director startup.
> >
> > This would make Bacula resilient to MySQL timeouts and avoid forcing
> > users to change their global MySQL config. (e.g. I don't mind granting
> > the Bacula user rights to create tables, I DO mind changing the MySQL
> > timeout for everybody.)
>
> I don't consider the above to be a Bacula bug, but rather a MySQL bug or a
> configuration problem.  Bacula assumes that none of the connections to
> database will timeout regardless of the idle time, and if they timeout,
> Bacula will most likely fail.
>
> I'm not convinced that using a non-temporary table would help since if the
> connection is dropped, on the next access, it will likely break, unless the
> client libraries automatically reconnect.  Yes, the name must be unique
> because there can be one table open for each job.
>
> I'm not sure why Eric suggested opening a bug, perhaps because we could
> pragmatically set the timeout very large.

I want to keep a trace of this problem somewhere, this is a very frequent
problem on user list. If we could find a solution, it will be great.

I think that we can set timeout values to 0 (or something like 8 days), and
this problem will disappear.

Bye

> Another alternative is to turn off batch inserts, or possibly to switch to
> PostgreSQL where there have never been any silly default timeouts that I am
> aware of.
>
> Regards,
>
> Kern