MySQL Bugs: #68404: Cannot use prepared statements if autoincrement is set

Bug #68404	Cannot use prepared statements if autoincrement is set
Submitted:	16 Feb 2013 20:41	Modified:	20 Mar 2013 19:44
Reporter:	Dennis Drew	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / C++	Severity:	S1 (Critical)
Version:	1.1.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If you call prepareStatement() using wild card (?,?,?) then try to bind you will have a problem if there are autoincrement fields. Of course you would not want to set the value for a autoincrement column, but if you try and skip one of them, the parser returns {_Str="Column count doesn't match value count at row 1" }. If you include all of the columns then the database write fails because you will chalange the value of the autoincrement field.

How to repeat:

DB rectape has a table with 3 columns - id (autoincrement)
duration (int) and localEntryPoint (string)

pstmt = con->prepareStatement("INSERT INTO rectape (id,duration,localEntryPoint) VALUES (?,?,?)");

pstmt->setInt(1,1);
pstmt->setInt(2,2);
pstmt->setString(3,"test")

try{
    pstmt->executeUpdate();
   }catch(sql::SQLException &e){
	strcpy( cStr1,e.what() );
	LOG4CXX_INFO(LOG.reportingLog, cStr1);
   }

>>> Fails because you are overwriting the autoincrement field <<<

pstmt = con->prepareStatement("INSERT INTO rectape (id,duration,localEntryPoint) VALUES (?,?)");

pstmt->setInt(2,2);
pstmt->setString(3,"test")

>>>preparedStatement fails because you skipped a field, there are three<<<

Suggested fix:

prepareSatement filter needs to be smart enough to recognize data fields that don't need to / should not be - written to.

Second case is correct behavior: query is invalid.

Thank you for the report.

As I wrote in previous comment, second error is not a bug, but badly written query.

Regarding to first error I can not repeat described behavior: data inserted fine unless I explicitly specify value for auto increment field which already exists in the table. Please check if this is not your case.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".