Java 如何查看/更改 MySQL 连接超时设置？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16192410/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can i see / change MySQL connection timeout settings?
提问by Edgar
I have a java program, when i log in, after ~600,000 milliseconds (i actually tried several times, and its always ~600,000, thats why i think theres somewhere set up a timeout for 600,000 miliseconds) my database connection crashes and my program no longer works (it always needs to be connected to database). It gives me Communication link failureerror. I Here are my mysql connection settings:
我有一个 Java 程序，当我登录时，大约 600,000 毫秒后（我实际上尝试了几次，并且总是 ~600,000，这就是为什么我认为某处设置了 600,000 毫秒的超时）我的数据库连接崩溃了，我的程序没有更长的工作（它总是需要连接到数据库）。它给了我Communication link failure错误。我这是我的 mysql 连接设置：
import java.sql.*;
import javax.swing.*;
public class mysqlconnect {
Connection conn = null;
public static Connection ConnectDb() {
try{
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://server_name/database_name","user_name","user_password");
return conn;
}catch (Exception e) {
JOptionPane.showMessageDialog(null, "Cant connect to db");
return null;
}
}
}
I tried adding ?autoReconnect=true& tcpKeepAliveto my code, but no luck. Is there any way to go to phpmyadmin and change some setting there (increase the timeout time)?
我尝试将?autoReconnect=true&添加tcpKeepAlive到我的代码中，但没有运气。有什么办法可以转到 phpmyadmin 并在那里更改一些设置（增加超时时间）？
采纳答案by ?mer Faruk Almal?
SET SESSION wait_timeout = 999999;//or anything you want
From mysql command line that will increase the timeout value. To be able to see the value:
从将增加超时值的 mysql 命令行。为了能够看到值：
SHOW VARIABLES LIKE 'wait_timeout';
回答by MENTAL
You can make this request to show your timeout in MySql
您可以发出此请求以在 MySql 中显示您的超时
SHOW VARIABLES LIKE 'connect_timeout';
Next you can update it to 60 secondes for exemple
接下来，您可以将其更新为 60 秒，例如
SET GLOBAL connect_timeout=60;
If the problem is the Query you can use Statement.setQueryTimeout
如果问题是您可以使用的查询 Statement.setQueryTimeout
回答by user3252230
You can set wait_timeoutNET_read_timeoutand connect_timeoutfor resolve the problem in following way.
您可以通过以下方式设置wait_timeoutNET_read_timeout和connect_timeout解决问题。
SHOW VARIABLES LIKE 'wait_timeout';
SET SESSION wait_timeout = 999999;
SHOW VARIABLES LIKE 'wait_timeout';
SET SESSION wait_timeout = 999999;
SHOW variables LIKE 'NET_read_timeout';
SET SESSION net_read_timeout = 1000;
SHOW variables LIKE 'NET_read_timeout';
SET SESSION net_read_timeout = 1000;
SHOW VARIABLES LIKE 'connect_timeout';
SET GLOBAL connect_timeout = 1000;
SHOW VARIABLES LIKE 'connect_timeout';
SET GLOBAL connect_timeout = 1000;
回答by BabaNew
I tipically do it with either one of the following ways:
我通常使用以下任何一种方式来做到这一点：
1) Edit the my.ini file. This file is tipically located on C:\ProgramData\MySQL\MySQL Server 5.6\my.ini
1) 编辑 my.ini 文件。此文件通常位于 C:\ProgramData\MySQL\MySQL Server 5.6\my.ini
[mysqld]
interactive_timeout=2147483
wait_timeout=2147483
(max value is 2147483 for Windows and 31536000 in other OS)
（Windows 的最大值为 2147483，其他操作系统的最大值为 31536000）
After that, restart the machine
之后，重新启动机器
2) Include the wait time out parameter in the JDBC url. Here's a JDBC URL taken from my code
2) 在 JDBC url 中包含等待超时参数。这是从我的代码中获取的 JDBC URL
jdbc:mysql://HOST:PORT/DATABASE_NAME?autoReconnect=true&sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH',interactive_timeout=2147483,wait_timeout=2147483
JDBC：MySQL的：// HOST：PORT / DATABASE_NAME autoReconnect的=真sessionVariables =的sql_mode = 'STRICT_TRANS_TABLES，NO_AUTO_CREATE_USER，NO_ENGINE_SUBSTITUTION，PAD_CHAR_TO_FULL_LENGTH'，interactive_timeout = 2147483，WAIT_TIMEOUT = 2147483
