Bedrock doesn't allow concurrent schema changes about bedrock HOT 10 OPEN

from bedrock.

Thanks for your report! We're undergoing a massive change that's unintentionally preventing that from working, but do think we'd like to support this functionality.

In the meantime, if you're looking for a non-code based work around, for our existing Bedrock deployments we just take down one node at a time (in a 4+ node cluster you can do this without incurring production downtime), use sqlite3 command line tools to go into interactive mode on the db and apply the schema change, then bring the node back up to replicate before taking down the next node. In a non-production environment or one with 3 nodes, you would just take down all the nodes, apply the change to each bedrock.db file, and then bring them back up.

from bedrock.

@tylerkaraszewski is this still a limitation of CONCURRENT transactions?

from bedrock.

Unless sqlite changed this without notifying us, then yes, it's still a limitation. I assume that's the case as I don't see them making this change without being asked specifically for it.

from bedrock.

from bedrock.

What do we need this for?

from bedrock.

from bedrock.

The idea behind concurrent commits is that they're automatically retryable.

If these two queries run simultaneously:

UPDATE table SET column = 1 WHERE key = 'A';

and

UPDATE table SET column = 2 WHERE key = 'A';

If they happen at the same time they conflict, and the second one is automatically retried and can succeed. At the end of the whole process, the value for column is either 1 or 2 for the row with the key A, but both queries succeed.

If you allow schema changes and run these two queries:

UPDATE table SET column = 1 WHERE key = 'A';

and

ALTER TABLE table RENAME to newTableName;

What happens when the second query finishes first? The first one conflicts, is automatically retried, and fails because the table it wants to write to doesn't exist.

Sure, we could detect queries that are going to change the DB schema and run them non-concurrently, but then that just exposes a host of other problems (like above) that we need to come up with solutions for.

from bedrock.

It's been a couple of years, has there been any updates on this issue?

We're using Prisma and the idea is to track changes to your database schema in code and be able to apply those changes in a known, repeatable manner both in development at deploy time. This same process is also part of Ruby on Rails. At deploy time any outstanding database migrations are run and new code referencing those changes is made live.

Changing the format of existing tables that are in use by the application in the middle of a deploy (someone executing a query against the "old" schema after the migration has run but before the code supporting that change is live) and the query will fail causing errors is a known issue, but is a generally accepted risk. This can be mitigated against, for example, by putting the site into maintenance mode.

If it's a node synchronization issue we can be sure that the migrations are only every applied to a single node, although it sounds like from @mcnamamj's comment that you actually need to apply DDL statements to each node individually?

from bedrock.

(Prisma here, which @cannikin mentioned) Saw the new release that probably includes this, but we are unfortunately blocked by another error right now so we can not really test if this now works: #818

from bedrock.