MySQL Bugs: #14955: INSERTs with large blob-data fail on connection through named-pipe-interface

Bug #14955	INSERTs with large blob-data fail on connection through named-pipe-interface
Submitted:	15 Nov 2005 17:31	Modified:	18 Jul 2007 17:44
Reporter:	Armin Schöffmann (OCA)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.15 & 4.0.15	OS:	Windows (WINXP_PROF_GER SP2)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
This issue covers MySQL 5.0.15 and 4.0.15

client: mysql C-API,
client and srv running on localhost connection through named_pipe-interface

when inserting larger BLOB-data (query-stringlen around 1mb) into a simple table, the query fails with:
"MySQL server has gone away", (log shows: "...Got an error reading communication packets")

the same query succeeds, if the connection was opened with MYSQL_PROTOCOL_TCP option, or if the blob-data-length is shorter e.g. 50kb.

The problem has been observed already with earlier version of 4.x.x but never investigated closer.

Regards,
Armin.

How to repeat:
Start the server with these (additional) my.ini options:

[mysqld]
[...]
net_buffer_length=8K
max_allowed_packet=32M
enable-named-pipe=1
[...]

the server-connection is opened by:
[...]

char option=1;
my_bool reconnect=true;

mysql_options(hdb.hmysql,MYSQL_OPT_CONNECT_TIMEOUT,(char*)&option);
mysql_options(hdb.hmysql,MYSQL_OPT_RECONNECT,(char*)&reconnect);
option=MYSQL_PROTOCOL_PIPE;
mysql_options(hdb.hmysql,MYSQL_OPT_PROTOCOL,(char*)&option);
mysql_real_connect(hdb.hmysql,server, psz_uid,psz_pwd,psz_db,0,NULL,CLIENT_MULTI_STATEMENTS);

create a simple table with a BLOB-column:
"CREATE TABLE IF NOT EXISTS`scan_data`(`serial_id`INT UNSIGNED NOT NULL,`graph`LONGBLOB,PRIMARY KEY(`serial_id))ENGINE=INNODB

insert a row with blob-data-length around 1MB.

the query will fail with "MySQL server has gone away":
the server's error-log shows: "051115 18:22:16 [Warning] Aborted connection 3 to db: 'alfredo' user: 'aegaeon' host: `localhost' (Got an error reading communication packets)"

repeat the above procedure, but set:

option=MYSQL_PROTOCOL_TCPIP;
instead of
option=MYSQL_PROTOCOL_PIPE;

the insert-query will work without problems.

						

Suggested fix:
none so far

Can you provide the complete test code? 

Thanks in advance.

hello miguel,
productionlogmysql.cpp shows the failing code (line 53 'result|=DoSQLQuery(psz_query,0,offset);'

I tried to create a simple test-example (single-threaded), to exactly reproduce the described error (my original report was generated after observations in the debugger).

Doing so, I had to recognize, that the problem can't be reproduced as  described - it is probably multi-threading-related:

If I create in our original code a distinct (new) mysql-connection for the INSERT-query, the code works also with the pipe-interface - no matter what blob-size i'm using.

I wrote now another (multi-threaded) code example, that simulates the thread-scenario in our application. Now the code fails not during the insert-statement, but during the connect-phase. With tcp/ip-option it works just normal.

how to repeat:
compile main.cpp  and link with the multi-threaded c-runtime

at file-top, you find a few defines that allow to:
- force a single-threaded execution (#define _SINGLETHREADED)
- set the number of concurrent threads (enum { THREAD_MAX=5})
- set the protocol-type (#define _PIPES) -> tcp/ip if undef

the example starts in the multi-threaded-build THREAD_MAX (5) threads with distinct mysql-structs that open 5 connections to the localhost-server (user 'root', pwd '')

each thread tries to create a database, a table and to insert a single row with a 1MB blob.

if flag _MORETHREADS is defined, each thread executes the insert-statement out of a (serialized) sub-thread with shared mysql-struct (just to test this situation).

On my machine (dual-core) the example fails  for 4 of 5 threads with "Failed: Can't open named pipe to host: .  pipe: (null) (32)"

with tcp-option everything is OK, same if the connection-threads are started with (long) delay.

So, enough text,
hope this helps,
armin.

Thank you for the feedback.

I was tested your sample and I got similar behavior that you have commented,
however on my side happens using named pipe and TCP/IP:

-- Using Pipe

T#0x00000EC4: connecting localhost, will use pipes
T#0x00000EC4: Success. Serverinfo: 5.0.18-nt
T#0x00000958: CREATE DATABASE IF NOT EXISTS`test_aegaeon`
T#0x00000958: Success.

T#0x00000958: CREATE TABLE IF NOT EXISTS`scan_data`(`serial_id`INT UNSIGNED NOT NULL,`
T#0x00000958: Success.

T#0x000002E8: START TRANSACTION
T#0x000002E8: Success.

T#0x000002E8: INSERT INTO`scan_data`(`serial_id`,`graph`)VALUES(3909962,'
T#0x000002E8: Failed: MySQL server has gone away

T#0x00000FB0 (main): cleanup

-- Using TCP/IP

T#0x000002A8: connecting localhost, will use tcp
T#0x000002A8: Success. Serverinfo: 5.0.18-nt
T#0x00000BC4: CREATE DATABASE IF NOT EXISTS`test_aegaeon`
T#0x00000BC4: Success.

T#0x00000BC4: CREATE TABLE IF NOT EXISTS`scan_data`(`serial_id`INT UNSIGNE
T#0x00000BC4: Success.

T#0x00000F30: START TRANSACTION
T#0x00000F30: Success.

T#0x00000F30: INSERT INTO`scan_data`(`serial_id`,`graph`)VALUES(4313606,'
T#0x00000F30: Failed: MySQL server has gone away

T#0x00000818 (main): cleanup

There is a bug already reported and verified that on 5.0, mysql_real_connect
resets the reconnect to 0 even when MYSQL_OPT_RECONNECT was applied
with mysql_options.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I wasn't able to repeat with latest released version:

T#0x000005F0: connecting localhost, will use pipes
T#0x000005F0: Success. Serverinfo: 5.0.45-community-nt
T#0x000005F0: CREATE DATABASE IF NOT EXISTS`test_aegaeon`
T#0x000005F0: Success.

T#0x000005F0: CREATE TABLE IF NOT EXISTS`scan_data`(`serial_id`INT UNSIGNED NOT NULL,`graph`LONGBLOB,PRIMARY KEY(`
T#0x000005F0: Success.

T#0x000005F0: START TRANSACTION
T#0x000005F0: Success.

T#0x000005F0: INSERT INTO`scan_data`(`serial_id`,`graph`)VALUES(2356223,'
T#0x000005F0: Success.

T#0x000005F0: connecting localhost, will use pipes
T#0x000005F0: Success. Serverinfo: 5.0.45-community-nt
T#0x000005F0: CREATE DATABASE IF NOT EXISTS`test_aegaeon`
T#0x000005F0: Success.

T#0x000005F0: CREATE TABLE IF NOT EXISTS`scan_data`(`serial_id`INT UNSIGNED NOT NULL,`graph`LONGBLOB,PRIMARY KEY(`
T#0x000005F0: Success.

T#0x000005F0: START TRANSACTION
T#0x000005F0: Success.

T#0x000005F0: INSERT INTO`scan_data`(`serial_id`,`graph`)VALUES(2356395,'
T#0x000005F0: Success.

T#0x000005F0: connecting localhost, will use pipes
T#0x000005F0: Success. Serverinfo: 5.0.45-community-nt
T#0x000005F0: CREATE DATABASE IF NOT EXISTS`test_aegaeon`
T#0x000005F0: Success.

T#0x000005F0: CREATE TABLE IF NOT EXISTS`scan_data`(`serial_id`INT UNSIGNED NOT NULL,`graph`LONGBLOB,PRIMARY KEY(`
T#0x000005F0: Success.

T#0x000005F0: START TRANSACTION
T#0x000005F0: Success.

T#0x000005F0: INSERT INTO`scan_data`(`serial_id`,`graph`)VALUES(2356785,'
T#0x000005F0: Success.

T#0x000005F0: connecting localhost, will use pipes
T#0x000005F0: Success. Serverinfo: 5.0.45-community-nt
T#0x000005F0: CREATE DATABASE IF NOT EXISTS`test_aegaeon`
T#0x000005F0: Success.

T#0x000005F0: CREATE TABLE IF NOT EXISTS`scan_data`(`serial_id`INT UNSIGNED NOT NULL,`graph`LONGBLOB,PRIMARY KEY(`
T#0x000005F0: Success.

T#0x000005F0: START TRANSACTION
T#0x000005F0: Success.

T#0x000005F0: INSERT INTO`scan_data`(`serial_id`,`graph`)VALUES(2356941,'
T#0x000005F0: Success.

T#0x000005F0: connecting localhost, will use pipes
T#0x000005F0: Success. Serverinfo: 5.0.45-community-nt
T#0x000005F0: CREATE DATABASE IF NOT EXISTS`test_aegaeon`
T#0x000005F0: Success.

T#0x000005F0: CREATE TABLE IF NOT EXISTS`scan_data`(`serial_id`INT UNSIGNED NOT NULL,`graph`LONGBLOB,PRIMARY KEY(`
T#0x000005F0: Success.

T#0x000005F0: START TRANSACTION
T#0x000005F0: Success.

T#0x000005F0: INSERT INTO`scan_data`(`serial_id`,`graph`)VALUES(2357113,'
T#0x000005F0: Success.

T#0x000005F0 (main): cleanup

C:\temp>

Hello
I don't know if this bug was burried deep in the log, but I got the same problem while trying to store a BLOB. Depending on BLOB contents if works or fails.It works whenever the BLOB contains ASCII encoded data while it fails when the BLOB contains an encoded binary file content (W7 exe file).
I can't provide much more details or code to repeate right now, but I d be glad to know if there has been a fix or not.
I ll try preparing a code sample if someone answer this query.

Pascal