Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 58554
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: SREZIC [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: 0.08123
Fixed in: 0.08124

Attachments
0001-test-case-for-RT-58554-FOR-UPDATE-together-with-LIMI.patch



Subject: for => "update", rows => 1 generates invalid SQL (MySQL)
Download (untitled) / with headers
text/plain 768b
Using the following query: $schema->resultset('Test')->find({ col => 1}, { for => "update", rows => 1}); Generates the following error: DBIx::Class::ResultSet::find(): DBI Exception: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1' at line 1 [for Statement "SELECT me.col FROM test me WHERE ( me.col = ? ) FOR UPDATE LIMIT 1" with ParamValues: 0=1] at ./dbixclass-forupdate.pl line 30 A manual test in the mysql shell show the same problem. If the "FOR UPDATE" and "LIMIT 1" expressions are swapped, then everything works. In the 'net I found an old post about this problem: http://lists.mysql.com/internals/3246 Regards, Slaven
On Mon Jun 21 04:10:56 2010, SREZIC wrote: Show quoted text
> Using the following query: > > $schema->resultset('Test')->find({ col => 1}, { for => "update", > rows => 1}); > > Generates the following error: > > DBIx::Class::ResultSet::find(): DBI Exception: DBD::mysql::st execute > failed: You have an error in your SQL syntax; check the manual that > corresponds to your MySQL server version for the right syntax to use > near 'LIMIT 1' at line 1 [for Statement "SELECT me.col FROM test me > WHERE ( me.col = ? ) FOR UPDATE LIMIT 1" with ParamValues: 0=1] at > ./dbixclass-forupdate.pl line 30 > > A manual test in the mysql shell show the same problem. If the "FOR > UPDATE" and "LIMIT 1" expressions are swapped, then everything works. In > the 'net I found an old post about this problem: > http://lists.mysql.com/internals/3246 >
Damn that sucks :) Please supply a patch [1] to t/71mysql.t illustrating the problem (also include a version with LIMIT and OFFSET keywords). The fix shouldn't be too hard (some overrides in DBIC::SQLAHacks::MySQL) Cheers [1] git://git.shadowcat.co.uk/dbsrgits/DBIx-Class.git
CC: SREZIC [...] cpan.org
Subject: Re: [rt.cpan.org #58554] for => "update", rows => 1 generates invalid SQL (MySQL)
Date: Tue, 22 Jun 2010 10:57:25 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Slaven Rezic <srezic [...] iconmobile.com>
Download (untitled) / with headers
text/plain 1.2k
Peter Rabbitson via RT wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=58554 > > > On Mon Jun 21 04:10:56 2010, SREZIC wrote: >
>> Using the following query: >> >> $schema->resultset('Test')->find({ col => 1}, { for => "update", >> rows => 1}); >> >> Generates the following error: >> >> DBIx::Class::ResultSet::find(): DBI Exception: DBD::mysql::st execute >> failed: You have an error in your SQL syntax; check the manual that >> corresponds to your MySQL server version for the right syntax to use >> near 'LIMIT 1' at line 1 [for Statement "SELECT me.col FROM test me >> WHERE ( me.col = ? ) FOR UPDATE LIMIT 1" with ParamValues: 0=1] at >> ./dbixclass-forupdate.pl line 30 >> >> A manual test in the mysql shell show the same problem. If the "FOR >> UPDATE" and "LIMIT 1" expressions are swapped, then everything works. In >> the 'net I found an old post about this problem: >> http://lists.mysql.com/internals/3246 >> >>
> > Damn that sucks :) Please supply a patch [1] to t/71mysql.t illustrating > the problem (also include a version with LIMIT and OFFSET keywords). The > fix shouldn't be too hard (some overrides in DBIC::SQLAHacks::MySQL) >
Patch attached. FWIW, this test case passes with DBIx::Class 0.08120. So a git bisect could probably help... Regards, Slaven
From 2926152c33b2bd41f7ba2ca383e51920b33a0e2b Mon Sep 17 00:00:00 2001 From: Slaven Rezic <srezic@iconmobile.com> Date: Tue, 22 Jun 2010 10:55:48 +0200 Subject: [PATCH] test case for RT #58554 (FOR UPDATE together with LIMIT ...) --- t/71mysql.t | 5 +++++ 1 files changed, 5 insertions(+), 0 deletions(-) mode change 100644 => 100755 t/71mysql.t diff --git a/t/71mysql.t b/t/71mysql.t old mode 100644 new mode 100755 index 8d5a323..d569d81 --- a/t/71mysql.t +++ b/t/71mysql.t @@ -334,4 +334,9 @@ my $schema2 = DBICTest::Schema->connect($dsn, $user, $pass); $schema2->resultset("Artist")->find(4); isa_ok($schema2->storage->sql_maker, 'DBIx::Class::SQLAHacks::MySQL'); +{ # RT #58554 + $schema->resultset('CD')->find({cdid => 1}, {for => 'update', rows => 1}); + pass 'Correct SQL for "LIMIT ... FOR UPDATE"'; +} + done_testing; -- 1.7.0.3


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.