| Bug #115291 | 8024 upgrade 8030 character set exception | ||
|---|---|---|---|
| Submitted: | 12 Jun 2024 7:43 | Modified: | 13 Jun 2024 11:17 |
| Reporter: | lei yue | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Installing | Severity: | S4 (Feature request) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[12 Jun 2024 7:44]
lei yue
args[0]
```
(gdb) p *args[0]
$28 = {
<Parse_tree_node_tmpl<Parse_context>> = {
_vptr.Parse_tree_node_tmpl = 0x48cc430 <vtable for Item_func_conv_charset+16>
},
members of Item:
next_free = 0x7fe68e74ad70,
str_value = {
m_ptr = 0x0,
m_length = 0,
m_charset = 0x4a6ffc0 <my_charset_bin>,
m_alloced_length = 0,
m_is_alloced = false
},
collation = {
collation = 0x4a7d400 <my_charset_utf8mb4_0900_ai_ci>,
derivation = DERIVATION_IMPLICIT,
repertoire = 3
},
item_name = {
<Name_string> = {
<Simple_cstring> = {
m_str = 0x0,
m_length = 0
}, <No data fields>},
members of Item_name_string:
m_is_autogenerated = true
},
orig_name = {
<Name_string> = {
<Simple_cstring> = {
m_str = 0x0,
m_length = 0
}, <No data fields>},
members of Item_name_string:
m_is_autogenerated = true
},
max_length = 1020,
marker = Item::MARKER_NONE,
cmp_context = INVALID_RESULT,
is_parser_item = true,
is_expensive_cache = -1 '\377',
m_data_type = 15 '\017',
fixed = true,
decimals = 31 '\037',
m_nullable = true,
null_value = false,
unsigned_flag = false,
m_is_window_function = false,
hidden = false,
m_in_check_constraint_exec_ctx = false,
derived_used = false,
static PROP_SUBQUERY = 1 '\001',
static PROP_STORED_PROGRAM = 2 '\002',
static PROP_AGGREGATION = 4 '\004',
static PROP_WINDOW_FUNCTION = 8 '\b',
static PROP_ROLLUP_EXPR = 16 '\020',
static PROP_GROUPING_FUNC = 32 ' ',
static PROP_SAVED_AGGREGATION = 64 '@',
m_accum_properties = 0 '\000'
}
```
args[1]
```
(gdb) p *args[1]
$29 = {
<Parse_tree_node_tmpl<Parse_context>> = {
_vptr.Parse_tree_node_tmpl = 0x4853210 <vtable for Item_field+16>
},
members of Item:
next_free = 0x7fe68e74acc0,
str_value = {
m_ptr = 0x0,
m_length = 0,
m_charset = 0x4a6ffc0 <my_charset_bin>,
m_alloced_length = 0,
m_is_alloced = false
},
collation = {
collation = 0x4bcf320 <my_charset_utf8mb4_general_ci>,
derivation = DERIVATION_IMPLICIT,
repertoire = 3
},
item_name = {
<Name_string> = {
<Simple_cstring> = {
m_str = 0x7fe68e74acb0 "ROLE_HOST",
m_length = 9
}, <No data fields>},
members of Item_name_string:
m_is_autogenerated = true
},
orig_name = {
<Name_string> = {
<Simple_cstring> = {
m_str = 0x0,
m_length = 0
}, <No data fields>},
members of Item_name_string:
m_is_autogenerated = true
},
max_length = 1020,
marker = Item::MARKER_NONE,
cmp_context = INVALID_RESULT,
is_parser_item = true,
is_expensive_cache = -1 '\377',
m_data_type = 15 '\017',
fixed = true,
decimals = 31 '\037',
m_nullable = true,
null_value = false,
unsigned_flag = false,
m_is_window_function = false,
hidden = false,
m_in_check_constraint_exec_ctx = false,
derived_used = false,
static PROP_SUBQUERY = 1 '\001',
static PROP_STORED_PROGRAM = 2 '\002',
static PROP_AGGREGATION = 4 '\004',
static PROP_WINDOW_FUNCTION = 8 '\b',
static PROP_ROLLUP_EXPR = 16 '\020',
static PROP_GROUPING_FUNC = 32 ' ',
static PROP_SAVED_AGGREGATION = 64 '@',
m_accum_properties = 0 '\000'
}
```
[12 Jun 2024 9:45]
MySQL Verification Team
Hi Mr. yue, Thank you for your bug report. However, let us inform you that this is a forum for the bugs with repeatable bugs reports. We have upgraded our server numerous times and never encountered an error that you report. Also, our database of bugs, which has more then hundred thousands bug reports does not have anything similar. Needless to say, current release is 8.0.37 and not 8.0.30. We are testing with the latest release only We can not go further without a fully repeatable test case. Can't repeat.
[12 Jun 2024 9:52]
lei yue
Initialize the database with default_collation_for_utf8mb4 = utf8mb4_general_ci, and then execute the following SQL statement, and an error will be reported ``` txsql> CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ENABLED_ROLES AS SELECT ROLE_NAME AS ROLE_NAME, ROLE_HOST AS ROLE_HOST, (SELECT IF(COUNT(*), 'YES', 'NO') FROM mysql.default_roles WHERE DEFAULT_ROLE_USER = ROLE_NAME AND CONVERT(DEFAULT_ROLE_HOST using utf8mb4) = ROLE_HOST AND USER = INTERNAL_GET_USERNAME() AND CONVERT(HOST using utf8mb4) = INTERNAL_GET_HOSTNAME()) AS IS_DEFAULT, IF(INTERNAL_IS_MANDATORY_ROLE(ROLE_NAME, ROLE_HOST), 'YES', 'NO') AS IS_MANDATORY FROM JSON_TABLE(INTERNAL_GET_ENABLED_ROLE_JSON(), '$[*]' COLUMNS ( ROLE_NAME VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_NAME', ROLE_HOST VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_HOST') ) current_user_enabled_roles; ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=' ```
[12 Jun 2024 9:54]
lei yue
Need to add cs processing in Item_func_conv_charset
```
if (cs2 == &my_charset_utf8mb4_0900_ai_ci &&
cs2 != thd->variables.default_collation_for_utf8mb4)
cs3 = thd->variables.default_collation_for_utf8mb4;
```
[12 Jun 2024 10:36]
MySQL Verification Team
Hi Mr. yue, Thank you for the feedback. However, this is not a bug. It is defined in SQL standard that two collations with the exactly same coercibility can not be converted, at all.. Hence, this error is expected behaviour , according to the SQL Standard. This is also described in our Reference Manual, section on collation and collation comparisons ...... Not a bug.
[13 Jun 2024 10:39]
MySQL Verification Team
Hi Mr. vue, Actually, we reconsidered the case and concluded that our upgrade procedure could be improved. Hence this is now a verified feature request for the Server Upgrade. Verified.
[13 Jun 2024 11:17]
lei yue
diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h
index d49aaf0671c..6b81bc91fb3 100644
--- a/sql/item_strfunc.h
+++ b/sql/item_strfunc.h
@@ -47,7 +47,9 @@
#include "sql/item_func.h" // Item_func
#include "sql/parse_location.h" // POS
#include "sql/sql_const.h"
+#include "sql/current_thd.h"
#include "sql_string.h"
+#include "sql_class.h" // THD
#include "template_utils.h" // pointer_cast
/* Changes from TXSQL start. */
@@ -983,6 +985,10 @@ class Item_charset_conversion : public Item_str_func {
Item_charset_conversion(THD *thd, Item *a, const CHARSET_INFO *cs_arg,
bool cache_if_const)
: Item_str_func(a), m_cast_cs(cs_arg) {
+ if (m_cast_cs == &my_charset_utf8mb4_0900_ai_ci &&
+ m_cast_cs != thd->variables.default_collation_for_utf8mb4)
+ m_cast_cs = thd->variables.default_collation_for_utf8mb4;
+
if (cache_if_const && args[0]->may_evaluate_const(thd)) {
uint errors = 0;
String tmp, *str = args[0]->val_str(&tmp);
@@ -1001,7 +1007,11 @@ class Item_charset_conversion : public Item_str_func {
}
Item_charset_conversion(const POS &pos, Item *a, const CHARSET_INFO *cs_arg,
bool to_char = false)
- : Item_str_func(pos, a), m_cast_cs(cs_arg), is_to_char(to_char) {}
+ : Item_str_func(pos, a), m_cast_cs(cs_arg), is_to_char(to_char) {
+ if (m_cast_cs == &my_charset_utf8mb4_0900_ai_ci &&
+ m_cast_cs != current_thd->variables.default_collation_for_utf8mb4)
+ m_cast_cs = current_thd->variables.default_collation_for_utf8mb4;
+ }
String *val_str(String *) override;
};
[13 Jun 2024 11:22]
MySQL Verification Team
Thank you Mr. yue for your effort. However, we shall make a fix in the upgrade procedure only.
Description: We modify default_collation_for_utf8mb4 = utf8mb4_general_ci. When using the 8.0.30 binary package to replace the 8.0.24 basedir upgrade, the following error will be encountered: ``` 2024-04-16T18:46:34.385683+08:00 4 [Note] [MY-013394] [Server] Checking 'sys' schema. 2024-04-16T18:46:34.405978+08:00 4 [System] [MY-013381] [Server] Server upgrade from '80024' to '80030' completed. mysqld: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=' 2024-04-16T18:46:34.412038+08:00 0 [ERROR] [MY-010943] [Server] System views initialization failed. 2024-04-16T18:46:34.412060+08:00 0 [ERROR] [MY-010119] [Server] Aborting 2024-04-16T18:46:34.412110+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting. ``` The error stack is ``` (gdb) bt #0 my_coll_agg_error (args=0x7fe68ece4b18, count=2, fname=0x3536e99 "=", item_sep=1) at /data1/mysql/mysql-8.0.30/sql/item.cc:2532 #1 0x00000000014ae666 in agg_item_collations (item_sep=1, flags=7, count=2, av=0x7fe68ece4b18, fname=<optimized out>, c=...) at /data1/mysql/mysql-8.0.30/sql/item.cc:2556 #2 agg_item_charsets (coll=..., fname=0x3536e99 "=", args=args@entry=0x7fe68ece4b18, nargs=nargs@entry=2, flags=flags@entry=7, item_sep=item_sep@entry=1, only_consts=true) at /data1/mysql/mysql-8.0.30/sql/item.cc:2692 #3 0x00000000014d741c in agg_item_charsets_for_comparison (item_sep=1, nitems=2, items=0x7fe68ece4b18, name=<optimized out>, c=...) at /data1/mysql/mysql-8.0.30/sql/item.h:3846 #4 agg_arg_charsets_for_comparison (item_sep=1, nitems=2, items=0x7fe68ece4b18, c=..., this=0x7fe68ece4a78) at /data1/mysql/mysql-8.0.30/sql/item_func.h:537 #5 Item_bool_func2::resolve_type (this=0x7fe68ece4a78, thd=0x7c54080) at /data1/mysql/mysql-8.0.30/sql/item_cmpfunc.cc:649 #6 0x00000000015319c7 in Item_func::fix_fields(THD*, Item**) () at /data1/mysql/mysql-8.0.30/sql/item_func.cc:420 #7 0x00000000014df3d3 in Item_cond::fix_fields(THD*, Item**) () at /data1/mysql/mysql-8.0.30/sql/sql_list.h:586 #8 0x00000000010f4440 in Query_block::setup_conds (this=this@entry=0x7fe68ece3010, thd=thd@entry=0x7c54080) at /data1/mysql/mysql-8.0.30/sql/sql_resolver.cc:1816 #9 0x000000000110efdf in Query_block::prepare(THD*, mem_root_deque<Item*>*) () at /data1/mysql/mysql-8.0.30/sql/sql_resolver.cc:294 #10 0x00000000011c245f in Query_expression::prepare(THD*, Query_result*, mem_root_deque<Item*>*, unsigned long long, unsigned long long) () at /data1/mysql/mysql-8.0.30/sql/sql_union.cc:510 #11 0x0000000001598347 in Item_subselect::fix_fields (this=0x7fe68ece53a8, thd=0x7c54080, ref=0x7fe68ece5a90) at /opt/rh/devtoolset-10/root/usr/include/c++/10/bits/unique_ptr.h:421 #12 0x0000000000fc7b3e in setup_fields(THD*, unsigned long, bool, bool, bool, mem_root_deque<Item*> const*, mem_root_deque<Item*>*, Bounds_checked_array<Item*>) () at /data1/mysql/mysql-8.0.30/sql/sql_base.cc:9131 #13 0x000000000110ef70 in Query_block::prepare(THD*, mem_root_deque<Item*>*) () at /data1/mysql/mysql-8.0.30/sql/sql_resolver.cc:277 #14 0x00000000011c245f in Query_expression::prepare(THD*, Query_result*, mem_root_deque<Item*>*, unsigned long long, unsigned long long) () at /data1/mysql/mysql-8.0.30/sql/sql_union.cc:510 #15 0x00000000011d1e8a in mysql_create_view(THD*, TABLE_LIST*, enum_view_create_mode) () at /data1/mysql/mysql-8.0.30/sql/sql_view.cc:613 #16 0x00000000010ab9d4 in mysql_execute_command(THD*, bool) () at /data1/mysql/mysql-8.0.30/sql/sql_parse.cc:5728 #17 0x00000000010dd42f in (anonymous namespace)::Execute_sql_statement::execute_server_code(THD*) () at /data1/mysql/mysql-8.0.30/sql/sql_prepare.cc:2351 #18 0x00000000010df2a0 in Prepared_statement::execute_server_runnable(Server_runnable*) () at /data1/mysql/mysql-8.0.30/sql/sql_prepare.cc:3289 #19 0x00000000010e172b in Ed_connection::execute_direct(Server_runnable*) () at /data1/mysql/mysql-8.0.30/sql/sql_prepare.cc:3940 #20 0x00000000010e18cc in Ed_connection::execute_direct(MYSQL_LEX_STRING) () at /data1/mysql/mysql-8.0.30/sql/sql_prepare.cc:3914 #21 0x000000000235d945 in dd::execute_query(THD*, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const&) () at /data1/mysql/mysql-8.0.30/sql/dd/impl/utils.cc:47 #22 0x00000000026e3238 in (anonymous namespace)::create_system_views(THD*, bool) () at /data1/mysql/mysql-8.0.30/sql/dd/info_schema/metadata.cc:457 #23 0x00000000026e5454 in create_non_dd_based_system_views (thd=0x7c54080) at /data1/mysql/mysql-8.0.30/sql/dd/info_schema/metadata.cc:588 #24 initialize (non_dd_based_system_view=true, thd=0x7c54080) at /data1/mysql/mysql-8.0.30/sql/dd/info_schema/metadata.cc:588 #25 dd::info_schema::init_non_dd_based_system_view(THD*) () at /data1/mysql/mysql-8.0.30/sql/dd/info_schema/metadata.cc:684 #26 0x00000000013ea716 in handle_bootstrap () at /data1/mysql/mysql-8.0.30/sql/bootstrap.cc:327 #27 0x0000000002df96c3 in pfs_spawn_thread (arg=0x7bcc8c0) at /data1/mysql/mysql-8.0.30/storage/perfschema/pfs.cc:2942 #28 0x00007ffff77bcea5 in start_thread () from /lib64/libpthread.so.0 #29 0x00007ffff64b2b0d in clone () from /lib64/libc.so.6 ``` The SQL that reports the error is ``` (gdb) p thd->m_query_string $2 = { str = 0x7fe68dfb8e58 "CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ENABLED_ROLES AS SELECT \n ROLE_NAME AS ROLE_NAME,\n ROLE_HOST AS ROLE_HOST,\n (SELECT IF(COUNT(*), 'YES', 'NO') FROM mysql.default_roles WHERE DEFAULT_ROLE_USER = ROLE_NAME AND CONVERT(DEFAULT_ROLE_HOST using utf8mb4) = ROLE_HOST AND USER = INTERNAL_GET_USERNAME() AND CONVERT(HOST using utf8mb4) = INTERNAL_GET_HOSTNAME()) AS IS_DEFAULT,\n IF(INTERNAL_IS_MANDATORY_ROLE(ROLE_NAME, ROLE_HOST), 'YES', 'NO') AS IS_MANDATORY FROM \n JSON_TABLE(INTERNAL_GET_ENABLED_ROLE_JSON(), '$[*]' COLUMNS ( ROLE_NAME VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_NAME', ROLE_HOST VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_HOST') ) current_user_enabled_roles", length = 759} ``` How to repeat: Refer to the upgrade steps in the official documentation: https://dev.mysql.com/doc/refman/8.0/en/upgrade-binary-package.html The above error will be reported when starting Suggested fix: default_collations_for_utf8mb4 is set to utf8mb4_general_ci which is incompatible with the default utf8mb4_0900_ai_ci and it does not cover all functions, the conversion (using utf8mb4) still uses the default utf8mb4_0900_ai_ci collation, causing creation of system views to fail