Problem retrieve a longblob from a mysql db

ROOT
1

Dear all,

I am a bit stuck with a problem retrieving blob from a mysql database.

I attached a script which put data (~68kB) in a db called “AMORE”, specifically in the table called aloneldcped :

mysql> desc aloneldcped; +------------+-----------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-----------+------+-----+-------------------+-------+ | moname | char(64) | NO | PRI | | | | updatetime | timestamp | NO | | CURRENT_TIMESTAMP | | | data | longblob | YES | | NULL | | +------------+-----------+------+-----+-------------------+-------+ 3 rows in set (0.06 sec)

Then the script tries to retrieve this blob. However the GetBinary methods fails :

root [8] .x testDB.C Error in <TMySQLStatement::GetBinary>: Code: -1 Msg: Cannot get statement parameters

If I decrease a bit the size of the data (change from 1350 lines to 1300) then it works seamlessly.

As it is a longblob, data bigger than 65kB shouldn’t be a problem to be retrieved.
Do I make a mistake ?
Or is it a bug ?

Thanks in advance,

Barth
testDB.C (1.33 KB)

2

Before calling TMySQLStatement::GetBinary, Amore calls TMySQLStatement::StoreResult() in which TMySQLStatement::SetSQLParamType(Int_t npar, int sqltype, bool sig, int sqlsize) is called.
There is a “switch(sqltype)” which treats MYSQL_TYPE_LONG_BLOB as MYSQL_TYPE_BLOB.
This switch sets the value of allocsize :
case MYSQL_TYPE_BLOB : allocsize = sqlsize >= 65525 ? sqlsize + 10 : 65535; break;
The problem is that the value used when SetSQLParamType is called is a “long unsigned” and not an “int”. So sqlsize is not 4,294,967,295 but -1. Therefore when one gets a long blob object, one allocates 64kB instead of 4GB ! So a long blob is treated like a simple blob which is the cause of the error.

I would propose to change the type of sqlsize from int to long unsigned and to modify the line 748 as follows :

case MYSQL_TYPE_BLOB : allocsize = sqlsize >= 65525 ? 1073741823 : 65535; break;

I have tested it and it seems to work (the big value is 1GB which is the biggest value I can use without error at execution).

What does the Root team think about this proposition ?
Best regards,

         Guillaume
3

Hi,

Indeed, what Guillaume says is correct. If we load a data bigger than 65k then there is a bug in TMysqlStatement::SetSQLParamType() :

Bool_t TMySQLStatement::SetSQLParamType(Int_t npar, int sqltype, bool sig, int sqlsize) // wrong type for sqlsize leading to an overflow { // SNIP case MYSQL_TYPE_LONG_BLOB: case MYSQL_TYPE_BLOB: allocsize = sqlsize >= 65525 ? sqlsize + 10 : 65535; // if there is the overflow, then sqlsize == -1 and allocsize == 65535 which is too small break;

If you check sqlsize in TMysqlStatement::SetSQLParamType() when executing the macro I previously attached, you will see that sqlsize=-1 and not 2^32, as it should, which leads to a wrong allocsize value of 65535.
This is due to an overflow coming from a wrong type of parameter (int instead of long unsigned).

Can a ROOT developer confirm our explanation ?

EDIT : sorry for this post which is a bit a duplicate of guillaume’s one. The first version of Guillaume’s post was shorter and I tried to fill some holes

4

Hi,

Which version of ROOT are you using?

Cheers,
Philippe.

5

I use 5.21/06 and Guillaume uses 5.22.

I use SLC4 and I don’t know what Guillaume uses as OS.

6

Hi,
As Barthélémy told, I am using Root v5.22/00 and SLC4.5.
Cheers,

Guillaume

7

Hi all

There is additional argument in TSQLStatement::SetBinary() method,
which defines maximum allowed size of binary data to be set.
By default this limit is 4K and thus it forces usage of normal BLOB type.

You should set bigger value in your code:

...
statement->SetBinary(2, message.Buffer(), message.Length() + sizeof(UInt_t), 0x100000);
...

Hope, this helps.

Regards,
Sergey

8

Hi,

The problem we think to have identified is in TMySQLStatement::SetSQLParamType called by StoreResult(). We never actually call SetBinary(…).

Philippe, have you been able to reproduce the problem ? what do you think ?

Cheers,
Barth

9

Hi, Barth

First of all, SetBinary is used in your TestDB.C script, line 17.
Without setting correct maximum size of binary field
you are not able to submit data bigger than 64K.
This was your first message in the thread.

That you are proposing is to allocatate 1GB of internal buffer for any binary field. If you have several of them, you will run out of memory.
Can you check actual value of sqlsize in SetSQLParamType after you modify call of SetBinary? As I understood, you getting -1 in current version?

I see your arguments about int and unsigned long in SetSQLParamType.
I will correct this soon.

Regards,
Sergey

10

Hi,

Sorry, I was focused on the retrieval of data, but I now understand what you mean.

I just tried to run the macro again with a bigger max value :

statement->SetBinary(2, message.Buffer(), message.Length() + sizeof(UInt_t), 0x100000);
and I got the same sqlsize = -1 and the error message

I can see that the data are properly set into the database, isn’t that the proof that SetBinary is working properly ?

For the proposal of Guillaume, I don’t know if it is a good idea or not. I let the ROOT (dream?) team solve the problem. :slight_smile:

Cheers

11

Hi, Barth

Can you put debug output in TMySQLStatement::StoreResult() method, line 188 (before calling of SetSQLParamType):

         Info("StoreResult","SetSQLParamType name %s length %lu max_length %lu", fields[n].name, fields[n].length, fields[n].max_length);

I would like to understand if one cat get real maximum size of column
in table instead of 4Gb, which is possible for longblob.

Regards, Sergey

12

Hi,

I get

Regards,
Barth

13

Hi, Barth

For time beeing I recommend to modify code as follows:

case MYSQL_TYPE_BLOB : allocsize = sqlsize >= 65525 ? 0x8000000 : 65535; break;

I guess, you have no data bigger than 128 Mb.

You can also use root svn repository - I submit patch, where this limit can be defined via static method TMySQLStatement::SetAllocSizeLimit(unsigned long sz). Default value is 128 Mb. I also put unsigned long in several methods to correctly treat size values. In most cases it should be enough.

But this is just workaround. To solve problem in general, one should be able to retrieve binary fields in portions, which is not supported by current
TSQLStatetment class interface.

Regards,
Sergey

14

Ok, thanks for the workaround. It solves our problem.

Cheers,
Barth