MySQL Bugs: #24360: .setFetchSize() breaks prepared SHOW and other commands

Bug #24360	.setFetchSize() breaks prepared SHOW and other commands
Submitted:	16 Nov 2006 11:34	Modified:	17 Nov 2006 13:48
Reporter:	Domas Mituzas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.0.4	OS:
Assigned to:		CPU Architecture:	Any

Description:
PreparedStatement s = conn.prepareStatement("SHOW PROCESSLIST");
s.setFetchSize(5); // any non-zero
s.execute();

raises an exception:

Thu Nov 16 13:25:55 EET 2006 TRACE: Last 20 packets received from server, from oldest->newest:

Server (re-used) com.mysql.jdbc.Buffer@b8e059 --------------------> Client

Packet payload:

29 00 00 86 13 65 75 63     ) . . . . e u c 
6a 70 6d 73 5f 6a 61 70     j p m s _ j a p 
61 6e 65 73 65 5f 63 69     a n e s e _ c i 
07 65 75 63 6a 70 6d 73     . e u c j p m s 
02 39 37 03 59 65 73 03     . 9 7 . Y e s . 
59                          Y 

Server (re-used) com.mysql.jdbc.Buffer@b8e059 --------------------> Client

Packet payload:

1e 00 00 87 0b 65 75 63     . . . . . e u c 
6a 70 6d 73 5f 62 69 6e     j p m s _ b i n 
07 65 75 63 6a 70 6d 73     . e u c j p m s 
02 39 38 00 03 59           . 9 8 . . Y 

Server (re-used) com.mysql.jdbc.Buffer@b8e059 --------------------> Client

Packet payload:

05 00 00 88 fe              . . . . . 

Client com.mysql.jdbc.Buffer@121f1d--------------------> Server

Packet payload:

11 00 00 00 03 53 45 54     . . . . . S E T 
20 61 75 74 6f 63 6f 6d     . a u t o c o m 
6d 69 74 3d 31 74 5f 72     m i t = 1 t _ r 
65 73                       e s 

Server (re-used) com.mysql.jdbc.Buffer@b8e059 --------------------> Client

Packet payload:

07 00 00 01 00 00 00        . . . . . . . 

Client com.mysql.jdbc.Buffer@121f1d--------------------> Server

Packet payload:

23 00 00 00 03 53 45 54     # . . . . S E T 
20 73 71 6c 5f 6d 6f 64     . s q l _ m o d 
65 3d 27 53 54 52 49 43     e = ' S T R I C 
54 5f 54 52 41 4e 53 5f     T _ T R A N S _ 
54 41 42 4c 45 53 27        T A B L E S ' 

Server (re-used) com.mysql.jdbc.Buffer@b8e059 --------------------> Client

Packet payload:

07 00 00 01 00 00 00        . . . . . . . 

Client com.mysql.jdbc.Buffer@121f1d--------------------> Server

Packet payload:

11 00 00 00 16 53 48 4f     . . . . . S H O 
57 20 50 52 4f 43 45 53     W . P R O C E S 
53 4c 49 53 54 52 49 43     S L I S T R I C 
54 5f                       T _ 

Server (re-used) com.mysql.jdbc.Buffer@b8e059 --------------------> Client

Packet payload:

0c 00 00 01 00 01 00 00     . . . . . . . . 
00 00 00 00                 . . . . 

Client com.mysql.jdbc.Buffer@362012--------------------> Server

Packet payload:

0b 00 00 00 17 01 00 00     . . . . . . . . 
00 00 01 00 00 00 00 00     . . . . . . . . 
00 00 00 00                 . . . . 

Server (re-used) com.mysql.jdbc.Buffer@b8e059 --------------------> Client

Packet payload:

01                          . 

Server (new) com.mysql.jdbc.Buffer@73a34b --------------------> Client

Packet payload:

18 00 00 02 03 64 65 66     . . . . . d e f 
00 00 00 02 49 64 00 0c     . . . . I d . . 
3f 00 0b 00 00 00 08 81     ? . . . . . . . 
00                          . 

Server (new) com.mysql.jdbc.Buffer@e59128 --------------------> Client

Packet payload:

1a 00 00 03 03 64 65 66     . . . . . d e f 
00 00 00 04 55 73 65 72     . . . . U s e r 
00 0c 3f 00 10 00 00 00     . . ? . . . . . 
fd 81 00                    . . . 

Server (new) com.mysql.jdbc.Buffer@9ced8e --------------------> Client

Packet payload:

1a 00 00 04 03 64 65 66     . . . . . d e f 
00 00 00 04 48 6f 73 74     . . . . H o s t 
00 0c 3f 00 40 00 00 00     . . ? . @ . . . 
fd 81 00                    . . . 

Server (new) com.mysql.jdbc.Buffer@b02efa --------------------> Client

Packet payload:

18 00 00 05 03 64 65 66     . . . . . d e f 
00 00 00 02 64 62 00 0c     . . . . d b . . 
3f 00 40 00 00 00 fd 80     ? . @ . . . . . 
00                          . 

Server (new) com.mysql.jdbc.Buffer@dc57db --------------------> Client

Packet payload:

1d 00 00 06 03 64 65 66     . . . . . d e f 
00 00 00 07 43 6f 6d 6d     . . . . C o m m 
61 6e 64 00 0c 3f 00 10     a n d . . ? . . 
00 00 00 fd 81 00           . . . . . . 

Server (new) com.mysql.jdbc.Buffer@c24c0 --------------------> Client

Packet payload:

1a 00 00 07 03 64 65 66     . . . . . d e f 
00 00 00 04 54 69 6d 65     . . . . T i m e 
00 0c 3f 00 07 00 00 00     . . ? . . . . . 
03 a1 00                    . . . 

Server (new) com.mysql.jdbc.Buffer@40c281 --------------------> Client

Packet payload:

1b 00 00 08 03 64 65 66     . . . . . d e f 
00 00 00 05 53 74 61 74     . . . . S t a t 
65 00 0c 3f 00 1e 00 00     e . . ? . . . . 
00 fd 80 00                 . . . . 

Server (new) com.mysql.jdbc.Buffer@a1d1f4 --------------------> Client

Packet payload:

1a 00 00 09 03 64 65 66     . . . . . d e f 
00 00 00 04 49 6e 66 6f     . . . . I n f o 
00 0c 3f 00 64 00 00 00     . . ? . d . . . 
fd 80 00                    . . . 

Server (re-used) com.mysql.jdbc.Buffer@b8e059 --------------------> Client

Packet payload:

05 00 00 0a fe              . . . . . 

Error message: java.lang.NullPointerException

Query being executed when exception was thrown:

com.mysql.jdbc.ServerPreparedStatement[1] - SHOW PROCESSLIST
Error number: 0
java.sql.SQLException: java.lang.NullPointerException

Query being executed when exception was thrown:

com.mysql.jdbc.ServerPreparedStatement[1] - SHOW PROCESSLIST
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
	at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:718)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:794)
	at lt.dammit.tests.mysqltest.mytest.main(mytest.java:33)

How to repeat:
import java.sql.*;

public class mytest {
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Connection conn = null;
		try {
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
			conn = DriverManager.getConnection("jdbc:mysql://localhost/test?dumpQueriesOnException=true&enablePacketDebug=true","root","");
		} catch (Exception e) {
			System.err.println ("Error message: " + e.getMessage ());
		}
		try {
			PreparedStatement s = conn.prepareStatement("SHOW PROCESSLIST");
			s.setFetchSize(1);
			s.execute();
			System.out.println(s);
		} catch (SQLException e) {
			   System.err.println ("Error message: " + e.getMessage ());
               System.err.println ("Error number: " + e.getErrorCode ());
               e.printStackTrace();
		}

	}

}

Suggested fix:
n/a, works on 3.1.x

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15493

Fixed for 5.0.5. Notice that there appears to be an underlying server bug with prepared statements in that preparing a "SHOW" command doesn't return any result set metadata like other result-set-returning statements (which is what caused the null pointer exception in the JDBC driver).