Confusing MySql Error

Question

I am trying to install an application on CentOS, and see this error in my apache log.

If any can help me understand what exactly is the case, I can try to work on a solution for the error.

This is what i see in the logs:

[20131010 16:23][notloggedin]: Database error: Invalid SQL: SELECT  DISTINCT c.name AS category_name, c.acl_id, b.*
           FROM bm_categories c
           INNER JOIN bm_bookmarks b ON c.id = b.category_id
                                         LEFT JOIN go_acl a ON a.acl_id = c.acl_id
           LEFT JOIN go_users_groups ug ON ( a.group_id = ug.group_id ) WHERE
                                         (c.user_id= 0
                                         OR ug.user_id =  0
                                         OR a.user_id =  0) ORDER BY category_name ASC , b.name ASC MySQL Error: 1054 (Unknown column 'c.acl_id' in 'field list')

This isn't the slightest bit confusing. Your `bm_categories` table is missing the `acl_id` column. — ceejayoz, Oct 10 '13 at 20:47
the confusing part is, it uses the naming convention like "c.acl_id" shoudlnt it be "bm_categories.acl_id"?? — Vijit Jain, Oct 10 '13 at 20:50
@ceejayoz thank you for the tip. I added the column, that gives rise to this error ""Unknown column 'b.category_id' in 'on clause'"" — Vijit Jain, Oct 10 '13 at 20:55
Same problem. Missing column in `bm_bookmarks`. Sounds like your database isn't properly set up. These are very straightforward error messages to anyone who knows SQL. — ceejayoz, Oct 10 '13 at 21:00
Then you're missing the column category_id in bm_bookmarks. As for why you're seeing 'c.acl_id' instead of 'bm_categories.acl_id' is because the query is using aliases. Notice 'FROM bm_categories c' instead of simply 'FROM bm_categories'. c = bm_categories, bm_bookmarks = b, go_acl = a, go_users_groups = ug. — , Oct 10 '13 at 21:00

score 1 · Accepted Answer · answered Oct 10 '13 at 21:06

This is your query:

  SELECT DISTINCT
          c.name AS category_name,
          c.acl_id,  
          b.* 
  FROM
          bm_categories c   <-- An alias is set, and thus "c" means "bm_categories"

  INNER JOIN
          bm_bookmarks b ON c.id = b.category_id  <-- Alias "b" is set for "bm_bookmarks"

  LEFT JOIN
          go_acl a ON a.acl_id = c.acl_id

  LEFT JOIN go_users_groups ug ON ( a.group_id = ug.group_id )

  WHERE
          (c.user_id= 0 OR ug.user_id = 0 OR a.user_id = 0)
  ORDER BY
          category_name ASC, b.name ASC

Basically you are getting confused with aliases. I tried commenting int he code above to help you understand when those are set.

It smells a bit like you need to import a database schema in order for whatever it is to work.

that is exactly what i needed. That has helped a lot. I have solved the error by creating columns in the concerned locations. Thanks to you, ceejayoz and yoonix — Vijit Jain, Oct 10 '13 at 21:24

Confusing MySql Error

1 Answers1