Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

SipTrace with MySQL #83

Closed
hogbinj opened this issue May 11, 2020 · 2 comments
Closed

SipTrace with MySQL #83

hogbinj opened this issue May 11, 2020 · 2 comments
Assignees
@liviuchircu
Labels
bug fixed

Comments

@hogbinj
Copy link

hogbinj commented May 11, 2020

The standard empty query returns no results.

Looking at the database
SELECT DISTINCT callid FROM sip_trace WHERE status='' AND direction='in' ORDER BY id DESC;

gets

ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'opensips.sip_trace.id' which is not in SELECT list; this is incompatible with DISTINCT

I fixed it in opensips/web/tools/system/siptrace/template/tracer.main.php

where line 140 needed to change id to callid to match the postgres call below it,
@liviuchircu liviuchircu self-assigned this May 11, 2020
@liviuchircu
Copy link
Member

liviuchircu commented May 11, 2020 •
edited
Loading

@hogbinj can you please do:

mysql> SHOW VARIABLES LIKE '%sql_mode%';

and post the output? I suspect you are using the ONLY_FULL_GROUP_BY mode, which does not allow such queries.

The id -> callid fix is not correct, because it changes the behavior of the tool: instead of displaying calls in "latest calls first" fashion, it will randomly display them in "reverse-alphabetical order of Call-ID"...

@liviuchircu
Copy link
Member

@hogbinj I managed to find a fix that will work with any SQL mode. Cheers!

liviuchircu added a commit that referenced this issue May 12, 2020
@liviuchircu
SIP Trace: Improve "group by Call-ID" query
795bd4c 
    * be compatible with any SQL mode.  This fixes:

	SELECT DISTINCT callid FROM sip_trace WHERE status='' AND
	    direction='in' ORDER BY id DESC;

	ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in
	    SELECT list, references column 'opensips.sip_trace.id' which
	    is not in SELECT list; this is incompatible with DISTINCT

    * be consistent across different backends (e.g. don't sort the
	results differently with MySQL vs. Postgres)

Fixes #83

(cherry picked from commit e2c4246)
liviuchircu added a commit that referenced this issue May 12, 2020
@liviuchircu
SIP Trace: Improve "group by Call-ID" query
9414f3a 
    * be compatible with any SQL mode.  This fixes:

	SELECT DISTINCT callid FROM sip_trace WHERE status='' AND
	    direction='in' ORDER BY id DESC;

	ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in
	    SELECT list, references column 'opensips.sip_trace.id' which
	    is not in SELECT list; this is incompatible with DISTINCT

    * be consistent across different backends (e.g. don't sort the
	results differently with MySQL vs. Postgres)

Fixes #83

(cherry picked from commit e2c4246)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@liviuchircu liviuchircu

Labels
bug fixed
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@liviuchircu @hogbinj