cognitom / cognitom/momy

mongo -> mysql import, ER_TOO_LONG_KEY

Open
#12 2 comments 0 reactions 0 assignees View on GitHub
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.