mongo -> mysql import, ER_TOO_LONG_KEY

[ghost]

If the _id is treated as a string, the CREATE TABLE command will use a VARCHAR(255) which is too large for a MYSQL string index if using collation of utf8mb4_general_ci

Fix for this was to change the type defines to VARCHAR(50) instead of VARCHAR(255)

Future releases should allow variable VARCHAR size in the JSON

Stack trace 9 Mar 21:07:05 - Connect to MySQL... 9 Mar 21:07:05 - { Error: ER_TOO_LONG_KEY: Specified key was too long; max key length is 767 bytes at Query.Sequence._packetToError (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14) at Query.ErrorPacket (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/protocol/sequences/Query.js:77:18) at Protocol._parsePacket (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/protocol/Protocol.js:280:23) at Parser.write (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/protocol/Parser.js:75:12) at Protocol.write (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/protocol/Protocol.js:39:16) at Socket.<anonymous> (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/Connection.js:103:28) at emitOne (events.js:96:13) at Socket.emit (events.js:189:7) at readableAddChunk (_stream_readable.js:176:18) at Socket.Readable.push (_stream_readable.js:134:10) -------------------- at Protocol._enqueue (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/protocol/Protocol.js:141:48) at Connection.query (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/Connection.js:208:25) at Promise (/usr/local/lib/node_modules/momy/lib/mysql.js:177:10) at MySQL.query (/usr/local/lib/node_modules/momy/lib/mysql.js:175:12) at query.then.then (/usr/local/lib/node_modules/momy/lib/mysql.js:116:24) at process._tickCallback (internal/process/next_tick.js:103:7) code: 'ER_TOO_LONG_KEY', errno: 1071, sqlState: '42000', index: 1 }

[cognitom]

@Bob565656 thanks for reporting!

It seems that we just need varchar(24)? ObjectID is 12 byte length. https://docs.mongodb.com/manual/reference/method/ObjectId/#ObjectId

[game5413]

If the _id is treated as a string, the CREATE TABLE command will use a VARCHAR(255) which is too large for a MYSQL string index if using collation of utf8mb4_general_ci

Fix for this was to change the type defines to VARCHAR(50) instead of VARCHAR(255)

Future releases should allow variable VARCHAR size in the JSON

Stack trace 9 Mar 21:07:05 - Connect to MySQL... 9 Mar 21:07:05 - { Error: ER_TOO_LONG_KEY: Specified key was too long; max key length is 767 bytes at Query.Sequence._packetToError (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14) at Query.ErrorPacket (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/protocol/sequences/Query.js:77:18) at Protocol._parsePacket (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/protocol/Protocol.js:280:23) at Parser.write (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/protocol/Parser.js:75:12) at Protocol.write (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/protocol/Protocol.js:39:16) at Socket.<anonymous> (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/Connection.js:103:28) at emitOne (events.js:96:13) at Socket.emit (events.js:189:7) at readableAddChunk (_stream_readable.js:176:18) at Socket.Readable.push (_stream_readable.js:134:10) -------------------- at Protocol._enqueue (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/protocol/Protocol.js:141:48) at Connection.query (/usr/local/lib/node_modules/momy/node_modules/mysql/lib/Connection.js:208:25) at Promise (/usr/local/lib/node_modules/momy/lib/mysql.js:177:10) at MySQL.query (/usr/local/lib/node_modules/momy/lib/mysql.js:175:12) at query.then.then (/usr/local/lib/node_modules/momy/lib/mysql.js:116:24) at process._tickCallback (internal/process/next_tick.js:103:7) code: 'ER_TOO_LONG_KEY', errno: 1071, sqlState: '42000', index: 1 }

In my case, i had change the collation of MySQL database to utf8_swedish_ci, and it work. After read some articles, some collation have different maximum for storing key.