I have two database with a table with utf8
table, I wanted to change the encoding of a column, I used two scenarios but a problem happened to me.
at first the table was like this :
CREATE TABLE `spool` (
`username` varchar(250) NOT NULL,
`xml` text NOT NULL,
`seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `seq` (`seq`),
KEY `i_despool` (`username`) USING BTREE,
KEY `i_spool_created_at` (`created_at`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
First scenario
in the first one I change the row encoding with bellow command :
ALTER TABLE spool MODIFY xml TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
and then I use show create table spool;
like this one :
CREATE TABLE `spool` (
`username` varchar(250) NOT NULL,
`xml` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `seq` (`seq`),
KEY `i_despool` (`username`) USING BTREE,
KEY `i_spool_created_at` (`created_at`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
second scenario
In the second table I first changed the table encoding and then I changed the xml
column encoding like this :
ALTER TABLE spool
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
and then I changed column encoding like below :
ALTER TABLE spool MODIFY xml TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
and then we can see table like this :
CREATE TABLE `spool` (
`username` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`xml` text COLLATE utf8mb4_unicode_ci NOT NULL,
`seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `seq` (`seq`),
KEY `i_despool` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=30849368 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
as you can see in the second scenario in front of the "`xml` text" we don't see the CHARACTER SET utf8mb4
and I think it causes error for me because when Ejabberd wants to insert a query in the second one I see bellow error but in the other table I don't have this error:
HY000Incorrect string value: '\\xF0\\x9F\\x98\\x8F\\xF0\\x9F...' for column 'xml' at row 1"
** Stacktrace: [{ejabberd_odbc,sql_query_t,1,[{file,"src/ejabberd_odbc.erl"},{line,173}]},{lists,foreach,2,[{file,"lists.erl"},{line,1336}]},{ejabberd_odbc,outer_transaction,3,[{file,"src/ejabberd_odbc.erl"},{line,443}]},{ejabberd_odbc,run_sql_cmd,4,[{file,"src/ejabberd_odbc.erl"},{line,380}]},{p1_fsm,handle_msg,10,[{file,"src/p1_fsm.erl"},{line,582}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,237}]}]
in the first scenario we don't see this error but in the second one the error exists. how to solve this problem?