CODEWITHSUNDEEP
javascriptmysqlsequelize.js
Jan Bouchner
Jan Bouchner

Reputation: 897

Sequelize - I am getting error: "This version of MySQL doesn't yet support 'item type for JSON"

I have just migrated from sequelize version 4.32.1 to version 5.6.1. Everything worked fine before this update. Now I am getting an error below for almost all my queries.

My MySQL server version is 5.7.25-0ubuntu0.16.04.2-log (Ubuntu) and none of db columns from tables in the query have JSON data type.

This is the body of a function where the error is thrown. A value of "accountIds" is array of numbers.

    let sqlQuery = `
        SELECT ua.*, page.page_id
        FROM user_instagram_fb_page_account ua
        LEFT JOIN user_facebook_page page ON page.id = ua.user_fb_page_id
        WHERE ua.user_account_ig_id IN($accountIds)
    `;
    return this._db.query(sqlQuery, {
        bind: {
            accountIds,
        },
        type: sequelize.QueryTypes.SELECT,
    });

And the error:

  { Error: This version of MySQL doesn't yet support 'item type for JSON'
   at Packet.asError (/Volumes/ZoomSphereDEV/projects/zoomsphere2-api/node_modules/mysql2/lib/packets/packet.js:684:17)
   at Execute.execute (/Volumes/ZoomSphereDEV/projects/zoomsphere2-api/node_modules/mysql2/lib/commands/command.js:28:26)
   at Connection.handlePacket (/Volumes/ZoomSphereDEV/projects/zoomsphere2-api/node_modules/mysql2/lib/connection.js:449:32)
   at PacketParser.Connection.packetParser.p [as onPacket] (/Volumes/ZoomSphereDEV/projects/zoomsphere2-api/node_modules/mysql2/lib/connection.js:72:12)
   at PacketParser.executeStart (/Volumes/ZoomSphereDEV/projects/zoomsphere2-api/node_modules/mysql2/lib/packet_parser.js:75:16)
   at Socket.Connection.stream.on.data (/Volumes/ZoomSphereDEV/projects/zoomsphere2-api/node_modules/mysql2/lib/connection.js:79:25)
   at Socket.emit (events.js:189:13)
   at Socket.EventEmitter.emit (domain.js:441:20)
   at addChunk (_stream_readable.js:284:12)
   at readableAddChunk (_stream_readable.js:265:11)
   at Socket.Readable.push (_stream_readable.js:220:10)
   at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
 code: 'ER_NOT_SUPPORTED_YET',
 errno: 1235,
 sqlState: '42000',
 sqlMessage:
  'This version of MySQL doesn\'t yet support \'item type for JSON\'',
 sql:
  'SELECT ua.*, page.page_id FROM user_instagram_fb_page_account ua\n            LEFT JOIN user_facebook_page page ON page.id = ua.user_fb_page_id\n            WHERE ua.user_account_ig_id IN(?)' },

Could you help me, please?

Upvotes: 2

Views: 2043

Answers (2)

Jan Bouchner
Jan Bouchner

Reputation: 897

According to the documentation [1] I need to rewrite all my raw SQL queries with array data types as parameters from "binding" to "replacements".

So this solution works:

const sqlQuery = `
    SELECT ua.*, page.page_id
    FROM user_instagram_fb_page_account ua
    LEFT JOIN user_facebook_page page ON page.id = ua.user_fb_page_id
    WHERE ua.user_account_ig_id IN(:accountIds)
`;
return this._db.query(sqlQuery, {
    replacements: {
        accountIds,
    },
    type: sequelize.QueryTypes.SELECT,
});

Another working solution is using a template literal:

const sqlQuery = `
    SELECT ua.*, page.page_id
    FROM user_instagram_fb_page_account ua
    LEFT JOIN user_facebook_page page ON page.id = ua.user_fb_page_id
    WHERE ua.user_account_ig_id IN(${accountIds})
`;
return this._db.query(sqlQuery, {
    type: sequelize.QueryTypes.SELECT,
});

[1] http://docs.sequelizejs.com/manual/raw-queries.html

Upvotes: 1

daktaklakpak
daktaklakpak

Reputation: 359

I ran into this problem too, but for the mysql2 library, not sequelize - and I was using a JSON field.

This worked fine:

UPDATE table SET field = '{}' WHERE id = 1

Mysql2 threw the error you got when I tried to run this:

UPDATE table SET field = '{}' WHERE id = 1 AND field = '{"key": "value"}'

Running this from the MySQL shell yields a valid query, but no rows will be matched.

I had to do this to get it to work:

UPDATE table SET field = '{}' WHERE id = 1 AND field = CAST('{"key": "value"}' AS JSON)

Maybe sequelize had to change their parser. If I were you, I would try to convert accountIds into a quoted string and try that instead.

Something like:

return this._db.query(sqlQuery, {
  bind: {
    accountIds.map( (accountId) => {
      return `'${accountId}'`
    }).join(','),
  },
  type: sequelize.QueryTypes.SELECT,
});

Upvotes: 2

Related Questions