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
