mysql - 连接到 MySql 的 Scala Play 显示运行时错误 "Table ' token '不存在”

Question

我正在尝试使用 jdbc 从 Play for Scala Controller 读取和写入远程 MySQL 数据库，而 SBT 在运行时显示错误:

"Table '{dbname}.token' doesn't exist"

尽管出现此错误，但 Sql 调用(选择)仍按预期完成。

我的问题是:

1. 这个“ token ”表是什么，我为什么需要它？

2. 有没有办法关闭对“ token ”表的要求

我用谷歌搜索了这个问题，但没有找到答案，也没有找到关于这个 token 表的任何信息。

详情如下:

版本:

• scala 2.11.6
• Play 2.2.6

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"

Controller 的相关部分:

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

来自 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]

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)

注意事项:

• 我还使用了使用 Postgres 的 Securesocial。

• 我打算在未来实现进化。

如有任何帮助，我们将不胜感激。提前致谢!

Answer 1

问题原来与 securesocial 竞争 db.default 配置有关。

因为这个项目部署在 heroku 上，所以 db.* 配置是在 Procfile 中设置的，而不是 application.conf，并且被设置为在不同的服务器上使用 postgres。

在 application.conf 中重新声明 db.default 会导致 securesocial 尝试查询 mysql 数据库，因此出现“表 token 不存在”错误。

解决方案是提供默认值以外的数据库配置并使用它:

application.conf:使用'custom'(或任何其他字符串)而不是'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 :使用上面的配置调用 withConnection()

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