Problem with UPDATE realtime when user logout


#1

Hello,

the 1.2.1 version of asterisk have any erros when a user has disconected.


when a user logout, the asterisk make a UPDATE with a trash in the fields, ipaddr and port, in some time, when this

trash have a “’” character, have a SQL error to…

look the logfile:

maquina 1
Jan 6 17:20:48 DEBUG[5423] res_config_mysql.c: MySQL RealTime: Update SQL: UPDATE sip_buddies SET ipaddr = ‘200.225.’, port = 'ÐWÙ·¬WÙ·qÌ ', regseconds = ‘0’, username = ‘12010000’, fullcontact = ‘’ WHERE name = ‘12010000’

maquina 2
Jan 6 17:21:46 DEBUG[4842] res_config_mysql.c: MySQL RealTime: Query: UPDATE sip_buddies SET ipaddr = ‘200.225.’, port = ‘Ðå·¬å·q¬’’, regseconds = ‘0’, username = ‘12010000’, fullcontact = ‘’ WHERE name = ‘12010000’
Jan 6 17:21:46 DEBUG[4842] res_config_mysql.c: MySQL RealTime: Query Failed because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘0’, username = ‘12010000’, fullcontact = ‘’ WHERE name = ‘12010000’’ at line 1

to correct this problem, we used this patch:

— channels/chan_sip.c-orig 2006-01-09 09:35:32.853568392 -0200
+++ channels/chan_sip.c 2006-01-09 09:35:46.974421696 -0200
@@ -1588,16 +1588,15 @@
{
char port[10];
char ipaddr[20];

  •   char regseconds[20] = "0";
    
  •   time_t nowtime;
    
  •  char regseconds[20];
    
  •   time(&nowtime);
    
  •   nowtime += expirey;
    
  •   snprintf(regseconds, sizeof(regseconds), "%d", (int)nowtime);   /* Expiration time */
    
  •   ast_inet_ntoa(ipaddr, sizeof(ipaddr), sin->sin_addr);
    
  •   snprintf(port, sizeof(port), "%d", ntohs(sin->sin_port));
    
  •   if (expirey) {  /* Registration */
    
  •           time_t nowtime;
    
  •           time(&nowtime);
    
  •           nowtime += expirey;
    
  •           snprintf(regseconds, sizeof(regseconds), "%ld", nowtime);       /* Expiration time */
    
  •           ast_inet_ntoa(ipaddr, sizeof(ipaddr), sin->sin_addr);
    
  •           snprintf(port, sizeof(port), "%d", ntohs(sin->sin_port));
    
  •   }
      if (fullcontact)
              ast_update_realtime("sippeers", "name", peername, "ipaddr", ipaddr, "port", port, "regseconds", regseconds, "username", username, "fullcontact", fullcontact, NULL);
      else
    

here have a full log file for analisys…

[root@SS1 asterisk]# cat messages
Jan 6 17:20:32 DEBUG[5423] chan_sip.c: = No match Their Call ID: 6b31d5175c3d7713 Their Tag 35770851 Our tag: as126d0c82
Jan 6 17:20:32 DEBUG[5423] chan_sip.c: Allocating new SIP dialog for 554b34316b0a467f - REGISTER (No RTP)
Jan 6 17:20:32 DEBUG[5423] chan_sip.c: **** Received REGISTER (2) - Command in SIP REGISTER
Jan 6 17:20:32 DEBUG[5423] chan_sip.c: = Found Their Call ID: 554b34316b0a467f Their Tag 6e5fe955 Our tag: as2d9810c5
Jan 6 17:20:32 DEBUG[5423] chan_sip.c: **** Received REGISTER (2) - Command in SIP REGISTER
Jan 6 17:20:32 VERBOSE[5423] logger.c: – Registered SIP ‘12010000’ at xxx.xxx.xxx.xxx port 7157 expires 3600
Jan 6 17:20:32 VERBOSE[5423] logger.c: – Saved useragent “eyeBeam release 3004t stamp 16741” for peer 12010000
Jan 6 17:20:32 DEBUG[5423] res_config_mysql.c: MySQL RealTime: Update SQL: UPDATE sip_buddies SET ipaddr = ‘xxx.xxx.xxx.xxx’, port = ‘7157’, regseconds = ‘1136578832’, username = ‘12010000’, fullcontact = ‘sip:12010000@10.32.9.127:7157’ WHERE name = ‘12010000’
Jan 6 17:20:32 DEBUG[5423] res_config_mysql.c: MySQL RealTime: Everything is fine.
Jan 6 17:20:32 DEBUG[5423] res_config_mysql.c: MySQL RealTime: Updated 1 rows on table: sip_buddies
Jan 6 17:20:32 DEBUG[5391] chan_sip.c: Checking device state for peer 12010000
Jan 6 17:20:32 DEBUG[5391] devicestate.c: Changing state for SIP/12010000 - state 1 (Not in use)
Jan 6 17:20:32 DEBUG[5588] app_queue.c: Device ‘SIP/12010000’ changed to state ‘1’ (Not in use)
Jan 6 17:20:36 DEBUG[5423] chan_sip.c: Allocating new SIP dialog for (No Call-ID) - NOTIFY (No RTP)
Jan 6 17:20:36 DEBUG[5423] chan_sip.c: = Found Their Call ID: 359a7206162502ba4b1ca0e930ba8baf@xxx.xxx.xxx.xxx Their Tag Our tag: as23503095
Jan 6 17:20:36 DEBUG[5423] chan_sip.c: Stopping retransmission on '359a7206162502ba4b1ca0e930ba8baf@xxx.xxx.xxx.xxx’ of Request 102: Match Found
Jan 6 17:20:38 DEBUG[5423] chan_sip.c: Auto destroying call '6b31d5175c3d7713’
Jan 6 17:20:47 DEBUG[5423] chan_sip.c: Auto destroying call '554b34316b0a467f’
Jan 6 17:20:48 DEBUG[5423] chan_sip.c: Allocating new SIP dialog for 554b34316b0a467f - REGISTER (No RTP)
Jan 6 17:20:48 DEBUG[5423] chan_sip.c: **** Received REGISTER (2) - Command in SIP REGISTER
Jan 6 17:20:48 DEBUG[5423] chan_sip.c: = Found Their Call ID: 554b34316b0a467f Their Tag 6e5fe955 Our tag: as3c49c8ab
Jan 6 17:20:48 DEBUG[5423] chan_sip.c: **** Received REGISTER (2) - Command in SIP REGISTER
Jan 6 17:20:48 VERBOSE[5423] logger.c: – Unregistered SIP '12010000’
Jan 6 17:20:48 DEBUG[5423] res_config_mysql.c: MySQL RealTime: Update SQL: UPDATE sip_buddies SET ipaddr = ‘200.225.’, port = 'ÐWÙ·¬WÙ·qÌ ', regseconds = ‘0’, username = ‘12010000’, fullcontact = ‘’ WHERE name = '12010000’
Jan 6 17:20:48 DEBUG[5423] res_config_mysql.c: MySQL RealTime: Everything is fine.
Jan 6 17:20:48 DEBUG[5423] res_config_mysql.c: MySQL RealTime: Updated 1 rows on table: sip_buddies
Jan 6 17:20:48 DEBUG[5391] chan_sip.c: Checking device state for peer 12010000
Jan 6 17:20:48 DEBUG[5391] devicestate.c: Changing state for SIP/12010000 - state 5 (Unavailable)
Jan 6 17:20:48 DEBUG[5589] app_queue.c: Device ‘SIP/12010000’ changed to state ‘5’ (Unavailable)
Jan 6 17:20:58 DEBUG[5423] chan_sip.c: Allocating new SIP dialog for (No Call-ID) - NOTIFY (No RTP)
Jan 6 17:21:03 DEBUG[5423] chan_sip.c: Auto destroying call '554b34316b0a467f’
Jan 6 17:21:33 DEBUG[5423] db.c: Unable to find key ‘12010001’ in family 'SIP/Registry’
Jan 6 17:21:33 DEBUG[5391] chan_sip.c: Checking device state for peer 12010001
Jan 6 17:21:33 DEBUG[5391] res_config_mysql.c: MySQL RealTime: Retrieve SQL: SELECT * FROM sip_buddies WHERE name = '12010001’
Jan 6 17:21:33 DEBUG[5391] res_config_mysql.c: MySQL RealTime: Everything is fine.
Jan 6 17:21:33 DEBUG[5391] db.c: Unable to find key ‘12010001’ in family 'SIP/Registry’
Jan 6 17:21:33 DEBUG[5391] devicestate.c: Changing state for SIP/12010001 - state 5 (Unavailable)
Jan 6 17:21:33 DEBUG[5590] app_queue.c: Device ‘SIP/12010001’ changed to state ‘5’ (Unavailable)
Jan 6 17:21:34 DEBUG[5423] chan_sip.c: Allocating new SIP dialog for (No Call-ID) - NOTIFY (No RTP)

[root@SS2 asterisk]# cat messages
Jan 6 17:20:38 DEBUG[4842] db.c: Unable to find key ‘12010002’ in family 'SIP/Registry’
Jan 6 17:20:38 DEBUG[4837] chan_sip.c: Checking device state for peer 12010002
Jan 6 17:20:38 DEBUG[4837] res_config_mysql.c: MySQL RealTime: Retrieve SQL: SELECT * FROM sip_buddies WHERE name = '12010002’
Jan 6 17:20:38 DEBUG[4837] res_config_mysql.c: MySQL RealTime: Everything is fine.
Jan 6 17:20:38 DEBUG[4837] db.c: Unable to find key ‘12010002’ in family 'SIP/Registry’
Jan 6 17:20:38 DEBUG[4837] devicestate.c: Changing state for SIP/12010002 - state 5 (Unavailable)
Jan 6 17:20:38 DEBUG[4904] app_queue.c: Device ‘SIP/12010002’ changed to state ‘5’ (Unavailable)
Jan 6 17:20:39 DEBUG[4842] chan_sip.c: Allocating new SIP dialog for (No Call-ID) - NOTIFY (No RTP)
Jan 6 17:21:30 DEBUG[4842] chan_sip.c: Allocating new SIP dialog for 00224e5aa670b63b - REGISTER (No RTP)
Jan 6 17:21:30 DEBUG[4842] chan_sip.c: **** Received REGISTER (2) - Command in SIP REGISTER
Jan 6 17:21:30 DEBUG[4842] chan_sip.c: = Found Their Call ID: 00224e5aa670b63b Their Tag f01bd35d Our tag: as0c94e728
Jan 6 17:21:30 DEBUG[4842] chan_sip.c: **** Received REGISTER (2) - Command in SIP REGISTER
Jan 6 17:21:30 VERBOSE[4842] logger.c: – Registered SIP ‘12010000’ at xxx.xxx.xxx.xxx port 7157 expires 3600
Jan 6 17:21:30 VERBOSE[4842] logger.c: – Saved useragent “eyeBeam release 3004t stamp 16741” for peer 12010000
Jan 6 17:21:30 DEBUG[4842] res_config_mysql.c: MySQL RealTime: Update SQL: UPDATE sip_buddies SET ipaddr = ‘xxx.xxx.xxx.xxx’, port = ‘7157’, regseconds = ‘1136578890’, username = ‘12010000’, fullcontact = ‘sip:12010000@10.32.9.127:7157’ WHERE name = ‘12010000’
Jan 6 17:21:30 DEBUG[4842] res_config_mysql.c: MySQL RealTime: Everything is fine.
Jan 6 17:21:30 DEBUG[4842] res_config_mysql.c: MySQL RealTime: Updated 1 rows on table: sip_buddies
Jan 6 17:21:30 DEBUG[4837] chan_sip.c: Checking device state for peer 12010000
Jan 6 17:21:30 DEBUG[4837] devicestate.c: Changing state for SIP/12010000 - state 1 (Not in use)
Jan 6 17:21:30 DEBUG[4905] app_queue.c: Device ‘SIP/12010000’ changed to state ‘1’ (Not in use)
Jan 6 17:21:39 DEBUG[4842] chan_sip.c: Allocating new SIP dialog for (No Call-ID) - NOTIFY (No RTP)
Jan 6 17:21:39 DEBUG[4842] chan_sip.c: = Found Their Call ID: 7a5e79aa108d63625c1ec1e202b8bf28@xxx.xxx.xxx.xxx Their Tag Our tag: as5c26dffa
Jan 6 17:21:39 DEBUG[4842] chan_sip.c: Stopping retransmission on '7a5e79aa108d63625c1ec1e202b8bf28@xxx.xxx.xxx.xxx’ of Request 102: Match Found
Jan 6 17:21:45 DEBUG[4842] chan_sip.c: Auto destroying call '00224e5aa670b63b’
Jan 6 17:21:46 DEBUG[4842] chan_sip.c: Allocating new SIP dialog for 00224e5aa670b63b - REGISTER (No RTP)
Jan 6 17:21:46 DEBUG[4842] chan_sip.c: **** Received REGISTER (2) - Command in SIP REGISTER
Jan 6 17:21:46 DEBUG[4842] chan_sip.c: = Found Their Call ID: 00224e5aa670b63b Their Tag f01bd35d Our tag: as419a590f
Jan 6 17:21:46 DEBUG[4842] chan_sip.c: **** Received REGISTER (2) - Command in SIP REGISTER
Jan 6 17:21:46 VERBOSE[4842] logger.c: – Unregistered SIP ‘12010000’
Jan 6 17:21:46 DEBUG[4842] res_config_mysql.c: MySQL RealTime: Update SQL: UPDATE sip_buddies SET ipaddr = ‘200.225.’, port = ‘Ðå·¬å·q¬’’, regseconds = ‘0’, username = ‘12010000’, fullcontact = ‘’ WHERE name = ‘12010000’
Jan 6 17:21:46 DEBUG[4842] res_config_mysql.c: MySQL RealTime: Everything is fine.
Jan 6 17:21:46 WARNING[4842] res_config_mysql.c: MySQL RealTime: Failed to query database. Check debug for more info.
Jan 6 17:21:46 DEBUG[4842] res_config_mysql.c: MySQL RealTime: Query: UPDATE sip_buddies SET ipaddr = ‘200.225.’, port = ‘Ðå·¬å·q¬’’, regseconds = ‘0’, username = ‘12010000’, fullcontact = ‘’ WHERE name = ‘12010000’
Jan 6 17:21:46 DEBUG[4842] res_config_mysql.c: MySQL RealTime: Query Failed because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘0’, username = ‘12010000’, fullcontact = ‘’ WHERE name = ‘12010000’’ at line 1
Jan 6 17:21:46 DEBUG[4837] chan_sip.c: Checking device state for peer 12010000
Jan 6 17:21:46 DEBUG[4837] devicestate.c: Changing state for SIP/12010000 - state 5 (Unavailable)
Jan 6 17:21:46 DEBUG[4907] app_queue.c: Device ‘SIP/12010000’ changed to state ‘5’ (Unavailable)
Jan 6 17:21:50 DEBUG[4842] chan_sip.c: Allocating new SIP dialog for (No Call-ID) - NOTIFY (No RTP)
Jan 6 17:22:01 DEBUG[4842] chan_sip.c: Auto destroying call ‘00224e5aa670b63b’