Post installation error DB Access Error with MySQL 5.7

Ask community to help.

Moderators: Amaradana, TurboPT, TL Developers

Post installation error DB Access Error with MySQL 5.7

Postby floscle » Fri Nov 13, 2015 7:41 am

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
floscle
TestLink user
 
Posts: 2
Joined: Thu Nov 12, 2015 6:25 pm

Re: Post installation error DB Access Error with MySQL 5.7

Postby fman » Sat Nov 14, 2015 9:24 am

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
fman
Member of TestLink Community
 
Posts: 3063
Joined: Tue Nov 15, 2005 7:19 am

Re: Post installation error DB Access Error with MySQL 5.7

Postby floscle » Mon Nov 16, 2015 1:37 pm

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
floscle
TestLink user
 
Posts: 2
Joined: Thu Nov 12, 2015 6:25 pm

Re: Post installation error DB Access Error with MySQL 5.7

Postby fman » Mon Nov 16, 2015 7:50 pm

>> 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
fman
Member of TestLink Community
 
Posts: 3063
Joined: Tue Nov 15, 2005 7:19 am


Return to Installation and configuration



Who is online

Users browsing this forum: No registered users and 2 guests

cron