[asterisk-bugs] [JIRA] (ASTERISK-26263) SQL error when using realtime and registering extension / inserting into ps_contacts

[Andreas Krüger (JIRA)]

    [ https://issues.asterisk.org/jira/browse/ASTERISK-26263?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=231928#comment-231928 ] 

Andreas Krüger commented on ASTERISK-26263:
-------------------------------------------

I agree with Jeppe. Problem is the variable holding the query used in the prepared statement. It's only allocating 255 chars, but as our table name is a bit long as it's server dependent, this query length exceeds the allocated variable.

Solution is to allocate more than 255 chars for the query. This bug has been introduced in 13.10 and newer versions, while it works fine in 13.9 and lower. Must be a result of the new res_odbc that has been written?

> SQL error when using realtime and registering extension / inserting into ps_contacts
> ------------------------------------------------------------------------------------
>
>                 Key: ASTERISK-26263
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-26263
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: Resources/res_config_odbc
>    Affects Versions: 13.10.0, 13.11.0
>         Environment: Ubuntu server
>            Reporter: Jeppe Ryskov Larsen
>            Assignee: Unassigned
>         Attachments: debug.log
>
>
> Hello.
> When upgrading from 13.9.1 to 13.10.0 or 13.11.0-rc1, we noticed an issue we havent seen before.
> We are using realtime with mysql.
> After the upgrade, when we tried registering a SIP phone (through WSS/WebRTC), we did not get any row inserted into ps_contacts. I turned on debugging in the CLI and noticed this:
> {code}
> INSERT INTO ps_contacts (id, qualify_timeout, reg_server, call_id, via_addr, outbound_proxy, expiration_time, path, endpoint, qualify_frequency, user_agent, authenticate_qualify, uri, via_port) VALUES (?, ?, ?, ?, ?, ?, ?,
> {code}
> The query it tries to execute gets cut off after in the value list, which caused the following error in the CLI
> {code}
> 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 '' at line 1
> {code}
> I can see in the CLI that all of the parameters were identified and set correctly:
> {code}
> [2016-08-02 23:27:53] DEBUG[3238]: res_config_odbc.c:129 custom_prepare: Parameter 1 ('id') = '10007-YRNU4HNN;@03e1e622887e8869da27281e003c3672'
> [2016-08-02 23:27:53] DEBUG[3238]: res_config_odbc.c:129 custom_prepare: Parameter 2 ('qualify_timeout') = '3.000000'
> [2016-08-02 23:27:53] DEBUG[3238]: res_config_odbc.c:129 custom_prepare: Parameter 3 ('reg_server') = 'odn1-voip-cluster02-asterisk03'
> [2016-08-02 23:27:53] DEBUG[3238]: res_config_odbc.c:129 custom_prepare: Parameter 4 ('call_id') = 'asqnfa0ebh0leu7nftrtd6'
> [2016-08-02 23:27:53] DEBUG[3238]: res_config_odbc.c:129 custom_prepare: Parameter 5 ('via_addr') = '192.0.2.245'
> [2016-08-02 23:27:53] DEBUG[3238]: res_config_odbc.c:129 custom_prepare: Parameter 6 ('outbound_proxy') = ''
> [2016-08-02 23:27:53] DEBUG[3238]: res_config_odbc.c:129 custom_prepare: Parameter 7 ('expiration_time') = '1470173873'
> [2016-08-02 23:27:53] DEBUG[3238]: res_config_odbc.c:129 custom_prepare: Parameter 8 ('path') = ''
> [2016-08-02 23:27:53] DEBUG[3238]: res_config_odbc.c:129 custom_prepare: Parameter 9 ('qualify_frequency') = '60'
> [2016-08-02 23:27:53] DEBUG[3238]: res_config_odbc.c:129 custom_prepare: Parameter 10 ('user_agent') = 'SIP.js/0.7.3'
> [2016-08-02 23:27:53] DEBUG[3238]: res_config_odbc.c:129 custom_prepare: Parameter 11 ('authenticate_qualify') = 'no'
> [2016-08-02 23:27:53] DEBUG[3238]: res_config_odbc.c:129 custom_prepare: Parameter 12 ('uri') = 'sip:dr0akpg6@[OMITTED]:56472;transport=WSS'
> [2016-08-02 23:27:53] DEBUG[3238]: res_config_odbc.c:129 custom_prepare: Parameter 13 ('via_port') = '0'
> {code}
> It seems really weird that the query would just get cut in half, but i tried putting the query together myself and executing it directly on the DB, and it gave me the exact same error, so i think this is what happens. If i finished the query manually and executed it, it inserted correctly. 



--
This message was sent by Atlassian JIRA
(v6.2#6252)