mongo -> mysql import, ER_TOO_LONG_KEY
- Dominant language
- JavaScript
- Stars
- 97
- Forks
- 35
- PR merge metrics
- No merged PRs in 30d
Description
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. (/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 }`
Contributor guide
No contributing guide indexed for this repository
Research direction
Start in lib/mysql.js around the query calls shown in the stack trace and trace the Mongo-to-MySQL table creation path. Check the type definitions used for a string _id and reproduce the import with utf8mb4_general_ci. Done means the CREATE TABLE query avoids ER_TOO_LONG_KEY, with the requested VARCHAR sizing or configuration behavior addressed.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- javascript, mongodb, mysql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100