Cannot set variable in mysql

[FelixZY]

Passing mysql user variables to sql scripts is supposedly supported (1 2). However, I'm unable to get this to work:

deploy/variable_test.sql

-- Deploy app:variable_test to mysql
begin;

SELECT sqitch.checkit(@hello <> NULL, "Variable is null");

COMMIT;

$ sqitch deploy -s @hello=world -s hello=world
Deploying changes to dev
  + variable_test .... ERROR 1644 (ERR0R) at line 4 in file: 'deploy/variable_test.sql': Variable is null
not ok
"mysql" unexpectedly returned exit value 1

Deploy failed

Am I doing something wrong? Is this functionality broken? It would be nice with an example in the mysql documentation.

[theory]

Hrm. Try this patch, which should show how variables are set up.

--- a/lib/App/Sqitch/Engine/mysql.pm
+++ b/lib/App/Sqitch/Engine/mysql.pm
@@ -404,6 +404,7 @@ sub _set_vars {
 sub _source {
     my ($self, $file) = @_;
     my $set = $self->_set_vars || '';
+    print "$set\n";
     return ('--execute' => "${set}source $file");
 }
 

See if you spot any errors.

[FelixZY]

$ perl -CAS /sqitch/t/sqitch deploy -s @hello=world -s hello=world
Deploying changes to dev
  + variable_test .. SET @"@hello" = 'world', @"hello" = 'world';

ERROR 1644 (ERR0R) at line 4 in file: 'deploy/variable_test.sql': Variable is null
not ok
"mysql" unexpectedly returned exit value 1

Deploy failed

[FelixZY]

Ok, I found the issue: Apparently, @var <> NULL is not supported. Instead, @var IS NOT NULL must be used instead.

Testing like this:

-- Deploy app:variable_test to mysql
begin;

SELECT
  sqitch.checkit (
    EXISTS (
      SELECT
        *
      FROM
        performance_schema.user_variables_by_thread
      WHERE
        variable_name = 'hello'
    ),
    'No such variable'
  );

SELECT
  sqitch.checkit (
    EXISTS (
      SELECT
        *
      FROM
        performance_schema.user_variables_by_thread
      WHERE
        variable_name = 'hello'
        AND variable_value = 'world'
    ),
    'No such variable'
  );

SELECT
  sqitch.checkit (@hello IS NOT NULL, 'Variable is NULL');

SELECT
  sqitch.checkit (@hello = 'world', 'Variable is not "world"');

COMMIT;

with sqitch -s hello=world proved that the variable was actually fine.