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:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.0.6 OS:Windows
Assigned to: CPU Architecture:Any

[7 Jun 2007 18:46] Joe Simone
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.
[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.