Re: [Bacula-devel] [Bacula-users] Proplem upgrading from 1.38 to 2.2

[Jari F. <ja...@ik...>]

> If you either send me an attachment with your "comments"
> or point me to an exact link to the thread, we'll at
> least take a quick look at it for anything that might
> stand out.  Sorry, I no longer have the time to search
> for such things :-(   I think Eric might be interested in
> this too since batch insert is mostly his baby :-) 
> 

--------- sql_create.c -------------------------------------------------

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;
}

------------------------------------------------------------------------

Path and Name are blobs. I'm not very good with MySQL (that means, there are younger guys who have tested everything and are great in what they are doing), but from my experience from mostly Oracle world is that a BLOB is not good at searching and indexing. Sometimes they are implemented as external files to the database, and are used to just store *stuff*.

Still, bacula generates the following SQL: 

    INSERT INTO Filename (Name) SELECT a.Name FROM (SELECT DISTINCT Name FROM batch) AS a WHERE NOT EXISTS (SELECT Name FROM Filename AS f WHERE f.Name = a.Name)

That looks suspicious to me.


------------------------------------------------------------------------

Another suspiciously looking thing is in the same SQL query:

    SELECT a.Name FROM (SELECT DISTINCT Name FROM batch) AS a 

Is actually (to me) same as

    SELECT DISTINCT a.Name FROM batch AS a

------------------------------------------------------------------------

Those things look suspicious to me. I did not try EXPLAIN PLAN for the queries, but there are 2 facts.

1) My system hangs on a 100% processor time by MySQL, and the query is as shown up there
2) My commmon sense with 20 years of SQL and C/C++ says says that the sql clause is suspicious. Double full table query for 1 exact thing.

Regards,
jarif