ScalikeJDBC + SQlite: Cannot change read-only flag after establishing a connection

Question

Trying to get working ScalikeJDBC and SQLite. Have a simple code based on provided examples:

import scalikejdbc._, SQLInterpolation._

object Test extends App {
  Class.forName("org.sqlite.JDBC")
  ConnectionPool.singleton("jdbc:sqlite:test.db", null, null)

  implicit val session = AutoSession

  println(sql"""SELECT * FROM kv WHERE key == 'seq' LIMIT 1""".map(identity).single().apply()))
}

It fails with 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)
...

I've tried both scalikejdbc 1.7 and 2.0, error remains. As sqlite driver I use "org.xerial" % "sqlite-jdbc" % "3.7.+".

What can I do to fix the error?

I've tried with your code, and it works fine. https://github.com/tkawachi/sqlite-scalikejdbc-test Please elaborate more detail. — kawty, Apr 30 '14 at 15:39
yeah, it works, seems it has something to do with access rights rather than driver or libraries. I had somewhat non trivial environment - was running code from Idea writing to network samba shared virtual drive. Thanks, deleting the question? Or it worth to stay? — dmitry, Apr 30 '14 at 15:56
I guess you're hitting some kind of a bug on the library. Please raise an issue at https://github.com/scalikejdbc/scalikejdbc/issues when you can create reproducible code. — kawty, Apr 30 '14 at 16:02

score 3 · Answer 1 · answered Aug 07 '17 at 07:13

The following will create two separate connections, one for read-only operations and the other for writes.

ConnectionPool.add("mydb", s"jdbc:sqlite:${db.getAbsolutePath}", "", "")
ConnectionPool.add(
  "mydb_ro", {
    val conf = new SQLiteConfig()
    conf.setReadOnly(true)
    val source = new SQLiteDataSource(conf)
    source.setUrl(s"jdbc:sqlite:${db.getAbsolutePath}")
    new DataSourceConnectionPool(source)
  }
)

Kazuhiro Sera · Accepted Answer · 2014-04-30T23:16:18.657

1

I found that the reason is that you're using "org.xerial" % "sqlite-jdbc" % "3.7.15-M1". This version looks still unstable.

Use "3.7.2" as same as @kawty.

edited Apr 30 '14 at 23:16

answered Apr 30 '14 at 16:17

Kazuhiro Sera

1,822
12
15

1

Just created an issue: https://bitbucket.org/xerial/sqlite-jdbc/issue/136/sqlexception-when-calling-setreadonly-in – Kazuhiro Sera May 01 '14 at 00:58

score 0 · Answer 3 · answered Apr 06 '19 at 18:14

Building on @Synesso's answer, I expanded slightly to be able to get config value from config files and to set connection settings:

import scalikejdbc._
import scalikejdbc.config.TypesafeConfigReader

case class SqlLiteDataSourceConnectionPool(source: DataSource, 
                                           override val settings: ConnectionPoolSettings)
  extends DataSourceConnectionPool(source)


// read settings for 'default' database
val cpSettings = TypesafeConfigReader.readConnectionPoolSettings()
val JDBCSettings(url, user, password, driver) = TypesafeConfigReader.readJDBCSettings()

// use those to create two connection pools
ConnectionPool.add("db", url, user, password, cpSettings)
ConnectionPool.add(
      "db_ro", {
        val conf = new SQLiteConfig()
        conf.setReadOnly(true)
        val source = new SQLiteDataSource(conf)
        source.setUrl(url)
        SqlLiteDataSourceConnectionPool(source, cpSettings)
      }
    )

// example using 'NamedDB'
val name: Option[String] = NamedDB("db_ro") readOnly { implicit session =>
  sql"select name from users where id = $id".map(rs => rs.string("name")).single.apply()
}

score 0 · Answer 4 · answered Sep 14 '19 at 22:51

This worked for me with org.xerial/sqlite-jdbc 3.28.0:

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

Interestingly, I wrote a different solution on the issue on the xerial repo:

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);

I don't recall why I needed the second, and was then able to simplify it back to the first one. But if the first doesn't work, you might try the second. It uses a SQLite-specific open_mode flag that then makes it safe (but unnecessary) to use the setDefaultReadOnly call.

ScalikeJDBC + SQlite: Cannot change read-only flag after establishing a connection

4 Answers4