| Bug #14087 | Illegal collation error | ||
|---|---|---|---|
| Submitted: | 17 Oct 2005 22:07 | Modified: | 22 Oct 2005 19:40 |
| Reporter: | Nagy Elemer Karoly | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S1 (Critical) |
| Version: | 3.1.11 broken 3.0.17 working | OS: | Linux (Debian Linux Etch) |
| Assigned to: | CPU Architecture: | Any | |
[18 Oct 2005 7:41]
Vasily Kishkin
Could you please say me values of: AD[Configuration.TitleAbstractIndex].Length AD[Configuration.AuthorAbstractIndex].Length Configuration.SubOrderIDLength
[18 Oct 2005 8:21]
Nagy Elemer Karoly
show create table orders:
CREATE TABLE `orders` (
`UID` bigint(20) NOT NULL auto_increment,
`READER_ID` varchar(12) NOT NULL default '',
`ADM_SYSNO` varchar(9) NOT NULL default '',
`ITEM_NO` varchar(6) NOT NULL default '',
`REQUEST_STATE` tinyint(4) default NULL,
`REQUEST_IP` varchar(63) default NULL,
`PREPARE_TIME` decimal(12,0) default NULL,
`ORDER_TIME` decimal(12,0) default NULL,
`PROCESS_TIME` decimal(12,0) default NULL,
`TRANSFER_TIME` decimal(12,0) default NULL,
`READY_TIME` decimal(12,0) default NULL,
`LOAN_TIME` decimal(12,0) default NULL,
`DELETE_TIME` decimal(12,0) default NULL,
`COMING_AT` decimal(12,0) default NULL,
`PROCESSED_BY` varchar(16) default NULL,
`TITLE_CACHE` varchar(64) default NULL,
`AUTHOR_CACHE` varchar(64) default NULL,
`LOCATION_CACHE` varchar(128) default NULL,
`ORDER_ID` int(11) default NULL,
`SUBORDER_ID` varchar(3) default NULL,
PRIMARY KEY (`UID`),
KEY `REQUEST_STATE_INDEX` (`REQUEST_STATE`),
KEY `READER_ID_REQUEST_STATE_INDEX` (`READER_ID`,`REQUEST_STATE`),
KEY `METADATA` (`READER_ID`,`ADM_SYSNO`,`ITEM_NO`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
The failed query is (it should not touch the *_CACHE columns):
PSs[2] = C.prepareStatement("select count(*) from Orders where READER_ID=? and "+
" ADM_SYSNO=? and ITEM_NO=? and not REQUEST_STATE<2");
Java code is available on (Configuration.java uses other files):
https://webmail.omikk.bme.hu/~eknagy/cinege-0.0.4.7.676_docsless.jar
(MD5: 55a59f0b06345a6f17a016f77e83dc8b)
Thank you for the fast reply
[22 Oct 2005 19:40]
Nagy Elemer Karoly
I found a leftover db.opt from a backup or previous version or another sql machine that stated latin1 colletion. Deleteing it solved the problem. The different behaviour of 3.0.17 and 3.1.11 is still puzzling, especially because 3.0.17 was working great. Sorry for bothering you because of my unobservance.
Description: I'm trying to connect to a server (4.1.14-Debian_6-log) with default-character-set =utf8 and JDBC_URL =jdbc:mysql://localhost:3306/cinege?useUnicode=true&characterEncoding=UTF-8 (jre: 1.5.0_04-b05) while the table is created with "create table if not exists Orders " + " (UID BIGINT not null auto_increment primary key, "+" READER_ID char(12) not null, ADM_SYSNO char(9) not null, "+ " ITEM_NO char(6) not null, REQUEST_STATE TINYINT, "+ " REQUEST_IP varchar("+(16*3+15)+"), PREPARE_TIME decimal(12, 0), "+ " ORDER_TIME decimal(12, 0), PROCESS_TIME decimal(12, 0), "+ " TRANSFER_TIME decimal(12, 0), READY_TIME decimal(12, 0), "+ " LOAN_TIME decimal(12, 0), DELETE_TIME decimal(12, 0), "+ " COMING_AT decimal(12, 0), PROCESSED_BY char(16), "+ " TITLE_CACHE varchar("+ AD[Configuration.TitleAbstractIndex].Length + "), AUTHOR_CACHE varchar("+ AD[Configuration.AuthorAbstractIndex].Length + "), LOCATION_CACHE varchar(128), ORDER_ID integer, "+ " SUBORDER_ID char("+Configuration.SubOrderIDLength + "), index REQUEST_STATE_INDEX (REQUEST_STATE), "+ " index READER_ID_REQUEST_STATE_INDEX (READER_ID, REQUEST_STATE), "+ " index METADATA (READER_ID,ADM_SYSNO, ITEM_NO))"); Connector/J 3.0.17 works fine but 3.1.11 (and I think 3.1.10) is broken and kills the query with an exception, making the data unaccessable. Adding "character set utf8" to the end of the query solves the problem but should be redundant as the server is set to utf-8. I am afraid this is a regression because it seems to works fine in 3.0.17 and may be connected with the "illegal collation" bug which is a MySQL-killer phrase on forums. Google has 122000 hits :( How to repeat: See above or email and I'll send you 100 KB code :) Suggested fix: Recommend 3.0 for 4.1 and 3.1 for 5.0?