Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-3695

LP:910123 - MariaDB 5.3.3 causes 1093 error on Drupal

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • None
    • None
    • None

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

      Attachments

        Activity

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

          elenst Elena Stepanova added a comment - 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';

          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

          sanja Oleksandr Byelkin added a comment - 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.

          sanja Oleksandr Byelkin added a comment - 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

          sanja Oleksandr Byelkin added a comment - 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).

          sanja Oleksandr Byelkin added a comment - 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

          ratzpo Rasmus Johansson (Inactive) added a comment - Launchpad bug id: 910123

          People

            sanja Oleksandr Byelkin
            metakel metakel (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.