0

Related tickets I've looked at: 1, 2, 3

My problem with the solutions presented is that they're asking the dumpfile to be changed, which is not possible in this case.

The slave thread is being blocked by this error:

  Error 'Cannot create a JSON value from a string with CHARACTER SET 'binary'.' on query. Default database: 'db'. Query: 'UPDATE ....

My replica Aurora MySQL is reading the binlog replication file as binary and cannot produce a json from it. One of the things I noted is this part in the json object

_utf8mb4'<json object here>' COLLATE 'utf8mb4_bin'

Is there anything I can change in the primary DB instance parameters? Or the aurora replica?

Any help on this is appreciated!

EDIT: More info on the schema

Char and coll variables of Aurora Replica:

character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database  latin1
character_set_filesystem    utf8mb4
character_set_results   utf8mb4
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /rdsdbbin/oscar-5.7.mysql_aurora.2.10.1.0.4.0/share/charsets/
collation_connection    utf8mb4_general_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci

Char and coll variables of primary DB:

character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8mb4
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /rdsdbbin/mysql-5.7.33.R2/share/charsets/
collation_connection    utf8mb4_general_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci

(Note, I manually changed character_set_filesystem to utf8mb4 in the Aurora replica to try as a potential solution but it still gives the same error)

TABLE with error have exactly the same CREATE statement in primary DB and replica

CREATE TABLE `item_list` (
  `_id` varchar(50) NOT NULL,
  `item_id` varchar(50) NOT NULL,
  `description_id` varchar(45) NOT NULL,
  `user_id` varchar(45) NOT NULL,
  `last_action` json DEFAULT NULL,
  `item_info` json DEFAULT NULL,
  PRIMARY KEY (`item_id`,`description_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The query text in last_sql_error (text was cut off)

UPDATE item_list\n    SET last_action =  NAME_CONST('p_last_action',_utf8mb4'{\\"amount\\": 25380, \\"message\\": \\"Send to JL@CW\\", \\"actionId\\": \\"send\\", \\"photoPath\\": \\"https://s3-us-west-1.amazonaws.com/VBFrNVYIr.jpg\\"' COLLATE 'utf8mb4_bin') WHERE item_id =  NAME_CONST('p_item_
EJCris
  • 1
  • 2
  • Provide complete SQL text of the problematic query. Provide complete CREATE TABLE for all tables mentioned in it. Provide the values for all session variables like `'char%'` and `'coll%'` (also check that both tables structures and charset/collation settings on master and slave are identical precisely). Provide the info as formatted code/text, none screenshots. – Akina Jan 12 '22 at 07:22
  • Added the information needed @Akina – EJCris Jan 12 '22 at 08:46
  • In the query text I see explicit `COLLATE 'utf8mb4_bin'`... for what reason? – Akina Jan 12 '22 at 08:51
  • The dumpfile was created automatically by the AWS primary DB instance and read automatically by the replica. I am also trying to find out how the dumpfile creates its queries but I've found nothing regarding it in AWS Documentation – EJCris Jan 12 '22 at 09:07
  • As I understand you use statement-based replication. If so then the binlog contains the statements which were executed on the master, is it? I doubt that the master server adds something into the query which must be executed on the slave... check how the same query looks like on the master side. – Akina Jan 12 '22 at 09:10
  • The dumpfile did indeed add something to our query to transform the json. I'll input the full result of our investigation below – EJCris Jan 14 '22 at 09:02

1 Answers1

0

For those who encountered the same issue, here's the result of our investigation.

We were looking at the wrong part of the code. There was another json query after this that caused the error

UPDATE db.item SET last_action = IFNULL(null, last_action) WHERE id = 1

In the binlog dumpfile, because the query is inside a stored procedure, it looked like this:

UPDATE db.item SET last_action = IFNULL(NAME_CONST('sp_param', null), last_action) WHERE id = 1

The dumpfile added the NAME_CONST(). If you run the exact query above in MySQL, it works perfectly. But if you run it in Aurora MySQL, you get the Cannot create a JSON value from a string with CHARACTER SET 'binary'.

Why? MySQL and Aurora MySQL returns different types for the same query

SELECT IFNULL(NAME_CONST('sp_param', value), last_action) FROM db.item WHERE id = 1;

MySQL --> returns a json
Aurora MySQL --> returns a blob which causes the error

Removing the NAME_CONST() fixes the query in Aurora MySQL. But as that is included in the dumpfile produced by the MySQL binlog replication, that cannot be the solution.

The solution is to use COALESCE instead of IFNULL as it has the same behavior in Aurora MySQL

UPDATE db.item SET last_action = COALESCE(NAME_CONST(sp_param), last_action) WHERE id = 1
EJCris
  • 1
  • 2