4.1.12 Prepared Statements stopped working

Hi everyone,

I've got a very strange problem with MySQL 4.1.12 and Prepared
Statements. In short:

SELECT * FROM mytable WHERE id=1

returns 1 row, as expected. However,

SELECT * FROM mytable WHERE id=?

and setting parameter 1 to 1 ("ps.setInt(1, 1);") does not return any
rows. This should not happen, am I right?

I created the table with:

CREATE TABLE mytable ( id INT PRIMARY KEY, class VARCHAR(32) )
INSERT INTO mytable ( id, class ) VALUES ( 1, 'hallo' )
INSERT INTO mytable ( id, class ) VALUES ( 2, 'hugo' )

I am issuing the statements using Connector/J 3.1.11. I have switched on
autoGenerateTestcaseScript in the driver, and the output is:

/* conn id 0 */ SET NAMES latin1;
/* conn id 0 */ SET character_set_results = NULL;
/* conn id 0 */ SHOW VARIABLES;
/* conn id 0 */ SHOW COLLATION;
/* conn id 0 */ SET autocommit=1;
/* conn id 0 */ PREPARE debug_stmt_3 FROM "select * from mytable where
id=?";
/* conn id 0 */ SET @debug_stmt_param3_0='1';
/* conn id 0 */ EXECUTE debug_stmt_3 USING @debug_stmt_param3_0;
/* conn id 0 */ DEALLOCATE PREPARE debug_stmt_3;

If I enter these commands into "mysql", it returns one row as expected.

I tried repairing the table, both with "REPAIR TABLE" and with
myisamchk. The table was ok, and the problem persisted.

The problem is independant of the JDK version, I've tried SUN JDK
1.5.0_04, 1.5.0_05 and 1.4.2_08. I also tried gij 4.0.1 (which comes
with Ubuntu Breezy).

The problem seems to be independant of the Connecter/J version, too. I
tried 3.1.9 and 3.1.11.

The problem persists if I create a completely new database (in fact I
created a new /var/lib/mysql, so there were no other databases around).

The problem does _not_ happen on MySQL 4.0.24. Downgrading is the only
workaround I found.

The problem started out of nothing. I am developing apps for MySQL 4.1
since its release. I have upgraded from Ubuntu Hoary to Breezy when
Breezy was released, but I am pretty sure that I had no problems
afterwards. I upgraded SUN JDK 1.5.0_04 to 1.5.0_05 the day the problem
started. But I tried downgrading the JDK already, and I even tried SUN
JDK 1.4.2 and gij as above. I am out of ideas.

Does anyone have any idea? Is this a known bug?

Regards,

Andreas


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: 4.1.12 Prepared Statements stopped working

Andreas,

Yes, your prepared statement works as expected in mysql client. Just checked
with 4.1.16-BK. So, if it is a bug, it is not related to server itself, but
it may be a
Connector/J 3.1.11 related bug. If you have a small Java program to repeat
it completely, please, report about this problem as a bug at
http://bugs.mysql.com.

Best regards,
--
Valeriy Kravchuk, Support Engineer
MySQL AB, Kiev, Ukraine, www.mysql.com

Are you MySQL certified? www.mysql.com/certification


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org