database – AgileAdam.com

Setting a LIMIT on an UPDATE query in Drupal 8 / 9

/ March 24, 2021

Here’s a quick tip regarding Drupal database manipulation. Specifically, I needed a way to flip a boolean value (from 1 to 0) on the first N rows in a database table that matched a specific set of conditions.

The Problem

It seems you cannot enforce a LIMIT on an UPDATE query using a static query in Drupal.

Here’s what I was trying to do:

$db = \Drupal::database();
$query = $db->query('UPDATE {mytable} SET ondemand=:zero WHERE ondemand=:one ORDER BY imported_at ASC LIMIT :limit', [
  ':zero' => 0,
  ':one' => 1,
  ':limit' => $qtyToConvert,
]);
$query->execute();

$db = \Drupal::database();

$query = $db->query('UPDATE {mytable} SET ondemand=:zero WHERE ondemand=:one ORDER BY imported_at ASC LIMIT :limit', [

':zero' => 0,

':one' => 1,

':limit' => $qtyToConvert,

]);

$query->execute();

This throws an error. I believe the issue is caused by the variable substitution wrapping the limit value in quotes. The error message starts with:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''10'' at line 1...

1	SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''10'' at line 1...

If you run this query manually in MySQL, it works fine with LIMIT 10 but doesn’t work with LIMIT '10' (it throws the same error shown above).

The Solution

Given dynamic queries are favored over static queries, and that this static query doesn’t work, I ended up getting to the finish line with two dynamic queries.

I have tested the solution on 10,000 records and it took < 6 seconds to process the entire request on my local dev lando site.

“code” is a unique identifier column in the database table.

$db = \Drupal::database();
$query = $db->select('mytable', 'mt')
  ->condition('ondemand', 1)
  ->orderBy('imported_at')
  ->fields('mt', ['code'])
  ->range(0, $qtyToConvert);
$codesToReset = $query->execute()->fetchCol();

$db->update('mytable')
  ->fields(['ondemand' => 0])
  ->condition('code', $codesToReset, 'IN')
  ->execute();
return count($codesToReset);

$db = \Drupal::database();

$query = $db->select('mytable', 'mt')

->condition('ondemand', 1)

->orderBy('imported_at')

->fields('mt', ['code'])

->range(0, $qtyToConvert);

$codesToReset = $query->execute()->fetchCol();

$db->update('mytable')

->fields(['ondemand' => 0])

->condition('code', $codesToReset, 'IN')

->execute();

return count($codesToReset);

Development

Cache-related WSOD! Help!

/ January 6, 2011

UPDATE: You can do this stuff with Drush (setting the variable with vset). It’s much faster!

Well, you’ve probably all had a White Screen Of Death a time or two while dealing with Drupal or PHP. They can be very frustrating, but are usually easy enough to resolve. The following illustrates how to recover from a Drupal WSOD as a result of making changes to your cache settings in the Site Configuration -> Performance admin page. Again, this “fix” should only be applied if you have issues that you know are related to recently altering the cache/aggregation/compression settings.

The fix requires a little bit of database work. Basically, you need to modify a few rows of the variable table. This table is where many of your Drupal settings are stored. Here’s a screenshot of the variables you need to set. The values you’re seeing are in the form of serialized data. To fix the WSOD, we need to disable all of the caching. This is accomplished by setting the values in between the quotes from 1 to 0 for all of these rows (except ‘clear’). If the rows don’t exist, don’t worry about it; this means the feature hasn’t been enabled. If you are uncomfortable modifying the values, you can actually delete the rows. Just make sure, when you re-gain access to your site, you visit the Performance settings page and re-save it. You don’t even have to enable anything. The act of saving this form should re-write all of the rows to the variable table.

read more
Development

Delete comments and disable commenting on all nodes of a particular content type

/ January 4, 2011

Please TEST these queries before running them on a live site! I do not want to be held responsible if something goes wrong.

Here is how a colleague explained his situation (the solution follows):

The trap is that if you create a content type and don’t remember to set comments off, all the nodes you create have to be updated manually to turn it off, even after you change the default in the content type settings. In our case, I imported a ton of records, and only later realized comments were on.

Part 1: Turn off commenting on all nodes of a particular content type (we’ll use ‘job’ as the content type in the examples below).

MySQL

UPDATE node SET comment = 0 WHERE type = 'job'

1

UPDATE node SET comment = 0 WHERE type = 'job'

Now, don’t forget to disable commenting on the content type itself (so that nodes (of this content type) created from this point forward do not allow comments).

Part 2: Delete all comments left on nodes of a particular content type. First, take a look at what comments you’ll be deleting… just to be sure of what you’re getting into.

MySQL

SELECT comments.cid, comments.nid, node.type FROM comments INNER JOIN node ON comments.nid = node.nid WHERE comments.nid = 'job'

1
2
3

SELECT comments.cid, comments.nid, node.type
FROM comments INNER JOIN node ON comments.nid = node.nid
WHERE comments.nid = 'job'

Now, let’s delete those comments:

MySQL

DELETE FROM comments USING node INNER JOIN comments WHERE comments.nid = node.nid AND node.type = 'job'

1
2

DELETE FROM comments USING node INNER JOIN comments
WHERE comments.nid = node.nid AND node.type = 'job'

read more