Can't do RDBOM->update_objects with many_to_many relationship contraint

[Alexander Karelas]

Table list (PApps::DB::List) has fields:

id AUTO_INC
code VARCHAR
etag INT

Table list_distro (PApps::DB::ListDistro) has fields:

list_id
distro_id

Table distro (PApps::DB::Distro) has fields:

id AUTO_INC
name VARCHAR
etag INT

list & distro are joined in a many-to-many relationship with list_distro joining them.

This script dies with an error. Shouldn't it work?

#!/usr/bin/perl

use v5.18;
use warnings;

use PApps::DB::List;

Rose::DB::Object::Manager->update_objects(
    object_class => 'PApps::DB::List',
    require_objects => ['distros'],
    set => { 't1.etag' => \'t1.etag + 1' },
    where => [
        'distros.name' => 'Mojolicious',
    ],
    debug => 1,
);

And the error:

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 'WHERE
   t3.name = 'Mojolicious'
ORDER BY t1.id' at line 5 at /home/pm/perl5/lib/perl5/Rose/DB/Object/Manager.pm line 3375.
update_objects() - 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 'WHERE
   t3.name = 'Mojolicious'
ORDER BY t1.id' at line 5 at /home/pm/perl5/lib/perl5/Rose/DB/Object/Manager.pm line 3375.
 at /home/pm/perl5/lib/perl5/Rose/DB/Object/Manager.pm line 3387.
        Rose::DB::Object::Manager::update_objects('Rose::DB::Object::Manager', 'object_class', 'PApps::DB::List', 'require_objects', 'ARRAY(0x12a2cb8)', 'set', 'HASH(0x12c04f0)', 'where', 'ARRAY(0x12c05b0)', ...) called at update-join.pl line 8

shell returned 255

Unfortunately the mysql logs are not showing the query for some strange reason.

[Alexander Karelas]

Just pinging for some attention...

- Alex

[David Christensen]

You’ve got debug => 1, so it should be spitting the generated query to STDERR. Does the query itself work in MySQL, and if not can you paste the query here? Which version of MySQL?

Regards,

David
--
David Christensen
End Point Corporation
da...@endpoint.com
785-727-1171

[Alexander Karelas]

I think debug => 1 only works for the "get_objects" method.

I didn't get the SQL command as output.

I use MySQL version 5.6.

- Alex

[Alexander Karelas]

This executes fine:

update list l join list_distro ld on (l.id = ld.list_id) left join
distro d on (ld.distro_id = d.id) set l.etag = l.etag + 1 where d.name =
'Mojolicious';

This fails, but with a different error than what we saw in my first example:

update list l join list_distro ld on (l.id = ld.list_id) left join
distro d on (ld.distro_id = d.id) set l.etag = l.etag + 1 where d.name =
'Mojolicious' order by l.id;

The error is now:

ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY

What should I do?

[Alexander Karelas]

I think ORDER BY has no place in an UPDATE query. RDBOM placed ORDER BY
in the UPDATE query for some reason. I guess it shouldn't, but maybe
that's not the only error.

- Alex