How to debug "Migration Failed" Error

Questions and Answers Database-SQL
1

I upgraded Strapi, and most everything seems to work, except on run develop I get the following error:

[2020-10-09T02:54:51.890Z] error Migration failed
[2020-10-09T02:54:51.892Z] error Error: ER_INVALID_USE_OF_NULL: Invalid use of NULL value

at Query.Sequence._packetToError (/path/strapi/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/path/strapi/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/path/strapi/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/path/strapi/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/path/strapi/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/path/strapi/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/path/strapi/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/path/strapi/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:209:13)
    at addChunk (_stream_readable.js:305:12)
    at readableAddChunk (_stream_readable.js:286:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:182:23)
    at Protocol._enqueue (/path/strapi/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/path/strapi/node_modules/mysql/lib/Connection.js:198:25)
    at /path/strapi/node_modules/knex/lib/dialects/mysql/index.js:130:18
From previous event:
    at Client_MySQL._query (/path/strapi/node_modules/knex/lib/dialects/mysql/index.js:124:12)
    at Client_MySQL.query (/path/strapi/node_modules/knex/lib/client.js:158:17)
    at /path/strapi/node_modules/knex/lib/transaction.js:331:24
From previous event:
    at Client_MySQL.trxClient.query (/path/strapi/node_modules/knex/lib/transaction.js:326:12)
    at Runner.query (/path/strapi/node_modules/knex/lib/runner.js:135:36)
    at Runner.<anonymous> (/path/strapi/node_modules/knex/lib/runner.js:228:25)
    at processImmediate (internal/timers.js:439:21)
From previous event:
    at Runner.queryArray (/path/strapi/node_modules/knex/lib/runner.js:227:12)
    at /path/strapi/node_modules/knex/lib/runner.js:37:25
From previous event:
    at Runner.run (/path/strapi/node_modules/knex/lib/runner.js:25:16)
    at SchemaBuilder.Target.then (/path/strapi/node_modules/knex/lib/interface.js:14:43)
    at processTicksAndRejections (internal/process/task_queues.js:93:5)

Any idea how I go about debugging this?

Again, everything seems to work (Strapi loads up, and I can add/edit entries per usual) but the errors make me think I should be doing something.

2

Can you provide a link to your project or a project that can duplicate the issue?

3

I can’t at this time… just hoping for some general leads / ideas of where to begin poking around.

4

What database are you using?

5

Using MySQL

6

Did you try changing a column to required by chance? I believe a default of NULL is applied to the column in the database and Strapi doesn’t set these defaults (or remove them if applied manually)

7

I was able to reproduce and resolve this issue with the helpful advice from @DMehaffy (thanks!)

To begin, I found that to reproduce the migration error I just needed to:

  • stop the server
  • TRUNCATE TABLE core_store in mysql
  • start the server

Then I was able to see the Error: ER_INVALID_USE_OF_NULL migration error each time.

Then I tracked the error to the migration of the users-permissions_user model.

Finally, I compared the sql table schema with the User.settings.json model definition. Here’s what I saw:

  • in the sql table, email was varchar(255) NULL
  • in the model definition, email was required: true

Sure enough, I had some old test data in the table which had a NULL value for email. So when the migration saw that the model had a required field, and tried to update the schema so that NULL is no longer allowed, sql complained about the inconsistency with the existing data.

After deleting the old rows so that none of them had a NULL email, I was able to re-run the migration from scratch with no errors.

8

Ultimately I did a full reinstall of Ubuntu to 20.04, started over with Node, and and reinstalled all modules, etc. That worked :slight_smile: