egghelp/eggheads community

mysqltcl - problem with mysqlsel

2007-12-15T12:44:47-04:00

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.

Statistics: Posted by nml375 — Sat Dec 15, 2007 12:44 pm

mysqltcl - problem with mysqlsel

2007-12-15T11:23:30-04:00

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?

Statistics: Posted by metroid — Sat Dec 15, 2007 11:23 am

mysqltcl - problem with mysqlsel

2007-12-13T16:07:33-04:00

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!

Statistics: Posted by _mAyDaY_ — Thu Dec 13, 2007 4:07 pm

mysqltcl - problem with mysqlsel

2007-12-13T15:27:07-04:00

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:
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:

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

Statistics: Posted by nml375 — Thu Dec 13, 2007 3:27 pm

mysqltcl - problem with mysqlsel

2007-12-13T15:18:58-04:00

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:

.....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:

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

Statistics: Posted by _mAyDaY_ — Thu Dec 13, 2007 3:18 pm

mysqltcl - problem with mysqlsel

2007-12-13T14:41:34-04:00

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...

Statistics: Posted by nml375 — Thu Dec 13, 2007 2:41 pm

mysqltcl - problem with mysqlsel

2007-12-13T12:04:54-04:00

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

Statistics: Posted by _mAyDaY_ — Thu Dec 13, 2007 12:04 pm

mysqltcl - problem with mysqlsel

2007-12-13T02:36:02-04:00

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

Statistics: Posted by rosc2112 — Thu Dec 13, 2007 2:36 am

mysqltcl - problem with mysqlsel

2007-12-12T15:36:07-04:00

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...

Statistics: Posted by nml375 — Wed Dec 12, 2007 3:36 pm

mysqltcl - problem with mysqlsel

2007-12-12T15:12:20-04:00

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.

Statistics: Posted by rosc2112 — Wed Dec 12, 2007 3:12 pm

mysqltcl - problem with mysqlsel

2007-12-12T07:00:48-04:00

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:

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

here's the code of the invite.tcl script

Code:

package require mysqltclset 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_1proc 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  "  return 0      }mysqlping $db_handleset 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?)

Statistics: Posted by _mAyDaY_ — Wed Dec 12, 2007 7:00 am