Bug #104035 Strict SQLMode not honored in SP's based on DB charset
Submitted: 16 Jun 2021 10:50 Modified: 16 Jun 2021 20:21
Reporter: Johannes Pretorius Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version: 8.0/5.7 OS:Ubuntu (20)
Assigned to: CPU Architecture:Any
Tags: charset, SQL_MODE

[16 Jun 2021 10:50] Johannes Pretorius
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
[16 Jun 2021 20:21] MySQL Verification Team
Thank you for the bug report.