Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
After I upgraded from MariaDB 5.2.10, my Drupal installation caught the below error log when I operated it (The Operation was: added a content to a nodequeue). After I downgraded back to 5.2.10, the problem was gone. The error reads:
PDOException: SQLSTATE[HY000]: General error: 1093 You can't specify target table 'nodequeue_nodes' for update in FROM clause: INSERT INTO
{nodequeue_nodes} (sqid, qid, nid, position, timestamp) VALUES (:sqid, :qid, :nid, IFNULL((SELECT MAX(position)+1 FROM (SELECT * from {nodequeue_nodes}WHERE sqid = :sqid) as nn), 1), :time); Array ( [:sqid] => 7 [:qid] => 7 [:nid] => 162 [:time] => 1325256693 ) in nodequeue_subqueue_add() (line 1042 of /xxx/modules/nodequeue/nodequeue.module).
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: MariaDB 5.3.3 causes 1093 error on Drupal
fix discovered such bad thing in:
create table t3 (a int);
select * from (select 1 as a) as t1 join (select * from test.t3) as t2;
drop table t3;
we have:
(gdb) p all_tables->select_lex
$4 = (st_select_lex *) 0x1c1aae8
(gdb) p all_tables->next_global->next_global->select_lex
$5 = (st_select_lex *) 0x1c1aae8
(gdb) p all_tables->alias
$6 = 0x1c9dc18 "t1"
(gdb) p all_tables->next_global->next_global->alias
$7 = 0x1c9e848 "t2"
i.e. two different derived tables refer to the same SELECT_LEX structure
Re: MariaDB 5.3.3 causes 1093 error on Drupal
The problem is present in 5.2 so should be fixed there first.
Re: MariaDB 5.3.3 causes 1093 error on Drupal
opened bug https://bugs.launchpad.net/maria/+bug/914224
Re: MariaDB 5.3.3 causes 1093 error on Drupal
It was my mistake the TABLE_LIST::select_lex shoudle be the same (in is where table is used).
Launchpad bug id: 910123
Re: MariaDB 5.3.3 causes 1093 error on Drupal
The queries similar to
CREATE TABLE t ( i INT );
INSERT INTO t VALUES ( (SELECT 1 FROM ( SELECT * FROM t ) as a) );
indeed produce ER_UPDATE_TABLE_USED in 5.3.3 with derived_merge=ON, and pass with derived_merge=OFF (in 5.3.3 derived_merge=ON by default).
It seems consistent with how it works, both in 5.2 and 5.3, with a view instead of the inner subquery:
CREATE TABLE t ( i INT );
CREATE ALGORITHM=<algorithm> VIEW v AS SELECT * FROM t;
INSERT INTO t VALUES ( (SELECT 1 FROM v) );
produces an error ER_VIEW_PREVENT_UPDATE if <algorithm>=MERGE, while with<algorithm>=TEMPTABLE it works all right.
I will assign it to Igor to confirm it's the expected behavior (or not confirm).
Meanwhile, you could disable derived_merge (generally or in the session) to keep using the old query:
SET optimizer_switch='derived_merge=off';