FAQ
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

Search Discussions

  • Charlie Garrison at Sep 7, 2007 at 10:02 am
    Good evening,
    On 6/9/07 at 11:15 PM -0700, Dustin Suchter 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@zeta.org.au>
    PO Box 141, Windsor, NSW 2756, Australia

    O< ascii ribbon campaign - stop html mail - www.asciiribbon.org
    http://www.ietf.org/rfc/rfc1855.txt
  • Dustin Suchter at Sep 7, 2007 at 8:22 pm
    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 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 at Sep 7, 2007 at 8:51 pm
    Good morning,
    On 7/9/07 at 12:22 PM -0700, Dustin Suchter 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@zeta.org.au>
    PO Box 141, Windsor, NSW 2756, Australia

    O< ascii ribbon campaign - stop html mail - www.asciiribbon.org
    http://www.ietf.org/rfc/rfc1855.txt
  • Jason Kohles at Sep 7, 2007 at 8:52 pm

    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@jasonkohles.com
    http://www.jasonkohles.com/
    "A witty saying proves nothing." -- Voltaire
  • Dustin Suchter at Sep 7, 2007 at 8:57 pm
    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
  • Antano Solar at Sep 8, 2007 at 3:49 am




    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
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20070908/9a6aba1e/attachment.htm

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupcatalyst @
categoriescatalyst, perl
postedSep 7, '07 at 7:15a
activeSep 8, '07 at 3:49a
posts7
users4
websitecatalystframework.org
irc#catalyst

People

Translate

site design / logo © 2022 Grokbase