Hi,
I have noticed that in some instances if you update a row in mysql via the mysql_db module and the actual row data does not change - affected_rows will return 0. This is the default behaviour for the mysql API as per - http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html
There is a flag (CLIENT_FOUND_ROWS) that can be used in the mysql_real_connect function that will cause affected_rows to return the number of rows that were "matched" - ie in the WHERE clause, as opposed to whether or not any data was changed.
If we don't it could be a problem for modules like usrloc where an update is done and if no row are "affected" and new row is added which would cause a duplicate.
I understand that updating a row with zero changes is not ideal and prob. not a typical use case but this IMO is irrelevant. I understand affected rows to mean the number of rows that were matched and not the number of rows that had data changed... - seems like a case of MySQL being too clever here ;)
Thoughts?
Cheers Jason
Interesting to say the least.
Thoughts?
My belief, which does not seem to be popular, is that the best practice utilizes stored procedures.
Many reasons why I like this including the following scenarios:
1) Need for a new column or structure in mysql will not result in the need of modifying Kamailio code.
Take for example Kamailio is updating a field with calls in progress per user in a company setting. Due to new employee controls, a table structure needs to change and fields are to be remapped and linked among several tables.
As far as Kamailio is concerned, this is irrelevant, since you can update the procedure instead of the sql statement.
2) Update rows
You can do the update statement within the procedure and return something like "fail" or "good" based on the successful run. Your kamailio result will simply need to check the result.
3) Speed
You can optimize the procedures as needed to take advantage of indexes, etc. without constantly changing the sql statement within kamailio.
4) One call, many actions
You can use one stored procedure to run many sql statements.
The above reasons are the most common scenarios where stored procedures have continually helped me.
Fred Posner The Palner Group, Inc. http://www.palner.com (web) +1-503-914-0999 (direct) +1-954-472-2896 (fax)
On 08/08/2014 08:00 AM, Jason Penton wrote:
Hi,
I have noticed that in some instances if you update a row in mysql via the mysql_db module and the actual row data does not change - affected_rows will return 0. This is the default behaviour for the mysql API as per - http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html
There is a flag (CLIENT_FOUND_ROWS) that can be used in the mysql_real_connect function that will cause affected_rows to return the number of rows that were "matched" - ie in the WHERE clause, as opposed to whether or not any data was changed.
If we don't it could be a problem for modules like usrloc where an update is done and if no row are "affected" and new row is added which would cause a duplicate.
I understand that updating a row with zero changes is not ideal and prob. not a typical use case but this IMO is irrelevant. I understand affected rows to mean the number of rows that were matched and not the number of rows that had data changed... - seems like a case of MySQL being too clever here ;)
Thoughts?
Cheers Jason
sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
On Friday 08 August 2014, Jason Penton wrote:
I have noticed that in some instances if you update a row in mysql via the mysql_db module and the actual row data does not change - affected_rows will return 0. This is the default behaviour for the mysql API as per - http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html
There is a flag (CLIENT_FOUND_ROWS) that can be used in the mysql_real_connect function that will cause affected_rows to return the number of rows that were "matched" - ie in the WHERE clause, as opposed to whether or not any data was changed.
If we don't it could be a problem for modules like usrloc where an update is done and if no row are "affected" and new row is added which would cause a duplicate.
If that happens, the table definition is wrong. It should have (a) unique key(s) to prevent double records. We'd better fix that.
Hi Alex,
I didn't check the table schemas for usrloc but I'm sure there may be other cases where the affected_rows function has been 'misunderstood'. In the code I picked this bug up (ims_pcscf_usrloc), I did exactly that, change the schema. Just wanted to discuss in case it was decided to change the connect flags to mitigate any future probs.
Also, if you merely change the the schema, some code would think the update had "failed" and do some other adverse failure code so not sure that would be an ideal final fix...
Cheers Jason
On Fri, Aug 8, 2014 at 4:06 PM, Alex Hermann alex@speakup.nl wrote:
On Friday 08 August 2014, Jason Penton wrote:
I have noticed that in some instances if you update a row in mysql via
the
mysql_db module and the actual row data does not change - affected_rows will return 0. This is the default behaviour for the mysql API as per - http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html
There is a flag (CLIENT_FOUND_ROWS) that can be used in the mysql_real_connect function that will cause affected_rows to return the number of rows that were "matched" - ie in the WHERE clause, as opposed
to
whether or not any data was changed.
If we don't it could be a problem for modules like usrloc where an update is done and if no row are "affected" and new row is added which would
cause
a duplicate.
If that happens, the table definition is wrong. It should have (a) unique key(s) to prevent double records. We'd better fix that.
-- Greetings,
Alex Hermann
sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
Hello,
maybe this can be introduced as a module parameter option for db_mysql. It will allow fixing quickly via config if similar situation arises. If it proves to become a common case, then it can turned on by default.
Cheers, Daniel
On 08/08/14 16:12, Jason Penton wrote:
Hi Alex,
I didn't check the table schemas for usrloc but I'm sure there may be other cases where the affected_rows function has been 'misunderstood'. In the code I picked this bug up (ims_pcscf_usrloc), I did exactly that, change the schema. Just wanted to discuss in case it was decided to change the connect flags to mitigate any future probs.
Also, if you merely change the the schema, some code would think the update had "failed" and do some other adverse failure code so not sure that would be an ideal final fix...
Cheers Jason
On Fri, Aug 8, 2014 at 4:06 PM, Alex Hermann <alex@speakup.nl mailto:alex@speakup.nl> wrote:
On Friday 08 August 2014, Jason Penton wrote: > I have noticed that in some instances if you update a row in mysql via the > mysql_db module and the actual row data does not change - affected_rows > will return 0. This is the default behaviour for the mysql API as per - > http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html > > There is a flag (CLIENT_FOUND_ROWS) that can be used in the > mysql_real_connect function that will cause affected_rows to return the > number of rows that were "matched" - ie in the WHERE clause, as opposed to > whether or not any data was changed. > > If we don't it could be a problem for modules like usrloc where an update > is done and if no row are "affected" and new row is added which would cause > a duplicate. If that happens, the table definition is wrong. It should have (a) unique key(s) to prevent double records. We'd better fix that. -- Greetings, Alex Hermann _______________________________________________ sr-dev mailing list sr-dev@lists.sip-router.org <mailto:sr-dev@lists.sip-router.org> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
ok agreed, will add it in.
On Wed, Aug 13, 2014 at 11:58 AM, Daniel-Constantin Mierla < miconda@gmail.com> wrote:
Hello,
maybe this can be introduced as a module parameter option for db_mysql. It will allow fixing quickly via config if similar situation arises. If it proves to become a common case, then it can turned on by default.
Cheers, Daniel
On 08/08/14 16:12, Jason Penton wrote:
Hi Alex,
I didn't check the table schemas for usrloc but I'm sure there may be other cases where the affected_rows function has been 'misunderstood'. In the code I picked this bug up (ims_pcscf_usrloc), I did exactly that, change the schema. Just wanted to discuss in case it was decided to change the connect flags to mitigate any future probs.
Also, if you merely change the the schema, some code would think the update had "failed" and do some other adverse failure code so not sure that would be an ideal final fix...
Cheers Jason
On Fri, Aug 8, 2014 at 4:06 PM, Alex Hermann alex@speakup.nl wrote:
On Friday 08 August 2014, Jason Penton wrote:
I have noticed that in some instances if you update a row in mysql via
the
mysql_db module and the actual row data does not change - affected_rows will return 0. This is the default behaviour for the mysql API as per - http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html
There is a flag (CLIENT_FOUND_ROWS) that can be used in the mysql_real_connect function that will cause affected_rows to return the number of rows that were "matched" - ie in the WHERE clause, as opposed
to
whether or not any data was changed.
If we don't it could be a problem for modules like usrloc where an
update
is done and if no row are "affected" and new row is added which would
cause
a duplicate.
If that happens, the table definition is wrong. It should have (a) unique key(s) to prevent double records. We'd better fix that.
-- Greetings,
Alex Hermann
sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
sr-dev mailing listsr-dev@lists.sip-router.orghttp://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
-- Daniel-Constantin Mierlahttp://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda Next Kamailio Advanced Trainings 2014 - http://www.asipto.com Sep 22-25, Berlin, Germany ::: Oct 15-17, San Francisco, USA
sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
-
Alex Hermann -
Daniel-Constantin Mierla -
Fred Posner -
Jason Penton