Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.6.16
-
None
-
None
Description
We are attempting to "lossily" copy the contents of a UTF8 value to a latin1 column. The losses and conversions to question-mark placeholders are acceptable.
MariaDB [(none)]> select @@version;
|
+---------------------+
|
| @@version |
|
+---------------------+
|
| 10.6.16-MariaDB-log |
|
+---------------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [phils_test]> show create table phils_test.t5;
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table
|
|
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t5 | CREATE TABLE `t5` (
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
`c2` varchar(256) NOT NULL,
|
PRIMARY KEY (`c1`)
|
) ENGINE=InnoDB AUTO_INCREMENT=112341240 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci `ENCRYPTION_KEY_ID`=3 |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.003 sec)
|
|
|
MariaDB [phils_test]> show create table phils_test.t6;
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table
|
|
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t6 | CREATE TABLE `t6` (
|
`c1` int(11) NOT NULL DEFAULT 0,
|
`c2` varchar(256) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
|
`c3` varchar(256) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci `ENCRYPTION_KEY_ID`=3 |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
delimiter $$
|
|
|
create or replace trigger name1
|
after insert
|
on phils_test.t5
|
for each row
|
begin
|
-- declare xxy varchar(256) character set utf8;
|
select convert(NEW.c2 using latin1) into @xxy;
|
insert into phils_test.t6(c1, c2, c3) values(NEW.c1, NEW.c2, @xxy);
|
end;
|
$$
|
|
|
delimiter ;
|
|
|
MariaDB [phils_test]> insert into phils_test.t5(c1, c2) values(NULL, 'Bilişim ruiaghaeruighbaeuio');
|
Query OK, 1 row affected (0.002 sec)
|
|
|
MariaDB [phils_test]> select * from t6;
|
+-----------+------------------------------+-----------------------------+
|
| c1 | c2 | c3 |
|
+-----------+------------------------------+-----------------------------+
|
| 112341242 | Bilişim ruiaghaeruighbaeuio | Bili?im ruiaghaeruighbaeuio |
|
+-----------+------------------------------+-----------------------------+
|
1 row in set (0.001 sec)
|
This works as we expect - the latin1 column has question marks in place of the utf8 characters
Remove the comment and the "@" from the session variables so the trigger now uses the DECLAREd variables instead, and:
MariaDB [phils_test]> insert into phils_test.t5(c1, c2) values(NULL, 'Bilişim ruiaghaeruighbaeuio'); |
ERROR 1977 (HY000): Cannot convert 'utf8mb3' character 0xC59F to 'latin1' |
Same error with a direct insert into t6:
MariaDB [(none)]> insert into phils_test.t6(c1, c2, c3) values(999, 'Bilişim ruiaghaeruighbaeuio', convert('Bilişim' using latin1)); |
ERROR 1977 (HY000): Cannot convert 'utf8mb3' character 0xC59F to 'latin1' |
Something isn't right here. Either all should work or all should fail.
Same thing also happens on:
MariaDB [phils_test]> select @@version; |
+---------------------------------------+
|
| @@version | |
+---------------------------------------+
|
| 10.11.7-MariaDB-1:10.11.7+maria~deb11 | |
+---------------------------------------+
|
1 row in set (0.000 sec) |