Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[6.15.1] Queues not working with MariaDB #31536

Closed
maciek-szn opened this issue Feb 19, 2020 · 7 comments
Closed

[6.15.1] Queues not working with MariaDB #31536

maciek-szn opened this issue Feb 19, 2020 · 7 comments
Labels

Comments

@maciek-szn
Copy link

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

Description:

I've added a comment to the issue #31368, but I guess it's better to create a new issue.

A queue worker using MariaDB driver is returning the following error due to changes made with pull request #31287.

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 * from jobswherequeue = default and ((reserved_atis null andavailable_at <= 1581714782) or (reserved_at<= 1581714692)) order byid asc limit 1 FOR UPDATE SKIP LOCKED)

I guess there is no implementation of FOR UPDATE SKIP LOCKED in MariaDB, so since version 6.14 of Laravel queues are broken.

Steps To Reproduce:

Just run your queue worker using MariaDB driver.

@sisve
Copy link
Contributor

sisve commented Feb 20, 2020

I would slightly want to highjack this issue to ask for clarification if MariaDB is a supported database platform for Laravel. It's not listed at https://laravel.com/docs/6.x/database

@driesvints
Copy link
Member

@sisve unofficially yes but there's been more and more discrepancies popping up over the years that it might be best that we separate the driver.

@driesvints driesvints added the bug label Feb 20, 2020
@themsaid
Copy link
Member

The changes are only applied if PDO::ATTR_DRIVER_NAME return mysql or pgsql. I guess for MariaDB it's still returning mysql?

@AegirLeet
Copy link
Contributor

AegirLeet commented Feb 20, 2020

The changes are only applied if PDO::ATTR_DRIVER_NAME return mysql or pgsql. I guess for MariaDB it's still returning mysql?

Yes, MariaDB is returning mysql. However, you can check PDO::ATTR_SERVER_VERSION, it will contain the string MariaDB somewhere if it's MariaDB. Example: 5.5.5-10.1.44-MariaDB-0ubuntu0.18.04.1

The easiest fix would probably be an additional !Str::contains($databaseVersion, 'MariaDB').

@themsaid
Copy link
Member

@AegirLeet can you test a change on your machine and open a PR with what worked for you?

@AegirLeet
Copy link
Contributor

@themsaid I've submitted a PR.

@driesvints
Copy link
Member

Will be in the next release.

rihardsgrislis pushed a commit to rihardsgrislis/framework that referenced this issue Sep 6, 2022
PlanetScale provides great serverless MySQL-compatible database and guide on how to integrate it in Laravel (https://planetscale.com/docs/tutorials/connect-laravel-app) but under hood uses Vitess.

However it looks that Vitess doesn't support "skip" queries:
```SQLSTATE[HY000]: General error: 1105 syntax error at position 185 near 'SKIP' (SQL: select * from `jobs` where `queue` = default and ((`reserved_at` is null and `available_at` <= 1662479913) or (`reserved_at` <= 1662479823)) order by `id` asc limit 1 FOR UPDATE SKIP LOCKED)```

Following laravel#31536 I've improved engine/version parsing for Vitess so it wouldn't use locks for popping:
```>>> DB::connection()->getPdo()->getAttribute(PDO::ATTR_DRIVER_NAME)
=> "mysql"

>>> DB::connection()->getPdo()->getAttribute(PDO::ATTR_SERVER_VERSION)
=> "8.0.23-vitess"```
rihardsgrislis pushed a commit to rihardsgrislis/framework that referenced this issue Sep 6, 2022
…ompatible Vitess engine

PlanetScale provides great serverless MySQL-compatible database and guide on how to integrate it in Laravel (https://planetscale.com/docs/tutorials/connect-laravel-app) but under hood uses Vitess.

However it looks that Vitess doesn't support "skip" queries:
```SQLSTATE[HY000]: General error: 1105 syntax error at position 185 near 'SKIP' (SQL: select * from `jobs` where `queue` = default and ((`reserved_at` is null and `available_at` <= 1662479913) or (`reserved_at` <= 1662479823)) order by `id` asc limit 1 FOR UPDATE SKIP LOCKED)```

Following laravel#31536 I've added a check for Vitess engine/version parsing so it wouldn't use locks for popping:
```>>> DB::connection()->getPdo()->getAttribute(PDO::ATTR_DRIVER_NAME)
=> "mysql"

>>> DB::connection()->getPdo()->getAttribute(PDO::ATTR_SERVER_VERSION)
=> "8.0.23-vitess"```

<!--
Please only send a pull request to branches that are currently supported: https://laravel.com/docs/releases#support-policy 

If you are unsure which branch your pull request should be sent to, please read: https://laravel.com/docs/contributions#which-branch

Pull requests without a descriptive title, thorough description, or tests will be closed.

In addition, please describe the benefit to end users; the reasons it does not break any existing features; how it makes building web applications easier, etc.
-->
taylorotwell pushed a commit that referenced this issue Sep 7, 2022
…ompatible Vitess engine (#44027)

* Update DatabaseQueue.php

PlanetScale provides great serverless MySQL-compatible database and guide on how to integrate it in Laravel (https://planetscale.com/docs/tutorials/connect-laravel-app) but under hood uses Vitess.

However it looks that Vitess doesn't support "skip" queries:
```SQLSTATE[HY000]: General error: 1105 syntax error at position 185 near 'SKIP' (SQL: select * from `jobs` where `queue` = default and ((`reserved_at` is null and `available_at` <= 1662479913) or (`reserved_at` <= 1662479823)) order by `id` asc limit 1 FOR UPDATE SKIP LOCKED)```

Following #31536 I've improved engine/version parsing for Vitess so it wouldn't use locks for popping:
```>>> DB::connection()->getPdo()->getAttribute(PDO::ATTR_DRIVER_NAME)
=> "mysql"

>>> DB::connection()->getPdo()->getAttribute(PDO::ATTR_SERVER_VERSION)
=> "8.0.23-vitess"```

* Update DatabaseQueue.php
taylorotwell pushed a commit to illuminate/queue that referenced this issue Sep 7, 2022
…ompatible Vitess engine (#44027)

* Update DatabaseQueue.php

PlanetScale provides great serverless MySQL-compatible database and guide on how to integrate it in Laravel (https://planetscale.com/docs/tutorials/connect-laravel-app) but under hood uses Vitess.

However it looks that Vitess doesn't support "skip" queries:
```SQLSTATE[HY000]: General error: 1105 syntax error at position 185 near 'SKIP' (SQL: select * from `jobs` where `queue` = default and ((`reserved_at` is null and `available_at` <= 1662479913) or (`reserved_at` <= 1662479823)) order by `id` asc limit 1 FOR UPDATE SKIP LOCKED)```

Following laravel/framework#31536 I've improved engine/version parsing for Vitess so it wouldn't use locks for popping:
```>>> DB::connection()->getPdo()->getAttribute(PDO::ATTR_DRIVER_NAME)
=> "mysql"

>>> DB::connection()->getPdo()->getAttribute(PDO::ATTR_SERVER_VERSION)
=> "8.0.23-vitess"```

* Update DatabaseQueue.php
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants