"Unknown column 'me.role'" error

[Dustin Suchter]

This is my first time using Catalyst and I'm hitting a problem that
I've been trying to debug for a shamefully long time now with no
success.

I started by going step-by-step through Jon Rockway's tutorial on
CPAN
http://search.cpan.org/~jrockway/Catalyst-Manual-5.701002/lib/Catalyst/Manual/Tutorial.pod
and I ended up with exactly the final project you'd expect, working
and all.

I have made two changes to the example system and am hitting an
error I can't seem to debug:

(1) I changed the storage engine from SQLite to MySQL.

(2) I went through the code and changed the ideas of "books" and
"authors" to "campaigns" and "clients" respectively. I literally
used search-replace in Vim to do the changes, paying close attention
to plurality, capitalization, and actual path/filenames.

Now when I login to my application I get an odd error (the entire
text from the error page is at the bottom of this email). As far as
I can tell the root of the problem is that the $c->login method is
derivatively causing a problem with a JOINed SQL statement.

Here's the broken statement, taken from the error message:

###begin broken SQL###
SELECT me.role FROM users me LEFT JOIN user_roles map_user_role ON (
map_user_role.user_id = me.id ) WHERE ( map_user_role.user_id = ? )
###end broken SQL###

Keep in mind the framework's inner guts generated this SQL
automagically - I had very little to do with it. Notice how the
SELECT uses the alias table "me" and selects the column "role",
despite the fact that the column "role" only exists in the other
table, "user_roles"?

This SQL is clearly broken, as I have verified by hand it does not
produce the correct output. However, a simple modification to this:

###being fixed SQL###
SELECT role FROM users me LEFT JOIN user_roles map_user_role ON (
map_user_role.user_id = me.id ) WHERE ( map_user_role.user_id = ? )
###end fixed SQL###

Does produce the right output.

Even weirder, though, is that I seem to be logged into my
application despite landing on an error page once I hit submit. To
explain more, if I go back to the "/login" page in my URL bar from
this error page, the application clearly recognizes that I am logged
in properly. How could that be?


###full text from the error page###
Campaign List

An error has occurred. We're terribly sorry about that, but it's one
of those things that happens from time to time. Let's just hope the
developers test everything properly before release...

Here's the error message, on the off-chance that it means something
to you: undef error - DBIx::Class::ResultSet::all(): DBI Exception:
DBD::mysql::st execute failed: Unknown column 'me.role' in 'field
list' [for Statement "SELECT me.role FROM users me LEFT JOIN
user_roles map_user_role ON ( map_user_role.user_id = me.id ) WHERE
( map_user_role.user_id = ? )" with ParamValues: 0='1'] at
/usr/local/lib/perl5/site_perl/5.8.8/Catalyst/Plugin/Authentication/Store/DBIC/User.pm
line 119
######

The overall question is, what the heck is going on?!?

Thanks to anyone who even read this far!
-d

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Charlie Garrison]

Good evening,

On 6/9/07 at 11:15 PM -0700, Dustin Suchter <dsuchter@...> wrote:

>The overall question is, what the heck is going on?!?

What does your conf file look like (probably yaml file)? I'm
guessing the user/roles setup doesn't match the database schema.

>Thanks to anyone who even read this far!

To be honest, I didn't read it all, I skipped ahead. So
apologies if I missed something.


Charlie

--
    Charlie Garrison  <garrison@...>
    PO Box 141, Windsor, NSW 2756, Australia

O< ascii ribbon campaign - stop html mail - www.asciiribbon.org
http://www.ietf.org/rfc/rfc1855.txt

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Dustin Suchter]

Unfortunately I thought of that one already, and the two do match.
Here's the useful YAML and below is the useful SQL just to double
check. I believe the underlying MySQL engine is 4.1.

# cat adblue.yml | grep -v "\s*#"
---
name: AdBlue
authentication:
    dbic:
        user_class: AdBlueDB::User
        user_field: username
        password_field: password
authorization:
    dbic:
        role_class: AdBlueDB::User
        role_field: role
        role_rel: map_user_role
        user_role_user_field: user_id

#
[lines from my SQL file]:

    249 --
    250 -- Table structure for table `users`
    251 --
    252
    253 CREATE TABLE `users` (
    254   `id`              int(32) unsigned              NOT NULL
auto_increment            COMMENT 'the unique ID of the users of the
AdBlue system',
    255   `email`           varchar(32) collate utf8_unicode_ci NOT
NULL                     COMMENT 'email address',
    256   `username`        varchar(32) collate utf8_unicode_ci NOT
NULL                     COMMENT 'client or admin username',
    257   `password`        varchar(32) collate utf8_unicode_ci
default 'passw0rd'           COMMENT 'passwords',
    258   `password_tmp`    varchar(32) collate utf8_unicode_ci
default 'passw0rd'           COMMENT 'a 1 time use temporary
password, used for initial signu
    259   `password_dirty`  tinyint(1)                    NOT NULL
default '0'               COMMENT 'if passwords are "dirty" that
means the TMP password is
    260   `creation_time`   timestamp                     NOT NULL
default CURRENT_TIMESTAMP COMMENT 'the time this user was created',
    261   `client_id`       int(32) unsigned              NOT NULL
default '0'               COMMENT 'if this is an external client,
this is their ID number'
    262   `deleted`         tinyint(1)                    NOT NULL
default '0'               COMMENT 'the status of this user - have
they been deleted?',
    263   PRIMARY KEY  (`id`),
    264   FOREIGN KEY  (`client_id`) REFERENCES clients(id)
    265 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
COMMENT='this table stores username and role informaion, not contact
' AUTO_INCREMENT=0;

    269 --
    270 -- Table structure for table `roles`
    271 --
    272
    273 CREATE TABLE `roles` (
    274   `id`              int(32)                       NOT NULL
auto_increment            COMMENT 'this is the unique ID of this role',
    275   `role`            varchar(32) collate utf8_unicode_ci NOT
NULL                     COMMENT 'this is the name of the role',
    276   PRIMARY KEY  (`id`)
    277 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
COMMENT='this table stores username informaion' AUTO_INCREMENT=0;


    293 --
    294 -- Table structure for table `user_roles`
    295 --
    296
    297 CREATE TABLE `user_roles` (
    298   `user_id`         int(32) unsigned              NOT NULL
                         COMMENT 'this is the user who has this role',
    299   `role_id`         int(32)                       NOT NULL
                         COMMENT 'this is the id of the role a user
has',
    300   FOREIGN KEY  (`user_id`) REFERENCES users(id),
    301   FOREIGN KEY  (`role_id`) REFERENCES roles(id)
    302 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
COMMENT='this table stores the relationship of what users have roles';



Charlie Garrison wrote:

> Good evening,
>
> On 6/9/07 at 11:15 PM -0700, Dustin Suchter <dsuchter@...> wrote:
>
>> The overall question is, what the heck is going on?!?
>
> What does your conf file look like (probably yaml file)? I'm guessing
> the user/roles setup doesn't match the database schema.
>
>> Thanks to anyone who even read this far!
>
> To be honest, I didn't read it all, I skipped ahead. So apologies if I
> missed something.
>
>
> Charlie
>

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Charlie Garrison]

Good morning,

On 7/9/07 at 12:22 PM -0700, Dustin Suchter <dsuchter@...> wrote:

>authorization:
>    dbic:
>        role_class: AdBlueDB::User
>        role_field: role
>        role_rel: map_user_role
>        user_role_user_field: user_id

Shouldn't that be:

authorization:
   dbic:
       role_class: AdBlueDB::Role


If that doesn't fix it, how about showing us your schemas for AdBlueDB::User and AdBlueDB::Role.


Charlie

--
   Charlie Garrison  <garrison@...>
   PO Box 141, Windsor, NSW 2756, Australia

O< ascii ribbon campaign - stop html mail - www.asciiribbon.org
http://www.ietf.org/rfc/rfc1855.txt

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Jason Kohles]

On Sep 7, 2007, at 3:22 PM, Dustin Suchter wrote:

> Unfortunately I thought of that one already, and the two do match.
> Here's the useful YAML and below is the useful SQL just to double
> check. I believe the underlying MySQL engine is 4.1.
>
> # cat adblue.yml | grep -v "\s*#"
> ---
> name: AdBlue
> authentication:
>     dbic:
>         user_class: AdBlueDB::User
>         user_field: username
>         password_field: password
> authorization:
>     dbic:
>         role_class: AdBlueDB::User

I think you meant AdBlueDB::Role for the role_class...

>         role_field: role
>         role_rel: map_user_role
>         user_role_user_field: user_id
>

--
Jason Kohles
email@...
http://www.jasonkohles.com/
"A witty saying proves nothing."  -- Voltaire



_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Dustin Suchter]

Yup - that was it!! I have no idea when I introduced that error in
my code, but it is fixed and working now. I also think I get why the
SQL was selecting "me.role" -> because I had the User class in place
of the role class. I does "map_user_role.role" in the correct case.

Thanks Charlie and Jason.

Jason Kohles wrote:

> On Sep 7, 2007, at 3:22 PM, Dustin Suchter wrote:
>
>> Unfortunately I thought of that one already, and the two do match.
>> Here's the useful YAML and below is the useful SQL just to double
>> check. I believe the underlying MySQL engine is 4.1.
>>
>> # cat adblue.yml | grep -v "\s*#"
>> ---
>> name: AdBlue
>> authentication:
>>     dbic:
>>         user_class: AdBlueDB::User
>>         user_field: username
>>         password_field: password
>> authorization:
>>     dbic:
>>         role_class: AdBlueDB::User
>
> I think you meant AdBlueDB::Role for the role_class...
>
>>         role_field: role
>>         role_rel: map_user_role
>>         user_role_user_field: user_id
>>
>

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Antano Solar]

Even weirder, though, is that I seem to be logged into my
application despite landing on an error page once I hit submit. To
explain more, if I go back to the "/login" page in my URL bar from
this error page, the application clearly recognizes that I am logged
in properly. How could that be?

That's because the sql was generated after creating the session.As the sql that you have shown is necessary for role and authorization and not authentication.

With Regards
Antano Solar John


_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/