I found that I cannot add item (content) into nodequeue at all, no matter I add then by the "Add to (queue name)" link, nor at the nodequeue->Add Content interface. The page turns into a white background page with:

Error

The website encountered an unexpected error. Please try again later.

At the Drupal database "Recent Log Messages" page, I found that the following error was caught:

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] => 10 [:qid] => 10 [:nid] => 178 [:time] => 1325191874 ) in nodequeue_subqueue_add() (line 1044 of /www/common/modules/nodequeue/nodequeue.module).

I am using MariaDB-5.3.3. I am not sure whether this is the database engine's problem or the nodequeue module's. I had tried both 7.x-2.x-dev and 7.x-2.0-beta1 and got the same result.

Comments

metakel’s picture

metakel commented
Status: Active » Fixed

I have just downgraded to MariaDB 5.2.10 and the problem is fixed.

mrfelton’s picture

mrfelton commented
Status: Fixed » Active

I get this problem too. Also on MariaDB-5.3.3. I'm reopening this because nothing has been fixed - you did just did a work around. Downgrading MySQL isn't an option for everyone.

Synchro’s picture

Synchro commented

I just ran into this with MariaDB 5.3.3. Downgrading to 5.3.2 allowed it to work, which isn't quite such a big issue as downgrading to 5.2.10, though the Maria DB repos don't make it easy to find old release packages.

That's one thing, however, I think the error is correct, and it shouldn't be allowing it! The MySQL docs say:

The target table of the INSERT statement may appear in the FROM clause of the SELECT part of the query. (This was not possible in some older versions of MySQL.) However, you cannot insert into a table and select from the same table in a subquery.

That's exactly what this query is doing, so it's a bit puzzling that it works in some cases! The query could probably be rewritten as a join, or a pair of queries in a transaction instead of a subselect.

metakel’s picture

metakel commented

Right, I can confirm that MariaDB 5.3.2 works too.

I was using 5.3.2. Then I upgraded to 5.3.3 and I got the problem. As a result I downgraded the database engine to 5.2.10.

I had filed a bug report to MariaDB's bug-reporting platform:

https://bugs.launchpad.net/maria/+bug/910123

It seems that the MariaDB's team is working on the issue.

amateescu’s picture

amateescu commented
Version: 7.x-2.x-dev » 7.x-3.x-dev
Status: Active » Patch (to be ported)

Committed a fix for this in 7.x-2.x.

http://drupalcode.org/project/nodequeue.git/commit/9572de5

amateescu’s picture

amateescu commented
Status: Patch (to be ported) » Fixed

Committed to 7.x-3.x as well.

http://drupalcode.org/project/nodequeue.git/commit/c8fb870

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

jweowu’s picture

jweowu commented

This new code looks slightly off to me:

-    db_query("INSERT INTO {nodequeue_nodes} (sqid, qid, nid, position, timestamp) VALUES (:sqid, :qid, :nid, COALESCE((SELECT MAX(position)+1 FROM (SELECT * from {nodequeue_nodes} WHERE sqid = :sqid) as nn), 1), :time)", array(':sqid' => $subqueue->sqid, ':qid' => $queue->qid, ':nid' => $nid, ':time' => REQUEST_TIME));

[...]

+      $position = db_query("SELECT MAX(position) + 1 FROM (SELECT * FROM {nodequeue_nodes} WHERE sqid = :sqid) as nn", array(':sqid' => $subqueue->sqid))->fetchField();
+      $position = !empty($position) ? $position : 1;
+      db_query("INSERT INTO {nodequeue_nodes} (sqid, qid, nid, position, timestamp) VALUES (:sqid, :qid, :nid, :position, :time)", array(':sqid' => $subqueue->sqid, ':qid' => $queue->qid, ':nid' => $nid, ':position' => $position,':time' => REQUEST_TIME));

1) I don't understand why the COALESCE functionality has been moved into PHP?

(Was the original code deemed too hard to read?)

2) I don't like ever seeing "SELECT *" in a query. Not a very big deal in this instance (at present), but to be avoided on principle, I think.

3) Perhaps up-case the keyword 'AS' and explicitly specify 'nn.position' in that select?