greet on join (phpBB3)

Fire-Fox · Post by **Fire-Fox** » Mon Oct 12, 2009 5:55 pm

I have a script, a mate made a while back, if i post that and the batabase struckture. could anyone of you guys then help me to get it working.

So when a user joins irc, the bot should give a greet with username and Rang title....

When i load the script. all okay

when i rejoin the channel "/hop" the partyline says "Tcl error [join:#phpBB3-Forum]: mysqlquery/db server: MySQL server has gone away"
- Why

joinmsg.tcl

Code: Select all

package require mysqltcl
setudef flag joinmsg 

bind join - * join:CHAN
bind rejn - * join:CHAN

##################
### Mysql path ###
##################
# SQL info
set sql(host) "xxxx"
set sql(user) "xxxx"
set sql(pass) "xxxx"
set sql(db) "xxxx"
set sql(port) "xxxx"

##################
### Mysql path ###
##################
if {![info exists db_handle] } {
set db_handle [mysqlconnect -host $sql(host) -user $sql(user) -password $sql(pass) -db $sql(db) -port $sql(port)]
}

###########################################
### Greeting MSG When User Join channel ###
###########################################
proc bt.join.rank_title { chan nick rank_title} {
if {[channel get $chan joinmsg]} {
switch $rank_title {

   ### The number is the number of the class on the site
   ### "return 0" means no msg for that class
   "1"   {putserv "PRIVMSG $chan :7Welcome To 4\:\:\:2 $username 4\:\:\:9 14 $rank_title"}
   
   "default"   {set rank_title "Not Found" }
  }
 }
} 
#################
### Join Proc ###
#################
proc join:CHAN {nick host hand chan} {
global botnick db_handle

  if {$nick != $botnick} {
 
   #    if {[mysqlping $db_handle] != 1} {
   #        putserv "notice $nick : The connection to the mysql server has been lost."
   #          return 0
   #    }
       
       set sqlnick "SELECT user_id FROM phpbb_users WHERE username='[mysqlescape nick]'"
       set resultusername [mysqlquery $db_handle $sqlnick]
             
       set nicki 0
       while {[set record [mysqlnext $resultusername]] != ""} {
         set user_id [lindex $record 0]
         incr nicki
       }
             
       if { $nick == 0 } { return 0 }
             
       if { $user_id == "" } { return 0 }
             
       set sql "SELECT rank_title FROM phpbb_ranks WHERE user_id='[mysqlescape $user_id]'"
       
       set result [mysqlquery $db_handle $sql]
             
       set i 0
       while {[set record [mysqlnext $result]] != ""} {
                   
         set nick [lindex $record 0];
         set rank_title [lindex $record 1];
       }
       
       set sql "SELECT rank_title FROM phpbb_ranks WHERE id='[mysqlescape $user_id]'"
       set result [mysqlquery $db_handle $sql]
             
       set i 0
       while {[set record [mysqlnext $result]] != ""} {
                   
         set nick [lindex $record 0];
         set rank_title [lindex $record 1];
       }
       mysqlendquery $result
       
       bt.join.rank_title $chan $nick $rank_title

    }
  }
  putlog "Joinmsg"

Userdatabase

Code: Select all

# Table: 'phpbb_users'
CREATE TABLE phpbb_users (
	user_id mediumint(8) UNSIGNED NOT NULL auto_increment,
	user_type tinyint(2) DEFAULT '0' NOT NULL,
	group_id mediumint(8) UNSIGNED DEFAULT '3' NOT NULL,
	user_permissions mediumtext NOT NULL,
	user_perm_from mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
	user_ip varchar(40) DEFAULT '' NOT NULL,
	user_regdate int(11) UNSIGNED DEFAULT '0' NOT NULL,
	username varchar(255) DEFAULT '' NOT NULL,
	username_clean varchar(255) DEFAULT '' NOT NULL,
	user_password varchar(40) DEFAULT '' NOT NULL,
	user_passchg int(11) UNSIGNED DEFAULT '0' NOT NULL,
	user_pass_convert tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
	user_email varchar(100) DEFAULT '' NOT NULL,
	user_email_hash bigint(20) DEFAULT '0' NOT NULL,
	user_birthday varchar(10) DEFAULT '' NOT NULL,
	user_lastvisit int(11) UNSIGNED DEFAULT '0' NOT NULL,
	user_lastmark int(11) UNSIGNED DEFAULT '0' NOT NULL,
	user_lastpost_time int(11) UNSIGNED DEFAULT '0' NOT NULL,
	user_lastpage varchar(200) DEFAULT '' NOT NULL,
	user_last_confirm_key varchar(10) DEFAULT '' NOT NULL,
	user_last_search int(11) UNSIGNED DEFAULT '0' NOT NULL,
	user_warnings tinyint(4) DEFAULT '0' NOT NULL,
	user_last_warning int(11) UNSIGNED DEFAULT '0' NOT NULL,
	user_login_attempts tinyint(4) DEFAULT '0' NOT NULL,
	user_inactive_reason tinyint(2) DEFAULT '0' NOT NULL,
	user_inactive_time int(11) UNSIGNED DEFAULT '0' NOT NULL,
	user_posts mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
	user_lang varchar(30) DEFAULT '' NOT NULL,
	user_timezone decimal(5,2) DEFAULT '0' NOT NULL,
	user_dst tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
	user_dateformat varchar(30) DEFAULT 'd M Y H:i' NOT NULL,
	user_style mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
	user_rank mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
	user_colour varchar(6) DEFAULT '' NOT NULL,
	user_new_privmsg int(4) DEFAULT '0' NOT NULL,
	user_unread_privmsg int(4) DEFAULT '0' NOT NULL,
	user_last_privmsg int(11) UNSIGNED DEFAULT '0' NOT NULL,
	user_message_rules tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
	user_full_folder int(11) DEFAULT '-3' NOT NULL,
	user_emailtime int(11) UNSIGNED DEFAULT '0' NOT NULL,
	user_topic_show_days smallint(4) UNSIGNED DEFAULT '0' NOT NULL,
	user_topic_sortby_type varchar(1) DEFAULT 't' NOT NULL,
	user_topic_sortby_dir varchar(1) DEFAULT 'd' NOT NULL,
	user_post_show_days smallint(4) UNSIGNED DEFAULT '0' NOT NULL,
	user_post_sortby_type varchar(1) DEFAULT 't' NOT NULL,
	user_post_sortby_dir varchar(1) DEFAULT 'a' NOT NULL,
	user_notify tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
	user_notify_pm tinyint(1) UNSIGNED DEFAULT '1' NOT NULL,
	user_notify_type tinyint(4) DEFAULT '0' NOT NULL,
	user_allow_pm tinyint(1) UNSIGNED DEFAULT '1' NOT NULL,
	user_allow_viewonline tinyint(1) UNSIGNED DEFAULT '1' NOT NULL,
	user_allow_viewemail tinyint(1) UNSIGNED DEFAULT '1' NOT NULL,
	user_allow_massemail tinyint(1) UNSIGNED DEFAULT '1' NOT NULL,
	user_options int(11) UNSIGNED DEFAULT '895' NOT NULL,
	user_avatar varchar(255) DEFAULT '' NOT NULL,
	user_avatar_type tinyint(2) DEFAULT '0' NOT NULL,
	user_avatar_width smallint(4) UNSIGNED DEFAULT '0' NOT NULL,
	user_avatar_height smallint(4) UNSIGNED DEFAULT '0' NOT NULL,
	user_sig mediumtext NOT NULL,
	user_sig_bbcode_uid varchar(8) DEFAULT '' NOT NULL,
	user_sig_bbcode_bitfield varchar(255) DEFAULT '' NOT NULL,
	user_from varchar(100) DEFAULT '' NOT NULL,
	user_icq varchar(15) DEFAULT '' NOT NULL,
	user_aim varchar(255) DEFAULT '' NOT NULL,
	user_yim varchar(255) DEFAULT '' NOT NULL,
	user_msnm varchar(255) DEFAULT '' NOT NULL,
	user_jabber varchar(255) DEFAULT '' NOT NULL,
	user_website varchar(200) DEFAULT '' NOT NULL,
	user_occ text NOT NULL,
	user_interests text NOT NULL,
	user_actkey varchar(32) DEFAULT '' NOT NULL,
	user_newpasswd varchar(40) DEFAULT '' NOT NULL,
	user_form_salt varchar(32) DEFAULT '' NOT NULL,
	PRIMARY KEY (user_id),
	KEY user_birthday (user_birthday),
	KEY user_email_hash (user_email_hash),
	KEY user_type (user_type),
	UNIQUE username_clean (username_clean)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

Fire-Fox · Post by **Fire-Fox** » Wed Oct 14, 2009 7:33 am

No one that will help me?!

Hatas · Post by **Hatas** » Wed Oct 14, 2009 9:17 am

hi. i think problem is in timiout db connection in long time idle. i have same problem but i have time for details

i think you have creat procedure ant check it on actions
proc db_status {}{
if {![info exists db_handle] } {
set db_handle [mysqlconnect -host $sql(host) -user $sql(user) -password $sql(pass) -db $sql(db) -port $sql(port)]
}
}

and in procedures source put on top : db_status;

somethinks like that

Fire-Fox · Post by **Fire-Fox** » Thu Oct 15, 2009 10:06 am

Now the problem with the mysql connect, now i just need help to insert the table the right places in the script