[MDEV-3695] LP:910123 - MariaDB 5.3.3 causes 1093 error on Drupal Created: 2011-12-30  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: metakel (Inactive) Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug910123.xml    

 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).

 Comments   
Comment by Elena Stepanova [ 2012-01-03 ]

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';

Comment by Oleksandr Byelkin [ 2012-01-10 ]

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

Comment by Oleksandr Byelkin [ 2012-01-10 ]

Re: MariaDB 5.3.3 causes 1093 error on Drupal
The problem is present in 5.2 so should be fixed there first.

Comment by Oleksandr Byelkin [ 2012-01-10 ]

Re: MariaDB 5.3.3 causes 1093 error on Drupal
opened bug https://bugs.launchpad.net/maria/+bug/914224

Comment by Oleksandr Byelkin [ 2012-01-10 ]

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).

Comment by Rasmus Johansson (Inactive) [ 2012-02-14 ]

Launchpad bug id: 910123

Generated at Thu Feb 08 06:50:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.