Post installation error DB Access Error with MySQL 5.7

[floscle]

Hi,

I've reported this isue and I'd like to know if someone else have the problem.

My server :
MySQL 5.7.9
PHP 5.6.14

After passing all the installation scripts (I was moving from 1.9.3 to 1.9.14), on the login page, I've got this error (all other seems to work well) :
DB Access Error - debug_print_backtrace() OUTPUT START

And into the log :
[15/Nov/9 13:08:17][ERROR][<nosession>][DATABASE]
ERROR ON exec_query() - database.class.php
1093 - You can't specify target table 'transactions' for update in FROM clause - DELETE FROM transactions WHERE id IN ( SELECT id FROM ( SELECT id FROM transactions t WHERE (SELECT COUNT(0) FROM events e WHERE e.transaction_id = t.id) = 0) XX )
THE MESSAGE : DELETE FROM transactions WHERE id IN ( SELECT id FROM ( SELECT id FROM transactions t WHERE (SELECT COUNT(0) FROM events e WHERE e.transaction_id = t.id) = 0) XX )
Query failed: errorcode[1093]
errormsg:You can't specify target table 'transactions' for update in FROM clause

It seems thats it's related to MySQL 5.7
The transaction table is used in a subquery to select records into a master query that delete records from this table
I think mysql changed this point, this is not allowed.

Someone else have seen it ?
Please give your server's config to knwo if it's related to MySQL upgrade or not.

Thanks

[fman]

Doing a search on Google: error 1093 mysql

provides:
http://stackoverflow.com/questions/4549 ... rom-clause

with several workaround to try. It seems that this is present since MySQL 5.6 => http://dev.mysql.com/doc/refman/5.6/en/update.html.

Probably never detected by people (like me) that are using XAMPP with MySQL.

THIS ISSUE HAS BEEN FOUND years ago there is a note inside TestLink code => logger.class.php
http://stackoverflow.com/questions/4471 ... -condition

Probably again MySQL is adding a level of pickyness that has broken this solution

=> http://dev.mysql.com/doc/refman/5.7/en/ ... tions.html


OFF TOPIC:
MySQL 5.7 has risen the level of strictness and this is creating issues with TABLE DEFINITIONS with TIMESTAMP columns with defaults, that are going to require some changes on table structures, and may be some logics => lot of retesting

[floscle]

The problem comes from the query optimizer.
It was modified in 5.7.6 : http://dev.mysql.com/doc/relnotes/mysql ... -optimizer
It can optimize this query by removing the subquery, so the limitation "you can't delete elements from a table used in where clause" trig
This is a comment on stackoverflow's first link you provided that give me the answer :
Beware, that from MySQL 5.7.6 on, the optimizer may optimize the sub-query away and still give you the error, unless you SET optimizer_switch = 'derived_merge=off';

I think you'll need to change a lot of think as you was saying in TestLink.
But a quick and dirty workaround is :
SET optimizer_switch = 'derived_merge=off'

I've added, in logger.class.php, line 709 :
$sql = "SET optimizer_switch = 'derived_merge=off'";
$this->db->exec_query($sql);

Thank you

[fman]

>> I think you'll need to change a lot of think as you was saying in TestLink.
?? what does this means?


Anyway with the help of TurboP (he find a different query to use) issue seems to be solved => as part of this other ticket
https://github.com/TestLinkOpenSourceTR ... fe1e90e320