SQL query error with MySQL 5.7+

[StinGer-ShoGuN]

Another consequence of my recent updates: now the main page of my killboard displays the following error message.

Database error: Expression #1 of ORDER BY clause is not in SELECT list, references column 'kll.kll_timestamp' which is not in SELECT list; this is incompatible with DISTINCT
SQL: SELECT DISTINCT kll_id FROM ((SELECT kll.* FROM kb3_kills kll INNER JOIN kb3_inv_detail ind ON ind.ind_kll_id = kll.kll_id WHERE ( ind.ind_crp_id IN (1 ) OR ind.ind_plt_id IN (14798 ) ) AND ind.ind_timestamp >= '2018-11-01 00:00:00' AND ind.ind_timestamp <= '2018-11-30 23:59:00' GROUP BY kll.kll_id order by ind.ind_timestamp desc ) UNION (SELECT kll.* FROM kb3_kills kll WHERE ( kll.kll_victim_id in ( 14798 ) OR kll.kll_crp_id in ( 1 ) ) AND kll.kll_timestamp >= '2018-11-01 00:00:00' AND kll.kll_timestamp <= '2018-11-30 23:59:00' order by kll.kll_timestamp desc ) ) kll order by kll.kll_timestamp desc

A solution is to set sql-mode in my.cnf.

[mysqld]
...
sql-mode = ""

A maybe better solution, is the following instead.

[mysqld]
...
sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

An even better solution (i.e. the solution), would be to change the query to include kll_timestamp in the SELECT DISTINCT. It would require more adjustments in the code though. So in common/includes/class.killlist.php, on line 601 (at least, this is the one used for the homepage if I'm correct), change to this:

$ssql = 'SELECT DISTINCT kll_id, kll_timestamp FROM '.$this->sqlinner_.$this->sql_;

[Salvoxia]

Hi,

that is a known issue.

Unfortunately, there are two problems with fixing this "properly".
The first is mods. Any custom SQL they might use still could cause that error.

The second is that there are queries which are generated dynamically. Back when the issue first arose, I took a look at what needed to change, one thing lead to another and to another and...

So I'm a bit ashamed to admit that I opted for this option: https://github.com/evekb/evedev-kb/blob/4.4/install/templates/install_step1.tpl#L5

I will gladly accept any proper fixes, though!

Best Regards,
Salvoxia

[StinGer-ShoGuN]

Huhu... never read that since I installed my KB... no I won't count. Which is the second sql-mode configuration I gave.

I see the concern with the mods. I'd says it would be a great opportunity to clean them up a bit though... 😋

The only "proper" solution then would be to analyse the queries to rewrite them when they are invalid. There might be a way to do so with MySQL directly, I don't know (I mean, it parses the query to give the error, so maybe we could get that error before actually executing the query).