MySQL:字符报错2种

作者: 重庆八怪 | 来源:发表于2021-03-19 23:05 被阅读0次

Illegal mix of collations (utf8_
从MySQL中使用emoji报错说起。。。
MySQL:字符报错2种
sql注入-updatexml报错注入
sqli-lab less-5、6 Double Query
phpMyAdmin连接MySQL8.0报错为：#2054 无法
Python含有单引号（'）插入数据库时报错
字符集与校对规则
SparkSql写Mysql报错：java.sql.BatchU
Django配置MySQL

blob这种数据最好的方式就是不过转换字符集，直接插入字节流，因为blob本生不是字符，不涉及到字符编码。
这里简单记录不做深入了解，遇到的两种错误，发生在不同阶段。

一、第一种错误

sql_mode 无关，未来可能强制为报错

image.png

错误码：ER_INVALID_CHARACTER_STRING

#0  Item::check_well_formed_result (this=0xf70ff4 <Name_string::copy(char const*, unsigned long, charset_info_st const*)+374>, str=0x7fffec107e20, send_error=false, truncate=232)
    at /opt/percona-server-locks-detail-5.7.22/sql/item.cc:6371
#1  0x0000000000efbdba in Item_string::init (this=0x3ccb7b8, str=0x3ccb7b0 "去", length=3, cs=0x2d74ea0 <my_charset_utf8_general_ci>, dv=DERIVATION_COERCIBLE, repertoire=3)
    at /opt/percona-server-locks-detail-5.7.22/sql/item.h:3650
#2  0x00000000016ef52e in PTI_text_literal_text_string::itemize (this=0x3ccb7b8, pc=0x7fffec107fc0, res=0x7fffec107f20)
    at /opt/percona-server-locks-detail-5.7.22/sql/parse_tree_items.h:490
#3  0x00000000017683d4 in PT_insert_values_list::contextualize (this=0x3ccb8a8, pc=0x7fffec107fc0) at /opt/percona-server-locks-detail-5.7.22/sql/parse_tree_nodes.cc:846
#4  0x0000000001768746 in PT_insert::contextualize (this=0x3ccb908, pc=0x7fffec107fc0) at /opt/percona-server-locks-detail-5.7.22/sql/parse_tree_nodes.cc:913
#5  0x00000000017689ca in PT_insert::make_cmd (this=0x3ccb908, thd=0x3527260) at /opt/percona-server-locks-detail-5.7.22/sql/parse_tree_nodes.cc:953
#6  0x00000000016b60b4 in MYSQLparse (YYTHD=0x3527260) at /opt/percona-server-locks-detail-5.7.22/sql/sql_yacc.yy:1698
#7  0x0000000001574f5f in parse_sql (thd=0x3527260, parser_state=0x7fffec10c5b0, creation_ctx=0x0) at /opt/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:7514
#8  0x0000000001571775 in mysql_parse (thd=0x3527260, parser_state=0x7fffec10c5b0) at /opt/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5788
#9  0x000000000156673d in dispatch_command (thd=0x3527260, com_data=0x7fffec10cd90, command=COM_QUERY) at /opt/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490
#10 0x00000000015655c5 in do_command (thd=0x3527260) at /opt/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021
#11 0x00000000016a635c in handle_connection (arg=0x39edf90) at /opt/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312
#12 0x00000000018ce0b2 in pfs_spawn_thread (arg=0x3481940) at /opt/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190
#13 0x00007ffff7bc6ea5 in start_thread () from /lib64/libpthread.so.0
#14 0x00007ffff66748dd in clone () from /lib64/libc.so.6

这里大概能看到这里正在准备插入的数据，其中Item_string::init 中可以看到这里字符集为utf8，如果更改 set names gbk则这里更改为gbk，如下（这里很可能就是转换字符集的作用）：

#0  Item::check_well_formed_result (this=0xf70f94 <Name_string::copy(char const*, unsigned long, charset_info_st const*)+278>, str=0x7fffec107e20, send_error=false, truncate=false)
    at /opt/percona-server-locks-detail-5.7.22/sql/item.cc:6371
#1  0x0000000000efbdba in Item_string::init (this=0x3ccb7d0, str=0x3ccb7c8 "去", length=3, cs=0x2d0c2e0 <my_charset_gbk_chinese_ci>, dv=DERIVATION_COERCIBLE, repertoire=3)
    at /opt/percona-server-locks-detail-5.7.22/sql/item.h:3650

这里很显然是解析阶段，item正在生成。因此我们可以确认这个错误是在转换字符集实施作用的时候出现的，而和表本生的字符集无关。
可以测试如下：


create table test(name blob);
insert into test values(char(11332864));
或者
insert into test values (0xACED00)
插入的为字节流 0xACED00
然后mysqldump导出导入，即可报错

因此导出包含blob，binary 等字段的表的时候，mysqldump带上

--hex-blob          Dump binary strings (BINARY, VARBINARY, BLOB) in
                      hexadecimal format.

用忽略转换字符集的字节流的方式（不是字符串）进行导出导入避免这种警告。

如果将断点设置在PTI_text_literal_text_string::itemize上，可以发现使用字节流的方式插入数据不会触发断点。

二、第二种错误

sql_mode 有关

image.png

错误码：ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

#0  push_warning_printf (thd=0x3513bc0, severity=Sql_condition::SL_WARNING, code=1366, format=0x2f4cf87 "Incorrect %-.32s value: '%-.128s' for column '%.192s' at row %ld")
    at /opt/percona-server-locks-detail-5.7.22/sql/sql_error.cc:757
#1  0x0000000000f29406 in Field_longstr::check_string_copy_error (this=0x47e0e70, original_string=0x3ccca88 "去", well_formed_error_pos=0x0, cannot_convert_error_pos=0x3ccca88 "去", 
    from_end_pos=0x3ccca8b "", end=0x3ccca8b "", count_spaces=true, cs=0x2d75540 <my_charset_utf8mb4_general_ci>) at /opt/percona-server-locks-detail-5.7.22/sql/field.cc:7012
#2  0x0000000000f2b35c in Field_varstring::store (this=0x47e0e70, from=0x3ccca88 "去", length=3, cs=0x2d75540 <my_charset_utf8mb4_general_ci>)
    at /opt/percona-server-locks-detail-5.7.22/sql/field.cc:7636
#3  0x0000000000f6f5eb in Item::save_str_value_in_field (this=0x3ccca90, field=0x47e0e70, result=0x3cccaa0) at /opt/percona-server-locks-detail-5.7.22/sql/item.cc:561
#4  0x0000000000f81680 in Item_string::save_in_field_inner (this=0x3ccca90, field=0x47e0e70, no_conversions=false) at /opt/percona-server-locks-detail-5.7.22/sql/item.cc:6895
#5  0x0000000000f80c2a in Item::save_in_field (this=0x3ccca90, field=0x47e0e70, no_conversions=false) at /opt/percona-server-locks-detail-5.7.22/sql/item.cc:6752
#6  0x00000000014ec3b9 in fill_record (thd=0x3513bc0, table=0x47dff30, ptr=0x47e0e68, values=..., bitmap=0x0, insert_into_fields_bitmap=0x0)
    at /opt/percona-server-locks-detail-5.7.22/sql/sql_base.cc:9702
#7  0x00000000014ec68e in fill_record_n_invoke_before_triggers (thd=0x3513bc0, ptr=0x47e0e60, values=..., table=0x47dff30, event=TRG_EVENT_INSERT, num_fields=1)
    at /opt/percona-server-locks-detail-5.7.22/sql/sql_base.cc:9784
#8  0x000000000178cbba in Sql_cmd_insert::mysql_insert (this=0x3ccd1e0, thd=0x3513bc0, table_list=0x3cccc40) at /opt/percona-server-locks-detail-5.7.22/sql/sql_insert.cc:751
#9  0x0000000001793615 in Sql_cmd_insert::execute (this=0x3ccd1e0, thd=0x3513bc0) at /opt/percona-server-locks-detail-5.7.22/sql/sql_insert.cc:3121
#10 0x000000000156bc46 in mysql_execute_command (thd=0x3513bc0, first_level=true) at /opt/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:3746
#11 0x0000000001571bed in mysql_parse (thd=0x3513bc0, parser_state=0x7fffec14e5b0) at /opt/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901
#12 0x000000000156673d in dispatch_command (thd=0x3513bc0, com_data=0x7fffec14ed90, command=COM_QUERY) at /opt/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490
#13 0x00000000015655c5 in do_command (thd=0x3513bc0) at /opt/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021
#14 0x00000000016a635c in handle_connection (arg=0x34a7a80) at /opt/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312
#15 0x00000000018ce0b2 in pfs_spawn_thread (arg=0x348db70) at /opt/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190
#16 0x00007ffff7bc6ea5 in start_thread () from /lib64/libpthread.so.0
#17 0x00007ffff66748dd in clone () from /lib64/libc.so.6

[root@mgr3 ~]# more /tmp/sql.sql 
insert into ttname values('去');

mysql> show create table ttname \G
*************************** 1. row ***************************
       Table: ttname
Create Table: CREATE TABLE `ttname` (
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> show variables like 'char%';
+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| character_set_client     | utf8mb4                       |
| character_set_connection | utf8mb4                       |
| character_set_database   | latin1                        |(本库是latin1)
| character_set_filesystem | binary                        |
| character_set_results    | utf8mb4                       |
| character_set_server     | utf8mb4                       |
| character_set_system     | utf8                          |
| character_sets_dir       | /opt/my_mysql/share/charsets/ |
+--------------------------+-------------------------------+

这种错误就是常规的，当数据存储到表字段的时候，使用转换后的字符集和表字符集进行匹配得到的错误。很明显这是insert语句执行阶段了。

三、show-warnings

可以使用 --show-warnings选项避免错误警告信息

[root@mgr3 ~]# /opt/my_mysql/bin/mysql --show-warnings -b test < log.log
Warning (Code 1300): Invalid utf8mb4 character string: 'ACED00'
Warning (Code 1300): Invalid utf8mb4 character string: 'ACED00'

不过报警告没有行数，报错才有行数，也是很尴尬？是否可以改一下代码，空了试试。

但是source不管是否报错都没有行数,且继续执行。

mysql> source /tmp/sql.sql
ERROR 1366 (HY000): Incorrect string value: '\xE5\x8E\xBB' for column 'name' at row 1
ERROR 1366 (HY000): Incorrect string value: '\xE5\x8E\xBB' for column 'name' at row 1
mysql> exit
Bye
[root@mgr3 ~]# /opt/mysql/mysql3306/install/bin/mysql  --socket=/tmp/mysql3325.sock --show-warnings  -b test </tmp/sql.sql
ERROR 1366 (HY000) at line 15: Incorrect string value: '\xE5\x8E\xBB' for column 'name' at row 1

其次source 可以使用--tee 命令输出日志到日志文件如下

 /opt/my_mysql/bin/mysql --tee /tmp/log.log --show-warnings

Illegal mix of collations (utf8_
MySQL进行字符串比较时发生错误今天在做一些多表查询时，MySQL报错：[Err] 1267 - Illega...
从MySQL中使用emoji报错说起。。。
# MySql中存储emoji字符报错 # 情景：用户在APP输入文本内容，客户端将用户输入的字符通过网络传递给服...
MySQL:字符报错2种
blob这种数据最好的方式就是不过转换字符集，直接插入字节流，因为blob本生不是字符，不涉及到字符编码。这里简单...
sql注入-updatexml报错注入
参考：MySQL updatexml报错注入MySQL updatexml()、extractvalue() 报错...
sqli-lab less-5、6 Double Query
利用报错注入，mysql5.6以上有三种第一种利用floor构造重复键group by报错输出字符长度限制为6...
phpMyAdmin连接MySQL8.0报错为：#2054 无法
phpMyAdmin连接MySQL8.0报错为：#2054 无法登录 MySQL 服务器一、报错场景：二、报错...
Python含有单引号（'）插入数据库时报错
插入MySQL时，如果内容中有引号等特殊符号，会报错，解决方法可以用反斜杠转义或者替换掉报错字符，还可以用pymy...
字符集与校对规则
MySQL字符集及校对规则的理解阅读目录：MySQL的字符集和校对规则 MySQL的字符集 MySQL与字符集 ...
SparkSql写Mysql报错：java.sql.BatchU
一、报错信息 SparkSql代码写入Mysql逻辑如下，代码会自动生成目标Mysql表，报错如图：报错信息： ...
Django配置MySQL
如果出现报错问题是关于 mysql_config 如果依旧报错mysql_config 查找mysql_conf...