mysql-connector-java挿入utf 8 mb 4文字失敗問題処理分析
14839 ワード
問題の説明
トラフィック・データベース・インスタンスの符号化がutf 8からutf 8 mb 4に変更されると、javaトラフィックは、表情記号などのワイド文字(4バイト)を挿入する際に、次のエラーを報告し続けます.
プログラムおよびデータベースが実行するバージョンおよび環境は、次のとおりです.
テスト環境では同じ
jdbc構成説明
connector-j-reference-charsetを参照すると、utf 8 mb 4文字を挿入する場合は、次の条件を満たす必要があります.
だから
問題解析の説明
mysql-connect-javaはどのように符号化を処理します
公式ドキュメントの条件を満たしているのか挿入に失敗しているのか、python、perlなどのスクリプトプログラムを使ってutf 8 mb 4文字を正常に挿入できるのか迷っています.
1694行のコード、すなわち我々が作成した
私たちのデータベースバージョンは
bug#81196は私たちが出会った問題と同じです.
プロトコル解析
tcpdumpを使用して、握手プロトコルのメッセージ情報を表示します.
MySQLの通信プロトコルフォーマットを参照:
上記のプロトコルフォーマットからtcpdumpメッセージの各フィールド情報を検索すると、以下のようになります.
MySQL Serverが返す
MySQLはクライアントにエンコーディングを返す方法
同じMySQLバージョンのdebugバージョンでテストします.以下にdebugバージョンのtrace情報を示します.
ここでの関数
コード
起動後に符号化関連パラメータを変更しても
この点、実行中のデータベースの符号化を修正することは
解決策
上記の解析から、
1.MySQL Serverを再起動する
データベースのプロファイルを変更する、元のutf 8に関する符号化をすべてutf 8 mb 4に変更し、
2.パッチ適用
bugs 81196によって提供される方法を参照すると、
jdbcは初期化時に変数パラメータの情報を取得するが、上述のようにcollationに関するパラメータはutf 8 mb 4に関する情報であるため、このパッチの方式でも問題を解決することができ、開発者が対応する
3.Connector/Jバージョンのアップグレード
上記
トラフィック・データベース・インスタンスの符号化がutf 8からutf 8 mb 4に変更されると、javaトラフィックは、表情記号などのワイド文字(4バイト)を挿入する際に、次のエラーを報告し続けます.
### Cause:java.sql.SQLException:Incorrect string value:\xF0\x9F\x98\x8E for column nick_name at row 1
;uncategorized SQLException for SQL[]; SQL state [HY000]; error code[1366];Incorrect string value: \xF0\x9F\x98\x8E for column nick_name at row 1プログラムおよびデータベースが実行するバージョンおよび環境は、次のとおりです.
Centos 7.6
kernel-3.10.0-957.1.3.el7.x86_64
mysql-connector-java-5.1.46
Percona-Server-5.6.38-rel83.0-Linuxテスト環境では同じ
mysql-connector-javaバージョンを使用し、プログラムを正常に挿入することができる.異なるのは、テスト環境がコードを修正した後にMySQLサービスを再起動したことであり、オンライン環境は以下の修正のみを行い、プログラムを再起動してMySQLサービスを再起動しない.set global character_set_client = utf8mb4;
set global character_set_connection = utf8mb4;
set global character_set_database = utf8mb4;
set global character_set_results = utf8mb4;
set global character_set_server = utf8mb4;
set global collation_server = utf8mb4_general_ci;
set global collation_database = utf8mb4_general_ci;
set global collation_connection = utf8mb4_general_ci;jdbc構成説明
connector-j-reference-charsetを参照すると、utf 8 mb 4文字を挿入する場合は、次の条件を満たす必要があります.
Connector/J 5.1.47 :
1. characterEncoding UTF8/UTF-8 , utf8mb4 ;
2. connectionCollation utf8mb4 , characterEncoding ;
Connector/J 5.1.47 :
1. MySQL character_set_server=utf8mb4;
2. characterEncoding UTF8/UTF-8, jdbc utf8mb4;だから
mysql-connector-java版についてはもともと、私たちの条件はすでに満足していたが、挿入に失敗した.また、characterEncodingのパラメータの値はconnector-j-reference-charsetリンクのTable 5.3に記載する符号名のみを指定し、残りの符号名を指定することができ、jdbcは接続を確立する際にエラーを報告する.問題解析の説明
mysql-connect-javaはどのように符号化を処理します
公式ドキュメントの条件を満たしているのか挿入に失敗しているのか、python、perlなどのスクリプトプログラムを使ってutf 8 mb 4文字を正常に挿入できるのか迷っています.
mysql-connector-java-5.1.46のソースプログラムを参照すると、次のコードが表示されます.//src/com/mysql/jdbc/ConnectionImpl.java
1616 private boolean configureClientCharacterSet(boolean dontCheckServerMatch) throws SQLException {
1617 String realJavaEncoding = getEncoding();
......
1689 if (realJavaEncoding != null) {
1690
1691 //
1692 // Now, inform the server what character set we will be using from now-on...
1693 //
1694 if (realJavaEncoding.equalsIgnoreCase("UTF-8") || realJavaEncoding.equalsIgnoreCase("UTF8")) {
1695 // charset names are case-sensitive
1696
1697 boolean utf8mb4Supported = versionMeetsMinimum(5, 5, 2);
1698 boolean useutf8mb4 = utf8mb4Supported && (CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex));
1699
1700 if (!getUseOldUTF8Behavior()) {
1701 if (dontCheckServerMatch || !characterSetNamesMatches("utf8") || (utf8mb4Supported && !characterSetNamesMatches("utf8mb4"))) {
1702 execSQL(null, "SET NAMES " + (useutf8mb4 ? "utf8mb4" : "utf8"), -1, null, DEFAULT_RESULT_SET_TYPE,
1703 DEFAULT_RESULT_SET_CONCURRENCY, false, this.database, null, false);
1704 this.serverVariables.put("character_set_client", useutf8mb4 ? "utf8mb4" : "utf8");
1705 this.serverVariables.put("character_set_connection", useutf8mb4 ? "utf8mb4" : "utf8");
1706 }
1707 } else {
1708 execSQL(null, "SET NAMES latin1", -1, null, DEFAULT_RESULT_SET_TYPE, DEFAULT_RESULT_SET_CONCURRENCY, false, this.database, null,
1709 false);
1710 this.serverVariables.put("character_set_client", "latin1");
1711 this.serverVariables.put("character_set_connection", "latin1");
1712 }
1713
1714 setEncoding(realJavaEncoding);1694行のコード、すなわち我々が作成した
characterEncodingのパラメータを見ることができ、後続のコードは符号化の自動検出である.1697行コードは、現在のMySQLバージョンがutf 8 mb 4符号化をサポートしているかどうかを判断するため(mysql-5.5.2バージョンがutf 8 mb 4符号化をサポートし始めた)、1698行のuseutf8mb4は、2つの条件によって決定される.utf8mb4Supported
CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex)私たちのデータベースバージョンは
5.6.38なので、第1の条件は満たされています.第2の条件のthis.io.serverCharsetIndexは以下のコードに由来しています.このコードは、プログラムがデータベースに接続されたときに行われた握手プロトコル処理であり、serverCharsetIndexはMySQL Serverが現在のセッションに返すコード番号(information_schema.COLLATIONSテーブルのIDフィールドに対応)であることがわかります.したがって、第2の条件は、現在のセッションで受信符号化番号がCharsetMapping.UTF8MB4_INDEXESの集合に存在するか否かを判定することである.//src/com/mysql/jdbc/MysqlIO.java
998 /**
999 * Initialize communications with the MySQL server. Handles logging on, and
1000 * handling initial connection errors.
1001 *
1002 * @param user
1003 * @param password
1004 * @param database
1005 *
1006 * @throws SQLException
1007 * @throws CommunicationsException
1008 */
1009 void doHandshake(String user, String password, String database) throws SQLException {
1010 // Read the first packet
......
1118 if ((versionMeetsMinimum(4, 1, 1) || ((this.protocolVersion > 9) && (this.serverCapabilities & CLIENT_PROTOCOL_41) != 0))) {
1119
1120 /* New protocol with 16 bytes to describe server characteristics */
1121 // read character set (1 byte)
1122 this.serverCharsetIndex = buf.readByte() & 0xff;
1123 // read status flags (2 bytes)
1124 this.serverStatus = buf.readInt();mysql-connector-java-4.1.47バージョンのchangelogを参照してください.See Using Character Sets and Unicode for details, including how to use the utf8mb3 character set now for connection. (Bug #23227334, Bug #81196)bug#81196は私たちが出会った問題と同じです.
serverCharsetIndexの値が上記の集合でない場合、jdbcはセッション確立後もSET NAMES utf8の動作を継続する.プロトコル解析
tcpdumpを使用して、握手プロトコルのメッセージ情報を表示します.
0000 fe ee 16 93 fe 2d 52 54 00 48 bd 50 08 00 45 08 .....-RT.H.P..E.
0010 00 8b 4a b8 40 00 40 06 cc 78 0a 94 07 09 0a 94 ..J.@[email protected]......
0020 07 04 0c e7 c5 0c b7 f4 a5 5a 5a 53 2f f9 80 18 .........ZZS/...
0030 00 e3 23 b2 00 00 01 01 08 0a a9 c6 ef e2 a9 b5 ..#.............
0040 7a 4b 53 00 00 00 0a 35 2e 36 2e 33 38 2d 38 33 zKS....5.6.38-83
0050 2e 30 2d 6c 6f 67 00 78 15 10 01 74 2d 7d 51 5e .0-log.x...t-}Q^
0060 64 5b 79 00 ff f7 21 02 00 7f 80 15 00 00 00 00 d[y...!.........
0070 00 00 00 00 00 00 70 48 56 56 30 29 7c 58 24 48 ......pHVV0)|X$H
0080 7e 64 00 6d 79 73 71 6c 5f 6e 61 74 69 76 65 5f ~d.mysql_native_
0090 70 61 73 73 77 6f 72 64 00 password.MySQLの通信プロトコルフォーマットを参照:
1 [0a] protocol version
string[NUL] server version
4 connection id
string[8] auth-plugin-data-part-1
1 [00] filler
2 capability flags (lower 2 bytes)
if more data in the packet:
1 character set
2 status flags
2 capability flags (upper 2 bytes)
if capabilities & CLIENT_PLUGIN_AUTH {
1 length of auth-plugin-data
} else {
1 [00]
}
string[10] reserved (all [00])
if capabilities & CLIENT_SECURE_CONNECTION {
string[$len] auth-plugin-data-part-2 ($len=MAX(13, length of auth-plugin-data - 8))
if capabilities & CLIENT_PLUGIN_AUTH {
string[NUL] auth-plugin name
}上記のプロトコルフォーマットからtcpdumpメッセージの各フィールド情報を検索すると、以下のようになります.
protocol version: 0a
server version: 35 2e 36 2e 33 38 2d 38 33 2e 30 2d 6c 6f 67 00
connection id: 78 15 10 01
auth-plugin-date: 74 2d 7d 51 5e 64 5b 79
[00] filler: 00
capability flags: ff f7
character set: 21
status: 02 00MySQL Serverが返す
character setは0 x 21(10進法33)であり、33はinformation_schema.COLLATIONS表のutf 8符号化に対応している.これは、MySQL Server符号化に関するパラメータを変更した後、新しいutf 8 mb 4符号化をクライアントに返さず、以前の符号化に戻ることを意味する.MySQLはクライアントにエンコーディングを返す方法
同じMySQLバージョンのdebugバージョンでテストします.以下にdebugバージョンのtrace情報を示します.
......
T@29 : | | | | | | server_mpvio_read_packet
T@29 : | | | | | >vio_read ここでの関数
send_server_handshake_packetは、クライアントに返される握手プロトコルを実現し、10496行はMySQL Serverが返す符号化情報である.//src/sql/sql_acl.cc
10419 static bool send_server_handshake_packet(MPVIO_EXT *mpvio,
10420 const char *data, uint data_len)
10421 {
......
10494 int2store(end, mpvio->client_capabilities);
10495 /* write server characteristics: up to 16 bytes allowed */
10496 end[2]= (char) default_charset_info->number;
10497 int2store(end + 3, mpvio->server_status[0]);コード
default_charset_info->numberについては、CHARSET_INFO構造体のタイプであり、以下のようになる.typedef struct charset_info_st
{
uint number;
uint primary_number;
uint binary_number;
....
MY_CHARSET_HANDLER *cset;
MY_COLLATION_HANDLER *coll;
} CHARSET_INFO;sql/mysqld.ccのコードから見ると、default_charset_infoはMySQL Serverが起動したときにのみ初期化して使用され、その値がcharacter-set-serverのパラメータ値であることがわかります.4020 int init_common_variables()
4021 {
4022 umask(((~my_umask) & 0666));
4023 connection_errors_select= 0;
......
4302 if (item_create_init())
4303 return 1;
4304 item_init();
......
4322 if (!(default_charset_info=
4323 get_charset_by_csname(default_character_set_name,
4324 MY_CS_PRIMARY, MYF(MY_WME))))
4325 {
4326 if (next_character_set_name)
4327 {
4328 default_character_set_name= next_character_set_name;
4329 default_collation_name= 0; // Ignore collation
4330 }
4331 else
4332 return 1; // Eof of the list
4333 }
4334 else
4335 break;
4336 }
7537 {"character-set-server", 'C', "Set the default character set.",
7538 &default_character_set_name, &default_character_set_name,
7539 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0 },起動後に符号化関連パラメータを変更しても
default_charset_infoの更新はトリガーされません.debugバージョンのtraceログから、上記の関連操作は接続が確立されたときにのみ初期化されます........
T@1 : get_charset_by_csname
T@1 : | enter: name: 'utf8' この点、実行中のデータベースの符号化を修正することは
default_charset_infoの更新をトリガーするものではなく、クライアントプロトコルパケットに返される符号化は以前の符号化である.解決策
上記の解析から、
mysql-connect-java-5.1.46はデータベースから返される符号化に依存するが、データベースからクライアントへ返される符号化は以前の符号化(パラメータcharacter-set-serverの値と一致)であるため、解決策として表情文字を挿入する方式は以下の式を用いることができる.1.MySQL Serverを再起動する
データベースのプロファイルを変更する、元のutf 8に関する符号化をすべてutf 8 mb 4に変更し、
MySQL Serverを再起動し、新しいdefault_charset_infoはcharacter-set-serverパラメータの値を継承し、クライアントに返す符号化はutf 8 mb 4符号化である.この方法は、新しく作成されたまたはテスト環境のデータベースに適しており、オンライン上の実行済みデータベースは一般的に再起動操作をしない.2.パッチ適用
bugs 81196によって提供される方法を参照すると、
5.1.38 ~ 5.1.46のバージョンに適用され、現在のセッションのcollationパラメータがutf 8 mb 4を含んでいるかどうかを追加的に取得してuseutf8mb4が真であるかどうかを決定し、以下に示す.diff --git a/src/com/mysql/jdbc/ConnectionImpl.java b/src/com/mysql/jdbc/ConnectionImpl.java
index 9da30ea..854ae59 100644
--- a/src/com/mysql/jdbc/ConnectionImpl.java
+++ b/src/com/mysql/jdbc/ConnectionImpl.java
@@ -1762,7 +1762,8 @@
// charset names are case-sensitive
boolean utf8mb4Supported = versionMeetsMinimum(5, 5, 2);
- boolean useutf8mb4 = utf8mb4Supported && (CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex));
+ boolean useutf8mb4 = utf8mb4Supported && (CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex)
+ || (getConnectionCollation() != null && StringUtils.startsWithIgnoreCase(getConnectionCollation(), "utf8mb4")));
if (!getUseOldUTF8Behavior()) {
if (dontCheckServerMatch || !characterSetNamesMatches("utf8") || (utf8mb4Supported && !characterSetNamesMatches("utf8mb4"))) {tcpdump -A -r ....の記事から見ると、12:08:22.994813 IP 10.0.21.17.50444 > 10.0.21.5.3303: Flags [P.], seq 261:1189, ack 110, win 115, options [nop,nop,TS val 2847242832 ecr 2848387046], length 928
..zP........./* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server,......
12:08:22.994939 IP 10.0.21.5.3303 > 10.0.21.17.50444: Flags [P.], seq 110:1137, ack 1189, win 250, options [nop,nop,TS val 2848387046 ecr 2847242832], length 1027
......zP..........def....auto_increment_increment..?...........*....def....character_set_client..!................def....character_set_connection..!...........+....def....character_set_results..!...........*....def....character_set_server..!...........&....def....collation_server..!.6........."
.........................2.utf8.utf8.utf8.utf8mb4.utf8mb4_general_ci..28800.GPL.1.....jdbcは初期化時に変数パラメータの情報を取得するが、上述のようにcollationに関するパラメータはutf 8 mb 4に関する情報であるため、このパッチの方式でも問題を解決することができ、開発者が対応する
mysql-connector-javaバージョンを修正してコンパイルする必要がある.3.Connector/Jバージョンのアップグレード
上記
5.1.47バージョンのcharacterEncodingパラメータがUTF8/UTF-8に設定場合、utf 8 mb 4に直接マッピングされ、低バージョンのようにデータベースから戻る符号化に依存する必要はなく、データベースを再起動することなく有効となる、詳細は5.1.47-changelogを参照.changelogからは5.1.46バージョンよりも変更が少ない、大きな更新をしていない、アップグレードすると既存の機能に影響を与えないことがわかる.しかし、オンラインアップグレードは、問題がすべての業務に影響を与えないように、バッチ操作を提案する.