egghelp.org community Forum Index
[ egghelp.org home | forum home ]
egghelp.org community
Discussion of eggdrop bots, shell accounts and tcl scripts.
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

mysqltcl - problem with mysqlsel

 
Post new topic   Reply to topic    egghelp.org community Forum Index -> Modules & Programming
View previous topic :: View next topic  
Author Message
_mAyDaY_
Voice


Joined: 12 Dec 2007
Posts: 4

PostPosted: Wed Dec 12, 2007 7:00 am    Post subject: mysqltcl - problem with mysqlsel Reply with quote

hello everyone,

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

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 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?)
Back to top
View user's profile Send private message
rosc2112
Revered One


Joined: 19 Feb 2006
Posts: 1454
Location: Northeast Pennsylvania

PostPosted: Wed Dec 12, 2007 3:12 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
nml375
Revered One


Joined: 04 Aug 2006
Posts: 2846

PostPosted: Wed Dec 12, 2007 3:36 pm    Post subject: Reply with quote

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...
_________________
NML_375, idling at #eggdrop@IrcNET
Back to top
View user's profile Send private message
rosc2112
Revered One


Joined: 19 Feb 2006
Posts: 1454
Location: Northeast Pennsylvania

PostPosted: Thu Dec 13, 2007 2:36 am    Post subject: Reply with quote

Ahh I misunderstood, sounded like the mysql server was dying.
Back to top
View user's profile Send private message
_mAyDaY_
Voice


Joined: 12 Dec 2007
Posts: 4

PostPosted: Thu Dec 13, 2007 12:04 pm    Post subject: Reply with quote

nml375 wrote:
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)

rosc2112 wrote:
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
Back to top
View user's profile Send private message
nml375
Revered One


Joined: 04 Aug 2006
Posts: 2846

PostPosted: Thu Dec 13, 2007 2:41 pm    Post subject: Reply with quote

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...
_________________
NML_375, idling at #eggdrop@IrcNET
Back to top
View user's profile Send private message
_mAyDaY_
Voice


Joined: 12 Dec 2007
Posts: 4

PostPosted: Thu Dec 13, 2007 3:18 pm    Post subject: Reply with quote

hey nml375, thnx for responding again!

nml375 wrote:
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]
......

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

Quote:
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
Back to top
View user's profile Send private message
nml375
Revered One


Joined: 04 Aug 2006
Posts: 2846

PostPosted: Thu Dec 13, 2007 3:27 pm    Post subject: Reply with quote

_mAyDaY_ wrote:
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".

_mAyDaY_ wrote:
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.....]
}

_________________
NML_375, idling at #eggdrop@IrcNET
Back to top
View user's profile Send private message
_mAyDaY_
Voice


Joined: 12 Dec 2007
Posts: 4

PostPosted: Thu Dec 13, 2007 4:07 pm    Post subject: Reply with quote

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

thnx for helping, i'll post the results tomorrow

EDIT: worked! thnx nml375 for your help!
Back to top
View user's profile Send private message
metroid
Owner


Joined: 16 Jun 2004
Posts: 771

PostPosted: Sat Dec 15, 2007 11:23 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
nml375
Revered One


Joined: 04 Aug 2006
Posts: 2846

PostPosted: Sat Dec 15, 2007 12:44 pm    Post subject: Reply with quote

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.
_________________
NML_375, idling at #eggdrop@IrcNET
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    egghelp.org community Forum Index -> Modules & Programming All times are GMT - 4 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Forum hosting provided by Reverse.net

Powered by phpBB © 2001, 2005 phpBB Group
subGreen style by ktauber