Of coarse I found this out after upgrading my test box from mysql 5.0.19 to 5.0.22. The lcr module runs the following query...
SELECT gw.ip_addr, gw.port, gw.uri_scheme, gw.transport, gw.strip, gw.prefix FROM gw, lcr WHERE ''sip:12345678910@eastern.mynoc.net'' LIKE lcr.from_uri AND '15184782222' LIKE CONCAT(lcr.prefix, '%') AND lcr.grp_id = gw.grp_id ORDER BY CHAR_LENGTH(lcr.prefix), lcr.priority DESC, RAND()
which returns
ERROR 1064 (42000): 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 ':12345678910@eastern.mynoc.net>'' LIKE lcr.from_uri AND '15184782222' LIKE CONCA' at line 1
the correct query would be ...
SELECT gw.ip_addr, gw.port, gw.uri_scheme, gw.transport, gw.strip, gw.prefix FROM gw, lcr WHERE 'sip:12345678910@eastern.mynoc.net' LIKE lcr.from_uri AND '15184782222' LIKE CONCAT(lcr.prefix, '%') AND lcr.grp_id = gw.grp_id ORDER BY CHAR_LENGTH(lcr.prefix), lcr.priority DESC, RAND();
Notice that I removed the double single quotes around <sip: 12345678910@eastern.mynoc.net> which is the from uri. This appears to be broken in current cvs as well.
Brandon Price writes:
SELECT gw.ip_addr, gw.port, gw.uri_scheme, gw.transport, gw.strip, gw.prefix FROM gw, lcr WHERE ''sip:12345678910@eastern.mynoc.net'' LIKE lcr.from_uri AND '15184782222' LIKE CONCAT(lcr.prefix, '%') AND lcr.grp_id = gw.grp_id ORDER BY CHAR_LENGTH(lcr.prefix), lcr.priority DESC, RAND()
lcr module only adds a single single quote before and after from uri:
q_len = snprintf(query, MAX_QUERY_SIZE, "SELECT %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s FROM %.*s, %.*s WHERE '%.*s' LIKE %.*s.%.*s AND '%.*s' LIKE CONCAT(%.*s.%.*s, '%%') AND %.*s.%.*s = %.*s.%.*s ORDER BY CHAR_LENGTH(%.*s.%.*s), %.*s.%.*s DESC, RAND()",
-- juha
I am pulling that query right out of my mysql query log. This works if I downgrade to an earlier version of mysql. Please reply. again the query being run is...
SELECT gw.ip_addr, gw.port, gw.uri_scheme, gw.transport, gw.strip, gw.prefix FROM gw, lcr WHERE ''sip:15183024234@eastern.mynoc.net'' LIKE lcr.from_uri AND '13143212222' LIKE CONCAT(lcr.prefix, '%') AND lcr.grp_id = gw.grp_id ORDER BY CHAR_LENGTH(lcr.prefix), lcr.priority DESC, RAND()
Would it have anything to do with the fact that the table type is ndbcluster not myisam? I am willing to provide an account on that box, but that is definitely the query being run.
On Aug 4, 2006, at 6:43 AM, Juha Heinanen wrote:
Brandon Price writes:
SELECT gw.ip_addr, gw.port, gw.uri_scheme, gw.transport, gw.strip, gw.prefix FROM gw, lcr WHERE ''sip:12345678910@eastern.mynoc.net'' LIKE lcr.from_uri AND '15184782222' LIKE CONCAT(lcr.prefix, '%') AND lcr.grp_id = gw.grp_id ORDER BY CHAR_LENGTH(lcr.prefix), lcr.priority DESC, RAND()
lcr module only adds a single single quote before and after from uri:
q_len = snprintf(query, MAX_QUERY_SIZE, "SELECT %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s FROM %.*s, %.*s WHERE '%.*s' LIKE %.*s.%.*s AND '%.*s' LIKE CONCAT(%.*s.%.*s, '%%') AND %.*s.%.*s = %.*s.%.*s ORDER BY CHAR_LENGTH(%.*s.%.*s), %.*s.%.*s DESC, RAND()",
-- juha
Also I changed line 1062 from ... q_len = snprintf(query, MAX_QUERY_SIZE, "SELECT %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s FROM %.*s, %.*s WHERE '%.*s' LIKE %.*s.%.*s AND '%.*s' LIKE CONCAT(%.*s.%.*s, '%%') AND %.*s.%.*s = %.*s.%.*s ORDER BY CHAR_LENGTH(%.*s.%.*s), %.*s.%.*s DESC, RAND()", to.. q_len = snprintf(query, MAX_QUERY_SIZE, "SELECT %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s FROM %.*s, %.*s WHERE %.*s LIKE %.*s.%.*s AND '%.*s' LIKE CONCAT(%.*s.%.*s, '%%') AND %.*s.%.*s = %.*s.%.*s ORDER BY CHAR_LENGTH(%.*s.%.*s), %.*s.%.*s DESC, RAND()",
and it works. Notice I removed the ''s after WHERE.
On Aug 4, 2006, at 6:43 AM, Juha Heinanen wrote:
Brandon Price writes:
SELECT gw.ip_addr, gw.port, gw.uri_scheme, gw.transport, gw.strip, gw.prefix FROM gw, lcr WHERE ''sip:12345678910@eastern.mynoc.net'' LIKE lcr.from_uri AND '15184782222' LIKE CONCAT(lcr.prefix, '%') AND lcr.grp_id = gw.grp_id ORDER BY CHAR_LENGTH(lcr.prefix), lcr.priority DESC, RAND()
lcr module only adds a single single quote before and after from uri:
q_len = snprintf(query, MAX_QUERY_SIZE, "SELECT %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s FROM %.*s, %.*s WHERE '%.*s' LIKE %.*s.%.*s AND '%.*s' LIKE CONCAT(%.*s.%.*s, '%%') AND %.*s.%.*s = %.*s.%.*s ORDER BY CHAR_LENGTH(%.*s.%.*s), %.*s.%.*s DESC, RAND()",
-- juha
Brandon Price writes:
q_len = snprintf(query, MAX_QUERY_SIZE, "SELECT %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s FROM %.*s, %.*s WHERE %.*s LIKE %.*s.%.*s AND '%.*s' LIKE CONCAT(%.*s.%.*s, '%%') AND %.*s.%.*s = %.*s.%.*s ORDER BY CHAR_LENGTH(%.*s.%.*s), %.*s.%.*s DESC, RAND()",
and it works. Notice I removed the ''s after WHERE.
if there is a problem, it must be in mysql client library, not lcr module. on the other hand, i don't understand why you didn't need to remove the quotes also from arround AND '%.*s' LIKE.
-- juha
Maybe the ' is part of the substituted string? Brandon, just log the URI and watch in syslog if the URI is plain or enclosed with ''.
regards klaus
Juha Heinanen wrote:
Brandon Price writes:
q_len = snprintf(query, MAX_QUERY_SIZE, "SELECT %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s FROM %.*s, %.*s WHERE %.*s LIKE %.*s.%.*s AND '%.*s' LIKE CONCAT(%.*s.%.*s, '%%') AND %.*s.%.*s = %.*s.%.*s ORDER BY CHAR_LENGTH(%.*s.%.*s), %.*s.%.*s DESC, RAND()",
and it works. Notice I removed the ''s after WHERE.
if there is a problem, it must be in mysql client library, not lcr module. on the other hand, i don't understand why you didn't need to remove the quotes also from arround AND '%.*s' LIKE.
-- juha
Users mailing list Users@openser.org http://openser.org/cgi-bin/mailman/listinfo/users
Yes. I agree. Must be.
On Aug 5, 2006, at 3:45 PM, Juha Heinanen wrote:
Brandon Price writes:
q_len = snprintf(query, MAX_QUERY_SIZE, "SELECT %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s, %.*s.%.*s FROM %.*s, %.*s WHERE %.*s LIKE %.*s.%.*s AND '%.*s' LIKE CONCAT(%.*s.%.*s, '%%') AND %.*s.%.*s = %.*s.%.*s ORDER BY CHAR_LENGTH(%.*s.%.*s), %.*s.%.*s DESC, RAND()",
and it works. Notice I removed the ''s after WHERE.
if there is a problem, it must be in mysql client library, not lcr module. on the other hand, i don't understand why you didn't need to remove the quotes also from arround AND '%.*s' LIKE.
-- juha
Users mailing list Users@openser.org http://openser.org/cgi-bin/mailman/listinfo/users
-
Brandon Price -
Juha Heinanen -
Klaus Darilion