3 Aug
2006
3 Aug
'06
4:32 p.m.
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(a)eastern.mynoc.net> which is the from uri. This appears to
be broken in current cvs as well.
4 Aug
4 Aug
12:43 p.m.
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
10:08 p.m.
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
10:21 p.m.
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
5 Aug
5 Aug
9:45 p.m.
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
7 Aug
7 Aug
8:57 a.m.
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(a)openser.org
http://openser.org/cgi-bin/mailman/listinfo/users
9:57 p.m.
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(a)openser.org
http://openser.org/cgi-bin/mailman/listinfo/users
6487
days inactive
6491
days old
6 comments
3 participants
participants (3)
-
Brandon Price -
Juha Heinanen -
Klaus Darilion