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 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 "ALTER PROCEDURE procname RECOMPILE;" or something like that.
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.
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 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 "ALTER PROCEDURE procname RECOMPILE;" or something like that. 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.