Saya mencoba membaca dan menulis dari database MySQL jarak jauh dari pengontrol Play for Scala menggunakan jdbc, dan SBT menampilkan kesalahan saat runtime:

"Tabel '{dbname}.token' tidak ada"

Panggilan sql (memilih) selesai seperti yang diharapkan meskipun ada kesalahan ini.

Pertanyaan saya adalah:

  1. Apa tabel 'token' ini dan mengapa saya menginginkannya?

  2. Apakah ada cara untuk mematikan persyaratan untuk tabel 'token'

Saya telah mencari di Google ini secara agresif dan tidak menemukan jawaban, atau memang apa pun tentang tabel token ini.

detailnya adalah sebagai berikut:

versi:

  • skala 2.11.6
  • mainkan 2.2.6

konfigurasi yang relevan di application.conf:

evolutionplugin=disabled
db.default.driver=com.mysql.jdbc.Driver
db.default.url="jdbc:mysql://152.135.194.149/mydb"
db.default.user=someuser
db.default.password="somepassword"

bagian pengontrol yang relevan:

import play.api.db._
import play.api.Play.current
def test = Action {

  DB.withConnection { conn =>
    val stm = conn.createStatement()
    val res = stm.executeQuery("""
    SELECT  *
    FROM    sometable
    """.stripMargin)

    while (res.next()) {
      // this println succeeds in showing the selected data
      println(res.getString(1), res.getString(2), res.getString(3), res.getString(4))
    }

  }

  Status(200)("good enough")
} // test

menyelesaikan pesan kesalahan dari SBT:

[ERROR] [11/15/2017 16:31:43.041] [play-akka.actor.default-dispatcher-3] [TaskInvocation] Table 'mydb.token' doesn't exist
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'mydb.token' doesn't exist
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2825)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2459)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2376)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2360)
    at com.jolbox.bonecp.PreparedStatementHandle.executeUpdate(PreparedStatementHandle.java:205)
    at anorm.Sql$class.executeUpdate(Anorm.scala:473)
    at anorm.SqlQuery.executeUpdate(Anorm.scala:481)
    at service.PgSqlUserService$$anonfun$deleteExpiredTokens$1.apply(PgSqlUserService.scala:521)
    at service.PgSqlUserService$$anonfun$deleteExpiredTokens$1.apply(PgSqlUserService.scala:512)
    at play.api.db.DBApi$class.withConnection(DB.scala:82)
    at play.api.db.BoneCPApi.withConnection(DB.scala:276)
    at play.api.db.DB$$anonfun$withConnection$3.apply(DB.scala:162)
    at play.api.db.DB$$anonfun$withConnection$3.apply(DB.scala:162)
    at scala.Option.map(Option.scala:145)
    at play.api.db.DB$.withConnection(DB.scala:162)
    at service.PgSqlUserService.deleteExpiredTokens(PgSqlUserService.scala:512)
    at securesocial.core.UserServicePlugin$$anonfun$onStart$1.apply$mcV$sp(UserService.scala:137)
    at akka.actor.Scheduler$$anon$9.run(Scheduler.scala:80)
    at akka.actor.LightArrayRevolverScheduler$$anon$3$$anon$2.run(Scheduler.scala:241)
    at akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:42)
    at akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(AbstractDispatcher.scala:386)
    at scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
    at scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
    at scala.concurrent.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
    at scala.concurrent.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)

[error] a.d.TaskInvocation - Table 'mydb.token' doesn't exist
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'mydb.token' doesn't exist
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_151]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_151]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_151]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_151]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.27.jar:na]
    at com.mysql.jdbc.Util.getInstance(Util.java:386) ~[mysql-connector-java-5.1.27.jar:na]

Izin pengguna MySQL:

mysql> select Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Alter_priv, Super_priv, Create_tmp_table_priv, Create_tablespace_priv from user where Host="%" and User="someuser";
+------+------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+-----------------------+------------------------+
| Host | User       | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Alter_priv | Super_priv | Create_tmp_table_priv | Create_tablespace_priv |
+------+------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+-----------------------+------------------------+
| %    | someuser   | Y           | Y           | Y           | Y           | Y           | Y         | Y          | Y          | Y                     | Y                      |
+------+------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+-----------------------+------------------------+
1 row in set (0.00 sec)

Catatan:

  • Saya juga menggunakan Securesocial, yang menggunakan Postgres.

  • Saya berniat di masa depan untuk memungkinkan evolusi.

Bantuan apa pun akan sangat dihargai. Terima kasih sebelumnya!

1
frymaster 16 November 2017, 20:27

1 menjawab

Jawaban Terbaik

Masalahnya ternyata terkait dengan securesocial yang bersaing untuk konfigurasi db.default.

Karena proyek ini di-deploy di heroku, konfigurasi db.* diset di Procfile, bukan di application.conf, dan diset untuk menggunakan postgres di server yang berbeda.

Mendeklarasikan ulang db.default di application.conf menyebabkan securesocial mencoba untuk menanyakan database mysql dan, dengan demikian, kesalahan 'token tabel tidak ada'.

Solusinya adalah menyediakan konfigurasi db selain default dan menggunakannya:

Application.conf: gunakan 'custom' (atau string lainnya) alih-alih 'default'

db.custom.driver=com.mysql.jdbc.Driver
db.custom.url="jdbc:mysql://152.135.194.149/mydb"
db.custom.user=someuser
db.custom.password="somepassword"

Controller: panggil withConnection() dengan konfigurasi dari atas

import play.api.db._
import play.api.Play.current
def test = Action {

  // note the 'custom'
  DB.withConnection("custom") { conn =>
    val stm = conn.createStatement()
    val res = stm.executeQuery("""
    SELECT  *
    FROM    sometable
    """.stripMargin)

    while (res.next()) {
      // this println succeeds in showing the selected data
      println(res.getString(1), res.getString(2), res.getString(3), res.getString(4))
    }

  }

  Status(200)("good enough")
} // test
1
frymaster 17 November 2017, 19:38