0

my error was: export cause db error, not import cause error.

MySQL version: 5.7.12 and my table defined as:

CREATE TABLE `process_ext_info` (
  `id` bigint(20) NOT NULL COMMENT 'key',
  `msg_info` longtext CHARACTER SET utf8 NOT NULL COMMENT 'task para',
  `schdTaskType` int(11) GENERATED ALWAYS AS (json_extract(`msg_info`,'$.extInfo.schdTaskType')) VIRTUAL COMMENT 'task type',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='task para info'

I use this to backup my db: mydumper -h 10.0.0.1 -u admin -p 123456 --no-locks -G -E -R --regex '^(processor-test).*' -o /bak/processor-test

while command finished success, my app begin report error from insert sql: <#2126615b> c.m.c.j.e.MysqlDataTruncation: Data truncation: Cannot create a JSON value from a string with CHARACTER SET 'binary'.

but my app didn't publish for long time. then I droped and recreate the 2 field: msg_info and schdTaskType,
the error stoped and my app auto back to normal.

did mydumper changed config of source db? how could I fix it? thanks.

youbl
  • 134
  • 1
  • 11
  • I reproduced the error in test envirment, and guess it's mysql bug. because sql `select * from process_ext_info limit 1` will report error, but sql `SELECT msg_info,JSON_EXTRACT(`msg_info`,'$.extInfo.schdTaskType') FROM process_ext_info` runed well,and return data normaly. so I guess, in mysql5.7.12, compute column with json_extract maybe have any bug, I'll test newly version for it. – youbl Feb 20 '23 at 09:25

0 Answers0