I've been getting internal MotherDuck errors, that are repeatable. I guess I'm using connections wrongly, but even if I am, it should stop me before throwing an internal error. Here are a bunch of error IDs (all will have the same cause): 3ad2fae7 27f86313 c910b334
My guess is the errors stem from making 2 connections to the same MotherDuck service account at the same time. One connection uses a Read Scaling token, the other uses a Read/Write token. The error comes when I try to execute any SQL against Read/Write connection that needs to actually change data. I can make the connection without error, and I can even execute a DELETE statement that doesn't delete any rows without it raising an error. But I get an internal error if the DELETE statement does need to delete rows, or I try to execute an INSERT, or a CREATE.
Why I am trying to do this?
My application (python) now connects to MothererDuck via a service account.
98% of the time, my application is reading data only. So I have a Read Scaling token and a connection my application keeps open. (Separate point, now I've read about connection instance caching I plan to investigate if it is better to only open the connection when each query needs to run). This all works nicely.
2% of the time, my application needs to write data. I've created a Read/Write token so my application can do this. But get the internal error whenever I try to write any data via this connection.
I have a separate databases to hold the "98%-of-time-read-data" and the "2%-of-time-read-write-data". So my Read Scaling token connection connects to a different database than my Read/Write token connection. I was expecting this pattern would work as separate database should avoid any database level locking issues.
I'm aware of the Data Freshness issue if I need to query recently written data via the Read Scaling connection - but I need to actually write data before I can tackle that!
.png){kind=logo}
