SQLException when calling setReadOnly

[Synesso]

This is an unresolved bug (2014) migrated from bitbucket. I did not find it in github issues and it is still a problem.

---

Since version 3.7.15-M1, we see the following exception:

Exception in thread "main" java.sql.SQLException: Cannot change read-only flag after establishing a connection. Use SQLiteConfig#setReadOnly and QLiteConfig.createConnection().
at org.sqlite.SQLiteConnection.setReadOnly(SQLiteConnection.java:447)
at org.apache.commons.dbcp.DelegatingConnection.setReadOnly(DelegatingConnection.java:377)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setReadOnly(PoolingDataSource.java:338)
at scalikejdbc.DBConnection$class.readOnlySession(DB.scala:138)
at scalikejdbc.DB.readOnlySession(DB.scala:498)
...

Such behaviour differs from other JDBC drivers.

see also: http://stackoverflow.com/questions/23390278/scalikejdbc-sqlite-cannot-change-read-only-flag-after-establishing-a-connecti

[gwenn]

It seems PRAGMA query_only may be used.

[spaudanjo]

Any plans to address this in the near future? :)

[shevek]

We hit this as well with HikariCP. HikariCP is explicitly setting readOnly to a value which it chooses, which may differ from the setting of the underlying DataSource. The solution was to tell HikariCP to leave it alone, in our case, by setting HCP's readOnly flag to the same as that of SQLite.

There is an argument to say that the result of an "advisory" (doc: "hint") call to sqlite should not be fatal. However, the doc on setReadOnly permits exceptions to be thrown under somewhat loosely defined circumstances ("if a database error occurs").

No strong conclusion about validity.

[timmc-bcov]

I ran into this at work, and the query_only pragma didn't work for me, I suppose because it interferes with prepared statements. (I couldn't set parameters since there were two statements in the string.) This workaround seems OK, though:

        PoolProperties props = new PoolProperties();
        props.setDriverClassName("org.sqlite.JDBC");
        props.setUrl("jdbc:sqlite:...");

        Properties extraProps = new Properties();
        extraProps.setProperty("open_mode", SQLiteOpenMode.READONLY.flag + "");
        props.setDbProperties(extraProps);
        // This line can be left in or removed; it no longer causes a problem
        // as long as the open_mode code is present.
        props.setDefaultReadOnly(true);

        return new DataSource(props);

[timmc-bcov]

This also worked (in 3.28.0), and was considerably simpler:

String path = ...
SQLiteConfig config = new SQLiteConfig();
config.setReadOnly(true);
return DriverManager.getConnection("jdbc:sqlite:" + path, config.toProperties());

[gotson]

Would be fixed by #432 i suppose