Presto JDBC driver SQLException

[janua]

I receive the following exception when trying to use doobie with the Presto JDBC driver:

Caused by: java.sql.SQLException: Connection is in auto-commit mode
    at com.facebook.presto.jdbc.PrestoConnection.rollback(PrestoConnection.java:160)
    at doobie.free.connection$ConnectionOp$Rollback$$anonfun$defaultTransK$42.apply(connection.scala:244)
    at doobie.free.connection$ConnectionOp$Rollback$$anonfun$defaultTransK$42.apply(connection.scala:244)
    at doobie.free.connection$ConnectionOp$$anonfun$primitive$1$$anonfun$apply$1.apply(connection.scala:85)
    at scalaz.effect.IO$$anonfun$apply$19$$anonfun$apply$20.apply(IO.scala:178)
    at scalaz.effect.IO$$anonfun$apply$19$$anonfun$apply$20.apply(IO.scala:178)
    at scalaz.std.FunctionInstances$$anon$1$$anonfun$map$1.apply(Function.scala:77)
    at scalaz.Free$$anonfun$run$1.apply(Free.scala:228)
    at scalaz.Free$$anonfun$run$1.apply(Free.scala:228)
    at scalaz.Free.go2$1(Free.scala:150)

In gitter, there has been conversation about it here and here, suggesting there would be a typeclass by 0.3, allowing me to switch off auto-commit. I can see the abstract Transactor class, but can't see how I could use this as a typeclass other than overriding it. Maybe there is another typeclass I am missing?

[tpolecat]

Actually I don't know what's going on because all provided transactors turn auto-commit off. Does Presto not support transactions?

[tpolecat]

Hey @adelbertc can you paste your hive transactor here? I think it's probably a similar situation since Presto looks like another janky hadoop thing.

[adelbertc]

Here ya go:

import doobie.imports._
import doobie.free.drivermanager.{delay, getConnection}

import java.sql.Connection

import scalaz.{Catchable, Monad}
import scalaz.concurrent.Task
import scalaz.syntax.apply._

/** Transactor to talk to Hive. Due to the Hive JDBC connector not supporting transactions, rolling back is a no-op. */
final class HiveTransactor[M[_] : Capture : Catchable : Monad] private(driver: String, url: String, user: String, pass: String)
    extends Transactor[M] {
  // No-op
  private val unit = Monad[ConnectionIO].point(())

  val connect: M[Connection] = (delay(Class.forName(driver)) *> getConnection(url, user, pass)).trans[M]

  /** Do nothing instead of default behavior, Hive JDBC is.. unreliable. */
  override def before = unit
  override def oops = unit
  override def after = unit
}

object HiveTransactor {
  def apply[M[_] : Capture : Catchable : Monad](driver: String, url: String, user: String, pass: String): Transactor[M] =
    new HiveTransactor(driver, url, user, pass)
}

This was on doobie 0.2.2.

[tpolecat]

Thanks @adelbertc … @janua can you give that a try? It's a transactor for databases that don't support transactions. Savor the irony.

[janua]

@tpolecat Yes I thought it was a bit strange considering the provided transactor turns auto-commit off by default.

I ended up with something similar to the HiveTransactor above, but now have a different issue that I don't think is related to doobie;

Caused by: com.facebook.presto.jdbc.NotImplementedException: Method Connection.prepareStatement is not yet implemented
    at com.facebook.presto.jdbc.PrestoConnection.prepareStatement(PrestoConnection.java:107)
    at doobie.free.connection$ConnectionOp$PrepareStatement4$$anonfun$defaultTransK$39.apply(connection.scala:235)
    at doobie.free.connection$ConnectionOp$PrepareStatement4$$anonfun$defaultTransK$39.apply(connection.scala:235)
    at doobie.free.connection$ConnectionOp$$anonfun$primitive$1$$anonfun$apply$1.apply(connection.scala:85)
    at scalaz.effect.IO$$anonfun$apply$19$$anonfun$apply$20.apply(IO.scala:178)
    at scalaz.effect.IO$$anonfun$apply$19$$anonfun$apply$20.apply(IO.scala:178)
    at scalaz.std.FunctionInstances$$anon$1$$anonfun$map$1.apply(Function.scala:77)
    at scalaz.Free$$anonfun$run$1.apply(Free.scala:228)
    at scalaz.Free$$anonfun$run$1.apply(Free.scala:228)
    at scalaz.Free.go2$1(Free.scala:150)

This is a problem with the Presto JDBC driver not supporting prepared statements, and is meant to be fixed in 0.153 which will imminently get released. Currently 0.152 is the official release.

I have used a local 0.153-SNAPSHOT and am still getting the same problem. Is it possible to avoid using prepared statements?

The presto issues are at prestodb/presto#1195 and prestodb/presto#5414

[tpolecat]

Ok, it sounds like Presto has a really really minimal driver. So you may need to use a lower-level API. If you can provide an example that works with raw JDBC I can show you how to do it with doobie.

[janua]

I'll wait for the official release of 0.153 to be sure I haven't missed something when I built it locally with maven, then I'll provide a JDBC example. Thanks!

[tpolecat]

👍 I'm going to close this for now but you can re-open when you're ready to continue.

[kylinsoong]

presto-jdbc-0.162 also not support transaction, If looks from the PrestoConnection

@Override
    public void setAutoCommit(boolean autoCommit)
            throws SQLException
    {
        checkOpen();
        if (!autoCommit) {
            throw new SQLFeatureNotSupportedException("Disabling auto-commit mode not supported");
        }
    }

The above codes hints don't allow to set autoCommit to false, else SQLFeatureNotSupportedException throw.

@Override
    public void commit()
            throws SQLException
    {
        checkOpen();
        if (getAutoCommit()) {
            throw new SQLException("Connection is in auto-commit mode");
        }
        throw new NotImplementedException("Connection", "commit");
    }

@Override
    public boolean getAutoCommit()
            throws SQLException
    {
        checkOpen();
        return true;
    }

getAutoCommit() always return true, that means SQLException("Connection is in auto-commit mode") will always throw if you invoke a commit().