MySQL Bugs: #11634: CommunicationException occurs with some select Statements

Bug #11634	CommunicationException occurs with some select Statements
Submitted:	29 Jun 2005 8:43	Modified:	1 Jul 2005 15:38
Reporter:	Anindya Mozumdar	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	mysql connector 3.1.10	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
WHAT I AM DOING
----------------------
I have two tables

CREATE Table1 (
   ObjId Integer,
   EntryDate DateTime
);
Type=InnoDB;

CREATE Table2 (
   ObjId Integer,
   Table1_ObjId Integer,
   Amount Double
);
Type=InnoDB;

I am using JBoss AS 4.0.0 to connect to the MySql Serrver 4.1.8.
My query is
String selectStmt =
  "SELECT t1.ObjId,t1.EntryDate,t2.ObjId,t2.Table1_ObjId,t2.Amount  WHERE t1.ObjId=t2.Table1_ObjId AND t1.EntryDate = SELECT MAX(EntryDate) FROM t1";
PreparedStatement pstmt = con.prepareStatement(selectStmt);
ResultSet rs = pstmt.executeQuery();

EXPECTED BEHAVIOUR
---------------------------
Return the rows with maximum EntryDate From t1

WHAT HAPPENS
------------------
On pstmt.executeQuery(), it throws a CommunicationException and closes the Connection.

EVERY MACHINE IN THE LAN WHICH HAD A CONNECTION TO THE SERVER NEEDS TO RECONNECT.

MORE INFORMATION
------------------------
1.I use a similar query with only one table, i.e, I donot do a join of two tables. Then it works perfectly.

2. With connector 3.1.6, the con.prepareStatement throws the Exception, while with 3.1.10, pstmt.executeQuery() causes it.

How to repeat:
..

Suggested fix:
..

hi! 

I would say that table definitions contains syntax errors (wrong ; before type=innodb and just 'create' instead of 'create table'). I assume it must be:

CREATE table Table1 (
   ObjId Integer,
   EntryDate DateTime
) Type=InnoDB;

CREATE table Table2 (
   ObjId Integer,
   Table1_ObjId Integer,
   Amount Double
)Type=InnoDB;

And syntax error in query: missing FROM clause, where must be list of tables with aliases (I assume t1 and t2 aliases of Table1 and Table2) and join definition.

1)Could you please to provide full query you used (in order to reproduce this bug) ?
2) if every client had to reconnect, probably mysql server  error log contains some additional info about this event. Could you check it?

Sorry for my shabby writing in the original bug report.

The create table statements are as follows :

CREATE TABLE Table1 (
  ObjId Integer,
  EntryDate DateTime
);
Type=InnoDB;

CREATE TABLE Table2 (
  ObjId Integer,
  Table1_ObjId Integer,
  Amount Double
);
Type=InnoDB;

The tables are created using a tool called DBDesigner available from fabforce.net.

In my JDBC code, I am using the following :-

String selectStmt = "SELECT t1.ObjId,t1.EntryDate,t2.ObjId,t2.Table1_ObjId,t2.Amount FROM Table1 t1, Table2 t2 WHERE t1.ObjId=t2.Table1_ObjId AND (t1.EntryDate = SELECT MAX(EntryDate) FROM Table1) ORDER BY t1.EntryDate DESC";
PreparedStatement pstmt = con.prepareStatement(selectStmt);
ResultSet rs = pstmt.executeQuery();

EXPECTED BEHAVIOUR
---------------------------
Return the rows with maximum EntryDate From t1

WHAT HAPPENS
------------------
On pstmt.executeQuery(), it throws a CommunicationException and closes
the Connection.

EVERY MACHINE IN THE LAN WHICH HAD A CONNECTION TO THE SERVER NEEDS TO
RECONNECT.

MORE INFORMATION
1. The query works perfectly in the mysql prompt, and IT IS ONLY USING JDBC that I get the error.
2. A "similar" query works perfectly with JDBC if there is no join involved, i.e, I am using a single table.
3. 2. With connector 3.1.6, the con.prepareStatement throws the Exception,
while with 3.1.10, pstmt.executeQuery() causes it.

Here is the message from the server logs which could possibly throw more light on the situation :-

Version: '4.1.8-max'  socket: '/tmp/mysql.sock'  port: 3306  Official MySQL-max binary
mysqld got signal 11;
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=8388600
read_buffer_size=131072
max_used_connections=8
max_connections=100
threads_connected=8
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x8b4ec18
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...
Cannot determine thread, fp=0x41e7601c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8132ce3
0x40053618
0x80f05d4
0x81637e1
0x811ce36
0x8119f5a
0x811a8ad
0x80f6473
0x8103995
0x8103b97
0x80fd182
0x8107ae7
0x815f0d5
0x81630c3
0x81dd4d1
0x817be74
0x817c479
0x817c920
0x81417e0
0x8141248
0x8140908
0x4004e2b6
0x420de407
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8b5e898 = SELECT t1.ObjId,t1.EntryDate,t2.ObjId,t2.Table1_ObjId,t2.Amount FROM Table1 t1, Table2 t2 WHERE t1.ObjId=t2.Table1_ObjId AND (t1.EntryDate = SELECT MAX(EntryDate) FROM Table1) ORDER BY t1.EntryDate DESC
thd->thread_id=8
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
                                                                                                                             
Number of processes running now: 0
050628 15:43:42  mysqld restarted

This is a server bug if the server crashes and the only difference is you use a join in the prepared statement.

I wasn't able reproduce server crash with version 4.1.13.

Please upgrade MySQL server to the recent version.

The error does not occur with mysql server version 4.1.12. Thanks.