SQL logic error when schemas out of sync

[psviderski]

trafficstars

When DB schema is changed (e.g. a new column is added) on node A and not on node B, inserts/updates from node A using new schema fail to apply on node B:

2024-09-26T10:59:26.534155Z ERROR corro_agent::agent::handlers: could not process multiple changes: rusqlite: SQL logic error (actor_id: Some(ActorId(f1d3880f-c3b7-4cbb-b0df-6e72a4459517)), version: None)
2024-09-26T10:59:27.033472Z ERROR process_multiple_changes:process_single_version:process_complete_version{actor_id=ActorId(f1d3880f-c3b7-4cbb-b0df-6e72a4459517) versions=Version(4)..=Version(4)}: corro_agent::agent::util: error=SQL logic error
2024-09-26T10:59:27.033544Z ERROR process_multiple_changes:process_single_version: corro_agent::agent::util: error=SQL logic error
2024-09-26T10:59:27.033636Z ERROR process_multiple_changes: corro_agent::agent::util: error=rusqlite: SQL logic error (actor_id: Some(ActorId(f1d3880f-c3b7-4cbb-b0df-6e72a4459517)), version: None)

A similar error occurs when trying to process a change for a new table that doesn't exist on node B:

2024-09-26T11:07:32.499239Z ERROR corro_agent::agent::handlers: could not process multiple changes: rusqlite: crsql - could not find the schema information for table todos (actor_id: Some(ActorId(a424b2f7-a75c-49dd-9f8e-cf1d5c4f0fc5)), version: None)
2024-09-26T11:07:47.472566Z  INFO corro_agent::agent::handlers: synced 26 changes w/ f1d3880fc3b74cbbb0df6e72a4459517 in 0.007235708s @ 3593.290387063712 changes/s
2024-09-26T11:07:47.499287Z ERROR process_multiple_changes:process_single_version:process_complete_version{actor_id=ActorId(a424b2f7-a75c-49dd-9f8e-cf1d5c4f0fc5) versions=Version(1)..=Version(1)}: corro_agent::agent::util: error=crsql - could not find the schema information for table todos
2024-09-26T11:07:47.499360Z ERROR process_multiple_changes:process_single_version: corro_agent::agent::util: error=crsql - could not find the schema information for table todos
2024-09-26T11:07:47.499462Z ERROR process_multiple_changes: corro_agent::agent::util: error=rusqlite: crsql - could not find the schema information for table todos (actor_id: Some(ActorId(a424b2f7-a75c-49dd-9f8e-cf1d5c4f0fc5)), version: None)

This behavior seems reasonable to prevent data loss. However, it requires every node in the cluster to run the same schema version. To comply with that requirement, what should be the process for deploying schema changes to the cluster? One approach could be having another distributed table that would store the latest schema along with a separate process that would watch the latest schema and synchronize it to db.schema_paths followed by executing corrosion reload. Is there a better way to handle this? How do you manage schema changes at Fly?

[jeromegn]

That's a great question. Schema synchronization has been largely left to users.

At Fly: We have a repository containing Corrosion-related configuration that we deploy independently.

Usually this is a multi-step process, similar to a migration for a normal database:

• Make changes to the schema
• Deploy to 1 node and make sure it applies properly
• Since schema changes should not be destructive w/ Corrosion, it shouldn't cause any issues as long as nothing writes with this new schema definition
• Deploy to all other nodes
• Deploy the change that uses the new schema (whatever writes to it)

If we do this properly, every node has the same schema and there are no errors. If there are no errors, that's ok because nodes will keep trying to synchronize versions they haven't been able to apply.

It's not perfect and a bit tedious... if you want to distribute the schema automatically via Corrosion, that sounds doable in the way you've described it, but a little scary sometimes. Automatic schema propagation was something we decided not to do to keep more explicit control of what happens to the schema.

[psviderski]

@gedw99 Can you please elaborate or provide some examples on how you use Benthos for managing transformations?

[psviderski]

Thanks for the advice! No, I haven’t come up with a strategy yet. I will eventually need a more or less automated and controlled way to deploy schema changes to a cluster of machines in uncloud project that uses corrosion as a decentralised cluster state. But at this stage I make sure the latest schema is embedded into the daemon binary. When the daemon starts, it updates the schema file for corrosion. With this in place, I just need to update all the nodes to the latest daemon version in a relatively short period of time. I’m fine with this for now.