How to debug "Migration Failed" Error

Questions and Answers Database-SQL
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.