MySQL Bugs: #1425: Memory allocation error for BLOB's

Bug #1425	Memory allocation error for BLOB's > 16 MB
Submitted:	28 Sep 2003 23:57	Modified:	4 Oct 2006 13:24
Reporter:	Marc Dzaebel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.06 (any Release?)	OS:	Windows (Windows 2000)
Assigned to:	Peter Harvey	CPU Architecture:	Any

Description:
The following memory allocation error can be reproduced by other languages than Java but a reproduction sample is included. The ODBC trace shows no useful information. The MAX-ALLOWED-PACKET paramter shows 200MB in the WinMySQLAdmin 1.4 shows 209714176 bytes for the mysqld.

Fault Message:

java.sql.SQLException: [MySQL][ODBC 3.51 Driver][mysqld-4.0.15-nt]Memory allocation error
	at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
	at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
	at sun.jdbc.odbc.JdbcOdbc.SQLParamData(JdbcOdbc.java:4738)
	at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:225)
	at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(JdbcOdbcPreparedStatement.java:136)
	at BlobTest.main(BlobTest.java:28)

OS: Windows 2000 client with [MySQL][ODBC 3.51_06 Driver]

my.ini

#This File was made using the WinMySQLAdmin 1.4 Tool
#22.09.2003 08:31:54

#Uncomment or Add only the keys that you know how works.
#Read the MySQL Manual for instructions

[mysqld]
basedir=C:/mysql
#bind-address=192.168.0.214
datadir=C:/mysql/data
max-allowed-packet=200M
max-heap-table-size=200M
#language=C:/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
#set-variable=key_buffer=16M
[WinMySQLadmin]
Server=C:/mysql/bin/mysqld-nt.exe
user=marc
password=marc
[mysql]
max-allowed-packet=200M
max-heap-table-size=200M
[mysqld-nt]
max-allowed-packet=200M
max-heap-table-size=200M

Second Mail to Venu:

Hi Venu,

thanks for your quick answer!

> First, we don't recommend to use JDBC:ODBC bridge inorder to talk to
> MySQL as it involves too many layers and also as this is not stable
> enough, and we never test(ed) the driver from this configuration.
> Second, does this happen with non-JAVA through direct ODBC calls ?

If you exchange the jdbc:odbc with the direct jdbc:mysql driver everything works fine in my sample. However, our software currently uses ODBC as a bridge to many different databases, so it would be a very hard work to change it to handle MySQL differently.

As I emphasised in my previous mail, this is not a language dependent error. Originally we encountered the problem from an other language (VisualObjects) and it's reproducible on different systems (Currently Windows 2000 client and server, remote and local). The error should (in my view) be reproducable with any other language. This error only occurs for >16 MB BLOB's!! It's important to notice that it's a problem that needs the max-allowed-packet parameter set to a higher value. However, the parameter doesn't change anything for >16 MB BLOB's. IMHO the problem should be located in the handling of big queries (>16MB) of the MyODBC driver.

By the way, what do you mean by "not stable enough"?

> Anyway, I will cross check this and let you know..

Thanks a lot! Looking forward to your results!

Any further questions are of course very welcome.

Marc Dzaebel

How to repeat:
import java.sql.*;  // Filename: BlobTest.java
import java.io.*;
public class BlobTest {
  public static void main(String[] args) throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection connection=DriverManager.getConnection("jdbc:odbc:MyODBC","","");
    connection.createStatement().execute(  // Erzeugung eine Test-Tabelle
           "CREATE TABLE IF NOT EXISTS `testblob`(      "+
           "  `id`     int(11)  NOT NULL auto_increment,"+
           "  `lblob`  longblob NOT NULL,               "+
           "   PRIMARY KEY (`id`)                       "+
           ") TYPE=MyISAM;" // MyISAM oder InnoDB doesn't matter here
       );
    File file=new File("C:\\\\downloads\\\\mysql-4.0.15-win.zip"); // 23 MB!!
    System.out.println("Transfering BLOB: "+file);
    FileInputStream fis=new FileInputStream(file);
    String query="INSERT testblob VALUES (null,?)";
    PreparedStatement ps=connection.prepareStatement(query);
    ps.setBinaryStream(1,fis,(int)file.length());
    ps.executeUpdate();
    ps.close();
    fis.close();     
}}

Is there a reason you're using the JDBC:ODBC bridge instead of the Type-IV (all Java) Connector/J JDBC driver for MySQL? You'll find (even by Sun's own admission) that the JDBC:ODBC bridge is buggy and not suited for production use.

We'll track down why this is happening in MyODBC, though.

Note, our company mainly develops with CA-VisualObjects rather than Java. To let you have a convenient reproduction sample, I translated the case to Java and got the same error. We want to enable our >1000 customers to work with MySQL as a Web-publishing backend too.

In which language would you like to have the reproduction sample?

Marc

I could able to reproduce this. 

Thanks

Hi Venu,

great, that you were able to reproduce the problem! I'd be interested in the code. Is there any schedule for a fix?

Thanks, Marc

I could only get this failure at values close to 16 MG values really lager would work.

I have tested an MyODBC 3.51.07 built from the latest BK tree and
compiled against the mysqlclient.lib 4.0.17 without any problems
with BLOB files only limited by my machine's RAM size.

My tests were: 15, 16, 18, 20, 30, 50, 100, 150 MB Blob sizes.

I am able to produce this with a test program I have created using just C and ODBC API. I would love to 
see Miguel's test codee on this to compare.

My thinking at this point is that the problem is really rooted in limitations to the current protocol. The 
max packet size can be altered (server and client) but what is really needed is an ability to send/receive 
LONGBLOBs in chunks.

see; #1605

I have created a test case which shows a problem. Problem may be related to other bug/CSC issues. Perhaps memory allocation but most likley a limitation in protocol for MySQL v4.0. 

I have attached my test files and requested Miguel re-evaluate.

Possibly related is CSC #2230

My testing shows the error BUT only under circumstances where the packet size clearly exceeds the max configured into the server. The default, in this case is 1,048,576 bytes. Increasing the max packet size in the server allowed larger packets - and larger BLOB's to go to/from the server.

The solution here is for the customer to configure the server to allow packet sizes large enough to handle expected BLOB sizes (and some extra since entire SQL statement must be included).

A simple search in the manual for "max_allowed_packet" will give details. In particular; check into the server options file (my.cnf).

I recommend closing this Bug.

NOTE: Some online doc implies both client and server need to have max_allowed_packet set - either will work but server is only option for doing this with MyODBC (at this time).

NOTE: There has been some discussion about chunking data to/from the server. MyODBC does not chunk data *to* the server as it is a feature only available in very recent protocol changes. Chunking data *from* the server is not, currently, supported in any version of the protocol (but there are some tricks to make this happen i.e. using substr). So in a future version of MyODBC (perhaps 3.52) chunking will likely be supported (when used against a supporting server/protocol and/or as a config option).

NOTE: Some confusion was created when looking into this bug because building a test in debug mode masked the error. Building the test in Release mode (or outside of VS Project) seems to work fine. This can be an issue for developers.

NOTE: This seems to be related to several other bugs. I think those bugs have already been closed. In one case; it appears a custom build was provided as solution. My testing indicates that this *may* not be required as I used latest "stable" releases (assuming MyODBC 3.51 in BK accurately reflects last stable release of MyODBC).

NOTE: Testing done with:
- MyODBC 3.51 (from BK) on XP
- MySQL 4.0.18 ("standard" binary rpm install from web)

NOTE: MySQL v3.23.x only supports, up to, 16M max packet size. MySQL v4.0 supports up to 2GB. I gather the custom build (mentioned above) was to have MyODBC 3.51 link against MySQL 4.0 client libraries (this from Mark Matthews). I assume this would allow up to 2GB to be sent/received. Future releases of MyODBC will link with more recent libraries (for example; latest stable libraries when building stable release of MyODBC).

We can not expect that our users will set all parameters correctly.

There should be no error and no memory allocation error even if max_allowed_packet is set to 2 bytes.

Workarounds are not good enough.

This bug has to be fixed so that no allocation errors and no crash occurs regardless of the settings on the client or server.

I experienced no "allocation errors and no crash" during my testing.

Peter:

Do you have any status on this issue?

I've recently encountered a similar scenario which consistently produces the same error.

ERROR [HY001] [MySQL][ODBC 3.51 Driver][mysqld-4.0.18-log]Memory allocation error

Using the MyODBC 3.51 driver, I currently have an ASP.NET client written in VB.NET to talk to a mysql 4.0.18 database running on Debian.

The application provides an web based interface for file uploads which the system stores in the mysql database. I do not encounter errors until I attempt to upload files larger than 14,674,944 bytes - at least that's the largest file I successfully uploaded. The limit may be slightly higher, but it's lower than 16,397,730 bytes. The field in which I store the files bears a longblob dbtype.

The application's web server runs IIS 5 on Windows 2000, SP4 and .NET Framework v1.1. The database server runs Debian test release MySQL v4.0.18.

Pertinent my.cnf settings:
#skip-networking
key_buffer              = 100M
max_allowed_packet      = 100M
thread_stack            = 128K
#
# Query Cache Configuration
#
query_cache_limit       = 1048576
query_cache_size        = 26214400
query_cache_type        = 1

[isamchk]
key_buffer              = 100M

-- End Settings --

Please let me know if I can supply any additional information.

Thanks for any reply.
Regards,
Craig Hunt

At least one "direction" of this will be fixed in Connector/ODBC 5.0 when it uses server-side prepared statements, since "long" data can be sent in chunks to the server.

Unfortunately, it's a limitation of the _server_ that long data must be sent entirely at once to the client for _reads_, and so until that changes, there will still be cases where the driver will not be able to allocate enough memory to _read_ large BLOBs.