Sql Error

[r3comp1le]

Fresh install and 1st attempt to hit Dashboard

Exception Value:    
(1055, "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'malspider.alert.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

Exception Location: /usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py in defaulterrorhandler, line 36

[jasheppa5]

I wasn't able to reproduce this with a fresh install of Ubuntu, but I think I know how to fix it. Based on the error message it has something to do with your mysql configuration and the GROUP BY query used to aggregate the alert count.

Before I push code, do you mind making a quick one line change?:

In /home/malspider/malspider/malspider_django/dashboard/functions/model_helper.py go to line 61 and replace the old sql statement with:

sql = """Select count(*),reason,org_id from alert GROUP BY reason, org_id""" ; I believe your more strict mysql setup is complaining about me not selecting the columns in the group by. If it works for you i'll push the code since this should work on less strict configurations as well.

Thanks, James

On Wed, Nov 30, 2016 at 2:33 PM, r3comp1le notifications@github.com wrote:

Fresh install and 1st attempt to hit Dashboard

Exception Value:
(1055, "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'malspider.alert.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

Exception Location: /usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py in defaulterrorhandler, line 36

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ciscocsirt/malspider/issues/16, or mute the thread https://github.com/notifications/unsubscribe-auth/AR0QELSgdeg1kq1hHGHpwyAnC96MS_75ks5rDc-TgaJpZM4LAluK .

[r3comp1le]

Thanks your recent commit fixed it!

[jasheppa5]

Hmm ok... So you didn't need to update the sql statement? I pushed a changed earlier today to speed up page performance and the error message seemed related.

-James

On Wed, Nov 30, 2016 at 3:24 PM, r3comp1le notifications@github.com wrote:

Thanks your recent commit fixed it!

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ciscocsirt/malspider/issues/16#issuecomment-263984831, or mute the thread https://github.com/notifications/unsubscribe-auth/AR0QEBcWAkxnKkul_6jtqATzE3Z0fGkJks5rDdt1gaJpZM4LAluK .

[r3comp1le]

No, I just used the new model_helper.py file and it loaded the Dashboard

[jasheppa5]

I was able to reproduce the error when I update the sql mode to "only_full_group_by". The best fix seems to be removing that from the sql mode:

set GLOBAL sql_mode = ''";

I can't include "id" in the group by without messing up the alert aggregation :-(

On Wed, Nov 30, 2016 at 3:26 PM, r3comp1le notifications@github.com wrote:

No, I just used the new model_helper.py file and it loaded the Dashboard

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ciscocsirt/malspider/issues/16#issuecomment-263985404, or mute the thread https://github.com/notifications/unsubscribe-auth/AR0QEEhIvWBhkytqFM48m66R0z09UN-Jks5rDdwEgaJpZM4LAluK .

[jasheppa5]

SSH to your Malspider box and at the terminal type:

mysql -u root -p

Type your mysql root password and you should be presented with a mysql prompt, then type this to remove the strict group_by setting:

mysql> set GLOBAL sql_mode = '';

Just curious, what version of mysql did you install?

On Wed, Nov 30, 2016 at 3:46 PM, r3comp1le notifications@github.com wrote:

place that in the model_helper.py?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ciscocsirt/malspider/issues/16#issuecomment-263990304, or mute the thread https://github.com/notifications/unsubscribe-auth/AR0QEPqlq7XBvSLPjPkE_mSg8TRu_ndVks5rDeCxgaJpZM4LAluK .

[r3comp1le]

5.7.16-0ubuntu0.14.04.1