Bug #63660 mysqld crashes on second call of sproc with CASE operator
Submitted: 7 Dec 2011 23:56 Modified: 8 Dec 2011 20:50
Reporter: Eric McNeill Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:v5.5.13 OS:Microsoft Windows (Win7, 2008r2)
Assigned to: CPU Architecture:Any
Tags: case, crash, executeBatch

[7 Dec 2011 23:56] Eric McNeill
Description:
I'm seeing a repeatable crash under very specific circumstances. I'm creating a sproc remotely via JDBC executeBatch(). The sproc contains a single SELECT with a CASE operator:

CREATE PROCEDURE CrasherSproc() 
  BEGIN 
    SELECT CASE WHEN IntField IS NULL THEN StringField ELSE 'notnull' END FROM CrashTest; 
  END

After creating the sproc, if I execute the sproc twice in a row (via JDBC or Workbench) mysqld crashes - see stack trace below.

It does NOT crash if I:
- Create the sproc via Workbench. I can copy/paste the contents of the exact same file my Java code is using into Workbench and the crash doesn't occur when I run it. 
- Don't use a CASE operator
- Remove the reference to the table's column from the CASE output, e.g.
    CASE WHEN IntField IS NULL THEN 'NO_TABLE_REFERENCE' ELSE 'notnull' END
- Execute the same SELECT via JDBC directly, as opposed to creating the sproc.

I've tried v5.1.18 of the JDBC connector. Haven't tried the newest server yet, will hopefully get to it soon.

How to repeat:
1. Create table with sample data:

CREATE TABLE CrashTest (
  Id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  StringField NVARCHAR(256) NOT NULL,
  IntField INT UNSIGNED NULL,
  PRIMARY KEY (Id)
)
ENGINE = INNODB;
INSERT INTO CrashTest (StringField, IntField) VALUES ('one', null);

2. Create sproc via JDBC:

public class CrashTest {
    
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        
        try {
            conn = DriverManager.getConnection("jdbc:mysql://zz-14e/nmwAnalyticsDb?useLegacyDatetimeCode=false&user=root&password=mypw"); 

            stmt = conn.createStatement();
            stmt.addBatch("DROP PROCEDURE IF EXISTS CrasherSproc");
            stmt.addBatch("CREATE PROCEDURE CrasherSproc() BEGIN SELECT CASE WHEN IntField IS NULL THEN StringField ELSE 'notnull' END FROM CrashTest; END");
            int[] retCodes = stmt.executeBatch();
            for(int retCode : retCodes) {
                System.out.println(retCode);
            }
            
        } catch(Exception e) {     
            System.out.println(e);
        } finally {
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            }
            catch(Exception e) {
                System.out.println(e);
            }
        }
    }
}

3. Run sproc *twice*, e.g. from Workbench:

CALL CrasherSproc();
CALL CrasherSproc();

4. mysqld crashes with below error log:

111207 15:26:04 [Note] Plugin 'FEDERATED' is disabled.
111207 15:26:04 [Note] Plugin 'partition' is disabled.
111207 15:26:04 InnoDB: The InnoDB memory heap is disabled
111207 15:26:04 InnoDB: Mutexes and rw_locks use Windows interlocked functions
111207 15:26:04 InnoDB: Compressed tables use zlib 1.2.3
111207 15:26:04 InnoDB: Initializing buffer pool, size = 4.7G
111207 15:26:05 InnoDB: Completed initialization of buffer pool
111207 15:26:05 InnoDB: highest supported file format is Barracuda.
111207 15:26:06  InnoDB: Waiting for the background threads to start
111207 15:26:07 InnoDB: 1.1.7 started; log sequence number 9016719
111207 15:26:07 [Note] Event Scheduler: Loaded 0 events
111207 15:26:07 [Note] D:\Program Files\NetMotion Server\analytics\database\bin\mysqld.exe: ready for connections.
Version: '5.5.13-enterprise-commercial-advanced'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Edition (Commercial)
111207 15:26:39 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=4
max_threads=300
thread_count=2
connection_count=2
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 664064 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x59f269a0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
000000013F251302    mysqld.exe!?fix_fields@Item_func@@UEAA_NPEAVTHD@@PEAPEAVItem@@@Z()
000000013F1DC2B7    mysqld.exe!?fix_fields@Item_func_case@@UEAA_NPEAVTHD@@PEAPEAVItem@@@Z()
000000013F212CF2    mysqld.exe!?setup_fields@@YA_NPEAVTHD@@PEAPEAVItem@@AEAV?$List@VItem@@@@W4enum_mark_columns@@PEAV3@_N@Z()
000000013F332048    mysqld.exe!?prepare@JOIN@@QEAAHPEAPEAPEAVItem@@PEAUTABLE_LIST@@IPEAV2@IPEAUst_order@@323PEAVst_select_lex@@PEAVst_select_lex_unit@@@Z()
000000013F33B3F8    mysqld.exe!?mysql_select@@YA_NPEAVTHD@@PEAPEAPEAVItem@@PEAUTABLE_LIST@@IAEAV?$List@VItem@@@@PEAV2@IPEAUst_order@@545_KPEAVselect_result@@PEAVst_select_lex_unit@@PEAVst_select_lex@@@Z()
000000013F33B866    mysqld.exe!?handle_select@@YA_NPEAVTHD@@PEAULEX@@PEAVselect_result@@K@Z()
000000013F1FF1D6    mysqld.exe!??0st_ha_create_information@@QEAA@AEBU0@@Z()
000000013F201BCF    mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@@Z()
000000013F345078    mysqld.exe!?exec_core@sp_instr_stmt@@UEAAHPEAVTHD@@PEAI@Z()
000000013F347CF9    mysqld.exe!?reset_lex_and_exec_core@sp_lex_keeper@@QEAAHPEAVTHD@@PEAI_NPEAVsp_instr@@@Z()
000000013F347F63    mysqld.exe!?execute@sp_instr_stmt@@UEAAHPEAVTHD@@PEAI@Z()
000000013F3494B1    mysqld.exe!?execute@sp_head@@AEAA_NPEAVTHD@@_N@Z()
000000013F34A5AE    mysqld.exe!?execute_procedure@sp_head@@QEAA_NPEAVTHD@@PEAV?$List@VItem@@@@@Z()
000000013F204875    mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@@Z()
000000013F2055CA    mysqld.exe!?mysql_parse@@YAXPEAVTHD@@PEADIPEAVParser_state@@@Z()
000000013F20637A    mysqld.exe!?dispatch_command@@YA_NW4enum_server_command@@PEAVTHD@@PEADI@Z()
000000013F20717C    mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z()
000000013F22C5AA    mysqld.exe!?do_handle_one_connection@@YAXPEAVTHD@@@Z()
000000013F22C814    mysqld.exe!handle_one_connection()
000000013F3B09DE    mysqld.exe!win_pthread_mutex_trylock()
000000013F553757    mysqld.exe!my_mb_ctype_mb()
000000013F55380B    mysqld.exe!my_mb_ctype_mb()
00000000777C652D    kernel32.dll!BaseThreadInitThunk()
00000000778FC521    ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0000000059F528C0): SELECT CASE WHEN IntField IS NULL THEN StringField ELSE 'notnull' END AS 'StringField' FROM CrashTest
Connection ID (thread ID): 9
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
[8 Dec 2011 0:20] Eric McNeill
I incorrectly listed my server version earlier. It's v5.5.13, with the JDBC version being 5.1.14.

I just tried a test on server v5.5.18 and could not reproduce the problem (that's with the Community Edition - we used the Advanced Edition but I don't have the latest copy of it). 

It'd be helpful if I could get some assistance with the stack trace figuring out if there's a known bug that was fixed. We OEM mysql with our product and we're close to shipping, and making a major change like this may be difficult.
[8 Dec 2011 5:16] Valeriy Kravchuk
This is a duplicate of bug #61470. This bug is actually fixed since 5.5.14, but is still private (for the reasons that are not clear to me).

If you want to get more details about the problem and fix, please, open service request.
[8 Dec 2011 20:50] Eric McNeill
Thanks Valeriy. When you say "This bug is actually fixed since 5.5.14, but is still private", what do you mean by private? The bug, or the fix?

I'll open an incident to get more specifics.
[9 Dec 2011 5:11] Valeriy Kravchuk
Bug report is private. Fix is in the source and binaries for months already...