- Added code for SQL that will automatically detect the ping interval at startup, and copy-pasted it around the code

- Removed the conf setting as it is not needed anymore

git-svn-id: https://rathena.svn.sourceforge.net/svnroot/rathena/trunk@9830 54d463be-8e91-2dee-dedb-b68131a5f0ec

8 files changed, 115 insertions, 63 deletions

diff --git a/Changelog-Trunk.txt b/Changelog-Trunk.txt
index fa0c353ea..2b682ac7e 100644
--- a/Changelog-Trunk.txt
+++ b/Changelog-Trunk.txt
@@ -8,6 +8,8 @@ IF YOU HAVE A WORKING AND TESTED BUGFIX PUT IT INTO STABLE AS WELL AS TRUNK.
 	  idling on the ground waiting for a new target.
 	* Cleaned up some the rude-attacked code, being attacked while under spider
 	  web now triggers rude-attacked. [Skotlex]
+	* Added code for SQL that will automatically detect the ping interval
+	  at startup, and copy-pasted it around the code; removed the conf setting
 	* Added a missing null pointer check in clif_parse_globalmessage
 	* Fixed the new socket code, which was triggering the inactivity timeout
 	  on the servers' listening sockets
diff --git a/conf-tmpl/Changelog.txt b/conf-tmpl/Changelog.txt
index 5f9a68738..b5b49e50c 100644
--- a/conf-tmpl/Changelog.txt
+++ b/conf-tmpl/Changelog.txt
@@ -1,5 +1,8 @@
 Date	Added
 
+2007/02/08
+	* Since the mysql ping interval is now autoconfigured, removed
+	the connection_ping_interval config setting in inter_athena.conf
 2007/02/01
 	* Improved a bit the description of the mvp item get time config settings.
 2007/01/29
diff --git a/conf-tmpl/inter_athena.conf b/conf-tmpl/inter_athena.conf
index 6b4db55b1..1822ba74f 100644
--- a/conf-tmpl/inter_athena.conf
+++ b/conf-tmpl/inter_athena.conf
@@ -56,13 +56,6 @@ lowest_gm_level: 1
 // (Note that this feature requires MySQL 4.1+)
 //default_codepage: 
 
-// Interval (in hours) at which servers do a ping on all sql-connections
-// to keep them alive (the default mysql settings makes a connection time-out
-// after 8 hours of inactivity).
-// 0 disables (default). Enable it only if you are getting
-// "MySQL server has gone Away" errors due to lack of activity on your server
-connection_ping_interval: 7
-
 // For IPs, ideally under linux, you want to use localhost instead of 127.0.0.1 
 // Under windows, you want to use 127.0.0.1.  If you see a message like
 // "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)"
diff --git a/src/char_sql/char.c b/src/char_sql/char.c
index d71ea3483..f81f03cad 100644
--- a/src/char_sql/char.c
+++ b/src/char_sql/char.c
@@ -64,7 +64,6 @@ int save_log = 0; //Have the logs be off by default when converting
 #else
 int save_log = 1;
 int db_use_sqldbs;
-int connection_ping_interval = 0;
 
 char login_db[256] = "login";
 char login_db_account_id[32] = "account_id";
@@ -4033,8 +4032,6 @@ void sql_config_read(const char *cfgName){ /* Kalaspuff, to get login_db */
 			strcpy(item_db_db,w2);
 		}else if(strcmpi(w1,"item_db2_db")==0){
 			strcpy(item_db2_db,w2);
-		} else if(strcmpi(w1,"connection_ping_interval")==0) {
-			connection_ping_interval = config_switch(w2);
 #endif
 		//support the import command, just like any other config
 		}else if(strcmpi(w1,"import")==0){
diff --git a/src/char_sql/char.h b/src/char_sql/char.h
index 1ad48fc9b..4a95fd0f8 100644
--- a/src/char_sql/char.h
+++ b/src/char_sql/char.h
@@ -90,7 +90,6 @@ extern char party_db[256];
 extern char pet_db[256];
 
 extern int db_use_sqldbs; // added for sql item_db read for char server [Valaris]
-extern int connection_ping_interval;
 
 extern char login_db_level[32];
 extern char login_db_account_id[32];
diff --git a/src/char_sql/inter.c b/src/char_sql/inter.c
index 326bd2ca1..052316e0a 100644
--- a/src/char_sql/inter.c
+++ b/src/char_sql/inter.c
@@ -297,10 +297,9 @@ int inter_log(char *fmt,...)
 	return 0;
 }
 
-/*======================================================
- * Does a mysql_ping to all connection handles. [Skotlex]
- *------------------------------------------------------
- */
+/*=============================================
+ * Does a mysql_ping to all connection handles
+ *---------------------------------------------*/
 int inter_sql_ping(int tid, unsigned int tick, int id, int data) 
 {
 	ShowInfo("Pinging SQL server to keep connection alive...\n");
@@ -309,6 +308,34 @@ int inter_sql_ping(int tid, unsigned int tick, int id, int data)
 		mysql_ping(&lmysql_handle);
 	return 0;
 }
+
+
+int sql_ping_init(void)
+{
+	int connection_timeout, connection_ping_interval;
+
+	// set a default value first
+	connection_timeout = 28800; // 8 hours
+
+	// ask the mysql server for the timeout value
+	if (!mysql_query(&mysql_handle, "SHOW VARIABLES LIKE 'wait_timeout'")
+	&& (sql_res = mysql_store_result(&mysql_handle)) != NULL) {
+		sql_row = mysql_fetch_row(sql_res);
+		if (sql_row)
+			connection_timeout = atoi(sql_row[1]);
+		if (connection_timeout < 60)
+			connection_timeout = 60;
+		mysql_free_result(sql_res);
+	}
+
+	// establish keepalive
+	connection_ping_interval = connection_timeout - 30; // 30-second reserve
+	add_timer_func_list(inter_sql_ping, "inter_sql_ping");
+	add_timer_interval(gettick() + connection_ping_interval*1000, inter_sql_ping, 0, 0, connection_ping_interval*1000);
+
+	return 0;
+}
+
 #endif //TXT_SQL_CONVERT
 
 // initialize
@@ -370,11 +397,7 @@ int inter_init_sql(const char *file)
 	inter_homunculus_sql_init(); // albator
 	inter_accreg_sql_init();
 
-	if (connection_ping_interval) {
-		add_timer_func_list(inter_sql_ping, "inter_sql_ping");
-		add_timer_interval(gettick()+connection_ping_interval*60*60*1000,
-				inter_sql_ping, 0, 0, connection_ping_interval*60*60*1000);
-	}
+	sql_ping_init();
 #endif //TXT_SQL_CONVERT
 	return 0;
 }
diff --git a/src/login_sql/login.c b/src/login_sql/login.c
index 1c9a95e22..9c276c835 100644
--- a/src/login_sql/login.c
+++ b/src/login_sql/login.c
@@ -89,6 +89,8 @@ static int online_check=1; //When set to 1, login server rejects incoming player
 static int ip_sync_interval = 0;
 
 MYSQL mysql_handle;
+MYSQL_RES* 	sql_res ;
+MYSQL_ROW	sql_row ;
 
 int ipban = 1;
 int dynamic_account_ban = 1;
@@ -109,7 +111,6 @@ char login_db[256] = "login";
 int log_login=1; //Whether to log the logins or not. [Skotlex]
 char loginlog_db[256] = "loginlog";
 bool login_gm_read = true;
-int connection_ping_interval = 0;
 
 // added to help out custom login tables, without having to recompile
 // source so options are kept in the login_athena.conf or the inter_athena.conf
@@ -348,10 +349,9 @@ int e_mail_check(char *email) {
 	return 1;
 }
 
-/*======================================================
- * Does a mysql_ping to all connection handles. [Skotlex]
- *------------------------------------------------------
- */
+/*=============================================
+ * Does a mysql_ping to all connection handles
+ *---------------------------------------------*/
 int login_sql_ping(int tid, unsigned int tick, int id, int data) 
 {
 	ShowInfo("Pinging SQL server to keep connection alive...\n");
@@ -359,6 +359,32 @@ int login_sql_ping(int tid, unsigned int tick, int id, int data)
 	return 0;
 }
 
+int sql_ping_init(void)
+{
+	int connection_timeout, connection_ping_interval;
+
+	// set a default value first
+	connection_timeout = 28800; // 8 hours
+
+	// ask the mysql server for the timeout value
+	if (!mysql_query(&mysql_handle, "SHOW VARIABLES LIKE 'wait_timeout'")
+	&& (sql_res = mysql_store_result(&mysql_handle)) != NULL) {
+		sql_row = mysql_fetch_row(sql_res);
+		if (sql_row)
+			connection_timeout = atoi(sql_row[1]);
+		if (connection_timeout < 60)
+			connection_timeout = 60;
+		mysql_free_result(sql_res);
+	}
+
+	// establish keepalive
+	connection_ping_interval = connection_timeout - 30; // 30-second reserve
+	add_timer_func_list(login_sql_ping, "login_sql_ping");
+	add_timer_interval(gettick() + connection_ping_interval*1000, login_sql_ping, 0, 0, connection_ping_interval*1000);
+
+	return 0;
+}
+
 //-----------------------------------------------------
 // Read Account database - mysql db
 //-----------------------------------------------------
@@ -400,11 +426,8 @@ int mmo_auth_sqldb_init(void) {
 		}
 	}
 
-	if (connection_ping_interval) {
-		add_timer_func_list(login_sql_ping, "login_sql_ping");
-		add_timer_interval(gettick()+connection_ping_interval*60*60*1000,
-				login_sql_ping, 0, 0, connection_ping_interval*60*60*1000);
-	}
+	sql_ping_init();
+
 	return 0;
 }
 
@@ -2282,9 +2305,6 @@ void sql_config_read(const char *cfgName){ /* Kalaspuff, to get login_db */
 			strcpy(login_server_db, w2);
 			ShowStatus ("set login_server_db : %s\n",w2);
 		} 
-		else if(strcmpi(w1,"connection_ping_interval")==0) {
-			connection_ping_interval = atoi(w2);
-		}
 		else if(strcmpi(w1,"default_codepage")==0){
 			strcpy(default_codepage, w2);
 			ShowStatus ("set default_codepage : %s\n",w2);
diff --git a/src/map/map.c b/src/map/map.c
index 5f6421a66..af3f48ad0 100644
--- a/src/map/map.c
+++ b/src/map/map.c
@@ -74,7 +74,6 @@ char map_server_pw[32] = "ragnarok";
 char map_server_db[32] = "ragnarok";
 char default_codepage[32] = ""; //Feature by irmin.
 int db_use_sqldbs = 0;
-int connection_ping_interval = 0;
 
 char item_db_db[32] = "item_db";
 char item_db2_db[32] = "item_db2";
@@ -3489,8 +3488,6 @@ int inter_config_read(char *cfgName)
 		} else if(strcmpi(w1,"use_sql_db")==0){
 			db_use_sqldbs = battle_config_switch(w2);
 			ShowStatus ("Using SQL dbs: %s\n",w2);
-		} else if(strcmpi(w1,"connection_ping_interval")==0) {
-			connection_ping_interval = battle_config_switch(w2);
 		}else if(strcmpi(w1, "char_server_ip") == 0){
 			strcpy(charsql_host, w2);
 		}else if(strcmpi(w1, "char_server_port") == 0){
@@ -3543,9 +3540,7 @@ int inter_config_read(char *cfgName)
 #ifndef TXT_ONLY
 /*=======================================
  *  MySQL Init
- *---------------------------------------
- */
-
+ *---------------------------------------*/
 int map_sql_init(void){
 
 	mysql_init(&mmysql_handle);
@@ -3578,6 +3573,7 @@ int map_sql_init(void){
 			}
 		}
 	}
+
 	if( strlen(default_codepage) > 0 ) {
 		sprintf( tmp_sql, "SET NAMES %s", default_codepage );
 		if (mysql_query(&mmysql_handle, tmp_sql)) {
@@ -3585,6 +3581,7 @@ int map_sql_init(void){
 			ShowDebug("at %s:%d - %s\n", __FILE__,__LINE__,tmp_sql);
 		}
 	}
+
 	return 0;
 }
 
@@ -3624,6 +3621,46 @@ int log_sql_init(void){
 	}
 	return 0;
 }
+
+/*=============================================
+ * Does a mysql_ping to all connection handles
+ *---------------------------------------------*/
+int map_sql_ping(int tid, unsigned int tick, int id, int data) 
+{
+	ShowInfo("Pinging SQL server to keep connection alive...\n");
+	mysql_ping(&mmysql_handle);
+	if (log_config.sql_logs)
+		mysql_ping(&logmysql_handle);
+	if(mail_server_enable)
+		mysql_ping(&mail_handle);
+	return 0;
+}
+
+int sql_ping_init(void)
+{
+	int connection_timeout, connection_ping_interval;
+
+	// set a default value first
+	connection_timeout = 28800; // 8 hours
+
+	// ask the mysql server for the timeout value
+	if (!mysql_query(&mmysql_handle, "SHOW VARIABLES LIKE 'wait_timeout'")
+	&& (sql_res = mysql_store_result(&mmysql_handle)) != NULL) {
+		sql_row = mysql_fetch_row(sql_res);
+		if (sql_row)
+			connection_timeout = atoi(sql_row[1]);
+		if (connection_timeout < 60)
+			connection_timeout = 60;
+		mysql_free_result(sql_res);
+	}
+
+	// establish keepalive
+	connection_ping_interval = connection_timeout - 30; // 30-second reserve
+	add_timer_func_list(map_sql_ping, "map_sql_ping");
+	add_timer_interval(gettick() + connection_ping_interval*1000, map_sql_ping, 0, 0, connection_ping_interval*1000);
+
+	return 0;
+}
 #endif /* not TXT_ONLY */
 
 int map_db_final(DBKey k,void *d,va_list ap)
@@ -3809,24 +3846,6 @@ void map_versionscreen(int flag) {
 	if (flag) exit(1);
 }
 
-
-#ifndef TXT_ONLY
-/*======================================================
- * Does a mysql_ping to all connection handles. [Skotlex]
- *------------------------------------------------------
- */
-int map_sql_ping(int tid, unsigned int tick, int id, int data) 
-{
-	ShowInfo("Pinging SQL server to keep connection alive...\n");
-	mysql_ping(&mmysql_handle);
-	if (log_config.sql_logs)
-		mysql_ping(&logmysql_handle);
-	if(mail_server_enable)
-		mysql_ping(&mail_handle);
-	return 0;
-}
-#endif
-
 /*======================================================
  * Map-Server Init and Command-line Arguments [Valaris]
  *------------------------------------------------------
@@ -3965,12 +3984,8 @@ int do_init(int argc, char *argv[]) {
 
 	if (log_config.sql_logs)
 		log_sql_init();
-	
-	if (connection_ping_interval) {
-		add_timer_func_list(map_sql_ping, "map_sql_ping");
-		add_timer_interval(gettick()+connection_ping_interval*60*60*1000,
-				map_sql_ping, 0, 0, connection_ping_interval*60*60*1000);
-	}
+
+	sql_ping_init();
 #endif /* not TXT_ONLY */
 
 	npc_event_do_oninit();	// npcのOnInitイベント?行