Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

mongo -> mysql import, ER_TOO_LONG_KEY #12

Open
ghost opened this issue Mar 9, 2017 · 2 comments
Open

mongo -> mysql import, ER_TOO_LONG_KEY #12

ghost opened this issue Mar 9, 2017 · 2 comments

Comments

@ghost
Copy link

ghost commented Mar 9, 2017

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
Copy link
Owner

@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
Copy link

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants