Bug #5194 Bulk Insert Failures with Prepared Statements
Submitted: 24 Aug 2004 23:47 Modified: 8 Sep 2004 19:08
Reporter: Ken Gieselman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.3-beta OS:Solaris (Solaris 2.9 AND Linux)
Assigned to: Konstantin Osipov CPU Architecture:Any

[24 Aug 2004 23:47] Ken Gieselman
Description:
I've encountered 2 issues with doing bulk inserts with prepared statements.  Tests (see attached test program) failed on Solaris 9 running both binary and built-from-source distributions of 4.1.3-beta, and on Linux (Fedora Core 2/kernel 2.6.6).
                                                                                
1) Bulk inserts starting at 16k+ of parameter data cause SIGBUS crashes with an unaligned address error.  If bulk inserts are started with a smaller value (see test program below), inserts will succeed, and increment significantly past that amount of data.

2) There is a limit of 65536 parameters in any given prepared statement in mysql_stmt_prepare, or its server-side counterparts.  Mysql_stmt_prepare returns normally, but the statement only has (total % 65536) params defined.  The first routine to return an error is usually mysql_stmt_execute, when the pre-parsed query string and the bound parameters dont match.
                                                                                

How to repeat:
See attached code for test.c --

1) To reproduce the first problem, set START_ROWS to 16 or more.  Execution succeeds with START_ROWS set to 15 or below.
                                                                                
2) To reproduce the second problem, set MAX_ROW_INSERTS to 263 or higher.  Execution fails on insert #263 (65750 parameters).
[24 Aug 2004 23:48] Ken Gieselman
Code to reproduce error conditions

Attachment: test.c (text/x-csrc), 9.69 KiB.

[24 Aug 2004 23:50] Ken Gieselman
GDB stack trace of Linux Failure (issue #1)

Attachment: linux_trace (application/octet-stream, text), 763 bytes.

[24 Aug 2004 23:52] Ken Gieselman
Debugger info from Solaris 9 Failure (issue #1)

Attachment: sol_trace (application/octet-stream, text), 1.15 KiB.

[31 Aug 2004 8:25] MySQL Verification Team
Thank you for the bug report. I was able to repeat on Windows:

Insert: 261 rows query: 131050b params: (data) 261000b (expected) 65250 (prep'd) 65250
Insert: 262 rows query: 131552b params: (data) 262000b (expected) 65500 (prep'd) 65500
Insert: 263 rows query: 132054b params: (data) 263000b (expected) 65750 (prep'd) 214
stmt exec failed (sets 263) [Incorrect arguments to mysql_stmt_execute]

When:

#define START_ROWS		16

below the call stack:

bug5194.exe!_output(_iobuf * stream=0x0056ca70, const char * format=0x004a562d, char * argptr=0x0012f9d4)  Line 677 + 0x1f	C
bug5194.exe!fprintf(_iobuf * str=0x0056ca70, const char * format=0x004a5610, ...)  Line 64 + 0x11	C
bug5194.exe!_checkchunk(st_irem * irem=0x00367930, const char * filename=0x004a4ea0, unsigned int lineno=192)  Line 446 + 0x1d	C
bug5194.exe!_sanity(const char * filename=0x004a4ea0, unsigned int lineno=192)  Line 490 + 0x11	C
bug5194.exe!_myrealloc(char * ptr=0x003658f0, unsigned int size=12295, const char * filename=0x004a4ea0, unsigned int lineno=192, int MyFlags=16)  Line 222 + 0xd	C
bug5194.exe!net_realloc(st_net * net=0x003653f0, unsigned long length=8505)  Line 192 + 0x1f	C++
bug5194.exe!my_realloc_str(st_net * net=0x003653f0, unsigned long length=4)  Line 1725 + 0x10	C
bug5194.exe!store_param(st_mysql_stmt * stmt=0x0036e9d8, st_mysql_bind * param=0x00ae17f0)  Line 2366 + 0x11	C
bug5194.exe!cli_stmt_execute(st_mysql_stmt * stmt=0x0036e9d8)  Line 2451 + 0xf	C
bug5194.exe!mysql_stmt_execute(st_mysql_stmt * stmt=0x0036e9d8)  Line 2681 + 0x10	C
bug5194.exe!main(int argc=1, char * * argv=0x00362ca0)  Line 204 + 0x9	C
bug5194.exe!mainCRTStartup()  Line 206 + 0x19	C
kernel32.dll!77e714c7() 	
ntdll.dll!77f944a8()
[31 Aug 2004 9:35] Hartmut Holzgraefe
regarding #2:

both server and client side statement structures keep track of the number of statement parameters in 'int param_count;' (32bit),
but only the lower 2 bytes are transfered without any bounds checking, leading to the observed '% 65536' truncation.
[8 Sep 2004 16:49] Konstantin Osipov
bk commit - 4.1 tree (konstantin:1.2010) BUG#5194
[8 Sep 2004 17:05] Konstantin Osipov
Some more comments:
- issus with bus error were due to a bug with network buffer overrun in libmysql, which
were fixed
- 65535 limit for placeholders count is intentional, we just didn't check it explicitly and 
there were no error message. This was fixed too.
Please test the latest development tree and provide  us with your comments.
Thank you for your interest in MySQL and prepared statements API.
[8 Sep 2004 19:08] Konstantin Osipov
Fixed in 4.1.5