| Bug #4368 | "like" fails in PreparedStatement, crashes server | ||
|---|---|---|---|
| Submitted: | 2 Jul 2004 5:14 | Modified: | 3 Sep 2004 20:55 |
| Reporter: | Daniel Blumenthal | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.3 | OS: | Linux (linux rhat8.0) |
| Assigned to: | Konstantin Osipov | CPU Architecture: | Any |
[2 Jul 2004 16:33]
Mark Matthews
What does the server error log say when this happens? This appears more like a bug with the server than with the JDBC driver, as nothing that the JDBC driver sends as a parameter should cause the server to crash.
[2 Jul 2004 16:47]
Daniel Blumenthal
/usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.1.3-beta' socket: '/tmp/mysql.sock' port: 3306 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=1 max_connections=100 threads_connected=1 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=0x84e7f90 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=0x41591bf8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x814cd0a 0x4002c47e 0x4207a4f3 0x80d9dbf 0x818e4a5 0x818491d 0x81878fc 0x8183efa 0x81619fe 0x81a0f7a 0x819f614 0x816140e 0x815ffa6 0x815f739 0x40026941 0x420da1ca New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/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 0x8513398 = select email from user where email like ? thd->thread_id=5 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.
[2 Jul 2004 17:13]
Daniel Blumenthal
here's the annotated trace: [root@localhost tmp]# resolve_stack_dump -s mysqld.sym -n mysqld.stack 0x814cd0a handle_segfault + 458 0x4002c47e _end + 935222030 0x4207a4f3 _end + 969096067 0x80d9dbf _ZNK10Item_field2eqEPK4Itemb + 95 0x818e4a5 _Z15remove_eq_condsP3THDP4ItemPNS1_11cond_resultE + 149 0x818491d _ZN4JOIN8optimizeEv + 141 0x81878fc _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 108 0x8183efa _Z13handle_selectP3THDP6st_lexP13select_result + 298 0x81619fe _Z21mysql_execute_commandP3THD + 782 0x81a0f7a _Z12execute_stmtP3THDP18Prepared_statementP6Stringb + 74 0x819f614 _Z18mysql_stmt_executeP3THDPcj + 308 0x816140e _Z16dispatch_command19enum_server_commandP3THDPcj + 5134 0x815ffa6 _Z10do_commandP3THD + 134 0x815f739 handle_one_connection + 905 0x40026941 _end + 935198673 0x420da1ca _end + 969488474
[2 Jul 2004 23:52]
Dean Ellis
Verified against 4.1.4 with C/J 3.1 2004-07-02 snapshot. Crash does not occur using the C API. Thank you for the report.
[28 Aug 2004 9:00]
Bill McCaffrey
If you run "set collation_connection = DEFAULT" on the connection after it is opened, this query will not crash the server in version 4.1.3
[2 Sep 2004 9:28]
Konstantin Osipov
Daniel, could you paste provide us with a SQL dump of you table? Not seeing it in the log.
[2 Sep 2004 9:34]
Konstantin Osipov
Additionally, could you follow instructions at http://www.mysql.com/doc/en/Using_stack_trace.html and send us resolved stack for the crash? In general, having a complete test case which can reproduce the problem will save us a lot of work and speedup bug processing significantly. Thank you!
[2 Sep 2004 14:29]
Daniel Blumenthal
Did you see the entry from 2 Jul 8:13am? This was made using the instructions in the file you mentioned. If this is not sufficient, or if I am misunderstanding you, then please let me know what additionally you are looking for. As for the table, I believe I reproduced it using the following table: create table foo (bar varchar(255)); Daniel
[2 Sep 2004 15:28]
Konstantin Osipov
OK, sorry, didn't notice it then.
[3 Sep 2004 7:34]
Konstantin Osipov
Daniel, I was able to create a test case for this bug in SQL syntax for prepared statements. It seems like one of your character set variables is not utf8. Apparently character_set_connection is different. Could you provide us with output of the query show variables like 'char%' ? select email from user where email REGEXP ? fails as intended. According to the standard we should give error when character sets of function arguments (user and ? as arguments of REGEXP in this case) are different. However, we realize that this is not convenient, and have fixed some functions to support arguments in different character sets, if these character sets are compatible (i.e. when looking for a latin1 string in utf8 column). However not all functions are fixed yet :), including REGEXP.
[3 Sep 2004 14:54]
Daniel Blumenthal
output to "show variables like 'char%'": +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | character_set | utf8 | | character_sets | big5 latin2_czech_ci dec8_swedish_ci cp850_general_ci latin1_german1_ci hp8_english_ci koi8r_general_ci latin1_swedish_ci latin2_general_ci swe7_swedish_ci ascii_general_ci ujis sjis cp1251_bulgarian_ci latin1_danish_ci hebrew tis620 euckr latin7_estonian_ci latin2_hungarian_ci koi8u_general_ci cp1251_ukrainian_ci gb2312 greek cp1250_general_ci latin2_croatian_ci gbk cp1257_lithuanian_ci latin5_turkish_ci latin1_german2_ci armscii8_general_ci utf8 cp1250_czech_ci ucs2 cp866_general_ci keybcs2 macce macroman cp852_general_ci latin7_general_ci latin7_general_cs macce_bin macce_ci macce_cs latin1_bin latin1_general_ci latin1_general_cs cp1251_bin cp1251_general_ci cp1251_general_cs macroman_bin macroman_ci macroman_cs cp1256_general_ci cp1257_bin cp1257_ci_ai cp1257_ci cp1257_cs binary armscii_bin ascii_bin cp1250_bin cp1256_bin cp866_bin dec8_bin greek_bin hebrew_bin hp8_bin keybcs2_bin koi8r_bin koi8u_bin latin2_bin latin5_bin latin7_bin cp850_bin cp852_bin swe7_bin utf8_bin | +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
[3 Sep 2004 15:00]
Daniel Blumenthal
****Ignore that last entry!**** That's actually from a different database (4.1.0) and doesn't have the appropriate information. This is the correct information: +--------------------------+----------------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/mysql/charsets/ | +--------------------------+----------------------------------------+ 7 rows in set (0.00 sec)
[3 Sep 2004 15:05]
Daniel Blumenthal
Sorry about that - I'm currently using 4.1.0 and had to shut that down, and start up 4.1.3 in order to perform the query you were looking for. When I encountered the problem, I remember thinking that the character_set_connection might be the problem, but it says that it's utf8 (as do the others), so that seemed like a dead end.
[3 Sep 2004 18:12]
Konstantin Osipov
bk commit - 4.1 tree (konstantin:1.1993) BUG#4368
[3 Sep 2004 20:55]
Konstantin Osipov
Fixed in 4.1.5
[1 Sep 2007 12:20]
satheesh kumar
Hi all
I am trying to... join 2 tables...I not able to comment It ...I am Getting error Like This...
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_cs,IMPLICIT) for operation '='
and one can help me how to fix it ...
[29 Dec 2009 14:36]
Mohammed Saleem
I had the same problem with Server version: 5.0.45 Source distribution and JDBC driver, I use mysql-connector-java-5.1.6-bin.jar
The problem was
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_cs,IMPLICIT)
for operation '='
I could solve it by changing the column's collate from latin1 to utf8_unicode_ci
and it works fine now.
More details:
The select statement was:
select count(*) from table where f_path = file_path;
this select was called inside a procedure, and the table definition was:
create table X{
....
file_path VARCHAR(50) character set latin1 collate latin1_general_ci,
....
KEY file_path(file_path),
})ENGINE=MyISAM DEFAULT CHARSET=utf8;
and changed the definition to
create table X{
....
file_path VARCHAR(50) collate utf8_unicode_ci,
....
KEY file_path(file_path),
})ENGINE=MyISAM DEFAULT CHARSET=utf8;
I hope it helps
Sleem
Description: The statement "select email from user where email LIKE ?" in a PreparedStatement causes an EOFException, and causes the mysql server to restart ("select email from user where email=?" works fine) It appears that this might be caused by a default character set/collation problem. The "email" column in my "user" table is defined: email varchar(100) character set utf8 collate utf8_general_ci NOT NULL default '' The statement "select email from user where email like convert(? using utf8) gives the following error: SQLException: errorCode=1267 message=General error message from server: "Illegal mix of collations (utf8_bin,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation 'like'" Furthermore, "select email from user where email REGEXP ?" gives the following curious error: SQLException: errorCode=1267 message=General error message from server: "Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'regexp'" The latin1_swedish_ci is referring to the "?", because the following statement is the only one which i can get to work: select email from user where email REGEXP convert(? using utf8) How to repeat: Here is my code: PreparedStatement pstmt = conn.prepareStatement("select email from user where email LIKE ?"); pstmt.setString(1,"yahoo"); ResultSet rset = pstmt.executeQuery(); // *** throws exception here Running this will throw the following exception: SQLException: errorCode=0 message=Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.io.EOFException STACKTRACE: java.io.EOFException at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1767) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2087) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2496) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1443) at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1239) at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:903) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1622) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205) I am using: rhat linux 8.0 jdk1.4.2 mysql 4.1.3-beta connector/j 3.1.2-alpha I compiled 4.1.3 using --with-charset=utf8. My database was created as follows: CREATE DATABASE myDB DEFAULT CHARACTER SET utf8 COLLATE utf8_bin All of my character_set_* database variables are set to utf8 by default. Suggested fix: Just looking at it, it seems like there's a problem with Connector/J not respecting the default charset and/or collation for statements with LIKE and REGEXP, when using a PreparedStatement. The code works fine when using a normal Statement. The fact that it completely crashes the server, though, seems pretty serious. Also, the fact that I compiled the database myself, using --with-charset=utf8, might have something to do with it (I'm guessing that it might not occur with latin1). Here is the configuration command I used: CFLAGS="-O3 -mcpu=pentiumpro" CXX=gcc CXXFLAGS="-O3 -mcpu=pentiumpro -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --with-charset=utf8 --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared