Bug #100607 Mysql escapes output where it shouldn't. Breaks schema generation tools
Submitted: 21 Aug 2020 17:22 Modified: 24 Aug 2020 5:45
Reporter: Vadim Sadykhov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:8.0-8.0.21, 8.0.21, 8.0.11 OS:Linux
Assigned to: CPU Architecture:Any
Tags: information_schema, regression

[21 Aug 2020 17:22] Vadim Sadykhov
Description:
For some reason information_schema schema has escaped data for generated columns in the tables. 
So when I use rails to create a schema.rb from my DB I get double escaped generated column meta information. 
There is one interesting moment that if I run Show create table addons it returns right result(not escaped meta).

My goal was to migrate from mysql 5.7 to 8 and in 5.7 everything works right but after version 8 it doesn't so this is showstopper for me :( 

How to repeat:
my current SQL_MODE:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 

Sql Statement:

CREATE TABLE `addons` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid_binary` binary(16) NOT NULL,
  `uuid_string` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (insert(insert(insert(insert(hex(`uuid_binary`),9,0,_utf8mb4'-'),14,0,_utf8mb4'-'),19,0,_utf8mb4'-'),24,0,_utf8mb4'-')) VIRTUAL NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The name of the addon.',
  `external_identifier` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'The id of the addon in external system.',
  `offering` tinyint unsigned DEFAULT NULL,
  `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT 'The status of the addon.',
  `external_updated_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `addons_uuid_binary_unique` (`uuid_binary`) USING BTREE,
  UNIQUE KEY `addons_external_identifier_unique` (`external_identifier`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

show create table addons - works right

But this returns bad meta data

SELECT generation_expression FROM information_schema.columns
WHERE table_schema = 'YOUR_SCHEMA'
AND table_name = 'addons'
AND column_name = 'uuid_string';

result:

insert(insert(insert(insert(hex(`uuid_binary`),9,0,_utf8mb4\'-\'),14,0,_utf8mb4\'-\'),19,0,_utf8mb4\'-\'),24,0,_utf8mb4\'-\')

should be
insert(insert(insert(insert(hex(`uuid_binary`),9,0,_utf8mb4'-'),14,0,_utf8mb4'-'),19,0,_utf8mb4'-'),24,0,_utf8mb4'-')
[24 Aug 2020 5:45] MySQL Verification Team
Hello Vadim Sadykhov,

Thank you for the report and test case.

regards,
Umesh
[16 Jul 2021 8:28] Erlend Dahl
Bug#104294 [REGRESSION] Unexpected escaped string returned in GENERATION_EXPRESSION

was marked as a duplicate.
[19 Dec 2022 19:08] Seth Willits
Confirmed still an issue in 8.0.31