DB Driver queue push - SQL Server 2012

[Justin-Barker]

Channel column value overwrite and downstream error:

SQLSTATE[07002]: [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error

Steps:

1. Add queue config
2. Create Job via gii/Job
3. \Yii::$app->queue->push(new Job);

'queue' => [
    'class' => \yii\queue\db\Queue::class,
    'db' => 'db', // DB connection component or its config
    'tableName' => '{{%queue}}', // Table name
    'channel' => 'ingestion', // Queue channel key
    'mutex' => \yii\mutex\FileMutex::class,
],

Overwrite:

Downstream:

[zhuravljov]

It looks like as db layer bug.

The insertion executes using native syntax: https://github.com/yiisoft/yii2-queue/blob/8c2b337f7d9ea934c2affdfc21c9fb387d0a0773/src/drivers/db/Queue.php#L157-L164

job is binary field. Maybe here: https://github.com/yiisoft/yii2/blob/20aa5080189cbacd4180d3b115752817921eb364/framework/db/mssql/QueryBuilder.php#L337-L341

@samdark what do you think?

[samdark]

Could be. I currently don't have MSSQL to verify it.

[achretien]

@zhuravljov I just tried this

'job'       => new Expression("CONVERT(VARBINARY(MAX), :data)", [':data' => $message]),

And it works well.

But you have to update the reservemethod also to convert by when getting jobs:

// Reserve one message
                    $payload = (new Query())
                        ->select(['*', new Expression('CONVERT(NVARCHAR(MAX), job) job')])
                        ->from($this->tableName)
                        ->andWhere(['channel' => $this->channel, 'reserved_at' => null])
                        ->andWhere('[[pushed_at]] <= :time - [[delay]]', [':time' => time()])
                        ->orderBy(['priority' => SORT_ASC, 'id' => SORT_ASC])
                        ->limit(1)
                        ->one($this->db);

@samdark do you have an idea to implement those mssql specific config?

[samdark]

Not really...

[SaWey]

Hi,

I encountered the same problem, there was a bug in https://github.com/yiisoft/yii2/blob/20aa5080189cbacd4180d3b115752817921eb364/framework/db/mssql/QueryBuilder.php#L337-L341 but this has been fixed already in the current master branch: https://github.com/yiisoft/yii2/commit/b0f2a522199bc2956179acd1da138f28aaa7bedb

However, this does not fix the trimming to 30 characters, it should still be as @achretien suggests:

$phName = $this->bindParam($value, $params);
$columns[$name] = new Expression("CONVERT(VARBINARY(MAX), $phName)", $params);

Related to https://github.com/yiisoft/yii2/issues/15245

For SQL server, I wrote a class with locking mechanism (sp_getapplock) and above fixes implemented: https://gist.github.com/SaWey/7a4856ffbbbe00bf4455d9dd9752dfac