mysqltcl - problem with mysqlsel

[_mAyDaY_]

hello everyone,

after weeks of googling and trying different combinations with the code, i'm out of ideas and need your help

i'm using a simple script to invite users to a channel using the MySQL db..
user sends his password and username in exchange for a channel invite

it all works nice, until db pings out (after 8 hours) and then the following msg is shown in the bot's log

Code: Select all

Tcl error [inv_1]: mysqlsel/db server: MySQL server has gone away

here's the code of the invite.tcl script

Code: Select all

package require mysqltcl

set dbuser "myDB_user"
set dbpassword "myDB_password"
set name "myDB_name"

set db_handle [mysqlconnect -host localhost -socket /var/run/mysqld/mysqld.sock -user $dbuser -password $dbpassword -db $name]

set userchan "#myChan"

bind msg - !invite inv_1

proc inv_1 { nick uhost handle arg } {

	global userchan db_handle
	 
	set arg [split $arg " "]
	set user1 [lindex $arg 0]
	set user1 [string range $user1 0 end]
	set pass1 [lindex $arg 1]
	set pass1 [string range $pass1 0 end]
	if { $user1 == "" || $pass1 == "" } { 
		 putserv "privmsg $nick :Use: !invite <username> <irc-key>" 
		 return 0
      }
	mysqlping $db_handle
	set sql "SELECT irckey FROM users WHERE username='[mysqlescape $user1]'"
	set result [mysqlsel $db_handle $sql -list]

    if {$result > 0} {

        set record [lindex $result 0];
        set pass [lindex $record 0];

        if {$pass1 == $pass} {
                    
            putserv "NOTICE $nick :Welcome :)"
            putserv "invite $nick $userchan"

        } else {

            puthelp "NOTICE $nick :Wrong password!"

        }

    } else {

        puthelp "NOTICE $nick :Your username ($user1) was not found in our records."

    }
    mysqlendquery $db_handle
}

is there a proper soultion to this problem?
if not, would it be wise to rehash the bot every 8 hours? (and how to do that?)

[rosc2112]

mem leak in mysql making it crash? You'd have to check your mysql logs and that's a different forum altogether to support mysql.

[nml375]

Memleaks??

It's just a simple matter of the mysql-server closing the connection to preserve resources.

One option may be to open the sql-connection on each invocation of the command. Another would be to use the mysqlping command to check the status of the connection upon each invocation...

[rosc2112]

Ahh I misunderstood, sounded like the mysql server was dying.

[_mAyDaY_]

One option may be to open the sql-connection on each invocation of the command. Another would be to use the mysqlping command to check the status of the connection upon each invocation...

About 1st option - how to do that? (ie. what modifications need to be made to the code above)

About 2nd option - please note that i already tryed that in the code (line above set sql)

Ahh I misunderstood, sounded like the mysql server was dying.

MySQL db is working the whole time, no problems with it, it's just that it closes the connection by default after 8 hours

[nml375]

Option 1:
Move the mysqlconnect command inside the proc, calling it at the beginning of the proc. Would probably be a good idea to close the connection manually at the end of the proc to preserve resources (don't remember if myslqtcl reuses existing connections or not).

Option 2:
Use the mysqlping to test wether the connection is alive or not. If not, reconnect...

[_mAyDaY_]

hey nml375, thnx for responding again!

Option 1:
Move the mysqlconnect command inside the proc, calling it at the beginning of the proc. Would probably be a good idea to close the connection manually at the end of the proc to preserve resources (don't remember if myslqtcl reuses existing connections or not).

would that be something like this? (i looked over mysqltcl documentation, and couldn't find how to close the connection manually)

Code: Select all

.....
proc inv_1 { nick uhost handle arg } {

   global userchan
   set db_handle [mysqlconnect -host localhost -socket /var/run/mysqld/mysqld.sock -user $dbuser -password $dbpassword -db $name]
......

Option 2:
Use the mysqlping to test wether the connection is alive or not. If not, reconnect...

how would that be done?

maybe this ... ?

Code: Select all

set result2 [mysqlping $db_handle]
if (result2 == false)
   mysqlconnect -host localhost....

btw. quote from the mysqltcl man page about mysqlping...

mysql::ping handle
Checks whether the connection to the server is working. If it has gone down, an automatic reconnection is attempted.
This function can be used by clients that remain idle for a long while, to check whether the server has closed the connection and reconnect if necessary.
Return True if server is alive

[nml375]

would that be something like this? (i looked over mysqltcl documentation, and couldn't find how to close the connection manually)

Yup, would be something like that. In order to close the connection, use "mysqlclose".

how would that be done?

maybe this ... ?

Code: Select all

set result2 [mysqlping $db_handle]
if (result2 == false)
   mysqlconnect -host localhost....

Almost, use braces rather than parenthesis...
Also, you really don't need an interim variable, just put the test directly into the if-conditional:

Code: Select all

if {![mysqlping $db_handle]} {
 set db_handle [mysqlconnect.....]
}

[_mAyDaY_]

i'm gonna try option 1 first
have to wait 8 hours first till i know the effect

thnx for helping, i'll post the results tomorrow

EDIT: worked! thnx nml375 for your help!

[metroid]

Actually, according to the documentation, it should already try to reconnect on it's own, if you open a new connection, won't you have 2 open?

[nml375]

That would depend on how mysqllib has been set up. all "mysqlping" does is extract the handle from the commandline, and call the function int mysql_ping(MYSQL *mysql) from the mysql C library.

If the "auto-reconnect" feature has been enabled, a new connection is attempted, and should be available once the command returns. If not, it will only return the current status of the connection handle. Wether "auto-reconnect" is enabled or not would be controlled by the int mysql_options(MYSQL *mysql, enum mysql_option option, const void *arg) C library function.