sails-mysql: Error: ER_TOO_LONG_KEY: Specified key was too long; max key length is 1000 bytes

[webcoweb]

Hello
I wanted to run sails with mysql, I set all of configuration with them such as:

path ==> myproject/config/datastores

  default: {
    adapter: 'sails-mysql',
    url: 'mysql://webselect:webselect@localhost:3306/sailsjsdb',
    connectTimeout: 20000
  },

path ==> myproject/config/models

migrate: 'alter',

but when I run this command :
sails lift

only archive table is made that includes (id,createdAt,fromModel,originalRecord,originalRecordId)

and cmd shows me these errors:

C:\Sailsjs\kajhome>sails lift

 info: Starting app...

 info: Initializing hook... (api/hooks/custom)
 info: Initializing apianalytics hook...  (requests to monitored routes will be logged!)
 info: ·• Auto-migrating...  (alter)
 info:    Hold tight, this could take a moment.

When attempting to perform the alter auto-migration strategy on model user, Sails encountered an unexpected error when performing the define step.  This could have happened for a number of different reasons: be it because your database went offline, because of a db permission issue, because of some database-specific edge case, or (more rarely) it could even be due to some kind of bug in this adapter.

Error details:

{ Error: ER_TOO_LONG_KEY: Specified key was too long; max key length is 1000 bytes
    at Query.Sequence._packetToError (C:\Sailsjs\kajhome\node_modules\mysql\lib\protocol\sequences\Sequence.js:48:14)
    at Query.ErrorPacket (C:\Sailsjs\kajhome\node_modules\mysql\lib\protocol\sequences\Query.js:83:18)
    at Protocol._parsePacket (C:\Sailsjs\kajhome\node_modules\mysql\lib\protocol\Protocol.js:280:23)
    at Parser.write (C:\Sailsjs\kajhome\node_modules\mysql\lib\protocol\Parser.js:73:12)
    at Protocol.write (C:\Sailsjs\kajhome\node_modules\mysql\lib\protocol\Protocol.js:39:16)
    at Socket.<anonymous> (C:\Sailsjs\kajhome\node_modules\mysql\lib\Connection.js:96:28)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11)
    at Socket.Readable.push (_stream_readable.js:208:10)
    at TCP.onread (net.js:594:20)
    --------------------
    at Protocol._enqueue (C:\Sailsjs\kajhome\node_modules\mysql\lib\protocol\Protocol.js:141:48)
    at PoolConnection.query (C:\Sailsjs\kajhome\node_modules\mysql\lib\Connection.js:201:25)
    at Object.sendNativeQuery (C:\Sailsjs\kajhome\node_modules\machinepack-mysql\machines\send-native-query.js:149:23)
    at wrapper (C:\Sailsjs\kajhome\node_modules\@sailshq\lodash\lib\index.js:3275:19)
    at Deferred.parley.now.flaverr.name [as _handleExec] (C:\Sailsjs\kajhome\node_modules\machine\lib\private\help-build-machine.js:1068:17)
    at Deferred.exec (C:\Sailsjs\kajhome\node_modules\parley\lib\private\Deferred.js:679:10)
    at Deferred.switch (C:\Sailsjs\kajhome\node_modules\machine\lib\private\help-build-machine.js:1306:16)
    at Object.runNativeQuery (C:\Sailsjs\kajhome\node_modules\sails-mysql\helpers\private\query\run-native-query.js:27:10)
    at spawnConnectionCb (C:\Sailsjs\kajhome\node_modules\sails-mysql\helpers\define.js:122:21)
    at Object.success (C:\Sailsjs\kajhome\node_modules\sails-mysql\helpers\private\connection\spawn-connection.js:37:14)
    at C:\Sailsjs\kajhome\node_modules\machine\lib\private\help-build-machine.js:1354:30
    at _tryToRunCb (C:\Sailsjs\kajhome\node_modules\parley\lib\private\Deferred.js:520:18)
    at C:\Sailsjs\kajhome\node_modules\parley\lib\private\Deferred.js:755:16
    at C:\Sailsjs\kajhome\node_modules\machine\lib\private\help-build-machine.js:966:24
    at Function.handlerCbs.success (C:\Sailsjs\kajhome\node_modules\machine\lib\private\help-build-machine.js:826:26)
    at _gotConnection (C:\Sailsjs\kajhome\node_modules\machinepack-mysql\machines\get-connection.js:88:20)
    at Ping.onOperationComplete [as _callback] (C:\Sailsjs\kajhome\node_modules\mysql\lib\Pool.js:99:5)
    at Ping.Sequence.end (C:\Sailsjs\kajhome\node_modules\mysql\lib\protocol\sequences\Sequence.js:96:24)
    at Ping.Sequence.OkPacket (C:\Sailsjs\kajhome\node_modules\mysql\lib\protocol\sequences\Sequence.js:105:8)
    at Protocol._parsePacket (C:\Sailsjs\kajhome\node_modules\mysql\lib\protocol\Protocol.js:280:23)
    at Parser.write (C:\Sailsjs\kajhome\node_modules\mysql\lib\protocol\Parser.js:73:12)
    at Protocol.write (C:\Sailsjs\kajhome\node_modules\mysql\lib\protocol\Protocol.js:39:16)
    at Socket.<anonymous> (C:\Sailsjs\kajhome\node_modules\mysql\lib\Connection.js:96:28)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11)
    at Socket.Readable.push (_stream_readable.js:208:10)
  code: 'ER_TOO_LONG_KEY',
  errno: 1071,
  sqlState: '42000',
  index: 0 }

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Some development data was recovered (see .tmp/automigration.user.7046.log).  Any existing user records were deleted, but your data from OTHER models (including any relationships tracked in foreign keys and join tables) might still be intact.  If you care about recovering any of that data, be sure to back it up now before you continue.

The best way to proceed from here is to clear out all of your old development data and start fresh; allowing Sails to generate new tables/collections(s) to reflect your app's models.  (In other words, to DELETE ALL EXISTING DATA stored in models.)

To do that, re-lift your app using the drop strategy:

sails lift --drop

After doing that once, you should be able to go about your business as usual.
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

For more about auto-migrations, visit:
https://sailsjs.com/docs/concepts/models-and-orm/model-settings#?migrate

On Tue Apr 24 2018 18:07:26 GMT+0430 (Iran Daylight Time), Sails attempted to auto-migrate
using the alter strategy, but was unable to transform all of your
existing development data automatically.  This temporary file was created
for your convenience, as a way of holding on to the unmigrated records that
were originally stored in the user model.
(Otherwise, this data would have been lost forever.)

================================
Recovered data (user):
================================
[]

--
For help with auto-migrations, visit:
http://sailsjs.com/docs/concepts/models-and-orm/model-settings#?migrate

For questions, additional resources, or to talk to a human, visit:
http://sailsjs.com/support


error: A hook (orm) failed to load!
error:
error: { Error: Automigrations failed.  (See logs above for help, and a summary of what went wrong.)
    at Timeout._onTimeout (C:\Sailsjs\kajhome\node_modules\waterline-utils\lib\auto-migrations\private\run-alter-strategy\private\inform-re-failed-alter-stratagem.js:213:55)
    at ontimeout (timers.js:475:11)
    at tryOnTimeout (timers.js:310:5)
    at Timer.listOnTimeout (timers.js:270:5) code: 'E_FAILED_ALTER_STRATEGEM' }

error: Could not load Sails app.
error:
error: Tips:
error:  • First, take a look at the error message above.
error:  • Make sure you've installed dependencies with npm install.
error:  • Check that this app was built for a compatible version of Sails.
error:  • Have a question or need help?  (http://sailsjs.com/support)

may you help me?

[sailsbot]

Hi @webcoweb! It looks like you may have removed some required elements from the initial comment template, without which I can't verify that this post meets our contribution guidelines. To re-open this issue, please copy the template from here, paste it at the beginning of your initial comment, and follow the instructions in the text. Then post a new comment (e.g. "ok, fixed!") so that I know to go back and check.

Sorry to be a hassle, but following these instructions ensures that we can help you in the best way possible and keep the Sails project running smoothly.

*If you feel this message is in error, or you want to debate the merits of my existence (sniffle), please contact [email protected]

[mikermcneil]

@webcoweb would you mind posting your model definitions?

[devfreddy]

@webcoweb I just ran into a similar issue, the conditions which triggered it

• Using utf8mb4 charset for my database instead of utf8
• Including unique: true, on a model's attribute (in my case, the email address field)

Due to a 'string' column having the 'unique' flag the underpinnings (sequelize?) tries to create a unique index for the field and doesn't have enough space. I fixed it by including the following in the attribute's definition:

columnType: 'varchar(185)',

You may have to look in your model's for similar fields and adjust.

[webcoweb]

Thanks @mdfrederick
I will try it as soon as possible.

[rafaelbnp]

Having the same issue:

Error (E_UNKNOWN) :: Encountered an unexpected error
: ER_TOO_LONG_KEY: Specified key was too long; max key length is 767 bytes

It only happens when I add unique: true to my 'string' model property. Adding columnType: 'varchar(185)' or columnType: 'varchar' didn't fix it. Still searching for a solution...

[devfreddy]

@rafael-paiva Although I have had no issue with what I did above. Sails Documentation (at the bottom) actually has:

When using unique: true on an attribute with the utf8mb4 character set in a MySQL database, you will need to set the column size manually via the columnType property to avoid a possible 'index too long' error. For example: columnType: varchar(100) CHARACTER SET utf8mb4.

Perhaps try an even shorter varchar(100).

[rafaelbnp]

@mdfrederick thanks for replying! I've tried varchar(100) and varchar(100) CHARACTER SET utf8mb4 as the documentation suggests but the issue is still present.

Probably because I'm still on Sails 0.12 and I couldn't find any mention to columnType on its concepts.

[devfreddy]

@rafael-paiva You should have led with the version :)

When using MySQL with the utf8mb4 character set, you will need to add the size constraint to the appropriate column in your table directly via MySQL. Otherwise, since type: 'string' is translated to varchar(255) in the MySQL adapter, the unique: true constraint will cause an 'index too long' error: ER_INDEX_COLUMN_TOO_LONG: Index column size too large. The maximum column size is 767 bytes.

Because you're on 0.12 you need to use the size parameter to drive the number of characters.

Ex:

myStringField: {
    type: 'string',
    size: 100
}

[rafaelbnp]

@mdfrederick thanks very much! You saved my Friday :)
Not sure how I missed that on the documentation though 🤦‍♂️

[kiknaio]

I'm getting quite similar issue with sails-mysql: 1.0.1.

error: Sending 500 ("Server Error") response: 
 AdapterError: Unexpected error from database adapter: ER_DATA_TOO_LONG: Data too long for column 'requirements' at row 1

Here's the model:

requirements: {
      type: 'string',
},

[ChenglongMa]

Refer to this: #1071 - Specified key was too long; max key length is 1000 bytes

Execute the following script on MySQL console will solve this problem:

SET GLOBAL storage_engine='InnoDb';

// For MySQL 5.6 and later:
SET GLOBAL default_storage_engine = 'InnoDB';

Thanks.