vitess: Bug Report: Cannot create a JSON value from a string with CHARACTER SET 'binary' in online ddl

Overview of the Issue

When running a Vitess onlineddl schema migration with json generated columns, the migration fails in the copy phase with Cannot create a JSON value from a string with CHARACTER SET 'binary'

Reproduction Steps

Create table:

CREATE TABLE `orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `orderPaymentMethod` json DEFAULT NULL,
  `stripePaymentMethod` tinyint(4) GENERATED ALWAYS AS (json_unquote(json_extract(`orderPaymentMethod`,'$.paymentMethod'))) STORED,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

Insert:

insert into orders(orderPaymentMethod) values (convert('{\"customerId\": \"cus_Il7WwbIODMoaE1\"}' using utf8mb4))

Note that if orderPaymentMethod is null here, the error does not surface, nor does it surface if there are no json generated columns

Run migration:

vtctl ApplySchema -- --ddl_strategy "vitess" --sql "ALTER TABLE orders ENGINE=INNODB;" TestKeyspace

See failures:

executor.go:4069] updateMigrationMessage: uuid=f3387b12_5323_11ee_8c7e_0a4b134e1d27, message=task error: failed inserting rows: Cannot create a JSON value from a string with CHARACTER SET 'binary'. (errno 3144) (sqlstate 22032) during query: insert into _f3387b12_5323_11ee_8c7e_0a4b134e1d27_20230914172717_vrepl(id,orderPaymentMethod) values (1,convert(JSON_OBJECT(_utf8mb4'customerId', _utf8mb4'cus_Il7WwbIODMoaE1') using utf8mb4))

executor.go:2082] cancelMigrations: cancelling f3387b12_5323_11ee_8c7e_0a4b134e1d27; reason: task error: failed inserting rows: Cannot create a JSON value from a string with CHARACTER SET 'binary'. (errno 3144) (sqlstate 22032) during query: insert into _f3387b12_5323_11ee_8c7e_0a4b134e1d27_20230914172717_vrepl(id,orderPaymentMethod) values (1,convert(JSON_OBJECT(_utf8mb4'customerId', _utf8mb4'cus_Il7WwbIODMoaE1') using utf8mb4))

controller.go:261] vreplication stream 5 going into error state due to Cannot create a JSON value from a string with CHARACTER SET 'binary'. (errno 3144) (sqlstate 22032) during query: insert into _f3387b12_5323_11ee_8c7e_0a4b134e1d27_20230914172717_vrepl(id,orderPaymentMethod) values (1,convert(JSON_OBJECT(_utf8mb4'customerId', _utf8mb4'cus_Il7WwbIODMoaE1') using utf8mb4))

Binary Version

v18

Operating System and Environment details

Centos8, Linux 5.4.141-hs22.el8.x86_64

Log Fragments

No response

About this issue

Original URL
State: open
Created 10 months ago
Comments: 17 (17 by maintainers)

Most upvoted comments

Wondering if changing the generated column query to something like, will help, in case the issue is with how MySQL internally processes json for generated columns.

CREATE TABLE `orders` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `orderPaymentMethod` json DEFAULT NULL,
  `stripePaymentMethod` tinyint GENERATED ALWAYS AS (cast(json_unquote(json_extract(`orderPaymentMethod`,_utf8mb4'$.paymentMethod')) as char charset utf8mb4)) STORED,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

rohit-nayak-ps on Sep 20, 2023

Ok so I tested with mysql 8 and my migration worked, even the v14 version, and also Rohit’s workaround works (with 5.7 and v14) - but would require us to change the schemas of everyone with json generated columns. I also tested changing the line that does set names binary to set names utf8mb4 in the table copy and that worked, but of course that probably has unintended side-effects (the comment explains that we need this because Tables may have varying character sets. To ship the bits without interpreting them we set the character set to be binary.).

So perhaps this is a mysql 5.7 + set names binary + json generated columns bug

olyazavr on Sep 20, 2023