• Development

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

    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:

    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:

    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.

  • Development

    Cache-related WSOD! Help!

    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.

    cache_wsod

  • Development

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

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

    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.

    Now, let’s delete those comments: