gpt4 book ai didi

mysql - flyway 连接超时

转载 作者:行者123 更新时间:2023-11-29 02:02:34 30 4
gpt4 key购买 nike

我正在使用 flyway 1.5 和 mysql Ver 14.14 Distrib 5.1.52,用于使用 readline 5.1 的 unknown-linux-gnu (x86_64)

我编写了以下迁移:

alter table table1 add column col_11 blob;
alter table table1 add column col_12 varchar(255) DEFAULT NULL;

alter table table2 add column col_21 blob;
alter table table2 add column col_22 varchar(255) DEFAULT NULL;

其中 table1 很小,table2 有 283,018 行。

迁移成功(列被添加到 table1table2)但是版本更新失败(我得到了 com.mysql.jdbc. exceptions.jdbc4.MySQLNonTransientConnectionException:连接关闭后不允许进行任何操作。)。

我的 my.cnf 文件包含下一行 wait_timeout=30 我认为发生的事情是将这两列添加到 table2花费了超过 wait_timout - 这导致迁移“失败” - 现在我遇到了这种情况:迁移成功但版本未更新。但后来我运行了这个 sql 查询:

show variables like "%timeout%";

得到这个:

+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+----------------------------+-------+

这表明 wait_timeout 大约为 8 小时...

所以我的问题是为什么会出现此连接超时?

主要是 - 我该如何修复它?

编辑:应用程序和数据库都在同一台机器上。

这是完整的堆栈跟踪:

Current schema version: 12.9.3
Migrating to version 12.9.3.1

Error while extracting database product name - falling back to empty error codes
org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:296)
at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:320)
at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:216)
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:140)
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.<init>(SQLErrorCodeSQLExceptionTranslator.java:103)
at org.springframework.jdbc.support.JdbcAccessor.getExceptionTranslator(JdbcAccessor.java:99)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:522)
at com.googlecode.flyway.core.metadatatable.MetaDataTable.insert(MetaDataTable.java:149)
at com.googlecode.flyway.core.migration.DbMigrator.applyMigration(DbMigrator.java:248)
at com.googlecode.flyway.core.migration.DbMigrator$1.doInTransaction(DbMigrator.java:148)
at com.googlecode.flyway.core.migration.DbMigrator$1.doInTransaction(DbMigrator.java:114)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
at com.googlecode.flyway.core.migration.DbMigrator.migrate(DbMigrator.java:113)
at com.googlecode.flyway.core.Flyway.migrate(Flyway.java:619)
at com.googlecode.flyway.commandline.Main.main(Main.java:79)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
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:1013)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1205)
at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1197)
at com.mysql.jdbc.ConnectionImpl.getMetaData(ConnectionImpl.java:3061)
at com.mysql.jdbc.ConnectionImpl.getMetaData(ConnectionImpl.java:3056)
at org.springfra
mework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:285)
... 15 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 654,699 milliseconds ago. The last packet sent successfully to the server was 17 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3102)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2991)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3532)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1749)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1666)
at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:512)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
... 9 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2552)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3002)
... 18 more
Application exception overridden by rollback exception
org.springframework.dao.RecoverableDataAccessException: StatementCallback; SQL [UPDATE schema_version SET current_version=0]; Communications link failure
The last packet successfully received from the server was 654,699 milliseconds ago. The last packet sent successfully to the server was 17 milliseconds ago.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 654,699 milliseconds ago. The last packet sent successfully to the server was 17 milliseconds ago.
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:97)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:522)
at com.googlecode.flyway.core.metadatatable.MetaDataTable.insert(MetaDataTable.java:149)
at com.googlecode.flyway.core.migration.DbMigrator.applyMigration(DbMigrator.java:248)
at com.googlecode.flyway.core.migration.DbMigrator$1.doInTransaction(DbMigrator.java:148)
at com.googlecode.flyway.core.migration.DbMigrator$1.doInTransaction(DbMigrator.java:114)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
at com.googlecode.flyway.core.migration.DbMigrator.migrate(DbMigrator.java:113)
at com.googlecode.flyway.core.Flyway.migrate(Flyway.java:619)
at com.googlecode.flyway.commandline.Main.main(Main.java:79)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 654,699 milliseconds ago. The last packet sent successfully to the server was 17 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3102)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2991)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3532)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1749)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1666)
at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:512)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
... 9 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2552)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3002)
... 18 more
FlywayException: Migration failed !
Occured in com.googlecode.flyway.core.migration.DbMigrator in method migrate, line number 164
Caused by java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
Occured in com.mysql.jdbc.MysqlIO in method readFully, line number 2552

最佳答案

我们刚刚遇到了这个问题,并设法找到了适合我们的解决方案。似乎 Flyway 至少使用了两个连接:一个用于锁定 schema_version 表,一个用于实际运行 alters。当更改花费足够长的时间导致连接锁定 schema_version 表超时时,就会出现此问题。解决此问题的最简单方法是将 MySQL wait_timeout 设置为相对较大的值。在我们的例子中,我们将其设置为 480 分钟(或 28800 秒):

set global wait_timeout=28800;

关于mysql - flyway 连接超时,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/12339164/

30 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com