New Killboard install with multiple SQL errors

Help and support in getting the EVE-Dev Killboard to run on your site.
Post Reply
Accla89
Noob
Posts: 6
Joined: Fri Apr 09, 2010 16:37
Location: New Eden
Contact:

New Killboard install with multiple SQL errors

Post by Accla89 » Fri Feb 01, 2019 16:47

Hello, everyone,

I have installed a new "Killboard" and get these messages

MySQL Server Server-Version: 5.7.25-0ubuntu0.16.04.2 - (Ubuntu)
PHP version 7.3.1

anyone has any idea what I can do ?
Database error: Expression #1 of ORDER BY clause is not in SELECT list, references column 'kb.ind.ind_timestamp' which is not in SELECT list; this is incompatible with DISTINCT
SQL: SELECT DISTINCT kll_id FROM kb3_kills kll STRAIGHT_JOIN kb3_ships shp ON ( shp.shp_id = kll.kll_ship_id ) STRAIGHT_JOIN kb3_ship_classes scl ON ( scl.scl_id = shp.shp_class ) INNER JOIN kb3_inv_detail ind ON (ind.ind_kll_id = kll.kll_id) WHERE ( ind.ind_plt_id in (1 ) ) AND ind.ind_timestamp >= '2019-01-28 00:00:00' AND ind.ind_timestamp <= '2019-02-03 23:59:59' AND shp.shp_class not in ( 2,3,11,53 ) order by ind.ind_timestamp desc

1450
Greenhorn
Posts: 16
Joined: Thu Jan 16, 2014 03:55

Re: New Killboard install with multiple SQL errors

Post by 1450 » Sat Feb 02, 2019 19:55

While the query should be re-written in code, the issue is brought to light by default settings changes in MySQL 5.7

You can set sql_mode="" in your my.cnf to revert to previous behavior which should bypass the warning.
Last edited by 1450 on Mon Feb 04, 2019 21:31, edited 1 time in total.

Accla89
Noob
Posts: 6
Joined: Fri Apr 09, 2010 16:37
Location: New Eden
Contact:

Re: New Killboard install with multiple SQL errors

Post by Accla89 » Sun Feb 03, 2019 14:10

thank you for your reply

I can't find this point in the Config.
which settings do I have to make exactly ?

greeting

1450
Greenhorn
Posts: 16
Joined: Thu Jan 16, 2014 03:55

Re: New Killboard install with multiple SQL errors

Post by 1450 » Sun Feb 03, 2019 16:29

You set the sql_mode variable in your my.cnf (MySQL server configuration)

You will likely need to add it, under the [mysqld] section.

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Accla89
Noob
Posts: 6
Joined: Fri Apr 09, 2010 16:37
Location: New Eden
Contact:

Re: New Killboard install with multiple SQL errors

Post by Accla89 » Sun Feb 03, 2019 17:09

the setting is ok
[mysqld]

sql-mode = ""


these settings are currently activated
mysql> SELECT @@sql_mode;
+----------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;
+----------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------- -------------------------------+
| @@SQL_MODE | @@GLOBAL.SQL_MODE |
+----------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------- -------------------------------+
| ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREA TE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------- -------------------------------+
1 row in set (0.00 sec)

1450
Greenhorn
Posts: 16
Joined: Thu Jan 16, 2014 03:55

Re: New Killboard install with multiple SQL errors

Post by 1450 » Mon Feb 04, 2019 21:31

It looks like you used a hyphen - rather than underscore _ in your my.cnf so it hasn't took effect (which is why the variables show the 5.7 defaults)

I actually believe MySQL 5.6 default was sql_mode="NO_ENGINE_SUBSTITUTION" and I also believe you can set it on the fly using `SET GLOBAL` just to test i.e.:

set global SQL_MODE="NO_ENGINE_SUBSTITUTION";

Edit: Just realized I originally put hyphen too - sorry! Been awhile since I've ran it :)

Accla89
Noob
Posts: 6
Joined: Fri Apr 09, 2010 16:37
Location: New Eden
Contact:

Re: New Killboard install with multiple SQL errors

Post by Accla89 » Thu Feb 07, 2019 00:19

Thanks @all

the Problem is solved \o/
Image

Post Reply

Return to “Support”

Who is online

Users browsing this forum: No registered users and 4 guests