Upgrade instructions unclear / Single database discouraged?

[2called-chaos]

I tried to upgrade from 0.6.0 to 0.9.0 but there's steps or info missing. What I'm supposed to do with the migration/schema in a single database scenario? Readme says to copy queue_schema.rb to a migration and delete the original file but that obviously fails because the tables already exists (see below for the error) but it's unclear whether anything changed. Since readme instructs to delete queue_schema in that case, should I do too when updating and just ignore that it existed?

And in the case of multi database:

This version collapsed all migrations into a single db/queue_schema.rb, that will use a separate queue database. If you're upgrading from a version < 0.6.0, you need to upgrade to 0.6.0 first, ensure all migrations are up-to-date, and then upgrade further.

So you are supposed to migrate a database that doesn't matter after the fact? The solid_queue tables in the single database setup are just garbage then, no? Is single database discouraged (readme doesn't sound like it but the processes do whereas before it was the opposite)? If so then how am I supposed to migrate?

Note: future changes to the schema will come in the form of regular migrations.

Is this still true? Is it gonna create migrations in db/queue_migrate which requires manual moving in the case of single database?

Frankly the easier update route seems to be to just start from scratch at the moment

EDIT: Actually doesn't fail because the tables already exists, I get the following error

-- create_table("solid_queue_blocked_executions", {:force=>:cascade})
   -> 0.0535s
-- create_table("solid_queue_claimed_executions", {:force=>:cascade})
   -> 0.0498s
-- create_table("solid_queue_failed_executions", {:force=>:cascade})
   -> 0.0479s
-- create_table("solid_queue_jobs", {:force=>:cascade})
-- create_table("solid_queue_blocked_executions", {:force=>:cascade})
   -> 0.0566s
-- create_table("solid_queue_claimed_executions", {:force=>:cascade})
   -> 0.0625s
-- create_table("solid_queue_failed_executions", {:force=>:cascade})
   -> 0.0594s
-- create_table("solid_queue_jobs", {:force=>:cascade})
rake aborted!
ActiveRecord::InvalidForeignKey: Mysql2::Error: Cannot delete or update a parent row: a foreign key constraint fails

[rosa]

Hey @2called-chaos!

I tried to upgrade from 0.6.0 to 0.9.0 but there's steps or info missing. What I'm supposed to do with the migration/schema in a single database scenario?

If you’re upgrading, you don’t need to do anything with the new schema file. These changes involve installation, they don’t affect existing apps that use Solid Queue from before. If you were using Solid Queue before, you can run the migrations up to version 0.8.0 without doing anything.

Since readme instructs to delete queue_schema in that case, should I do too when updating and just ignore that it existed?

The README’s instructions are also under installation, if you’re installing Solid Queue for the first time and want to use a single DB.

So you are supposed to migrate a database that doesn't matter after the fact? The solid_queue tables in the single database setup are just garbage then, no?

No, of course not. Nowhere it says that you have to delete your database. These instructions are for people using Solid Queue before version 0.6.0 and wanting to upgrade. If you upgraded directly from 0.4.0 to 0.8.0, you’d miss the migrations added in version 0.6.0 for example, because they have been deleted and replaced by a schema file. People starting with Solid Queue for the first time in version >= 0.8.0 can just use the new schema file. People using it from before can’t do that because they have existing DBs that they need to migrate, thus the need to go first to version 0.6.0 and then up. Once you have your DB, the fact that the migrations are deleted from the gem or that a new schema file is added doesn’t affect in anything. These are used for installation, when you start using it.

If so then how am I supposed to migrate?

Again, if you’re in version > 0.6.0, then you have exactly the same DB as in the schema. You don’t have to migrate anything.

Note: future changes to the schema will come in the form of regular migrations. Is this still true? Is it gonna create migrations in db/queue_migrate which requires manual moving in the case of single database?

I added that less than 24h ago, so yes, it’s definitely true. This is for everyone wondering how they’ll be able to upgrade in the future if we make changes to the schema. In the case of a single DB, you’ll just ned to install the migration and possibly move the file under your generic db/migrate folder so it’s applied to your main DB.

Frankly the easier update route seems to be to just start from scratch at the moment

If you want to do that, feel free, but definitely not the easier route, especially if you were already in a version with all the migrations and are using SQ in production, because in that case you don’t have to do anything whereas starting from scratch might be more work to ensure no interruption in job processing.

Is single database discouraged

In general, yes, the default/recommended way is to use separate DBs, but there’s no problem with using a single one. If you’re using SQLite, I strongly recommend using a separate one, in any case, which was the case since the beginning. Rails will configure a separate DB by default as well, but as I said, no issues with running a single DB.

Is this clearer? I’m not sure which steps on UPGRADING are missing, because all the steps are there. There’s no step to do anything with the schema because you don’t have to do anything with it.

[2called-chaos]

Thanks for the reply @rosa! I guess part of the reason I got confused is the "and then upgrade further." part. So I did the intermediate steps such as running bin/rails generate solid_queue:install again to generate the binstub but that also modifies production.rb to use the separate DB and creates the schema file which I didn't quite know the purpose of and what to do with it which is why I referred to the readme. In hindsight I maybe should have done that on 0.7

Before that change it appeared to be the default to not use a separate database (with it requiring explicit parameter) so the change here got me I guess.

You kinda need to check the readme again if you are not familiar yet with the multiple database support as you need to change database.yml which is only implied in the upgrade guide, if you were to accept all the changes the generator does. Or in other words if you had a single DB setup you are just left wondering what you are supposed to do now with these changes so you look at readme which says to move it to migrations and revert the change to production environment.

This version [..] will use a separate queue database.

At first I thought it was mandatory to be honest, only the readme clarifies that single DB is still a thing

[rosa]

Got it. I'll try to clarify all those points in UPGRADING and/or the README.

[rosa]

Ok, I pushed some changes in https://github.com/rails/solid_queue/commit/ad44cc813890ba12703cafe02768e7e5564c23ca, and I also realised there were some migration instructions in version 0.7.0 that weren't necessary (no migrations in that version) so I deleted that. Do you think it's clearer now?

[motdotla]

hi @rosa. thanks for all the work on solid queue. concerning separate vs single:

In general, yes, the default/recommended way is to use separate DBs, but there’s no problem with using a single one.

why? can you elaborate? the single database was appealing to me as it felt simpler cognitively than maintaining a redis instance with sidekiq. now i sort of feel like i'm back at the same level of complexity. did you run into performance issues with a single database using solid queue in production - is that why the recommendation?

[motdotla]

i will grant that if i was instead using sqlite (i'm using postgres) then separate dbs 'feels' better.

but from the perspective of a postgres user:

what i like about single database

• less cognitive load
• less setup
• simple setup for the bi tool i prefer - https://github.com/ankane/blazer

what i like about multi-database

• less risk - i can blow away my queue/cache databases and application data is untouched
• separate database backup strategies

i guess, i'm trying to determine if less risk and the backup strategies outweighs what i perceive as some added complexity. are there also some performance benefits you saw for this approach with postgres/mysql?

maybe @dhh can chime in here if he has time - as maybe the core of this change is around prioritizing sqlite more in the future. that tracks well in my mind if so.

[dhh]

It's not just about sqlite. I would have it split for any DB engine. Keeping transient data like queues, caches, and cable transmissions together with system of record data is a mix of concerns. It also has the potential to lead to bad patterns around depending on shared transactions that'll then break when you split it out. I don't see the cost of having the separate databases as a material overhead. It's still the same engine. It's like a namespace where we separate concerns.

But ultimately you can do whatever you want. Solid just suggests a good default. If you want to override that and run all in the same DB, have at it. Copy and paste the schemas into a migration and update the DB config and you're set.

[motdotla]

thanks. strong point on the shared transactions. i'll move to separate dbs.