RC3 blocker: Block displays stale or inconsistent data when block cache is turned on

The problem doesn't seem to be related only to the user login block. Since the same issue came up with the navigation block too, I removed "user login (block)" from the title.

As far as I can tell, during a fresh installation of Drupal 6 RC2, system.install populates incorrect values in the blocks table.

At some point, a new column for specifying the caching was added to the blocks table. The new cache column gets value 1 as default which translates to BLOCK_CACHE_PER_ROLE.

The updater function system_update_6027() in system.install assigns cache values for existing rows according to the block declarations in already installed modules. The user module declares blocks "Log In" and "Navigation" with cache setting BLOCK_NO_CACHE (maps to -1) .

The current system.install does not specify values when creating rows for "Log In" and "Navigate" blocks so they are defined as BLOCK_CACHE_PER_ROLE.

So at the end, when the site administrator turns on the page and block caching, all "Log In" and "Navigate" blocks are cached on role-by-role basis and all authenticated users will see the latest cached block from whoever happens to produce it.

The reason I was not able to reproduce this earlier was because I had user = 1 as the second tester. The block caching treats user 1 differently.

This patch adds the BLOCK_NO_CACHE (-1) values to the system.installer, so now the initial values for a new Drupal 6 installation are correct.

The database updater might need additional code to change the cache setting from 1 -> -1 for existing RC2-based installations?

@yched: Unfortunately the proposed patch isn't complete and the schema updater is broken.

After applying the patch, the function system.install.system_update_6047() contains 3 rows like these:

$ret[] = update_sql("UPDATE {blocks} SET cache = %d WHERE module = '%s' AND delta = '%s'", -1, 'user', '0');

.. etc.

When I checked the documentation and contents of update_sql(), I noticed that %-substitution parameters are not supported.

This explains the output from running the update.php?op=results:

The following queries were executed

system module

Update #6047

• UPDATE {blocks} SET cache = %d WHERE module = '%s' AND delta = '%s'
• UPDATE {blocks} SET cache = %d WHERE module = '%s' AND delta = '%s'
• UPDATE {blocks} SET cache = %d WHERE module = '%s' AND delta = '%s'

I was surprised, because no errors were reported back to the UI even when the commands are not valid SQL and typed manually produce an error message from MySQL:

Error Code : 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 '} SET cache = %d WHERE module = '%s' AND delta = '%s'' at line 1

When I debugged the code, I noticed the actual SQL commands that got finally passed to MySQL were not those shown above, but instead they were futher altered by a callback function in database.mysql-common.inc at line 41:

$query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);

The callback performs some search-replace operations based on the %s and %d placeholders. Since the placeholders haven't been properly passed anywhere, the final UPDATE query is:

UPDATE blocks SET cache = 1 WHERE module = '' AND delta = ''

This isn't invalid SQL, so it doesn't trigger an error.

(I think there is a separate issue needed to make the update_sql() function in database.inc safer. It should not accept variable argument calls, if it can't forward those to db_query anyway.)

Additionally, I think the proposed patch is missing a "ALTER TABLE" command needed to change the default value for blocks.cache from 1 to -1.

Indeed, my last observation about the default value for blocks.cache was incorrect.

I must have been confused by the addition of 'cache' => BLOCK_NO_CACHE to system.module, but that code was of course unrelated to the schema.

Upgraded my RC2 to RC3, then compared full db SQL dumps before & after.

system_update_6047() worked. Looks fine to me.