Cannot import binary data > 1M into MySQL BE

[wbiebel]

MS Access 2007 frontend, MySQL 5.1 backend. max_allowed_packet = 1024M set in my-huge.ini
Nonetheless an error occurs if I try to import data more than 1M.
What can be wrong?

[thhui]

No need to import any binary data into mysql backend.
Just give it a file link instead on the table field.

[wbiebel]

Thx for the workaround, but is not the answer to my question.

[thhui]

Mysql configuration to change max packet size

In MySQL 3.23, the largest possible packet is 16MB, due to limits in the client/server protocol. In MySQL 4.0.1 and up, the limit is 1GB.

Both the client and the server have their own max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server.

Mysql Client:
If you are using the mysql client program, its default max_allowed_packet variable is 16MB. That is also the maximum value before MySQL 4.0. To set a larger value from 4.0 on, start mysql like this:
shell> mysql --max_allowed_packet=32M
That sets the packet size to 32MB.

Mysql Server
The server's default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns). For example, to set the variable to 16MB, start the server like this:

shell> mysqld --max_allowed_packet=16M

You can also use an option file to set max_allowed_packet. For example, to set the size for the server to 16MB, add the following lines in an option file:

[mysqld]
max_allowed_packet=16M

Before MySQL 4.0, use this syntax instead:

[mysqld]
set-variable = max_allowed_packet=16M

.../mysql/bin/my.cnf
By default configuration
------------------------------------------------------------
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
------------------------------------------------------------

change "max_allowed_packet = 1M" to "max_allowed_packet = 32M"

[wbiebel]

Thx for the long answer, but, as you can see in my original post, I have already done all these settings properly.
It does not work anyway, and this is the reason for the post.

[thhui]

Restart the mysql db after your change of configuration.

Check to see whether its new setting is OK.
Something like that:-

At the MySQL> command prompt, you can type:
show variables like 'max_allowed_packet'
to see if the changes you made are working.

Setting too high packet size will eat so much memory.

So recommend to use alternative method.

[wbiebel]

Thx again. I have done this also already: restarted more then one time, and show the variable values: the variable is set properly.

It does not work. In my opinion the lack is not at the MySQL-side but at the Access-side. In the doku can be red: the size of the packet has to be set both by the server and the client. But I don't know how to set the packet-size in Access.

[thhui]

Use php frontend or other mysql client (simply mysql command) to test the mysql database setup and import data.
See whether it is working or not.

[thhui]

Out of luck and accident, I somehow know how to tweak the buffer size issues on the Access-ODBC backend.

Just open the windows ODBC link of access backend (After further checking: mysql-odbc do not have buffer options) and there is an advanced option that allow us to change the max-buffer size in access odbc (Why not in mysql-odbc?).

My default for msaccess backend file - max buffer size is 2048KB.

It seems too bad that Mysql ODBC's setting is not that clear!

[thhui]

By the way, I just have a look on the mysql server configuration options and found that

Query Cache Limit

Sets the maximum amount of memory to be allocated for storage of old query results. The default value is only 1MB.(the same restriction as your situation).
This variable is only available in versions of MySQL > 4.1.x.


Do you change it to a greater number for your setting?

[thhui]

I've read in the web and found that writing to longblob data type is not efficient and eating too much memory.

It could be a lot better to write it to a bunch of blob (64K) rows data and then link them by code altogether to recover them to one file.

[thhui]

I found the following link quite useful for your reference.

You should take a thorough look on it:-

Accessing MySQL BLOB columns using Visual Basic 6 or MS Access
http://dev.mysql.com/tech-resources/...-handling.html

Recommended connection string used for your application.

DRIVER={MySQL ODBC 3.51 Driver};SERVER=123.456.789.100;DATABASE=mysqldatab ase;UID=sampleuser;PWD=12345;OPTION=16427

The most useful one is the OPTION=16427

A quick note on the option value; The options specified include the following:
1 - Client Can't handle the real column width being returned.
2 - Have MySQL return found rows value.
8 - Allow Big Values: We want the server to send out the BLOB values to our VB client (large packets), so this option must be set.
32 - Toggle Dynamic cursor support.
16384 - Convert LongLong to Int: This just helps makes sure that large numeric results get properly interpreted.
The 1, 2, and 32 options are taken from the ADO code sample on the MySQL web site

[weekend00]

I don't think this relate to buffer size or packet size.

maybe this setting affect the speed, but should not affect can do or can not do.

[thhui]

You missed some important points in mysql odbc configuration

Option can be one or more of the following values:
•1 - The client can't handle that MyODBC returns the real width of a column.
•2 - The client can't handle that MySQL returns the true value of affected rows. If this flag is set then MySQL returns 'found rows' instead. One must have MySQL 3.21.14 or newer to get this to work.
•4 - Make a debug log in c:\myodbc.log. This is the same as putting MYSQL_DEBUG=d:t:O,c::\myodbc.log in AUTOEXEC.BAT.
+++•8 - Don't set any packet limit for results and parameters<=== Packet size related
•16 - Don't prompt for questions even if driver would like to prompt.
•32 - Enable or disable the dynamic cursor support. This is not allowed in MyODBC 2.50.
•64 - Ignore use of database name in 'database.table.column'.
•128 - Force use of ODBC manager cursors (experimental).
•256 - Disable the use of extended fetch (experimental).
•512 - Pad CHAR fields to full column length.
•1024 - SQLDescribeCol() will return fully qualified column names.
•2048 - Use the compressed server/client protocol.
•4096 - Tell server to ignore space after function name and before '(' (needed by PowerBuilder). This will make all function names keywords!
•8192 - Connect with named pipes to a MySQLd server running on NT.
•16384 - Change LONGLONG columns to INT columns (some applications can't handle LONGLONG).
•32768 - Return 'user' as Table_qualifier and Table_owner from SQLTables (experimental).
•65536 - Read parameters from the client and ODBC groups from my.cnf.
•131072 - Add some extra safety checks (should not be needed but...).
If you want to have multiple options, you should add the above flags! For example: 16 + 1024 = 1030 and use Option= 1030;.
For more information, go to MyODBC Reference Manual.

[thhui]

In the article
MySQL Performance Blog

http://www.mysqlperformanceblog.com/...-memory-usage/

Setting too high for buffer packet size to 1GB may eat up to 3GB memory!!!