MySQL Bugs: #62455: ALTER SCHEMA or TABLE needs to recompile associated routines

Bug #62455	ALTER SCHEMA or TABLE needs to recompile associated routines
Submitted:	16 Sep 2011 22:13	Modified:	29 Sep 2011 16:57
Reporter:	denixx baykin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5.15.0	OS:	Any (Win XP SP3)
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, charsets, routines

Description:
I run server as C:\mysql-5.5.15-win32\bin\mysqld.exe --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

I create schema with utf8 charset, then table with one field VARCHAR(50) utf8, then stored procedure where i do simple insertion.
Then I run test java-code where I insert 4byte utf8 symbols with stored procedure and retrieve exception:
java.sql.SQLException: Incorrect string value: '\xF0\x9D\x90\x96\xE2\x9C...' for column '_testValue' at row 9
It's ok for utf8.

After that I alter those things in DB to utf8mb4 but still retrieve exception.
Then I drop and create again a stored procedure and java program works without exception.

How to repeat:
Run these SQL code in Workbench:

CREATE SCHEMA `test` DEFAULT CHARACTER SET utf8 ;

CREATE  TABLE `test`.`testutf8mb4` (
  `testValue` VARCHAR(50) NOT NULL ,
  PRIMARY KEY (`testValue`) );

USE `test`;
DROP procedure IF EXISTS `testRecord`;
DELIMITER $$
USE `test`$$
CREATE PROCEDURE `testRecord`(
    _testValue VARCHAR(50)
)
BEGIN
IF EXISTS (select 1 from `test`.`testutf8mb4` where testValue = _testValue)
THEN 
select CONCAT('testValue: ', _testValue, ' already exists.');
ELSE INSERT INTO `test`.`testutf8mb4` (
        `testValue`
    ) VALUES (
        _testValue
    );
select CONCAT('testValue: ', _testValue, ' Record added.');
END IF;
END
$$
DELIMITER ;

After that run the attached program. It will show the exception.

After that run the SQL code in Workbench:

ALTER SCHEMA `test`  DEFAULT CHARACTER SET utf8mb4  DEFAULT COLLATE utf8mb4_unicode_ci ;
ALTER TABLE `test`.`testutf8mb4` CHARACTER SET = utf8mb4 , COLLATE = utf8mb4_unicode_ci ;
ALTER TABLE `test`.`testutf8mb4` CHANGE COLUMN `testValue` `testValue` VARCHAR(50) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL  ;

Then run the attached program again.
it will show the exception again.

After that run the SQL code in Workbench:

USE `test`;
DROP procedure IF EXISTS `testRecord`;
DELIMITER $$
USE `test`$$
CREATE PROCEDURE `testRecord`(
    _testValue VARCHAR(50)
)
BEGIN
IF EXISTS (select 1 from `test`.`testutf8mb4` where testValue = _testValue)
THEN 
select CONCAT('testValue: ', _testValue, ' already exists.');
ELSE INSERT INTO `test`.`testutf8mb4` (
        `testValue`
    ) VALUES (
        _testValue
    );
select CONCAT('testValue: ', _testValue, ' Record added.');
END IF;
END
$$
DELIMITER ;

Then run the program again. It will work without error.

Here is java program.

Attachment: Test10_utf8mb4_3.java (application/octet-stream, text), 2.51 KiB.

If you want to repeat this again, you need to drop the schema and restart server.

Why do you expect that ALTER SCHEMA should change collation for routines or variables inside routines in that schema? Our manual, http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html, says:

"For character data types, if there is a CHARACTER SET attribute in the declaration, the specified character set and its default collation is used. If the COLLATE attribute is also present, that collation is used rather than the default collation. If there is no CHARACTER SET attribute, the database character set and collation in effect at routine creation time are used."

I think this is not a bug.

Ok, thats a "feature".

Then how I can quickly update the procedure without the source?
Only with
SHOW CREATE PROCEDURE test.testRecord;
Copy the contents of Create Procedure field.
Make DROP PROCEDURE test.testRecord;
Then make a paste the contents of a Create Procedure field, and does not forget about DELIMITER $$.
Execute this and at last get an working stored procedure.

I think there is too much to do manually.
There is a need in the command RECOMPILE PROCEDURE procname;

And documentation.
When people are faced with the problem, they are looking for a solution in the documentation.
For example, I searched in google before I realized that the problem is in the stored procedure.
I thought I was to blame ALTER TABLE in a malfunction.

Documentation needs an updates:
http://dev.mysql.com/doc/refman/5.5/en/alter-table.html
Here insert a note that charset change not affect stored procedures ant they need a recompilation somewhere after the "If you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set".

http://dev.mysql.com/doc/refman/5.5/en/charset-applications.html
Here add the warning link, leading to ALTER TABLE page, where problem is described.

I think then google will be more helpful after indexing these two pages :)

Let's consider this a valid documentation request. We should say explicitly that procedures need recompilation.

Feel free to open new feature request for ALTER PROCEDURE ... RECOMPILE or something like that.

Where can I do a feature request?
Here, in MySQL bugs tracker?

Yes, just set severity 4 for a new bug and it will be treated as a feature request.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

This is not an ALTER TABLE issue. Instead, I have added a note to the ALTER DATABASE, CREATE PROCEDURE, and charset-applications pages about it.