Redwood: "yarn rw db up" Error querying the database: No such table: _migration

[andrew-hwahin]

When I run "yarn rw db up", I received the error of Error querying the database: No such table: _migration.

MySql Action Output:
Apply changes to _migration Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2 SQL Statement: CREATE TABLEworker_transfer._migration( )

redwoodjs: 0.19.2
MySQL: 8.0.21
OS: Windows 10 x64

[thedavidprice]

Hi @andrew-hwahin Thanks for checking in about this and sorry for your trouble here. Getting the DB working if often harder than we wish it was.

I've never tried Prisma with MySQL. But it is supported and should work. I did a quick search for Prisma Issues here and didn't see anything related. So this makes me think it's something with your api/prisma/migrations being out of sync with you DB. (Is it possible that you saved some migrations for SQLite and _then_ switched over to MySQL?)

So one thing to try is just starting over.

1. Start with a fresh DB (Drop your DB tables)
2. Delete the existing migrations directory api/prisma/migrations
3. Then save a new migration yarn rw db save
4. And then apply the migration yarn rw db up

Did that work?

[andrew-hwahin]

Hi @thedavidprice thanks for the response, yes you are right, I switched from PostgreSQL to MySQL but at the same time I also tried the steps that you listed. I also did try to search from Prisma issue but couldn't find any related error too. 😢🤔

[thedavidprice]

Ah, bummer. I was hoping that would do the trick. 🙁

Ok, before we assume something is broken with Prisma and MySQL, here's a few more things to try.

• are there any permissions issues with your MySQL DB? Can you connect from the command line and run a query directly... or from something like MySQL Workbench?
• You can try to run the Prisma commands directly without the Redwood CLI wrapper
  
  
  
  • E.g. yarn prisma migrate save --experimental and yarn prisma migrate up --experimental
  
  
  • note: you'll likely need to add the Datasource URL in schema.prisma directly for testing (you'll know to do this if you see the error)
  
  
• Lastly, maybe create the table _migration manually and then see if you can run the Prisma command

If none of that works, I'm out of ideas. Probably best to create an issue on Prisma GitHub.

[andrew-hwahin]

@thedavidprice thank you for your help 🙇‍♂️🙏 The issue is gone when I connect to MySQL hosted on DigitalOcean instead of my local machine.

[thedavidprice]

Great to hear you got it working. And thanks for the update!

[andrew-hwahin]

According to this issue in Prisma repo, this issue is caused by

MySQL is case-insensitive on Windows. All identifiers are internally lower-cased. The migration engine diffing logic cannot handle this at the moment.

[Tobbe]

@andrew-hwahin Thanks for getting back to us about that. I'm sure more Windows users will eventually run in to this limitation.