Error: SQLITE_ERROR: duplicate column name: event

[Gribbs]

Describe the bug
I've been building out my content-types in the admin ui and somehow I've created a duplicate column and now strapi develop command is not working. I get the Error:

strapi develop
{ Error: SQLITE_ERROR: duplicate column name: event errno: 1, code: 'SQLITE_ERROR' }
[2019-06-19T10:51:38.357Z] debug ⛔️ Server wasn't able to start properly.
[2019-06-19T10:51:38.357Z] error Error: SQLITE_ERROR: duplicate column name: event

I've seen in other issues it seems it can be fixed in the .tmp/data.db file but I'm not sure where and afraid to edit it in case I destroy hours of work. Is there an easier way to recover from this?

Any help will be appreciated.

Expected behavior
The server should start and let me correct any error.

Screenshots
If applicable, add screenshots to help explain your problem.

Code snippets

Snip Removing DB file as it contains user private data -DMehaffy

System

• Node.js version: v10.0.0
• NPM version: 5.6.0
• Strapi version: 3.0.0-beta.6
• Database: default SQLite
• Operating system: 10.14.5 (18F132)

[derrickmehaffy]

@Gribbs always make a backup, and short answer is no, you will need to go clean up the DB. I recommend getting a GUI client depending on your OS:

Linux: DBeaver
Windows: HeidiSQL
Mac: No idea

[derrickmehaffy]

Also @Gribbs I removed a link to your DB file as it contains your email and an encrypted form of your password. Never a good idea to share that stuff on the internet as it allows for someone to offline try and crack the password.

[Gribbs]

thanks @derrickmehaffy
It worries me that a small mistake like changing the capitalization of a word in the admin ui completely destroys the application upon save and you have to go hunting in a wall of SQL to find the cause of the issue. I think a history should be built into the platform so you can revert to the last save or something?

[derrickmehaffy]

It's a well known issue that only happens on SQLite which shouldn't even be used in production so it wasn't deemed as a priority fix. It stems from the fact that SQLite doesn't have a proper ALTER TABLE command which can cause conflicts on renaming columns (aka alterting them) when that table has data in it.

Basically what has to be done is it makes a copy of the model, copies the data with the rename change, deletes the old one and renames the table.

With data in the mix, it's possible to have a duplicate if the old table wasn't deleted first.

[derrickmehaffy]

Your best bet is to preform the column rename yourself manually, or delete the table and let Strapi recreate it.

Either way marking this as closed for now, as the issue is well documented.

[Gribbs]

thanks for the advice @derrickmehaffy
Cleaned it up successfully using DB Browser for SQLite

[scottnorland]

This can be solved locally by modifying the database settings file. Ex: api/product/models/Product.settings.json

[jassehomar]

Thanks 👍 @scottnorland this works, but it just takes you back to the original column name.

[christopher-francisco]

Where is the SQLite file located?

[lauriejim]

Hello @chris-fran , if you check your config/environment/database.json file you will find the location of the database.
in .tmp/data.db

[christopher4lis]

Although this issue is well documented, I think it's good to re-open it. This is only providing a negative experience for those who just want to change the label of their field—a simple change like that shouldn't require the user to look up documentation and figure out what went wrong, it should just work.

[derrickmehaffy]

@christopher4lis it all comes down to #1114 locking this as it's a duplicate