Mysql bug parade

Ok, just to tell the truth this is not a real bug parade, but today I’m struggling with many different bugs. The worst thing is that each time you spend a lot of time trying to figure out what is wrong in your code. So when there is a bug in something like mysql the first step is a review of your own code. After loosing time in trying to find the bug you reach the point where the assertion “my code is correct” is true.

What next? Google and spend some hours googling. Finally in a remote ranked page at position 232 of the google result you find a response of an anonymous developer who answers in the year 2009 to  question posted in year 2005. The solution proposed seems very silly. Absurd. But you are desperate. So you try the solution and…IT WORKS!!!

 

  • Alter table errno:150 [solved]

alter table failure add index FKBF3C318AB88FBECE (idsent),add constraint FKBF3C318AB88FBECE foreign key (idsent) references sent_mail (idsent);

raises an error:  Can’t create table ‘mercurius.#sql-61c_4a’ (errno: 150)
solution:
set foreign_key_checks = 0 ;
alter table failure add index FKBF3C318AB88FBECE (idsent),add constraint FKBF3C318AB88FBECE foreign key (idsent) references sent_mail (idsent);
set foreign_key_checks = 1;
Works!
  • Alter table errno:150 [unsolved]
Same issue as above: alter table QRTZ_JOB_LISTENERS add index FK2E3C7E25EC364202 (JOB_GROUP, JOB_NAME),
add constraint FK2E3C7E25EC364202 foreign key (JOB_GROUP, JOB_NAME) references QRTZ_JOB_DETAILS (JOB_GROUP, JOB_NAME);
raises an error:  Can’t create table ‘mercurius.#sql-61c_4a’ (errno: 150)
same solution as above doesn’t work!
  • Insert and incorrect mysqld_stmt_execute
A standard prepared statement
insert into … values(?,?,?)… raises an exception.
java.lang.RuntimeException: Unable to continue: Error while commiting the transaction
which is caused by a
Caused by: java.sql.BatchUpdateException: Incorrect arguments to mysqld_stmt_execute
adding the parameter useServerPrepStmts=false to the connection works:
jdbc:mysql://localhost:3306/mercurius?useServerPrepStmts=false