[Bug] RomM v3.0.1: "Duplicate column name 'p_name'" on start-up about romm HOT 19 CLOSED

Hey, just an update. I ended up reinstalling Docker from scratch, cleaning out everything. I was able to get it working afterwards with v3.1.0-rc.1. Going to wait for v3.1.0 final, then will test fully. :)

Happy to close this one off though.

from romm.

Yes, I tried to wipe everything between attempts, so I delete both the romm and the romm-db containers and remove the storage mounts.

from romm.

Ok so you have 3 options here:

a) exec sh into the romm container, edit /backend/alembic/versions/1.8_.py and under the upgrade function (the migration), comment out the block with op.batch_alter_table("platforms") as batch_op: and the line batch_op.add_column(sa.Column("p_name", sa.String(length=150), nullable=True)). run migrations, then after the next failure go back in and comment out up to the next line that fails, then keep doing that till the migration completes

b) open the roms table in maraidb, delete the p_name column, then run the migration. when it fails next do the same thing with whichever column is also a duplicate.

c) in maraidb, skip the entire migration by changing the value in alembic_version table to 1.8.1 (though this will break things of the entire migration didnt run the last time)

from romm.

Had the same problem with a clean installation using the latest docker image.
Solved it by commenting out the individual lines in the migration scripts:

• /backend/alembic/versions/1.8_.py
• /backend/alembic/versions/0014_asset_files.py

I attached the modified migration scripts.
migration_scripts.zip

Maybe they help to find the bug in the migration.

from romm.

If you remove everything under ./storage/romm-db and start again, does it still fail?

from romm.

Yes, I still get the same error. Same if I delete ./storage/romm only and restart, or when I delete both.

from romm.

[RomM][alembic.runtime.migration] Running upgrade 1.7.1 -> 1.8, update to 1.8

This tells me it's using a database somewhere which is currently on 1.7.1. Are you deleting the romm-db container between attempts?

from romm.

Would there be some way to override what version of the DB RomM thinks it's using, or even skip the migration altogether?

from romm.

Are you comfortable connecting to your DB with a tool like dbeaver, or the mariadb cli?

from romm.

Yeah, that shouldn't be a problem, any commands you want me to try?

from romm.

No, not having any joy I'm afraid.

1. Each of the upgrade functions was causing problems, so I had to skip them all, but then I just got a Got error 42 "No message of desired type" from storage engine InnoDB error.

2. I attempted this but kept running into more and more issues. While I was able to slowly work through each one, I eventually ran into a Tablespace is missing for a table error.

3. I also got a Tablespace is missing for a table error when setting the version to 1.8.1.

4. I attempted to use the scripts provided by @Silver-Rabbit , but getting the same tablespace error as above.

I'm not sure why a migration is even necessary - is it not possible to use the latest schema if doing a fresh install?

from romm.

@zodac If you wipe your DB, delete any SQLite database you have mounted, and start fresh, it should work. Obviously it means loosing any manual linking of games with IGDB, so it's really a last resort type of thing.

FWIW I've done it 4 times so far on my 2K+ collection of games, due to bad migrations in past beta versions!

from romm.

I was deleting the volumes for both mariadb and romm (all listed in the OP), though I wasn't running any SQL commands to drop tables/delete DBs first. Are there any other things I need to wipe? I thought there was no SQLite for v3?

from romm.

Did you do it in this order: stop mariadb container, rm mariadb, rm the volume, then start mariadb again?

from romm.

Yes, that order, but I do both mariadb and romm.

from romm.

Can you post the romm logs when it starts up again?

from romm.

Sure. I did the following process:

• Stopped both containers
• Removed both containers
• Removed both containers' volumes
• Started the containers again

Attached the log from RomM: romm.log

from romm.

Table 'old_roms' already exists

For some reason you're got virtual tables sticking around during the migration, and I can't figure out why. Can you post your docker-compose.yml for romm and mariadb?

from romm.

I had posted it in the OP. I've also tried with names volumes (a volumes: block in my docker-compose), but that's having the same problem. I also rm'd any extra docker volumes in case one was created by the Dockerfile that I had missed,

from romm.