MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

[ejunker]

I'm using MySQL 5.5.29 and after upgrading to 0.4.0 I get this error:

Mysql2::Error: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery': UPDATE delayed_jobs SET locked_at = '2013-02-10 23:59:21', locked_by = 'delayed_job host:twitzip pid:4918' WHERE delayed_jobs.id IN (SELECT id FROM delayed_jobs WHERE ((run_at <= '2013-02-10 23:59:21' AND (locked_at IS NULL OR locked_at < '2013-02-10 23:54:21') OR locked_by = 'delayed_job host:twitzip pid:4918') AND failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 1 FOR UPDATE) AND ((run_at <= '2013-02-10 23:59:21' AND (locked_at IS NULL OR locked_at < '2013-02-10 23:54:21') OR locked_by = 'delayed_job host:twitzip pid:4918') AND failed_at IS NULL)

[kellydunn]

👍 I am also getting this on MySQL 5.0.x and MySQL 5.1.x

After some brief investigation, it appears as though that there are a couple of MySQL support issues.

The first of which comes from using LIMIT inside of a subquery that gets constructed here.

The second issue is attempting to update a table of the same name it uses in a subquery here.

Both of which MySQL versions 5.1.x and 5.0.x do not support.
http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html

It would be nice if a core-contributor could chime in as to the purpose of these uses of limiting and updating. From an un-informed perspective, they seem uneeded as the code appears to only be concerned with a single eligible job at any point in time.

[sferik]

Those change were introduced in #29.

@scosman, can you work on a patch that works on MySQL as well as Postgres? Otherwise, I will need to revert your changes.

I'm going to work on making the tests run on MySQL, Postgres, and SQLite. Currently, the only run on SQLite. 😦

[scosman]

Sorry about that. Revert for now and I'll submit an update later (I don't have a MySQL install handy to test changes on).

What compatibility are you targeting for the project? Postgres, MySQL and SQLite?

[kellydunn]

In the meantime, using an earlier version of the gem (0.3.3 @f276ec2cb2) seems to be working with my local test suite.

I eagerly await the new changeset 😸

[sferik]

What compatibility are you targeting for the project? Postgres, MySQL and SQLite?

Yes, for now. Those are the three most popular open-source databases. This project can’t afford to test against commercial databases (Oracle, Microsoft SQL Server, etc.)

I should have tests running (and failing) under MySQL on Travis CI later today.

@scosman I can help you get MySQL up and running on your local machine. Even though you wrote this code, I don’t hold you responsible for the failure. I reviewed and merged your pull request and should have tested again MySQL. Let’s work on fixing this together. Let me know what time is convenient for you.

[scosman]

Unfortunately I don't have time to install MySQL this week, but this "should" fix the issue:
scosman@c9703fa

MySQL supports UPDATE...LIMIT which makes this easy using basic active_record updates. I couldn't test this fix as I don't have MySQL set up, but from reading AR/MySQL documentation it should work.

Anyone have a local environment to test on?

[sferik]

If someone can verify this fix, I'll merge it upstream. To do so, make the following change to your Gemfile:

-gem 'delayed_job_active_record'
+gem 'delayed_job_active_record', :git => 'git@github.com:scosman/delayed_job_active_record.git'

The run the command

bundle install

[jensblond]

Fixed worked for me. Thanks.

[sferik]

I got the tests running on Postgres, MySQL and SQLite on Travis. Then I applied @scosman's patch and everything passes. This ought to prevent database-specific failures in the future.

@scosman Thank you for pushing this patch so quickly!

@jensblond Thank you for testing this patch!

I'll release gem version 0.4.1 shortly.

[duritong]

Unfortunately, these changes do not work on PostgreSQL 8.4 which is for example what ships with RHEL6.