Description:
Good day
Note:
a) InnoDB used for transaction database test
b) sql mode = TRADITIONAL or
ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
used
Have picked up that the database default charset affects if the SQLmode will be honored or not inside Stored Procedures. Note in 5.7 the table charset also has a bit more affect, but the same rule applies.
In the stored procedure the current session sql mode, the global sqlmode and even the procedure sql mode is totally ignored.
Basically if the database is created with default being latin1 and you create tables of different charsets and call Stored procedures to use them , the stored procedures will based on the tables used charset comply to the sql strict mode or not. In 5.7 this is also seen with ut8 as default, but will focus on 8.0 for this.
What was tested was to create a transaction inside a SP (same transaction control I used to allow calling SP inside the SP and honor the same trasnaction), then if a field is for example to long, instead of truncating it and make it fit, we want a error thrown (strict) and rollback the transaction. Based on this test weather it will or throw error (:Data too long for column 'testval' ) or create warning ( Data truncated for column 'testval') , the transaction control system fails due to the warning actually bypassing SQLError and not rolling back the transaction (the transaction control is a sub issue, the main issue is that the sql mode is not honored).
Based on the output of each SP, one can determine if the sqlmode is followed or note.
NOTE I have tested to set the session sql mode before calling the SP's and that is how I picked up global, session and procedure sql modes are ignored.
Interesting enough when one changes the default charset of database to something that works, you MUST re-create the SP's to have them see this and honor it
Thank you
Johannes Pretorius (South Africa)
How to repeat:
insure database setup is in or traditional sql mode or ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
to insure strict mode for TRANSACTIONAL Tables
Create a database with charset latin1, create tables as per the test scripts below and run the stored procedures also at bottom of scripts.
Look at output :
Warning = "Data truncated for column 'testval'"
Error = "Data too long for column 'testval' "
As we are in strict mode, we only want errors
Now delete all the tables (optional) and stored procedures, change the charset to utf8 or utf8mb4, recreate the SP's and review. Can do this between different charsets and see the result.
Notice the calling of the stored procedures at end of script, this is what is used to test the result
SQL script
================ START ========
CREATE TABLE `test_data_utf8mb4` (
`ipkID` bigint NOT NULL AUTO_INCREMENT,
`testval` varchar(5) DEFAULT NULL,
PRIMARY KEY (`ipkID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 ;
CREATE TABLE `test_data_utf8mb3` (
`ipkID` bigint NOT NULL AUTO_INCREMENT,
`testval` varchar(5) DEFAULT NULL,
PRIMARY KEY (`ipkID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 ;
CREATE TABLE `test_data_utf8` (
`ipkID` bigint NOT NULL AUTO_INCREMENT,
`testval` varchar(5) DEFAULT NULL,
PRIMARY KEY (`ipkID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 ;
CREATE TABLE `test_data_latin1` (
`ipkID` bigint NOT NULL AUTO_INCREMENT,
`testval` varchar(5) DEFAULT NULL,
PRIMARY KEY (`ipkID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 ;
CREATE TABLE `test_data` (
`ipkID` bigint NOT NULL AUTO_INCREMENT,
`testval` varchar(5) DEFAULT NULL,
PRIMARY KEY (`ipkID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 ;
DELIMITER $$
CREATE PROCEDURE `testTransWarning_utf8mb4`(in vDummy varchar(100))
BEGIN
declare vTmp varchar(120);
declare MSG varchar(128);
declare exit handler for sqlexception
begin
/*need MIN mysql 5.6.4 */
get diagnostics condition 1 MSG = message_text;
set MSG = substring(concat('[err]:',MSG),1,128);
rollback;
set @g_transaction_started = 0;
signal sqlstate '45000' set message_text = MSG;
end;
set autocommit = 0;
if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
start transaction;
set @g_transaction_started = 1;
end; else begin
set @g_transaction_started = @g_transaction_started + 1;
end; end if;
insert into test_data_utf8mb4 (testval) values ('test1');
set MSG = 'test warning';
signal sqlstate '01000' set message_text = MSG;
insert into test_data_utf8mb4 (testval) values ('test2');
/*set MSG = 'test error';
signal sqlstate '45000' set message_text = MSG;*/
set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234';
insert into test_data_utf8mb4 (testval) select vTmp;
update test_data_utf8mb4 set sKeyValue = vTmp where testval = 'test1';
if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
commit;
set @g_transaction_started = 0;
set autocommit = 1;
end; else begin
set @g_transaction_started = @g_transaction_started - 1;
end; end if;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `testTransWarning_utf8mb3`(in vDummy varchar(100))
BEGIN
declare vTmp varchar(120);
declare MSG varchar(128);
declare exit handler for sqlexception
begin
/*need MIN mysql 5.6.4 */
get diagnostics condition 1 MSG = message_text;
set MSG = substring(concat('[err]:',MSG),1,128);
rollback;
set @g_transaction_started = 0;
signal sqlstate '45000' set message_text = MSG;
end;
set autocommit = 0;
if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
start transaction;
set @g_transaction_started = 1;
end; else begin
set @g_transaction_started = @g_transaction_started + 1;
end; end if;
insert into test_data_utf8mb3 (testval) values ('test1');
set MSG = 'test warning';
signal sqlstate '01000' set message_text = MSG;
insert into test_data_utf8mb3 (testval) values ('test2');
/*set MSG = 'test error';
signal sqlstate '45000' set message_text = MSG;*/
set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234';
insert into test_data_utf8mb3 (testval) select vTmp;
update test_data_utf8mb3 set sKeyValue = vTmp where testval = 'test1';
if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
commit;
set @g_transaction_started = 0;
set autocommit = 1;
end; else begin
set @g_transaction_started = @g_transaction_started - 1;
end; end if;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `testTransWarning_utf8`(in vDummy varchar(100))
BEGIN
declare vTmp varchar(120);
declare MSG varchar(128);
declare exit handler for sqlexception
begin
/*need MIN mysql 5.6.4 */
get diagnostics condition 1 MSG = message_text;
set MSG = substring(concat('[err]:',MSG),1,128);
rollback;
set @g_transaction_started = 0;
signal sqlstate '45000' set message_text = MSG;
end;
set autocommit = 0;
if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
start transaction;
set @g_transaction_started = 1;
end; else begin
set @g_transaction_started = @g_transaction_started + 1;
end; end if;
insert into test_data_utf8 (testval) values ('test1');
set MSG = 'test warning';
signal sqlstate '01000' set message_text = MSG;
insert into test_data_utf8 (testval) values ('test2');
/*set MSG = 'test error';
signal sqlstate '45000' set message_text = MSG;*/
set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234';
insert into test_data_utf8 (testval) select vTmp;
update test_data_utf8 set sKeyValue = vTmp where testval = 'test1';
if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
commit;
set @g_transaction_started = 0;
set autocommit = 1;
end; else begin
set @g_transaction_started = @g_transaction_started - 1;
end; end if;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `testTransWarning_latin1`(in vDummy varchar(100))
BEGIN
declare vTmp varchar(120);
declare MSG varchar(128);
declare exit handler for sqlexception
begin
/*need MIN mysql 5.6.4 */
get diagnostics condition 1 MSG = message_text;
set MSG = substring(concat('[err]:',MSG),1,128);
rollback;
set @g_transaction_started = 0;
signal sqlstate '45000' set message_text = MSG;
end;
set autocommit = 0;
if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
start transaction;
set @g_transaction_started = 1;
end; else begin
set @g_transaction_started = @g_transaction_started + 1;
end; end if;
insert into test_data_latin1 (testval) values ('test1');
set MSG = 'test warning';
signal sqlstate '01000' set message_text = MSG;
insert into test_data_latin1 (testval) values ('test2');
/*set MSG = 'test error';
signal sqlstate '45000' set message_text = MSG;*/
set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234';
insert into test_data_latin1 (testval) select vTmp;
update test_data_latin1 set sKeyValue = vTmp where testval = 'test1';
if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
commit;
set @g_transaction_started = 0;
set autocommit = 1;
end; else begin
set @g_transaction_started = @g_transaction_started - 1;
end; end if;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `testTransWarning`(in vDummy varchar(100))
BEGIN
declare vTmp varchar(120);
declare MSG varchar(128);
declare exit handler for sqlexception
begin
/*need MIN mysql 5.6.4 */
get diagnostics condition 1 MSG = message_text;
set MSG = substring(concat('[err]:',MSG),1,128);
rollback;
set @g_transaction_started = 0;
signal sqlstate '45000' set message_text = MSG;
end;
set autocommit = 0;
if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
start transaction;
set @g_transaction_started = 1;
end; else begin
set @g_transaction_started = @g_transaction_started + 1;
end; end if;
insert into test_data (testval) values ('test1');
set MSG = 'test warning';
signal sqlstate '01000' set message_text = MSG;
insert into test_data (testval) values ('test2');
/*set MSG = 'test error';
signal sqlstate '45000' set message_text = MSG;*/
set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234';
insert into test_data (testval) select vTmp;
update test_data set sKeyValue = vTmp where testval = 'test1';
if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
commit;
set @g_transaction_started = 0;
set autocommit = 1;
end; else begin
set @g_transaction_started = @g_transaction_started - 1;
end; end if;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `testTransWarning_utf8mb4_tmptable`(in vDummy varchar(100))
BEGIN
declare vTmp varchar(120);
declare MSG varchar(128);
declare exit handler for sqlexception
begin
/*need MIN mysql 5.6.4 */
get diagnostics condition 1 MSG = message_text;
set MSG = substring(concat('[err]:',MSG),1,128);
rollback;
set @g_transaction_started = 0;
signal sqlstate '45000' set message_text = MSG;
end;
set autocommit = 0;
if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
start transaction;
set @g_transaction_started = 1;
end; else begin
set @g_transaction_started = @g_transaction_started + 1;
end; end if;
drop temporary table if exists tmpresult;
create temporary table tmpresult (
vaccountcode varchar(10),
iaccessionid bigint,
iaccountid bigint,
sAccessionNumber varchar(20),
imemberid bigint,
ipatientid bigint
) engine=memory;
insert into test_data_utf8mb4 (testval) values ('test1');
set MSG = 'test warning';
signal sqlstate '01000' set message_text = MSG;
insert into test_data_utf8mb4 (testval) values ('test2');
/*set MSG = 'test error';
signal sqlstate '45000' set message_text = MSG;*/
set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234';
insert into test_data_utf8mb4 (testval) select vTmp;
update test_data_utf8mb4 set sKeyValue = vTmp where testval = 'test1';
insert into tmpresult (vaccountcode) select sKeyValue from test_data_utf8mb4;
select * from tmpresult;
if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
commit;
set @g_transaction_started = 0;
set autocommit = 1;
end; else begin
set @g_transaction_started = @g_transaction_started - 1;
end; end if;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `testTransWarning_utf8_tmptable`(in vDummy varchar(100))
BEGIN
declare vTmp varchar(120);
declare MSG varchar(128);
declare vteller integer;
declare exit handler for sqlexception
begin
/*need MIN mysql 5.6.4 */
get diagnostics condition 1 MSG = message_text;
set MSG = substring(concat('[err]:',MSG),1,128);
rollback;
set @g_transaction_started = 0;
signal sqlstate '45000' set message_text = MSG;
end;
set autocommit = 0;
if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
start transaction;
set @g_transaction_started = 1;
end; else begin
set @g_transaction_started = @g_transaction_started + 1;
end; end if;
drop temporary table if exists tmpresult;
create temporary table tmpresult (
vaccountcode varchar(10),
iaccessionid bigint,
iaccountid bigint,
sAccessionNumber varchar(20),
imemberid bigint,
ipatientid bigint
) engine=memory;
insert into test_data_utf8 (testval) values ('test1');
select count(*) into vteller from test_data_utf8 ;
set MSG = 'test warning';
signal sqlstate '01000' set message_text = MSG;
insert into test_data_utf8 (testval) values ('test2');
/*set MSG = 'test error';
signal sqlstate '45000' set message_text = MSG;*/
set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234';
insert into test_data_utf8 (testval) select vTmp;
update test_data_utf8 set sKeyValue = vTmp where testval = 'test1';
insert into tmpresult (vaccountcode) select sKeyValue from test_data_utf8;
select * from tmpresult;
if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
commit;
set @g_transaction_started = 0;
set autocommit = 1;
end; else begin
set @g_transaction_started = @g_transaction_started - 1;
end; end if;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `testTransWarning_tmptable`(in vDummy varchar(100))
BEGIN
declare vTmp varchar(120);
declare MSG varchar(128);
declare exit handler for sqlexception
begin
/*need MIN mysql 5.6.4 */
get diagnostics condition 1 MSG = message_text;
set MSG = substring(concat('[err]:',MSG),1,128);
rollback;
set @g_transaction_started = 0;
signal sqlstate '45000' set message_text = MSG;
end;
set autocommit = 0;
if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
start transaction;
set @g_transaction_started = 1;
end; else begin
set @g_transaction_started = @g_transaction_started + 1;
end; end if;
drop temporary table if exists tmpresult;
create temporary table tmpresult (
vaccountcode varchar(10),
iaccessionid bigint,
iaccountid bigint,
sAccessionNumber varchar(20),
imemberid bigint,
ipatientid bigint
) engine=memory;
insert into test_data (testval) values ('test1');
set MSG = 'test warning';
signal sqlstate '01000' set message_text = MSG;
insert into test_data (testval) values ('test2');
/*set MSG = 'test error';
signal sqlstate '45000' set message_text = MSG;*/
set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234';
insert into test_data (testval) select vTmp;
update test_data set sKeyValue = vTmp where testval = 'test1';
insert into tmpresult (vaccountcode) select sKeyValue from test_data;
select * from tmpresult;
if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin
commit;
set @g_transaction_started = 0;
set autocommit = 1;
end; else begin
set @g_transaction_started = @g_transaction_started - 1;
end; end if;
END$$
DELIMITER ;
CALL `testTransWarning`('test');
CALL `testTransWarning_latin1`('test');
CALL `testTransWarning_tmptable`('test');
CALL `testTransWarning_utf8`('test');
CALL `testTransWarning_utf8_tmptable`('test');
CALL `testTransWarning_utf8mb3`('test');
CALL `testTransWarning_utf8mb4`('test');
CALL `testTransWarning_utf8mb4_tmptable`('test');
================ END ==========
Result with latin1 as default
==================================
CALL `testTransWarning_latin1`('test');
ERROR 1265 (01000): Data truncated for column 'testval' at row 1
CALL `testTransWarning_tmptable`('test');
ERROR 1265 (01000): Data truncated for column 'testval' at row 1
CALL `testTransWarning_utf8`('test');
ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1
CALL `testTransWarning_utf8_tmptable`('test');
ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1
CALL `testTransWarning_utf8mb3`('test');
ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1
CALL `testTransWarning_utf8mb4`('test');
ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1
CALL `testTransWarning_utf8mb4_tmptable`('test');
ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1
====================
result with utf8 as default
=========================
CALL `testTransWarning`('test');
ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1
CALL `testTransWarning_latin1`('test');
ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1
CALL `testTransWarning_tmptable`('test');
ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1
CALL `testTransWarning_utf8`('test');
ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1
CALL `testTransWarning_utf8_tmptable`('test');
ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1
CALL `testTransWarning_utf8mb3`('test');
ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1
CALL `testTransWarning_utf8mb4`('test');
ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1
CALL `testTransWarning_utf8mb4_tmptable`('test');
ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1
==============
Suggested fix:
Use test script (maybe expand on it, to insure temp tables dont have affect on it also) as part of test cases in future maybe or something similar
Description: Good day Note: a) InnoDB used for transaction database test b) sql mode = TRADITIONAL or ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION used Have picked up that the database default charset affects if the SQLmode will be honored or not inside Stored Procedures. Note in 5.7 the table charset also has a bit more affect, but the same rule applies. In the stored procedure the current session sql mode, the global sqlmode and even the procedure sql mode is totally ignored. Basically if the database is created with default being latin1 and you create tables of different charsets and call Stored procedures to use them , the stored procedures will based on the tables used charset comply to the sql strict mode or not. In 5.7 this is also seen with ut8 as default, but will focus on 8.0 for this. What was tested was to create a transaction inside a SP (same transaction control I used to allow calling SP inside the SP and honor the same trasnaction), then if a field is for example to long, instead of truncating it and make it fit, we want a error thrown (strict) and rollback the transaction. Based on this test weather it will or throw error (:Data too long for column 'testval' ) or create warning ( Data truncated for column 'testval') , the transaction control system fails due to the warning actually bypassing SQLError and not rolling back the transaction (the transaction control is a sub issue, the main issue is that the sql mode is not honored). Based on the output of each SP, one can determine if the sqlmode is followed or note. NOTE I have tested to set the session sql mode before calling the SP's and that is how I picked up global, session and procedure sql modes are ignored. Interesting enough when one changes the default charset of database to something that works, you MUST re-create the SP's to have them see this and honor it Thank you Johannes Pretorius (South Africa) How to repeat: insure database setup is in or traditional sql mode or ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION to insure strict mode for TRANSACTIONAL Tables Create a database with charset latin1, create tables as per the test scripts below and run the stored procedures also at bottom of scripts. Look at output : Warning = "Data truncated for column 'testval'" Error = "Data too long for column 'testval' " As we are in strict mode, we only want errors Now delete all the tables (optional) and stored procedures, change the charset to utf8 or utf8mb4, recreate the SP's and review. Can do this between different charsets and see the result. Notice the calling of the stored procedures at end of script, this is what is used to test the result SQL script ================ START ======== CREATE TABLE `test_data_utf8mb4` ( `ipkID` bigint NOT NULL AUTO_INCREMENT, `testval` varchar(5) DEFAULT NULL, PRIMARY KEY (`ipkID`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 ; CREATE TABLE `test_data_utf8mb3` ( `ipkID` bigint NOT NULL AUTO_INCREMENT, `testval` varchar(5) DEFAULT NULL, PRIMARY KEY (`ipkID`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 ; CREATE TABLE `test_data_utf8` ( `ipkID` bigint NOT NULL AUTO_INCREMENT, `testval` varchar(5) DEFAULT NULL, PRIMARY KEY (`ipkID`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 ; CREATE TABLE `test_data_latin1` ( `ipkID` bigint NOT NULL AUTO_INCREMENT, `testval` varchar(5) DEFAULT NULL, PRIMARY KEY (`ipkID`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 ; CREATE TABLE `test_data` ( `ipkID` bigint NOT NULL AUTO_INCREMENT, `testval` varchar(5) DEFAULT NULL, PRIMARY KEY (`ipkID`) ) ENGINE=InnoDB AUTO_INCREMENT=5 ; DELIMITER $$ CREATE PROCEDURE `testTransWarning_utf8mb4`(in vDummy varchar(100)) BEGIN declare vTmp varchar(120); declare MSG varchar(128); declare exit handler for sqlexception begin /*need MIN mysql 5.6.4 */ get diagnostics condition 1 MSG = message_text; set MSG = substring(concat('[err]:',MSG),1,128); rollback; set @g_transaction_started = 0; signal sqlstate '45000' set message_text = MSG; end; set autocommit = 0; if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin start transaction; set @g_transaction_started = 1; end; else begin set @g_transaction_started = @g_transaction_started + 1; end; end if; insert into test_data_utf8mb4 (testval) values ('test1'); set MSG = 'test warning'; signal sqlstate '01000' set message_text = MSG; insert into test_data_utf8mb4 (testval) values ('test2'); /*set MSG = 'test error'; signal sqlstate '45000' set message_text = MSG;*/ set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234'; insert into test_data_utf8mb4 (testval) select vTmp; update test_data_utf8mb4 set sKeyValue = vTmp where testval = 'test1'; if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin commit; set @g_transaction_started = 0; set autocommit = 1; end; else begin set @g_transaction_started = @g_transaction_started - 1; end; end if; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `testTransWarning_utf8mb3`(in vDummy varchar(100)) BEGIN declare vTmp varchar(120); declare MSG varchar(128); declare exit handler for sqlexception begin /*need MIN mysql 5.6.4 */ get diagnostics condition 1 MSG = message_text; set MSG = substring(concat('[err]:',MSG),1,128); rollback; set @g_transaction_started = 0; signal sqlstate '45000' set message_text = MSG; end; set autocommit = 0; if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin start transaction; set @g_transaction_started = 1; end; else begin set @g_transaction_started = @g_transaction_started + 1; end; end if; insert into test_data_utf8mb3 (testval) values ('test1'); set MSG = 'test warning'; signal sqlstate '01000' set message_text = MSG; insert into test_data_utf8mb3 (testval) values ('test2'); /*set MSG = 'test error'; signal sqlstate '45000' set message_text = MSG;*/ set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234'; insert into test_data_utf8mb3 (testval) select vTmp; update test_data_utf8mb3 set sKeyValue = vTmp where testval = 'test1'; if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin commit; set @g_transaction_started = 0; set autocommit = 1; end; else begin set @g_transaction_started = @g_transaction_started - 1; end; end if; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `testTransWarning_utf8`(in vDummy varchar(100)) BEGIN declare vTmp varchar(120); declare MSG varchar(128); declare exit handler for sqlexception begin /*need MIN mysql 5.6.4 */ get diagnostics condition 1 MSG = message_text; set MSG = substring(concat('[err]:',MSG),1,128); rollback; set @g_transaction_started = 0; signal sqlstate '45000' set message_text = MSG; end; set autocommit = 0; if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin start transaction; set @g_transaction_started = 1; end; else begin set @g_transaction_started = @g_transaction_started + 1; end; end if; insert into test_data_utf8 (testval) values ('test1'); set MSG = 'test warning'; signal sqlstate '01000' set message_text = MSG; insert into test_data_utf8 (testval) values ('test2'); /*set MSG = 'test error'; signal sqlstate '45000' set message_text = MSG;*/ set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234'; insert into test_data_utf8 (testval) select vTmp; update test_data_utf8 set sKeyValue = vTmp where testval = 'test1'; if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin commit; set @g_transaction_started = 0; set autocommit = 1; end; else begin set @g_transaction_started = @g_transaction_started - 1; end; end if; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `testTransWarning_latin1`(in vDummy varchar(100)) BEGIN declare vTmp varchar(120); declare MSG varchar(128); declare exit handler for sqlexception begin /*need MIN mysql 5.6.4 */ get diagnostics condition 1 MSG = message_text; set MSG = substring(concat('[err]:',MSG),1,128); rollback; set @g_transaction_started = 0; signal sqlstate '45000' set message_text = MSG; end; set autocommit = 0; if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin start transaction; set @g_transaction_started = 1; end; else begin set @g_transaction_started = @g_transaction_started + 1; end; end if; insert into test_data_latin1 (testval) values ('test1'); set MSG = 'test warning'; signal sqlstate '01000' set message_text = MSG; insert into test_data_latin1 (testval) values ('test2'); /*set MSG = 'test error'; signal sqlstate '45000' set message_text = MSG;*/ set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234'; insert into test_data_latin1 (testval) select vTmp; update test_data_latin1 set sKeyValue = vTmp where testval = 'test1'; if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin commit; set @g_transaction_started = 0; set autocommit = 1; end; else begin set @g_transaction_started = @g_transaction_started - 1; end; end if; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `testTransWarning`(in vDummy varchar(100)) BEGIN declare vTmp varchar(120); declare MSG varchar(128); declare exit handler for sqlexception begin /*need MIN mysql 5.6.4 */ get diagnostics condition 1 MSG = message_text; set MSG = substring(concat('[err]:',MSG),1,128); rollback; set @g_transaction_started = 0; signal sqlstate '45000' set message_text = MSG; end; set autocommit = 0; if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin start transaction; set @g_transaction_started = 1; end; else begin set @g_transaction_started = @g_transaction_started + 1; end; end if; insert into test_data (testval) values ('test1'); set MSG = 'test warning'; signal sqlstate '01000' set message_text = MSG; insert into test_data (testval) values ('test2'); /*set MSG = 'test error'; signal sqlstate '45000' set message_text = MSG;*/ set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234'; insert into test_data (testval) select vTmp; update test_data set sKeyValue = vTmp where testval = 'test1'; if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin commit; set @g_transaction_started = 0; set autocommit = 1; end; else begin set @g_transaction_started = @g_transaction_started - 1; end; end if; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `testTransWarning_utf8mb4_tmptable`(in vDummy varchar(100)) BEGIN declare vTmp varchar(120); declare MSG varchar(128); declare exit handler for sqlexception begin /*need MIN mysql 5.6.4 */ get diagnostics condition 1 MSG = message_text; set MSG = substring(concat('[err]:',MSG),1,128); rollback; set @g_transaction_started = 0; signal sqlstate '45000' set message_text = MSG; end; set autocommit = 0; if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin start transaction; set @g_transaction_started = 1; end; else begin set @g_transaction_started = @g_transaction_started + 1; end; end if; drop temporary table if exists tmpresult; create temporary table tmpresult ( vaccountcode varchar(10), iaccessionid bigint, iaccountid bigint, sAccessionNumber varchar(20), imemberid bigint, ipatientid bigint ) engine=memory; insert into test_data_utf8mb4 (testval) values ('test1'); set MSG = 'test warning'; signal sqlstate '01000' set message_text = MSG; insert into test_data_utf8mb4 (testval) values ('test2'); /*set MSG = 'test error'; signal sqlstate '45000' set message_text = MSG;*/ set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234'; insert into test_data_utf8mb4 (testval) select vTmp; update test_data_utf8mb4 set sKeyValue = vTmp where testval = 'test1'; insert into tmpresult (vaccountcode) select sKeyValue from test_data_utf8mb4; select * from tmpresult; if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin commit; set @g_transaction_started = 0; set autocommit = 1; end; else begin set @g_transaction_started = @g_transaction_started - 1; end; end if; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `testTransWarning_utf8_tmptable`(in vDummy varchar(100)) BEGIN declare vTmp varchar(120); declare MSG varchar(128); declare vteller integer; declare exit handler for sqlexception begin /*need MIN mysql 5.6.4 */ get diagnostics condition 1 MSG = message_text; set MSG = substring(concat('[err]:',MSG),1,128); rollback; set @g_transaction_started = 0; signal sqlstate '45000' set message_text = MSG; end; set autocommit = 0; if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin start transaction; set @g_transaction_started = 1; end; else begin set @g_transaction_started = @g_transaction_started + 1; end; end if; drop temporary table if exists tmpresult; create temporary table tmpresult ( vaccountcode varchar(10), iaccessionid bigint, iaccountid bigint, sAccessionNumber varchar(20), imemberid bigint, ipatientid bigint ) engine=memory; insert into test_data_utf8 (testval) values ('test1'); select count(*) into vteller from test_data_utf8 ; set MSG = 'test warning'; signal sqlstate '01000' set message_text = MSG; insert into test_data_utf8 (testval) values ('test2'); /*set MSG = 'test error'; signal sqlstate '45000' set message_text = MSG;*/ set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234'; insert into test_data_utf8 (testval) select vTmp; update test_data_utf8 set sKeyValue = vTmp where testval = 'test1'; insert into tmpresult (vaccountcode) select sKeyValue from test_data_utf8; select * from tmpresult; if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin commit; set @g_transaction_started = 0; set autocommit = 1; end; else begin set @g_transaction_started = @g_transaction_started - 1; end; end if; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `testTransWarning_tmptable`(in vDummy varchar(100)) BEGIN declare vTmp varchar(120); declare MSG varchar(128); declare exit handler for sqlexception begin /*need MIN mysql 5.6.4 */ get diagnostics condition 1 MSG = message_text; set MSG = substring(concat('[err]:',MSG),1,128); rollback; set @g_transaction_started = 0; signal sqlstate '45000' set message_text = MSG; end; set autocommit = 0; if ((@g_transaction_started = 0) or (@g_transaction_started is null)) then begin start transaction; set @g_transaction_started = 1; end; else begin set @g_transaction_started = @g_transaction_started + 1; end; end if; drop temporary table if exists tmpresult; create temporary table tmpresult ( vaccountcode varchar(10), iaccessionid bigint, iaccountid bigint, sAccessionNumber varchar(20), imemberid bigint, ipatientid bigint ) engine=memory; insert into test_data (testval) values ('test1'); set MSG = 'test warning'; signal sqlstate '01000' set message_text = MSG; insert into test_data (testval) values ('test2'); /*set MSG = 'test error'; signal sqlstate '45000' set message_text = MSG;*/ set vTmp = '22345r45423145235tswdfgdg34tge4q31t56b34q5t6dovfigju304fj4fgj 04tufg340fju40fj34v043gj3042342342343234'; insert into test_data (testval) select vTmp; update test_data set sKeyValue = vTmp where testval = 'test1'; insert into tmpresult (vaccountcode) select sKeyValue from test_data; select * from tmpresult; if ((@g_transaction_started = 1) or (@g_transaction_started = 0) or (@g_transaction_started is null)) then begin commit; set @g_transaction_started = 0; set autocommit = 1; end; else begin set @g_transaction_started = @g_transaction_started - 1; end; end if; END$$ DELIMITER ; CALL `testTransWarning`('test'); CALL `testTransWarning_latin1`('test'); CALL `testTransWarning_tmptable`('test'); CALL `testTransWarning_utf8`('test'); CALL `testTransWarning_utf8_tmptable`('test'); CALL `testTransWarning_utf8mb3`('test'); CALL `testTransWarning_utf8mb4`('test'); CALL `testTransWarning_utf8mb4_tmptable`('test'); ================ END ========== Result with latin1 as default ================================== CALL `testTransWarning_latin1`('test'); ERROR 1265 (01000): Data truncated for column 'testval' at row 1 CALL `testTransWarning_tmptable`('test'); ERROR 1265 (01000): Data truncated for column 'testval' at row 1 CALL `testTransWarning_utf8`('test'); ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1 CALL `testTransWarning_utf8_tmptable`('test'); ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1 CALL `testTransWarning_utf8mb3`('test'); ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1 CALL `testTransWarning_utf8mb4`('test'); ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1 CALL `testTransWarning_utf8mb4_tmptable`('test'); ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1 ==================== result with utf8 as default ========================= CALL `testTransWarning`('test'); ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1 CALL `testTransWarning_latin1`('test'); ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1 CALL `testTransWarning_tmptable`('test'); ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1 CALL `testTransWarning_utf8`('test'); ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1 CALL `testTransWarning_utf8_tmptable`('test'); ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1 CALL `testTransWarning_utf8mb3`('test'); ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1 CALL `testTransWarning_utf8mb4`('test'); ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1 CALL `testTransWarning_utf8mb4_tmptable`('test'); ERROR 1644 (45000): [err]:Data too long for column 'testval' at row 1 ============== Suggested fix: Use test script (maybe expand on it, to insure temp tables dont have affect on it also) as part of test cases in future maybe or something similar