Mysql Question posted in
The official MySQL documentation can be found here.

MySQL error when inserting data into JSON column ER_INVALID_JSON_CHARSET

Last Saturday I’ve upgraded our database into MySQL 8 since AWS is deprecating 5.7.
Everything seemed to be working at first, but now we are seeing issues in our system.

I’m using expressJS & sequelize with Node.js. The frontend is sending the data as a POST, and then we are using a .create on that table that has the column as JSON.

Here is the error I am getting:

  original: Error: Cannot create a JSON value from a string with CHARACTER SET 'binary'.
      at Packet.asError (/Users/test/code/root/node_modules/mysql2/lib/packets/packet.js:728:17)
      at Execute.execute (/Users/test/code/root/node_modules/mysql2/lib/commands/command.js:29:26)
      at Connection.handlePacket (/Users/test/code/root/node_modules/mysql2/lib/connection.js:481:34)
      at PacketParser.onPacket (/Users/test/code/root/node_modules/mysql2/lib/connection.js:97:12)
      at PacketParser.executeStart (/Users/test/code/root/node_modules/mysql2/lib/packet_parser.js:75:16)
      at Socket.<anonymous> (/Users/test/code/root/node_modules/mysql2/lib/connection.js:104:25)
      at Socket.emit (node:events:517:28)
      at Socket.emit (node:domain:489:12)
      at addChunk (node:internal/streams/readable:368:12)
      at readableAddChunk (node:internal/streams/readable:341:9)
      at Readable.push (node:internal/streams/readable:278:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    code: 'ER_INVALID_JSON_CHARSET',
    errno: 3144,
    sqlState: '22032',
    sqlMessage: "Cannot create a JSON value from a string with CHARACTER SET 'binary'.",
    sql: 'INSERT INTO `guest_item` (`id`,`parentId`,`parentBookItemId`,`order`,`itemType`,`itemId`,`itemData`,`createdBy`,`updatedBy`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?,?,?,?,?,?,?,?);',
    parameters: [
      2002,
      null,
      0,
      'contact',
      '5',
      '{"id":1,"name":"JANE DOE","email":"[email protected]","jobTitle":"Director of Sales","primaryPhoneNumber":"1112223333","image":null,"propertyName":""}',
      3,
      3,
      '2024-02-28 10:11:02',
      '2024-02-28 10:11:02'
    ]
  }

The table and code are very simple:

      itemData: {
        type: DataTypes.JSON,
        allowNull: true
      },

          await GuestItem.create({
            ...
            itemData: chapterItem.itemData, //this is an object
            ...
          });

If I save this data into the database using a SQL query it works, but through Sequelize it doesn’t.
Here are the things I have tried:

  • Setting a connection charset
  • Make sure the POST call is using Content-type application/json with utf8
  • Using JSON.stringify and parse to clean up the JSON
  • Removing non-utf8 characters using a script.

None of the above has worked. The only solution I found that works is to replace the column with a TEXT and then use getting and setters. We are not using JSON function. But I’m worried there could be issues in our codebase, that could cause issues elsewhere (we have other JSON columns on other tables where we do use JSON functions).

Would someone know what is causing this please?

Tags:

2

Answers


  1. 0

    This may not answer your question, but it may solve your problem.

    With version 8 of MySQL a lot changed, for better or worse is another discussion, but it comes down to compatibility and the direction they have taken is less than ideal.

    In steps MariaDB to save the day!

    Install the latest stable, set your default charset to utf8mb4, unless you have reasons to not, and call it a day.

    Login or Signup to reply.
  2. 0

    Looking at the item that you are encoding, I would have to suggest that the binary object that you are trying to store, albeit an empty one, could well be the image element of itemData (NULL in the example above). I would first encode the "image" into base 64 which is text safe and then retry. it isn’t that you have a whole field which is binary, but an element which is and cannot be coerced.

    I hope this helps you.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search