java - 从 MySQL 5.5 升级到 5.6 并获取 "SQLException: Connection is closed"

Question

我正在将 Play Framework 与 eBean 结合使用。升级到 MySQL 5.6 在运行我的数据库演化时导致以下异常:

java.sql.SQLException: Connection is closed!
 com.jolbox.bonecp.ConnectionHandle.checkClosed(ConnectionHandle.java:459)
 com.jolbox.bonecp.ConnectionHandle.prepareStatement(ConnectionHandle.java:1011)
 play.api.db.evolutions.Evolutions$.prepare(Evolutions.scala:128)
 play.api.db.evolutions.Evolutions$.updateLastProblem$1(Evolutions.scala:247)
 play.api.db.evolutions.Evolutions$.applyScript(Evolutions.scala:289)
 play.api.db.evolutions.EvolutionsPlugin.handleWebCommand(Evolutions.scala:584)
 play.core.ReloadableApplication$$anonfun$handleWebCommand$1$$anonfun$apply$5$$anonfun$apply$6.apply(ApplicationProvider.scala:193)
 play.core.ReloadableApplication$$anonfun$handleWebCommand$1$$anonfun$apply$5$$anonfun$apply$6.apply(ApplicationProvider.scala:192)
 scala.collection.LinearSeqOptimized$class.foldLeft(LinearSeqOptimized.scala:110)
 scala.collection.immutable.List.foldLeft(List.scala:83)
 play.core.ReloadableApplication$$anonfun$handleWebCommand$1$$anonfun$apply$5.apply(ApplicationProvider.scala:192)
 play.core.ReloadableApplication$$anonfun$handleWebCommand$1$$anonfun$apply$5.apply(ApplicationProvider.scala:191)
 scala.Option.flatMap(Option.scala:170)
 play.core.ReloadableApplication$$anonfun$handleWebCommand$1.apply(ApplicationProvider.scala:191)
 play.core.ReloadableApplication$$anonfun$handleWebCommand$1.apply(ApplicationProvider.scala:191)
 scala.Option.orElse(Option.scala:266)
 play.core.ReloadableApplication.handleWebCommand(ApplicationProvider.scala:189)
 play.core.server.Server$$anonfun$getHandlerFor$1.apply(Server.scala:69)
 play.core.server.Server$$anonfun$getHandlerFor$1.apply(Server.scala:69)
 scala.util.control.Exception$Catch$$anonfun$either$1.apply(Exception.scala:125)
 scala.util.control.Exception$Catch$$anonfun$either$1.apply(Exception.scala:125)
 scala.util.control.Exception$Catch.apply(Exception.scala:103)
 scala.util.control.Exception$Catch.either(Exception.scala:125)
 play.core.server.Server$class.getHandlerFor(Server.scala:69)
 play.core.server.NettyServer.getHandlerFor(NettyServer.scala:37)
 play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$10.apply(PlayDefaultUpstreamHandler.scala:157)
 play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$10.apply(PlayDefaultUpstreamHandler.scala:157)
 scala.util.Either.fold(Either.scala:99)
 play.core.server.netty.PlayDefaultUpstreamHandler.messageReceived(PlayDefaultUpstreamHandler.scala:142)
 org.jboss.netty.channel.SimpleChannelUpstreamHandler.handleUpstream(SimpleChannelUpstreamHandler.java:70)
 org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564)
 org.jboss.netty.channel.DefaultChannelPipeline$DefaultChannelHandlerContext.sendUpstream(DefaultChannelPipeline.java:791)
 com.typesafe.netty.http.pipelining.HttpPipeliningHandler.messageReceived(HttpPipeliningHandler.java:62)
 org.jboss.netty.channel.SimpleChannelHandler.handleUpstream(SimpleChannelHandler.java:88)
 org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564)
 org.jboss.netty.channel.DefaultChannelPipeline$DefaultChannelHandlerContext.sendUpstream(DefaultChannelPipeline.java:791)
 org.jboss.netty.handler.codec.http.HttpContentDecoder.messageReceived(HttpContentDecoder.java:108)
 org.jboss.netty.channel.SimpleChannelUpstreamHandler.handleUpstream(SimpleChannelUpstreamHandler.java:70)
 org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564)
 org.jboss.netty.channel.DefaultChannelPipeline$DefaultChannelHandlerContext.sendUpstream(DefaultChannelPipeline.java:791)
 org.jboss.netty.channel.Channels.fireMessageReceived(Channels.java:296)
 org.jboss.netty.handler.codec.frame.FrameDecoder.unfoldAndFireMessageReceived(FrameDecoder.java:459)
 org.jboss.netty.handler.codec.replay.ReplayingDecoder.callDecode(ReplayingDecoder.java:536)
 org.jboss.netty.handler.codec.replay.ReplayingDecoder.messageReceived(ReplayingDecoder.java:435)
 org.jboss.netty.channel.SimpleChannelUpstreamHandler.handleUpstream(SimpleChannelUpstreamHandler.java:70)
 org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564)
 org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:559)
 org.jboss.netty.channel.Channels.fireMessageReceived(Channels.java:268)
 org.jboss.netty.channel.Channels.fireMessageReceived(Channels.java:255)
 org.jboss.netty.channel.socket.nio.NioWorker.read(NioWorker.java:88)
 org.jboss.netty.channel.socket.nio.AbstractNioWorker.process(AbstractNioWorker.java:108)
 org.jboss.netty.channel.socket.nio.AbstractNioSelector.run(AbstractNioSelector.java:318)
 org.jboss.netty.channel.socket.nio.AbstractNioWorker.run(AbstractNioWorker.java:89)
 org.jboss.netty.channel.socket.nio.NioWorker.run(NioWorker.java:178)
 org.jboss.netty.util.ThreadRenamingRunnable.run(ThreadRenamingRunnable.java:108)
 org.jboss.netty.util.internal.DeadLockProofWorker$1.run(DeadLockProofWorker.java:42)
 java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
 java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
 java.lang.Thread.run(Thread.java:745)

我还在应用程序日志中发现了这一点:

    2015-01-29 09:13:03,742 - [ERROR] - from com.jolbox.bonecp.ConnectionHandle in New I/O worker #1 
Database access problem. Killing off this connection and all remaining connections in the connection pool. SQL State = HY000

我尝试过 mysql-connector-java 5.1.18 和 5.1.34，也尝试过 Play 2.3.6 和 2.3.4。除了使用 MySQL 5.5 而不是 5.6 之外，似乎没有什么可以解决这个问题。

Answer 1

从boncp打开日志记录后，我能够识别哪个SQL语句导致连接关闭。我试图在不删除外键的情况下更改外键列。 MySQL 5.5 可以运行而 5.6 不能运行的原因如下(来自 5.6 的发行说明):

As of 5.6.7, the server prohibits changes to foreign key columns with the potential to cause loss of referential integrity. A workaround is to use ALTER TABLE ... DROP FOREIGN KEY before changing the column definition and ALTER TABLE ... ADD FOREIGN KEY afterward.