Why does `SET NAMES utf8` change the behaviour of `REPLACE(uuid(),…)` calls?
2
While investigating an issue with a failing migration I found out the following strange behaviour. Using SET NAMES utf8 on my client session changes the behaviour of REPLACE(uuid(),'','') calls.
mysql> select replace(uuid(),'','') from mysql.user;
+--------------------------------------+
| replace(uuid(),'','') |
+--------------------------------------+
| 4b483d57-ecdc-11e8-844f-0242ac120002 |
| 4b483d57-ecdc-11e8-844f-0242ac120002 |
| 4b483d57-ecdc-11e8-844f-0242ac120002 |
+--------------------------------------+
3 rows in set (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> select replace(uuid(),'','') from mysql.user;
+--------------------------------------+
| replace(uuid(),'','') |
+--------------------------------------+
| 539c0b5c-ecdc-11e8-844f-0242ac120002 |
| 539c0b79-ecdc-11e8-844f-0242ac120002 |
| 539c0b7f-ecdc-11e8-844f-0242ac120002 |
+--------------------------------------+
3 rows in set (0.01 sec)
As you can see the generated UUID's are unique only after setting NAMES to utf8. The way I found out about SET NAMES utf8 was passing the query through MySQL Workbench.
I would greatly appreciate if someone here can explain how character sets (NAMES) influence the output of REPLACE(UUID(), ...) calls. Thanks in advance.
Update: adding a snippet to prove that the problem 1) is not with UUID() generating non-unique values and 2) relates to utf8mb4 charset
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
mysql> select uuid, replace(uuid,'','') from ( select uuid() as uuid from mysql.user) tmp;
+--------------------------------------+--------------------------------------+
| uuid | replace(uuid,'','') |
+--------------------------------------+--------------------------------------+
| e41d2fe4-ed70-11e8-844f-0242ac120002 | e41d2dc2-ed70-11e8-844f-0242ac120002 |
| e41d3042-ed70-11e8-844f-0242ac120002 | e41d2dc2-ed70-11e8-844f-0242ac120002 |
| e41d309c-ed70-11e8-844f-0242ac120002 | e41d2dc2-ed70-11e8-844f-0242ac120002 |
+--------------------------------------+--------------------------------------+
3 rows in set (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> select uuid, replace(uuid,'','') from ( select uuid() as uuid from mysql.user) tmp;
+--------------------------------------+--------------------------------------+
| uuid | replace(uuid,'','') |
+--------------------------------------+--------------------------------------+
| e9059092-ed70-11e8-844f-0242ac120002 | e9059117-ed70-11e8-844f-0242ac120002 |
| e90591a1-ed70-11e8-844f-0242ac120002 | e905923e-ed70-11e8-844f-0242ac120002 |
| e9059380-ed70-11e8-844f-0242ac120002 | e90593e1-ed70-11e8-844f-0242ac120002 |
+--------------------------------------+--------------------------------------+
3 rows in set (0.00 sec)
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> select uuid, replace(uuid,'','') from ( select uuid() as uuid from mysql.user) tmp;
+--------------------------------------+--------------------------------------+
| uuid | replace(uuid,'','') |
+--------------------------------------+--------------------------------------+
| ef564f32-ed70-11e8-844f-0242ac120002 | ef564d0c-ed70-11e8-844f-0242ac120002 |
| ef564fa4-ed70-11e8-844f-0242ac120002 | ef564d0c-ed70-11e8-844f-0242ac120002 |
| ef565019-ed70-11e8-844f-0242ac120002 | ef564d0c-ed70-11e8-844f-0242ac120002 |
+--------------------------------------+--------------------------------------+
3 rows in set (0.00 sec)
Update 2: Adding EXPLAIN queries below to trace the actual SQL code as suggested by @qbolec. That reveals that the use of CONVERT(... using ...) is the culprit for the non-unique UUIDs. I still do not exactly understand why as this is not the behaviour I expect from CONVERT() func.
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select replace(uuid(),'','') from mysql.user;W
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | PRIMARY | 276 | NULL | 7 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select replace(convert(uuid() using utf8mb4),'','') AS `replace(uuid(),'','')` from `mysql`.`user`
Show warnings enabled.
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select replace(uuid(),'','') from mysql.user;W
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | PRIMARY | 276 | NULL | 7 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select replace(uuid(),'','') AS `replace(uuid(),'','')` from `mysql`.`user`
Show warnings enabled.
mysql replace mysql-workbench uuid
asked Nov 20 '18 at 16:00
AlexAlex
1018
add a comment |
2
While investigating an issue with a failing migration I found out the following strange behaviour. Using SET NAMES utf8 on my client session changes the behaviour of REPLACE(uuid(),'','') calls.
mysql> select replace(uuid(),'','') from mysql.user;
+--------------------------------------+
| replace(uuid(),'','') |
+--------------------------------------+
| 4b483d57-ecdc-11e8-844f-0242ac120002 |
| 4b483d57-ecdc-11e8-844f-0242ac120002 |
| 4b483d57-ecdc-11e8-844f-0242ac120002 |
+--------------------------------------+
3 rows in set (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> select replace(uuid(),'','') from mysql.user;
+--------------------------------------+
| replace(uuid(),'','') |
+--------------------------------------+
| 539c0b5c-ecdc-11e8-844f-0242ac120002 |
| 539c0b79-ecdc-11e8-844f-0242ac120002 |
| 539c0b7f-ecdc-11e8-844f-0242ac120002 |
+--------------------------------------+
3 rows in set (0.01 sec)
As you can see the generated UUID's are unique only after setting NAMES to utf8. The way I found out about SET NAMES utf8 was passing the query through MySQL Workbench.
I would greatly appreciate if someone here can explain how character sets (NAMES) influence the output of REPLACE(UUID(), ...) calls. Thanks in advance.
Update: adding a snippet to prove that the problem 1) is not with UUID() generating non-unique values and 2) relates to utf8mb4 charset
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
mysql> select uuid, replace(uuid,'','') from ( select uuid() as uuid from mysql.user) tmp;
+--------------------------------------+--------------------------------------+
| uuid | replace(uuid,'','') |
+--------------------------------------+--------------------------------------+
| e41d2fe4-ed70-11e8-844f-0242ac120002 | e41d2dc2-ed70-11e8-844f-0242ac120002 |
| e41d3042-ed70-11e8-844f-0242ac120002 | e41d2dc2-ed70-11e8-844f-0242ac120002 |
| e41d309c-ed70-11e8-844f-0242ac120002 | e41d2dc2-ed70-11e8-844f-0242ac120002 |
+--------------------------------------+--------------------------------------+
3 rows in set (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> select uuid, replace(uuid,'','') from ( select uuid() as uuid from mysql.user) tmp;
+--------------------------------------+--------------------------------------+
| uuid | replace(uuid,'','') |
+--------------------------------------+--------------------------------------+
| e9059092-ed70-11e8-844f-0242ac120002 | e9059117-ed70-11e8-844f-0242ac120002 |
| e90591a1-ed70-11e8-844f-0242ac120002 | e905923e-ed70-11e8-844f-0242ac120002 |
| e9059380-ed70-11e8-844f-0242ac120002 | e90593e1-ed70-11e8-844f-0242ac120002 |
+--------------------------------------+--------------------------------------+
3 rows in set (0.00 sec)
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> select uuid, replace(uuid,'','') from ( select uuid() as uuid from mysql.user) tmp;
+--------------------------------------+--------------------------------------+
| uuid | replace(uuid,'','') |
+--------------------------------------+--------------------------------------+
| ef564f32-ed70-11e8-844f-0242ac120002 | ef564d0c-ed70-11e8-844f-0242ac120002 |
| ef564fa4-ed70-11e8-844f-0242ac120002 | ef564d0c-ed70-11e8-844f-0242ac120002 |
| ef565019-ed70-11e8-844f-0242ac120002 | ef564d0c-ed70-11e8-844f-0242ac120002 |
+--------------------------------------+--------------------------------------+
3 rows in set (0.00 sec)
Update 2: Adding EXPLAIN queries below to trace the actual SQL code as suggested by @qbolec. That reveals that the use of CONVERT(... using ...) is the culprit for the non-unique UUIDs. I still do not exactly understand why as this is not the behaviour I expect from CONVERT() func.
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select replace(uuid(),'','') from mysql.user;W
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | PRIMARY | 276 | NULL | 7 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select replace(convert(uuid() using utf8mb4),'','') AS `replace(uuid(),'','')` from `mysql`.`user`
Show warnings enabled.
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select replace(uuid(),'','') from mysql.user;W
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | PRIMARY | 276 | NULL | 7 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select replace(uuid(),'','') AS `replace(uuid(),'','')` from `mysql`.`user`
Show warnings enabled.
mysql replace mysql-workbench uuid
asked Nov 20 '18 at 16:00
AlexAlex
1018
Its normal the uuid varies on the first block. Maybe, the problem are because the first query has been executed too fast (uuid is time based). Check wikipedia (en.wikipedia.org/wiki/Universally_unique_identifier) for more information about uuid composition.
– Sakura Kinomoto
Nov 21 '18 at 0:46
Have you tried to set back the connection/client charset to the value it had originally and what comes out then?
– Mike Lischke
Nov 21 '18 at 8:47
@MikeLischke I just tried that with the same effect. Original charset is utf8mb4. I updated my question to include the test commands I used.
– Alex
Nov 21 '18 at 9:47
1
You can tryEXPLAIN <your query>followed bySHOW WARNINGSGto see what your query looks like after optimizer applies it's magic. I have a suspicion, that optimizer changes the query into non-equivalent form in whichuuid()is called multiple times per single row. My belief is that it should never do that, but perhaps it only does so for certain code-pages/configurations. IMHO it is worth reporting as a bugs.mysql.com
– qbolec
Nov 22 '18 at 9:21
add a comment |
2
2
2
While investigating an issue with a failing migration I found out the following strange behaviour. Using SET NAMES utf8 on my client session changes the behaviour of REPLACE(uuid(),'','') calls.
mysql> select replace(uuid(),'','') from mysql.user;
+--------------------------------------+
| replace(uuid(),'','') |
+--------------------------------------+
| 4b483d57-ecdc-11e8-844f-0242ac120002 |
| 4b483d57-ecdc-11e8-844f-0242ac120002 |
| 4b483d57-ecdc-11e8-844f-0242ac120002 |
+--------------------------------------+
3 rows in set (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> select replace(uuid(),'','') from mysql.user;
+--------------------------------------+
| replace(uuid(),'','') |
+--------------------------------------+
| 539c0b5c-ecdc-11e8-844f-0242ac120002 |
| 539c0b79-ecdc-11e8-844f-0242ac120002 |
| 539c0b7f-ecdc-11e8-844f-0242ac120002 |
+--------------------------------------+
3 rows in set (0.01 sec)
As you can see the generated UUID's are unique only after setting NAMES to utf8. The way I found out about SET NAMES utf8 was passing the query through MySQL Workbench.
I would greatly appreciate if someone here can explain how character sets (NAMES) influence the output of REPLACE(UUID(), ...) calls. Thanks in advance.
Update: adding a snippet to prove that the problem 1) is not with UUID() generating non-unique values and 2) relates to utf8mb4 charset
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
mysql> select uuid, replace(uuid,'','') from ( select uuid() as uuid from mysql.user) tmp;
+--------------------------------------+--------------------------------------+
| uuid | replace(uuid,'','') |
+--------------------------------------+--------------------------------------+
| e41d2fe4-ed70-11e8-844f-0242ac120002 | e41d2dc2-ed70-11e8-844f-0242ac120002 |
| e41d3042-ed70-11e8-844f-0242ac120002 | e41d2dc2-ed70-11e8-844f-0242ac120002 |
| e41d309c-ed70-11e8-844f-0242ac120002 | e41d2dc2-ed70-11e8-844f-0242ac120002 |
+--------------------------------------+--------------------------------------+
3 rows in set (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> select uuid, replace(uuid,'','') from ( select uuid() as uuid from mysql.user) tmp;
+--------------------------------------+--------------------------------------+
| uuid | replace(uuid,'','') |
+--------------------------------------+--------------------------------------+
| e9059092-ed70-11e8-844f-0242ac120002 | e9059117-ed70-11e8-844f-0242ac120002 |
| e90591a1-ed70-11e8-844f-0242ac120002 | e905923e-ed70-11e8-844f-0242ac120002 |
| e9059380-ed70-11e8-844f-0242ac120002 | e90593e1-ed70-11e8-844f-0242ac120002 |
+--------------------------------------+--------------------------------------+
3 rows in set (0.00 sec)
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> select uuid, replace(uuid,'','') from ( select uuid() as uuid from mysql.user) tmp;
+--------------------------------------+--------------------------------------+
| uuid | replace(uuid,'','') |
+--------------------------------------+--------------------------------------+
| ef564f32-ed70-11e8-844f-0242ac120002 | ef564d0c-ed70-11e8-844f-0242ac120002 |
| ef564fa4-ed70-11e8-844f-0242ac120002 | ef564d0c-ed70-11e8-844f-0242ac120002 |
| ef565019-ed70-11e8-844f-0242ac120002 | ef564d0c-ed70-11e8-844f-0242ac120002 |
+--------------------------------------+--------------------------------------+
3 rows in set (0.00 sec)
Update 2: Adding EXPLAIN queries below to trace the actual SQL code as suggested by @qbolec. That reveals that the use of CONVERT(... using ...) is the culprit for the non-unique UUIDs. I still do not exactly understand why as this is not the behaviour I expect from CONVERT() func.
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select replace(uuid(),'','') from mysql.user;W
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | PRIMARY | 276 | NULL | 7 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select replace(convert(uuid() using utf8mb4),'','') AS `replace(uuid(),'','')` from `mysql`.`user`
Show warnings enabled.
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select replace(uuid(),'','') from mysql.user;W
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | PRIMARY | 276 | NULL | 7 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select replace(uuid(),'','') AS `replace(uuid(),'','')` from `mysql`.`user`
Show warnings enabled.
mysql replace mysql-workbench uuid
asked Nov 20 '18 at 16:00
AlexAlex
1018
While investigating an issue with a failing migration I found out the following strange behaviour. Using SET NAMES utf8 on my client session changes the behaviour of REPLACE(uuid(),'','') calls.
mysql> select replace(uuid(),'','') from mysql.user;
+--------------------------------------+
| replace(uuid(),'','') |
+--------------------------------------+
| 4b483d57-ecdc-11e8-844f-0242ac120002 |
| 4b483d57-ecdc-11e8-844f-0242ac120002 |
| 4b483d57-ecdc-11e8-844f-0242ac120002 |
+--------------------------------------+
3 rows in set (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> select replace(uuid(),'','') from mysql.user;
+--------------------------------------+
| replace(uuid(),'','') |
+--------------------------------------+
| 539c0b5c-ecdc-11e8-844f-0242ac120002 |
| 539c0b79-ecdc-11e8-844f-0242ac120002 |
| 539c0b7f-ecdc-11e8-844f-0242ac120002 |
+--------------------------------------+
3 rows in set (0.01 sec)
As you can see the generated UUID's are unique only after setting NAMES to utf8. The way I found out about SET NAMES utf8 was passing the query through MySQL Workbench.
I would greatly appreciate if someone here can explain how character sets (NAMES) influence the output of REPLACE(UUID(), ...) calls. Thanks in advance.
Update: adding a snippet to prove that the problem 1) is not with UUID() generating non-unique values and 2) relates to utf8mb4 charset
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
mysql> select uuid, replace(uuid,'','') from ( select uuid() as uuid from mysql.user) tmp;
+--------------------------------------+--------------------------------------+
| uuid | replace(uuid,'','') |
+--------------------------------------+--------------------------------------+
| e41d2fe4-ed70-11e8-844f-0242ac120002 | e41d2dc2-ed70-11e8-844f-0242ac120002 |
| e41d3042-ed70-11e8-844f-0242ac120002 | e41d2dc2-ed70-11e8-844f-0242ac120002 |
| e41d309c-ed70-11e8-844f-0242ac120002 | e41d2dc2-ed70-11e8-844f-0242ac120002 |
+--------------------------------------+--------------------------------------+
3 rows in set (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> select uuid, replace(uuid,'','') from ( select uuid() as uuid from mysql.user) tmp;
+--------------------------------------+--------------------------------------+
| uuid | replace(uuid,'','') |
+--------------------------------------+--------------------------------------+
| e9059092-ed70-11e8-844f-0242ac120002 | e9059117-ed70-11e8-844f-0242ac120002 |
| e90591a1-ed70-11e8-844f-0242ac120002 | e905923e-ed70-11e8-844f-0242ac120002 |
| e9059380-ed70-11e8-844f-0242ac120002 | e90593e1-ed70-11e8-844f-0242ac120002 |
+--------------------------------------+--------------------------------------+
3 rows in set (0.00 sec)
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> select uuid, replace(uuid,'','') from ( select uuid() as uuid from mysql.user) tmp;
+--------------------------------------+--------------------------------------+
| uuid | replace(uuid,'','') |
+--------------------------------------+--------------------------------------+
| ef564f32-ed70-11e8-844f-0242ac120002 | ef564d0c-ed70-11e8-844f-0242ac120002 |
| ef564fa4-ed70-11e8-844f-0242ac120002 | ef564d0c-ed70-11e8-844f-0242ac120002 |
| ef565019-ed70-11e8-844f-0242ac120002 | ef564d0c-ed70-11e8-844f-0242ac120002 |
+--------------------------------------+--------------------------------------+
3 rows in set (0.00 sec)
Update 2: Adding EXPLAIN queries below to trace the actual SQL code as suggested by @qbolec. That reveals that the use of CONVERT(... using ...) is the culprit for the non-unique UUIDs. I still do not exactly understand why as this is not the behaviour I expect from CONVERT() func.
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select replace(uuid(),'','') from mysql.user;W
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | PRIMARY | 276 | NULL | 7 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select replace(convert(uuid() using utf8mb4),'','') AS `replace(uuid(),'','')` from `mysql`.`user`
Show warnings enabled.
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select replace(uuid(),'','') from mysql.user;W
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | PRIMARY | 276 | NULL | 7 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select replace(uuid(),'','') AS `replace(uuid(),'','')` from `mysql`.`user`
Show warnings enabled.
mysql replace mysql-workbench uuid
mysql replace mysql-workbench uuid
asked Nov 20 '18 at 16:00
AlexAlex
1018
asked Nov 20 '18 at 16:00
AlexAlex
1018
edited Nov 22 '18 at 11:36
Alex
asked Nov 20 '18 at 16:00
AlexAlex
1018
asked Nov 20 '18 at 16:00
AlexAlex
1018
asked Nov 20 '18 at 16:00
AlexAlex
1018
1018
Its normal the uuid varies on the first block. Maybe, the problem are because the first query has been executed too fast (uuid is time based). Check wikipedia (en.wikipedia.org/wiki/Universally_unique_identifier) for more information about uuid composition.
– Sakura Kinomoto
Nov 21 '18 at 0:46
Have you tried to set back the connection/client charset to the value it had originally and what comes out then?
– Mike Lischke
Nov 21 '18 at 8:47
@MikeLischke I just tried that with the same effect. Original charset is utf8mb4. I updated my question to include the test commands I used.
– Alex
Nov 21 '18 at 9:47
1
You can tryEXPLAIN <your query>followed bySHOW WARNINGSGto see what your query looks like after optimizer applies it's magic. I have a suspicion, that optimizer changes the query into non-equivalent form in whichuuid()is called multiple times per single row. My belief is that it should never do that, but perhaps it only does so for certain code-pages/configurations. IMHO it is worth reporting as a bugs.mysql.com
– qbolec
Nov 22 '18 at 9:21
add a comment |
Its normal the uuid varies on the first block. Maybe, the problem are because the first query has been executed too fast (uuid is time based). Check wikipedia (en.wikipedia.org/wiki/Universally_unique_identifier) for more information about uuid composition.
– Sakura Kinomoto
Nov 21 '18 at 0:46
Have you tried to set back the connection/client charset to the value it had originally and what comes out then?
– Mike Lischke
Nov 21 '18 at 8:47
@MikeLischke I just tried that with the same effect. Original charset is utf8mb4. I updated my question to include the test commands I used.
– Alex
Nov 21 '18 at 9:47
1
You can tryEXPLAIN <your query>followed bySHOW WARNINGSGto see what your query looks like after optimizer applies it's magic. I have a suspicion, that optimizer changes the query into non-equivalent form in whichuuid()is called multiple times per single row. My belief is that it should never do that, but perhaps it only does so for certain code-pages/configurations. IMHO it is worth reporting as a bugs.mysql.com
– qbolec
Nov 22 '18 at 9:21
Its normal the uuid varies on the first block. Maybe, the problem are because the first query has been executed too fast (uuid is time based). Check wikipedia (en.wikipedia.org/wiki/Universally_unique_identifier) for more information about uuid composition.
– Sakura Kinomoto
Nov 21 '18 at 0:46
Its normal the uuid varies on the first block. Maybe, the problem are because the first query has been executed too fast (uuid is time based). Check wikipedia (en.wikipedia.org/wiki/Universally_unique_identifier) for more information about uuid composition.
– Sakura Kinomoto
Nov 21 '18 at 0:46
Have you tried to set back the connection/client charset to the value it had originally and what comes out then?
– Mike Lischke
Nov 21 '18 at 8:47
Have you tried to set back the connection/client charset to the value it had originally and what comes out then?
– Mike Lischke
Nov 21 '18 at 8:47
@MikeLischke I just tried that with the same effect. Original charset is utf8mb4. I updated my question to include the test commands I used.
– Alex
Nov 21 '18 at 9:47
@MikeLischke I just tried that with the same effect. Original charset is utf8mb4. I updated my question to include the test commands I used.
– Alex
Nov 21 '18 at 9:47
1
1
You can try
EXPLAIN <your query> followed by SHOW WARNINGSG to see what your query looks like after optimizer applies it's magic. I have a suspicion, that optimizer changes the query into non-equivalent form in which uuid() is called multiple times per single row. My belief is that it should never do that, but perhaps it only does so for certain code-pages/configurations. IMHO it is worth reporting as a bugs.mysql.com– qbolec
Nov 22 '18 at 9:21
You can try
EXPLAIN <your query> followed by SHOW WARNINGSG to see what your query looks like after optimizer applies it's magic. I have a suspicion, that optimizer changes the query into non-equivalent form in which uuid() is called multiple times per single row. My belief is that it should never do that, but perhaps it only does so for certain code-pages/configurations. IMHO it is worth reporting as a bugs.mysql.com– qbolec
Nov 22 '18 at 9:21
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53396913%2fwhy-does-set-names-utf8-change-the-behaviour-of-replaceuuid-calls%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53396913%2fwhy-does-set-names-utf8-change-the-behaviour-of-replaceuuid-calls%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Its normal the uuid varies on the first block. Maybe, the problem are because the first query has been executed too fast (uuid is time based). Check wikipedia (en.wikipedia.org/wiki/Universally_unique_identifier) for more information about uuid composition.
– Sakura Kinomoto
Nov 21 '18 at 0:46
Have you tried to set back the connection/client charset to the value it had originally and what comes out then?
– Mike Lischke
Nov 21 '18 at 8:47
@MikeLischke I just tried that with the same effect. Original charset is utf8mb4. I updated my question to include the test commands I used.
– Alex
Nov 21 '18 at 9:47
1
You can try
EXPLAIN <your query>followed bySHOW WARNINGSGto see what your query looks like after optimizer applies it's magic. I have a suspicion, that optimizer changes the query into non-equivalent form in whichuuid()is called multiple times per single row. My belief is that it should never do that, but perhaps it only does so for certain code-pages/configurations. IMHO it is worth reporting as a bugs.mysql.com– qbolec
Nov 22 '18 at 9:21