Daniel-Constantin Mierla writes:

I just pushed a patch for it, can you test and see if all goes fine with your config?

cfg_rpc module error is now gone, but got another one:

Oct 1 08:37:19 char /usr/bin/sip-proxy[26419]: ERROR: <core> [db.c:419]: db_table_version(): invalid type (8) or nul (0) version columns for lcr_rule Oct 1 08:37:19 char /usr/bin/sip-proxy[26419]: ERROR: <core> [db.c:447]: db_check_table_version(): querying version for table lcr_rule Oct 1 08:37:19 char /usr/bin/sip-proxy[26419]: ERROR: lcr [lcr_mod.c:687]: mod_init(): error during table version check Oct 1 08:37:19 char /usr/bin/sip-proxy[26419]: ERROR: <core> [core/sr_module.c:890]: init_mod(): Error while initializing module lcr (/usr/lib/x86_64-linux-gnu/sip-proxy/modules/lcr.so)

Earlier master K from sept 17 starts OK, so I don't think there is anything wrong in the db.

-- Juha

Daniel-Constantin Mierla writes:

This was related to adding support for UNSIGNED types. So far we had only signed types in DB API, but latest MySQL version is inserting 0 without any error when the actual value is a signed negative value.

Should be fixed now in latest master, try with it and see if all fine now.

I built K again and got a new error:

Oct 1 09:55:37 char /usr/bin/sip-proxy[18235]: ERROR: lcr [lcr_mod.c:1047]: insert_gws(): lcr_gw id at row <0> is null or not int Oct 1 09:55:37 char /usr/bin/sip-proxy[18235]: CRITICAL: lcr [lcr_mod.c:759]: mod_init(): failed to reload lcr tables Oct 1 09:55:37 char /usr/bin/sip-proxy[18235]: ERROR: <core> [core/sr_module.c:890]: init_mod(): Error while initializing module lcr (/usr/lib/x86_64-linux-gnu/sip-proxy/modules/lcr.so) Oct 1 09:55:37 char /usr/bin/sip-proxy[18235]: INFO: <core> [core/sctp_core.c:53]: sctp_core_destroy(): SCTP API not initialized

This starts to get serious.

-- Juha

Just in case someone wants to go ahead and update the code for some modules, in addition for DB1_INT or DB1_BINGINT, the code has to handle the type DB1_UINT or DB1_UBINGINT if the database table column has UNSIGNED type. All types have to be taken in consideration, because only MySQL defines the UNSIGNED, others that were analyzed so far (e.g., postgres client lib) has only signed types.

Cheers, Daniel

On 01.10.18 09:19, Daniel-Constantin Mierla wrote:

It is the same case -- the database schema defined unsigned types of columns although internally only signed types were supported. This works fine as long as the value inserted in the database is a positive integer (not a negative integer).

Will review all database table definitions and update accordingly. Once is finished, I will post an update.

Cheers, Daniel

On 01.10.18 08:59, Juha Heinanen wrote:

...

Daniel-Constantin Mierla writes:

...

This was related to adding support for UNSIGNED types. So far we had only signed types in DB API, but latest MySQL version is inserting 0 without any error when the actual value is a signed negative value.

Should be fixed now in latest master, try with it and see if all fine now.

I built K again and got a new error:

Oct 1 09:55:37 char /usr/bin/sip-proxy[18235]: ERROR: lcr [lcr_mod.c:1047]: insert_gws(): lcr_gw id at row <0> is null or not int Oct 1 09:55:37 char /usr/bin/sip-proxy[18235]: CRITICAL: lcr [lcr_mod.c:759]: mod_init(): failed to reload lcr tables Oct 1 09:55:37 char /usr/bin/sip-proxy[18235]: ERROR: <core> [core/sr_module.c:890]: init_mod(): Error while initializing module lcr (/usr/lib/x86_64-linux-gnu/sip-proxy/modules/lcr.so) Oct 1 09:55:37 char /usr/bin/sip-proxy[18235]: INFO: <core> [core/sctp_core.c:53]: sctp_core_destroy(): SCTP API not initialized

This starts to get serious.

-- Juha

On 01.10.18 09:35, Juha Heinanen wrote:

Daniel-Constantin Mierla writes:

...

It is the same case -- the database schema defined unsigned types of columns although internally only signed types were supported. This works fine as long as the value inserted in the database is a positive integer (not a negative integer).

I don't have any negative values in any of the lcr tables.

That was the reason that all worked fine so far.

But as soon as a value goes negative, things get broken. For example, with the modules storing expire/lifetime (e.g., usrloc, dialog) being timestamp + interval, if interval is very long, then the value goes over mid of UNSIGNED, practically becoming negative when using DB1_INT to send to db insert command, and mysql will store 0.

This made it necessary to introduce unsigned types in DB API, and it seems some modules need to be updated to deal with it because their database schema used UNSIGNED when the code didn't actually supported that. So it is an issue existing for long time, but revealed recently as timestamp value got higher and going to be over max signed int. It was also discussed before the summer in the last IRC devel meeting.

This update with new database types DB1_UINT and DB1_UBIGINT started on Friday. I expected that only insert/updates with high values to be affected, but it seems that loading is also hit.

We will work further on it.

Cheers, Daniel

-- Juha

...

Will review all database table definitions and update accordingly. Once is finished, I will post an update.

Cheers, Daniel

On 01.10.18 08:59, Juha Heinanen wrote:

...

Daniel-Constantin Mierla writes:

...

This was related to adding support for UNSIGNED types. So far we had only signed types in DB API, but latest MySQL version is inserting 0 without any error when the actual value is a signed negative value.

Should be fixed now in latest master, try with it and see if all fine now.

I built K again and got a new error:

Oct 1 09:55:37 char /usr/bin/sip-proxy[18235]: ERROR: lcr [lcr_mod.c:1047]: insert_gws(): lcr_gw id at row <0> is null or not int Oct 1 09:55:37 char /usr/bin/sip-proxy[18235]: CRITICAL: lcr [lcr_mod.c:759]: mod_init(): failed to reload lcr tables Oct 1 09:55:37 char /usr/bin/sip-proxy[18235]: ERROR: <core> [core/sr_module.c:890]: init_mod(): Error while initializing module lcr (/usr/lib/x86_64-linux-gnu/sip-proxy/modules/lcr.so) Oct 1 09:55:37 char /usr/bin/sip-proxy[18235]: INFO: <core> [core/sctp_core.c:53]: sctp_core_destroy(): SCTP API not initialized

This starts to get serious.

-- Juha

-- Daniel-Constantin Mierla -- www.asipto.com www.twitter.com/miconda -- www.linkedin.com/in/miconda Kamailio World Conference -- www.kamailioworld.com Kamailio Advanced Training, Nov 12-14, 2018, in Berlin -- www.asipto.com

On 01.10.18 09:48, Juha Heinanen wrote:

Daniel-Constantin Mierla writes:

...

But as soon as a value goes negative, things get broken. For example, with the modules storing expire/lifetime (e.g., usrloc, dialog) being timestamp + interval, if interval is very long, then the value goes over mid of UNSIGNED, practically becoming negative when using DB1_INT to send to db insert command, and mysql will store 0.

I have large integer value 4294967295 in defunct column of lcr_gw table. Type of defunct column is unsigned int. I guess that is the one giving the trouble.

The problem with storing 0 instead of actual value is revealed when inserting via kamailio code. Now the issue is that UNSIGNED columns are set to type DB1_UINT and the code in some modules doesn't recognize the type.

Cheers, Daniel

Daniel-Constantin Mierla writes:

Given that it may take some time to propagate the new unsigned types, I added a paramter to db_mysql to enable/disable converting to these types. By default is off, so it should work as before last  Friday.

Can you give another try with latest master and see the results?

Now my K started OK. I haven't made much testing yet.

-- Juha

On 01.10.18 13:56, Juha Heinanen wrote:

Daniel-Constantin Mierla writes:

...

This update with new database types DB1_UINT and DB1_UBIGINT started on Friday. I expected that only insert/updates with high values to be affected, but it seems that loading is also hit.

So, for example, below DB1_INT should be changed to DB1_UNIT and VAL_INT to VAL_UNIT?

-- Juha

---

if(VAL_NULL(ROW_VALUES(row) + 11)) { defunct_until = 0; } else { if(VAL_TYPE(ROW_VALUES(row) + 11) != DB1_INT) { LM_ERR("lcr_gw defunct at row <%u> is not int\n", i); return 0; } defunct_until = (unsigned int)VAL_INT(ROW_VALUES(row) + 11); if(defunct_until > 4294967294UL) { LM_DBG("skipping disabled gw <%u>\n", gw_id); continue; } }

You have to change:

if(VAL_TYPE(ROW_VALUES(row) + 11) != DB1_INT) {

to something like:

if(VAL_TYPE(ROW_VALUES(row) + 11) != DB1_INT && VAL_TYPE(ROW_VALUES(row) + 11) != DB1_UINT) {

Getting the value with VAL_INT(ROW_VALUES(row) + 11) is still ok, because the value is stored in a union and signed/unsigned int uses the same 4 bytes.

The alternative is to remove UNSIGNED from the column definition. Alexander Dubovikov mentioned that there is a MySQl server setting for strict data type usage, that is turned on, but can be turned off via mysql server (or client) config.   More info on the issue: apparently, based on checking some of the DB variants libs, only MySQL does the difference with UNSIGNED. The others seem work with signed types only.

I also looked quickly at using flags instead of data type for unsigned (mysql does it like that), but that seems be more complex change in the DB API, because the column is specified as name only, so by changing that to a struct I expect that most of queries will be affected. Maybe pushing that to value type ... needs more analyzis...

Cheers, Daniel

On 01.10.18 17:50, Juha Heinanen wrote:

Daniel-Constantin Mierla writes:

...

You have to change:

if(VAL_TYPE(ROW_VALUES(row) + 11) != DB1_INT) {

to something like:

if(VAL_TYPE(ROW_VALUES(row) + 11) != DB1_INT && VAL_TYPE(ROW_VALUES(row) + 11) != DB1_UINT) {

Why do I need to test both DB1_INT and DB1_UINT, since the column cannot hold a negative integer value?

Because postgres, sqlite and other database servers via unixodbc do not use unsigned types.

If you look at postgres schema, all these columns are just INTEGER, see:

utils/kamctl/postgres/lcr-create.sql

So UNSIGNED is more or less mysql proprietary extension, hitting now back with the new strict checking of the values in the latest version. If you search for standard sql data types, there is no 'unsigned' type listed.

...

The alternative is to remove UNSIGNED from the column definition. Alexander Dubovikov mentioned that there is a MySQl server setting for strict data type usage, that is turned on, but can be turned off via mysql server (or client) config.

There is reason why the column in unsigned int. I want to be able store in that column positive int value 4294967295 (0xFFFFFFFF). If I change the column to int, my understanding is that is cannot anymore store this value.

You can store it, but it will be shown as a negative number (-1) when looking into the database via a query with an sql client.

Inside the C code you can cast to (unsigned int) and then it works fine. This is how lcr and other module work with the other DB server types, which do not have unsigned column type.

So, if we want to stick to sql standard, then we have to remove unsigned from the definition of sql columns. If we want to continue we the current db schema, the modules must handle both DB1_UINT (for mysql) and DB1_INT (for the others), casting internally to unsigned int.

Cheers, Daniel