[GET] Daily.json return HTTP 500

[ddhuy]

After client logs in to RC via web Admin UI, client sends GET Daily.json to server and receives HTTP 500

[GET]: http://admin-ui:8080/restcomm/2012-04-24/Accounts/ACae6e420f425248d6a26948c17a9e2acf/Usage/Records/Daily.json?EndDate=2017-12-05&StartDate=2017-11-05 [GET]: http://admin-ui:8080/restcomm/2012-04-24/Accounts/ACae6e420f425248d6a26948c17a9e2acf/Usage/Records/Daily.json?Category=SMS&EndDate=2017-12-05&StartDate=2017-11-05

server.log

[gsaslis]

Hi @ddhuy,

Thanks for reporting this!

Can you please share which restcomm version this is for and how you've deployed?

From a quick look, this looks like a valid bug... are you thinking of contributing a PR?

[ddhuy]

Hi @gsaslis , The restcomm version is 8.3.0. I built and deployed it following the guidelines here How to build Restcomm-Connect from source & Starting Restcomm-Connect. My MySQL version is mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper

Yes, I would like to contribute the fix. Now I am investigating this and will provide the PR soon.

[ddhuy]

The sql mode ONLY_FULL_GROUP_BY was added in MySQL version 5.7.5 and later. Removing it will help solve the issue. IMO we should take a note in installation documents about this.

[gsaslis]

@ddhuy thanks for the feedback on this.

So, if I understand the problem correctly, you were running with Mysql 5.7.20 and you were having those errors and then you changed the only_full_group_by mode and those errors in your server.log went away?

Btw, did you change the sql_mode on your server or on the client ?

(Hint: Use GitHubGist for sharing log files, so it becomes easier to reference particular log lines and view online ;) )

[ddhuy]

@gsaslis Yes, your understanding is correctly.

In getDailyCalls GROUP BY clause, we group the records by 4 fields: start_date, end_date, account_sid, api_version while in the SELECT list we select more fields than that so MySQL returns us an exception about only_full_group_by. At my side I fixed the problem by changing the sql mode in my restcomm server.

In MySQL manual it said only_full_group_by is turned on by default in MySQL version 5.7.5

[gsaslis]

@ddhuy could you please share a link with the code sample you mentioned?

@leftyb as far as I know, we're running a MySQL version greater than 5.7.5 in production, without facing this problem. Any idea what could be different?

[ddhuy]

Hi @gsaslis and @leftyb , here is the piece of code I mentioned. Please take a look at getDailyCalls

[gsaslis]

@ddhuy thanks for that.

This seems like a weird case, because I would expect since we are also running a MySQL > 5.7.5 to be also running into this.

I would really like to try to reproduce this locally. Can you please help me with the exact steps to follow so that I can reproduce this on my machine? (both in terms of installation, but also in terms of actions in Restcomm that will result in the problem)?

Thanks in advance!

[ddhuy]

Hi @gsaslis Sorry for late response. I have tried again with new fresh installation of Linux OS, MySql & Restcomm. But seem the problem still happens to me.

Following is my steps: Step 1: Follow guideline here to get & compile Restcomm (How to build Restcomm from Source)

Step 2: Follow the guideline here to setup Restcomm With MySQL (How to get started with Restcomm-Connect and MySql)

Step 3: Log-in with default admin credential. The problem happens

Note: Linux distro is Ubuntu 16.04.3 (4.13.0-32-generic) MySQL Version is mysql Ver 14.14 Distrib 5.7.21 JDK version 1.7.0_80

[peteroyle]

Hi @gsaslis. One can still get this running MySQL > 5.7.5 if they are using the defaults for sql_mode, or if they have specifically enabled sql_mode=ONLY_FULL_GROUP_BY.

We are also experiencing this and we too managed to work around it just now by reconfiguring sql_modes on our MySQL server. The change that we made was to specify sql_mode like this in my.conf on the MySQL server: sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

That is, it contains every default except for ONLY_FULL_GROUP_BY. Perhaps you're not getting this in your production environment because you have configured MySQL similarly?

[gsaslis]

Thanks for confirming @peteroyle!

@leftyb can you confirm about this? it sounds like a valid issue / suggestion to fix.