Child pages
  • [deployment] The multiline SQL with "#" in them result in deployment error [5.2.2-B1]
Skip to end of metadata
Go to start of metadata

In-Portal built-in deployment engine, that allows to rollout updates easily on production and staging servers. The deployment process looks like this:

  1. executing database migrations
  2. importing language pack
  3. resetting caches (unit config cache, section cache, mod-rewrite cache)
  4. refreshing themes (looking for added templates)

As part of "executing database migrations" step:

  • the "/modules/custom/install/project_upgrades.sql" file is parsed to detect available revisions
  • only revisions that were not yet executed are kept
  • SQLs from these revisions are executed

The code, that parses SQLs is doing some magic to separate SQLs one from another especially in cases if:

  • the "#" (comment) symbol is used
  • SQL is split across multiple lines

This is how it looks in code:

deployment_helper.php, "applyRevisions" method
$sqls = str_replace("\r\n", "\n", $sqls); // convert to linux line endings

$no_comment_sqls = preg_replace("/#\s([^;]*?)\n/is", "# \\1;\n", $sqls); // add ";" to each comment end to ensure correct split
$sqls = explode(";\n", $no_comment_sqls . "\n"); // ensures that last sql won't have ";" in it

The supported use cases for above code looks like this:

-- single line SQL
UPDATE TableName SET FieldName = 5 WHERE OtherField = 6;
 
-- multiline SQL
UPDATE TableName
SET FieldName = 5
WHERE OtherField = 6;
 
-- comment, that begins at the beginning of the line
# comment text
 
-- comment coming after SQL
UPDATE TableName SET FieldName = 5; # this is a comment
 
-- SQL, that contains CSS color codes
UPDATE TableName SET Styles = 'color: #FFF;';

This SQL however causes ";" to be added right after "IF(RmNumber = '', NULL, CONCAT('RM# ', RmNumber))" line resulting in SQL being split into 2 SQLs down the road.

UPDATE Addresses
SET AddressCustom = CONCAT_WS(
	', ',
	IF(Bldg = '', NULL, Bldg),
	IF(Dept = '', NULL, Dept),
	IF(RmNumber = '', NULL, CONCAT('RM# ', RmNumber))
)
WHERE AddressCustom = '' AND (Bldg <> '' OR Dept <> '' OR RmNumber <> '');

The problem is with 'RM# ' part where whitespace comes right after "#" symbol.

Solution

Related Tasks