[Home]

Summary:ASTERISK-16577: update_odbc always reports that : Key field 'ipaddr' does not exist in table ... Update will fail
Reporter:Sergey Panov (spanov)Labels:
Date Opened:2010-08-16 15:04:31Date Closed:2011-01-05 15:38:42.000-0600
Priority:MinorRegression?No
Status:Closed/CompleteComponents:Resources/res_config_odbc
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:( 0) ast_sip.sql
( 1) cli.JPG
Description:I've set up reatime configuration for sippeers and sipregs via ODBC connected to Oracle 11g. There are a lot of warning (update_odbc always reports that : Key field 'ipaddr' does not exist in table ...  Update will fail) in the CLI while new SIP registration.
I assume that it's case sensitive issue since actually, the column ipaddr is updated correctly. By default Oracle return all columns in uppercase.

****** ADDITIONAL INFORMATION ******

I tried version 1.6.2.11 as well as 1.6.2.9 with the same results.
Comments:By: Sergey Panov (spanov) 2010-08-16 15:38:09

DDL for table creating was attached.
By: Tilghman Lesher (tilghman) 2010-08-16 18:55:56

Your assumption is incorrect, because the column comparison is already done in case-insensitive mode.  Perhaps your Oracle driver does not trim spaces from column names when returning results from SQLColumns()?  That would be one way that this could fail.
By: Tilghman Lesher (tilghman) 2010-08-17 10:04:45

If you set your verbose level to 10 or higher and reload res_odbc.so, you should see some lines that start with "Found" the next time the table is referenced.  Please paste those lines into a FILE and upload that file into the file upload area.
By: Sergey Panov (spanov) 2010-08-17 12:10:19

I've set up level 15 (asterisk -rvvvvvvvvvvvvvvv).
There is no such line.
By: Sergey Panov (spanov) 2010-08-17 12:57:41

I've put some logging to ast_odbc_find_column function. There is no invocation of strcasecmp function in it.
By: Tilghman Lesher (tilghman) 2010-08-17 12:58:02

If you set your verbose level to 10 or higher AND did a 'module reload res_odbc.so' and you still did not see those lines, then you are not using 1.6.2 (or higher).
By: Tilghman Lesher (tilghman) 2010-08-17 13:01:40

I don't know how you're missing it, but the use of strcasecmp is on line 544 in res_odbc.c.
By: Sergey Panov (spanov) 2010-08-17 13:31:40

I've attached screen shot of CLI. You can find asterisk version and output there.
By putting some logging to ast_odbc_find_column function I meant that I added the following line

struct odbc_cache_columns *ast_odbc_find_column(struct odbc_cache_tables *table, const char *colname
{
struct odbc_cache_columns *col;
AST_RWLIST_TRAVERSE(&table->columns, col, list) {
ast_log(LOG_WARNING, "Compare '%s' '%s'",col->name,colname);
if (strcasecmp(col->name, colname) == 0) {
return col;
}
....
<------>}


and I assumed that I saw warnings like Compare .....
But nothing happened. So that I assumed that AST_RWLIST_TRAVERSE can find no columns.
By: Tilghman Lesher (tilghman) 2010-08-17 15:34:35

Okay, so clearly SQLColumns was not able to query the columns in the database.  I suspect a permissions error.
By: Sergey Panov (spanov) 2010-08-18 00:11:47

No, I don't think so. I tried SYSTEM user who has full rights on the whole database, still the same WARNING. Please advice.
By: Tilghman Lesher (tilghman) 2010-08-18 02:38:34

I have no Oracle database here with which to test.  You are on your own on this one.
By: Sergey Panov (spanov) 2010-08-18 02:58:06

I can provide any necessary information if it help to resolve the issue. Feel free to ask me any additional questions/tests.
By: khoanv (khoanv) 2010-08-18 04:42:27

Hi Spanov, do you use Easysoft Oracle ODBC driver for connecting to database?
By: Sergey Panov (spanov) 2010-08-18 04:47:11

Hi,

No I don't. I use Oracle ODBC from Oracle 11.2.0.1 distributive (libsqora.so)
By: khoanv (khoanv) 2010-08-18 05:02:26

One more question: which odbc version and which linux distribution are you using?
By: Sergey Panov (spanov) 2010-08-18 05:16:49

unixODBC-2.2.12-198.17
OS: SUSE Linux Enterprise Server 11 x64.
Kernel: 2.6.32.12-0.7-default
By: Tilghman Lesher (tilghman) 2010-08-18 15:40:03

You may also be interested in an alternate ODBC driver, which in the past I've had far more luck to perform correctly:

http://home.fnal.gov/~dbox/oracle/odbc/
By: Birgit Arkesteijn (birgita) 2010-12-03 06:37:28.000-0600

I had the same problem.
The situation improved when I configured the tablename in upper case.

extconfig.conf:
iaxpeers => odbc,sfa_realtime,LOCK_IAX_BUDDIES
instead of
iaxpeers => odbc,sfa_realtime,lock_iax_buddies

I can now see debug:
Found  column with type 3 with len 11, octetlen 11, and numlen (0,10)
Found  column with type 12 with len 40, octetlen 40, and numlen (0,0)
Found  column with type 12 with len 10, octetlen 10, and numlen (0,0)
Found  column with type 12 with len 40, octetlen 40, and numlen (0,0)

However, the columnnames are empty. :-(
All the other information does correspond with my table definition:

CREATE TABLE realtime.LOCK_IAX_BUDDIES
(
 id                    NUMBER(11,0) PRIMARY KEY USING INDEX TABLESPACE realtime_index not null,
 name                  VARCHAR2(40) not null,
 type                  VARCHAR2(10) DEFAULT 'friend' not null,
 username              VARCHAR2(40),

Versions:
OS: openSUSE 11.3 "Teal", 2.6.34-12
unixODBC: 2.2.12-203.1
Oracle driver: 11.2

I realise it is probably not an Asterisk bug.
No idea if it is the unixODBC driver or Oracle.
Any suggestions on finding out perhaps?
By: Birgit Arkesteijn (birgita) 2010-12-06 09:00:26.000-0600

(Sorry for keeping updating this issue, that isn't actually an asterisk issue.)
I managed to fix it. I compiled & installed a new version of unixODBC.

Make sure the tablename is in CAPITALS in extconfig.conf.
Make sure when you build unixODBC the oracle drivers are build as well (I edited the DRVConfig/Makefile).
I got still some warnings at startup time, but I think that is a timing issue.

Versions:
OS: openSUSE 11.3 "Teal", 2.6.34-12
unixODBC: 2.3.0
Oracle install client: 11.2.0
Asterisk: SVN-branch-1.8-r296230M