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'-')
