How to insert a binary data size of 4GB into a longblob column.

I was using unixODBC as the driver manager and mysql ODBC driver to connect
to the mysql server. I am using Linux platform and coding with C++. I had
created a table with a column type of longblob and wish to use ODBC function
call to insert a 4GB of data into the mysql database. At first, I got error
saying that the data size is over the limit of max_allowed_packet size, so I
changed the max_allowed_packet to around 1GB (that is the max
max_allowed_packet variable can take). Then I start to sent a 1GB data to
the mysql database. It will complaint about "Out of memory (Needed
500000048 bytes)". Seems like I could only sent max of 500 MB of data,
otherwise the mysql server will complaint about the above out of memory
problem. Not sure how to get around this problem. Could any help, Thanks
in advance.
--
View this message in context: http://www.nabble.com/How-to-insert-a-binary-data-size-of-4G B-into-a-longblob-column.-tf3885758.html#a11014268
Sent from the MySQL - ODBC mailing list archive at Nabble.com.


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re: How to insert a binary data size of 4GB into a longblob column.

I think it will be difficult. The full blob will be buffered once in the
driver and once in the server.

Jess

On Thu, Jun 07, 2007 at 12:07:09PM -0700, crazyODBC wrote:
>
> I was using unixODBC as the driver manager and mysql ODBC driver to connect
> to the mysql server. I am using Linux platform and coding with C++. I had
> created a table with a column type of longblob and wish to use ODBC function
> call to insert a 4GB of data into the mysql database. At first, I got error
> saying that the data size is over the limit of max_allowed_packet size, so I
> changed the max_allowed_packet to around 1GB (that is the max
> max_allowed_packet variable can take). Then I start to sent a 1GB data to
> the mysql database. It will complaint about "Out of memory (Needed
> 500000048 bytes)". Seems like I could only sent max of 500 MB of data,
> otherwise the mysql server will complaint about the above out of memory
> problem. Not sure how to get around this problem. Could any help, Thanks
> in advance.
> --
> View this message in context: http://www.nabble.com/How-to-insert-a-binary-data-size-of-4G B-into-a-longblob-column.-tf3885758.html#a11014268
> Sent from the MySQL - ODBC mailing list archive at Nabble.com.
>
>
> --
> MySQL ODBC Mailing List
> For list archives: http://lists.mysql.com/myodbc
> To unsubscribe: http://lists.mysql.com/myodbc?unsub=jbalint@mysql.com

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re: How to insert a binary data size of 4GB into a longblob column.

crazyODBC wrote:
> I was using unixODBC as the driver manager and mysql ODBC driver to connect
> to the mysql server. I am using Linux platform and coding with C++. I had
> created a table with a column type of longblob and wish to use ODBC function
> call to insert a 4GB of data into the mysql database. At first, I got error
> saying that the data size is over the limit of max_allowed_packet size, so I
> changed the max_allowed_packet to around 1GB (that is the max
> max_allowed_packet variable can take). Then I start to sent a 1GB data to
> the mysql database. It will complaint about "Out of memory (Needed
> 500000048 bytes)". Seems like I could only sent max of 500 MB of data,
> otherwise the mysql server will complaint about the above out of memory
> problem. Not sure how to get around this problem. Could any help, Thanks
> in advance.

What ODBC call are you using to insert the data? I think you need to
insert it in small chunks using SQLPutData.

Martin

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re: How to insert a binary data size of 4GB into a longblob column.

On Fri, Jun 08, 2007 at 10:46:44AM +0100, Martin Evans wrote:
> What ODBC call are you using to insert the data? I think you need to
> insert it in small chunks using SQLPutData.

Unfortunately, the MySQL client/server protocol does not support sending
a BLOB in chunks, so using SQLPutData() just results in the whole BLOB
being buffered in the driver until is has all of the data and can send
the the whole INSERT/UPDATE statement to the server.

One way to work around this is to use the LOAD_FILE() function, but this
requires the file be on the server host already. But the file still must
be smaller than the max_allow_packet size.

Another is to roll your own chunking of files on the server using
multiple records.

Obviously these aren't ideal solutions, but I'm not aware of any
short-term plans to improve the protocol-level BLOB handling in MySQL.

Jim Winstead
MySQL Inc.

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org