| Bug #28956 | Regression bug in Connector/J 5.0.6 when passing query parameters | ||
|---|---|---|---|
| Submitted: | 7 Jun 2007 18:46 | Modified: | 18 Jul 2007 9:43 |
| Reporter: | Joe Simone | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 5.0.6 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[15 Jun 2007 9:32]
Tonci Grgin
Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php If you can provide more information, feel free to add it to this bug and change the status back to 'Open'. Thank you for your interest in MySQL. Explanation: Hi Joe and thanks for your report. I will need to see server's general query log (relevant part) at the time error occur attached. Also, please attach full test case containing DDL and DML statements for tables in question. What is your JDK/JRE version? Does latest driver, http://downloads.mysql.com/snapshots/mysql-connector-java-5.0/mysql-connector-java-5.0-nig..., have the same problem?
[15 Jun 2007 16:40]
Joe Simone
I downloaded the latest driver as described but the problem still remains. I'll provide you with some DDL to reproduce the problem as time permits.
[15 Jun 2007 20:42]
Tonci Grgin
Thanks Joe, waiting on your info.
[26 Jun 2007 17:14]
Joe Simone
# The above failing query is based upon these following 4 tables and fails whether or not any results are returned. # DB is InnoDB/utf8 CREATE TABLE event ( id varchar(50) primary key not null, name varchar(250) not null, description text not null, notificationFrom varchar(100) not null, #housingDescription text default null, #travelDescription text default null, #locationDescription text default null, preRegistrationDescription text default null, uninvitedContentDescription text default null, #postRegistrationDescription text default null, locales varchar(250) not null default 'en', alias varchar(20) not null, resourcelink varchar(50) not null, # flags ... enableregistration tinyint not null default 0, enableenrollment tinyint not null default 0, enablelogin tinyint not null default 1, enablematerialsview tinyint not null default 0, enable_bar_code tinyint not null default 0, enableVenueDrillDown tinyint not null default 1, showsearch tinyint not null default 1, showactivities tinyint not null default 1, showactivitiesbyday tinyint not null default 1, showactivitiesbycategory tinyint not null default 1, showactivitiesbycategoryday tinyint not null default 1, showactivitiesbyvenueday tinyint not null default 1, showactivitiesbyspeaker tinyint not null default 1, startdate varchar(12) not null, starttime varchar(12), enddate varchar(12) not null, endtime varchar(12), formattedenddate varchar(50) not null, formattedstartdate varchar(50) not null, duration integer unsigned not null, bar_code_type varchar(50), bar_code_width integer not null default 0, bar_code_height integer not null default 0, status varchar(50) not null, testing_email_address varchar(60), location text not null, passwordtype varchar(50) not null, useridtype varchar(50) not null, preferreddateformat varchar(50), preferredtimeformat varchar(50), currencysymbol varchar(50), currencytype varchar(50), #regearlydate datetime, #reglatedate datetime, #regregulardate datetime, version integer unsigned not null default 0, created datetime not null, createdby varchar(60) not null, updated datetime, updatedby varchar(60), # FKs conference_id varchar(50), uninvitedlist_id varchar(50) default null, )ENGINE=InnoDB ; CREATE TABLE person ( id varchar(50) primary key not null, prefixname varchar(20) default null, prefixname2 varchar(20) default null, firstname varchar(100) not null, middlename varchar(50) default null, lastname varchar(100) not null, suffixname varchar(20) default null, bio text, emailaddressalternate varchar(60), localepref char(5) not null default 'en_US', cancontact tinyint unsigned default null, canshare tinyint unsigned default null, ismanager tinyint unsigned default null, isvip tinyint unsigned default null, companycountry varchar(100), companydepartment varchar(100), companydivision varchar(100), companyjobtitle varchar(100), companymanagerserial varchar(50), companyname varchar(100), companyserial varchar(50), companymanagerfirstname varchar(50) default null, companymanagerlastname varchar(50) default null, companymanageremailaddress varchar(60) default null, companyjobtitleother varchar(50) default null, mailingaddress1 varchar(100), mailingaddress2 varchar(100), mailingcity varchar(100), mailingstate varchar(100), mailingzipcode varchar(50), mailingcountry varchar(100), mailingaddresstype varchar(50) default null, mailingprovince varchar(50) default null, memberid varchar(50), phonenumber varchar(50), phonenumberhome varchar(50), phonenumbermobile varchar(50), faxnumber varchar(50), registeredbyemailaddress varchar(60), registeredbyname varchar(100), registeredbyserial varchar(50), registrationstatus varchar(50) not null, registrationtype varchar(50) not null, guestnames text default null, version integer unsigned not null default 0, created datetime not null, createdby varchar(60) not null, updated datetime, updatedby varchar(60), # FKs ... systemuser_uid varchar(60), event_id varchar(50), person_id varchar(50) default null, regtype_id varchar(50) default null, # Lucene indexing: 0=not indexed, 1=indexed indexed tinyint unsigned not null default 0 )ENGINE=InnoDB ; CREATE TABLE housing ( id varchar(50) primary key not null, gender char(2) default null, isneeded tinyint unsigned default null, issmoker tinyint unsigned default null, hotelconfirmationcode varchar(50) default null, hotelbookingstatus varchar(50), hotelbedsrequested integer unsigned default null, hotelbedconfig varchar(50) default null, plannedcheckindate date default null, plannedcheckoutdate date default null, planneddurationdays integer unsigned default null, actualcheckindate date default null, actualcheckoutdate date default null, actualdurationdays integer unsigned default null, prefroommatefirstname varchar(50), prefroommatelastname varchar(50), prefroommateserial varchar(20), specialdietary varchar(50), specialmedical varchar(50), specialneeds text default null, policy varchar(50) default null, roomtype varchar(50) default null, roomclass varchar(50) default null, specialdietaryother text default null, specialmedicalother text default null, roommaterequestedemail varchar(60) default null, roommateassignedemail varchar(60) default null, custom1 varchar(50) default null, custom2 varchar(50) default null, custom3 varchar(50) default null, custom4 varchar(50) default null, custom5 varchar(50) default null, version integer unsigned not null default 0, created datetime not null, createdby varchar(60) not null, updated datetime default null, updatedby varchar(60) default null, # FKs venuepref1_id varchar(50), venuepref2_id varchar(50), stay_id varchar(50), person_id varchar(50) )ENGINE=InnoDB ; CREATE TABLE history ( id varchar(50) primary key not null, `by` varchar(100) not null, `when` datetime not null, `key` varchar(50) not null, field varchar(50), version integer unsigned not null, object varchar(50) not null, type varchar(5) not null, value text, comment varchar(250) )ENGINE=InnoDB ;
[28 Jun 2007 13:57]
Tonci Grgin
Joe, I will need to see complete test case attached to this report. I can not repeat your problems with mine. What I can say for now is this: are you calling SetString (or whatever) with index of 0?
[28 Jun 2007 15:00]
Mark Matthews
I can verify this bug because while fixing another bug I'm staring right at the line of code that causes this bug, so I'll fix it now.
[4 Jul 2007 13:41]
Joe Simone
Do you guys need anything from me? I assume you know what the problem is and have fixed it.
[5 Jul 2007 7:19]
Tonci Grgin
Joe, if Mark says he'll fix it then I consider this report closed. Thanks for your interest in MySQL.
[18 Jul 2007 9:43]
MC Brown
This has been documented in the 5.0.7 changelog: Parser in client-side prepared statements runs to end of statement, rather than end-of-line for '#' comments. Also added support for '--' single-line comments.
Description: The error is : Database or query error. Details: Parameter index out of range (1 > number of parameters, which is 0). Query worked with all previous version of connector/J. Does not work with 5.0.6 so I had to go back to 5.0.5 which works. Note: A simple query with a single replacement parm like ... select e.name from event e where e.id = ? Works. While the query below (How To Repeat section) does not. Fails on server version 5.0.37 and 5.0.41. How to repeat: #Failing Query select p.lastname, p.firstname, h1.`field` , h1.value "Previous Value", #h1.version "v1", #h1.`when` "fromDate", h2.value "New Value", #h2.version "v2", h2.by "By", substr(h2.`when`,1,19) "When" from e2m.event e, e2m.person p, e2m.housing hou, history h1 left join history h2 on h1.version < h2.version and h1.version = (select max(t.version) from history t where t.`field` = h1.`field` and t.version < h2.version and t.`key` = h1.`key`) and h1.`field` = h2.`field` and # "from" and "to" always name the same field h1.`value` != h2.`value` # values are different where e.id = ? AND p.event_id = e.id AND hou.person_id = p.id AND h1.`key` = hou.id AND h2.`key` = hou.id and h1.`field` != 'HOTEL_BOOKING_STATUS' order by p.lastname, p.firstname, h1.`field`, h1.`when`, h2.`when` Suggested fix: This problem has only appeared with 5.0.6. No problems were seen with 5.0.5, 5.0.4, 5.0.3 etc.