ERROR: cannot execute CREATE TABLE in a read-only transaction Where: SQL statement

Get Help
1

Getting ERROR: cannot execute CREATE TABLE in a read-only transaction Where: SQL statement error after updating to Metabase 0.35.4. We have many reports which create some temp tables. I checked some discussion and found that there were some changes related to connections done in this version and readOnly was enforced. Could you please help me out to fix this issue, because right now we are not able to use any reports.

2

Hi @vindev
Referencing is everything. ERROR: cannot execute CREATE TABLE in a read-only transaction
You would have to rewrite your queries, so they don’t need write permissions.

3

@flamber Thanks,
But all those reports used to work till 0.34.x. Is there a way I can change any Metabase configuration to make it work. Because there are lot of such reports and it’ll be huge effort rewriting those.

4

@vindev You have three options; rewrite the queries, create your own build without readOnly, or downgrade to 0.34.3
You are welcome to open an issue about this, but I doubt that the read-only functionality will be changed, since it’s a security layer to ensure that Metabase is not able to write to parts of your data source.

5

@flamber, Thanks for your inputs.
Will downgrading Metabase to 0.34.3 have any issues/problems that I should know before proceeding?

6

@vindev Metabase doesn’t officially support downgrading, so you should always revert to backups. But I think you might be able to downgrade without any problems. That is not possible with 0.36.0, which changes the schema completely, so downgrading will break some functionality.

7

@flamber Can you please point out the commit which introduced readOnly. I tried to find it out from the commit messages but it was not clear and was not able to find it.

8

@vindev Pretty sure it was part of https://github.com/metabase/metabase/pull/11832

9

@flamber Thanks,
I changed the readOnly to false(in the file src/metabase/driver/sql_jdbc/execute.clj) and now it is able to create those temp tables. But I’m also able to run update/insert/delete queries from metabase. Earlier this was not allowed. How do I fix this.

10

@vindev Create a database user that doesn’t have those privileges. You are trying to change some of the core functionality, so that’s not simple and can cause other side effects.
I would recommend that you downgrade, change all the queries and then upgrade to a release.