Framework: [6.14] Queues stopped working in MSSQL

[Krunch]

• Laravel Version: 6.14.0
• PHP Version: 7.3.14
• Database Driver & Version: Azure SQL

Description:

Pull request #31287 broke MSSQL support for Queues/Workers.

Error generated: Illuminate\Database\QueryException SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR. (SQL: select top 1 * from [jobs] FOR UPDATE where [queue] = default and (([reserved_at] is null and [available_at] <= 1580841485) or ([reserved_at] <= 1580841395)) order by [id] asc)

Query generated in Laravel 6.14.0: select * from [jobs] FOR UPDATE where [queue] = ? and (([reserved_at] is null and [available_at] <= ?) or ([reserved_at] <= ?)) order by [id] asc

Query generated in Laravel 6.13.x: select * from [jobs] with(rowlock,updlock,holdlock) where [queue] = ? and (([reserved_at] is null and [available_at] <= ?) or ([reserved_at] <= ?)) order by [id] asc

Steps To Reproduce:

Run php artisan queue:work while using MSSQL as your database and Laravel 6.14.0.

[gdesoto-lhs]

Ditto. After an interesting morning, I reverted back to v6.13.1 and queue's started working again. I use both queue:work and queue:listen workers.

Laravel: 6.14.0
PHP: 7.3.7
Database: MS SQL Server 2017

[themsaid]

Compiling locks is disabled in SqlServer grammar, see https://github.com/laravel/framework/blob/6.x/src/Illuminate/Database/Query/Grammars/SqlServerGrammar.php#L277

Are you extending the grammar or using a package that overrides this behaviour?

[danishjamil]

@themsaid I am facing the same issue, no package is being used which overrides this or extending grammar

SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR. (SQL: select top 1 * from [jobs] FOR UPDATE where [queue] = default and (([reserved_at] is null and [available_at] <= 1580920564) or ([reserved_at] <= 1580920474)) order by [id] asc) in /var/www/

[Krisell]

@themsaid We're having the same problem, without any special packages.

SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR. (SQL: select top 1 * from [exam_jobs] FOR UPDATE where [queue] = default and (([reserved_at] is null and [available_at] <= 1580916436) or ([reserved_at] <= 1580916346)) order by [id] asc)

[themsaid]

I've pushed a fix and will make a release soon. Thanks everyone

[diverpl]

I think it's related with this issue.

Laravel Version: 6.15.1
PHP Version: 7.3.14
Database Driver & Version: 10.1.44-MariaDB-cll-lve

I'm getting the error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SKIP LOCKED' at line 1 (SQL: select * fromjobswherequeue= default and ((reserved_atis null andavailable_at<= 1581714782) or (reserved_at<= 1581714692)) order byidasc limit 1 FOR UPDATE SKIP LOCKED)

I'm pretty sure it's related to pull request #31287.