Alex Stetsenko

Although it might be a good time to start testing the third milestone of MySQL 5.5, I am sure many projects are still in transition between versions 5.0 and 5.1.

MySQL 5.0 to 5.1 upgrade is well documented and went pretty smoothly in our case. However our project was heavily relied on stored procedures and that gave us some troubles to deal with.

The first issue was just annoying warning “Creation context of stored routine .. is invalid” for every call of any stored routine (stored procedures and functions worked as expected).

It turned out that the upgrade to 5.1 had added four columns to the mysql.proc table:

• character_set_client,
• collation_connection,
• db_collation,
• body_utf8.

Those columns were created with a default value of NULL and that was generating the warning. But if a stored procedure/function is created in ‘native’ 5.1 environment, it has these values set to the system defaults and no warning is shown.

Manual altering the mysql.proc table and setting the value of ‘character_set_client’, ‘collation_connection’ and ‘db_collation’ to valid values prevented the error form appearing:

UPDATE proc SET
  character_set_client = 'utf8',
  collation_connection = 'utf8_general_ci',
  db_collation = 'latin1_swedish_ci';

This issue was reported as bug and marked as closed for version 5.1.21 in 2007. But it looked like it had made it back to version 5.1.4x somehow in 2010.

The second issue was more complicated and actually some stored procedures stopped working because of weird “Column count doesn’t match value count at row 1” error (which did not make much sense though).

Additional research showed that some insert queries did not work withing prepare\execute blocks due to incompatible changes in 5.1.25 to the way that the server handles prepared statements. The bug #21774 shed some light on this issue too.

For example the statement like below (within a stored procedure) may produce the error mentioned above:

DECLARE vNum INT;
 
CREATE TABLE t (f TINYINT);
 
SET vNum = 1;
SET @SQL = CONCAT('INSERT INTO t SELECT ', vNum);
 
PREPARE QUERY FROM @SQL;
EXECUTE QUERY;
DEALLOCATE PREPARE QUERY;

Simply changing vNum type from INT to TINYINT helped (so the variable type matches the column type in the table).

I hope this will save couple hours to someone unless it has been taken care of in newer MySQL releases.