How to Debug 1054 Database Error?

Overview

This is the type of error you will often see when database table upon request have been made do not have all needed columns to complete the call.
Error might be scary but, because SQL syntax is written in pure English, in almost every case it gives you the exact place where you should start troubleshooting.
Most often cause of the error is crippled database due the JomSocial upgrade from version older then previous active version. To find out how to upgrade properly, please visit this link
If however you didn't followed the recommendations from the link above, do not panic. For a start, lets examine and explain the error

Error Example & Explanation

The usual output of error would look something like this:

Error 500, database function failed with error number 1054 
Unknown column 'a.friends' in 'field list' SQL=SELECT a.`userid` as _userid , a.`status` as _status , a.`points` as _points, a.`posted_on` as _posted_on, a.`avatar` as _avatar , a.`thumb` as _thumb , a.`invite` as _invite, a.`params` as _cparams, a.`view` as _view, a.`friends` as _friends, a.`groups` as _groups, a.`events` as _events, a.`friendcount` as _friendcount, a.`alias` as _alias, a.`profile_id` as _profile_id, a.`storage` as _storage, a.`watermark_hash` as _watermark_hash, a.`search_email` as _search_email, s.`userid` as _isonline, u.* FROM `prefix_community_users` as a LEFT JOIN `prefix_users` u ON u.`id`=a.`userid` LEFT OUTER JOIN `prefix_session` s ON s.`userid`=a.`userid` AND s.`client_id` !='1' WHERE a.`userid`='62'


This might look like there is a lot going on, but actually, it is just a missing column and error is clearly pointing to it
In very begining of error, it says Unknown column 'a.friends'
The next point of interest is FROM statement which will tell as from what table this column is missing. In our case this is FROM `prefix_community_users`
Translated to language common to average web admin, this means that column friends is missing from table prefix_community_users
Now we know exactly where the problem is, lets see how to fix it

Fixing the Error

Requirements

In order to fix the error, you will need following

  1. Localhost Server. See http://docs.joomla.org/J2.5:Use_Joomla!_on_your_own_computer
  2. Clean instance of exact Joomla version from your site running on localhost
  3. Clean instance of exact JomSocial version running at Joomla on localhost
  4. Make sure that localhost environment runs smooth, without errors, and without third party extensions installed
  5. Code editor or IDE of your choice. For simple usage we recommend Notepad++
If, for whatever reason you already don't have environment of this kind, this is the right time to create it if you want to be serious web admin

Finding Out The Table Structure

Knowing the table structure is the crucial for understanding and fixing the error.
To do that, we will go to the database of working site on localhost and with phpMyAdmin will export only table that have problem on live site
In our case that is `prefix_community_users` table, so navigate to the database of localhost site click on Export tab, select the table to export and click Go

Phpmyadmin1.png

Save the file and open it with your code editor. At the very begining, you should find the area where table structure is described

Table-structure.png
Note: You can also see the structure of table by clicking on the structure tab in phpMyAdmin, but for most users, this tab wont tell anything useful

Constructing The Fix Query

Now that we know the table structure, it is safe to construct the ALTER SQL query which will add the missing column in the database table on our live site.
You can read more about ALTER statement here http://www.w3schools.com/sql/sql_alter.asp
The query to fix error shown at the beginning of this guide would be 

ALTER TABLE prefix_community_users ADD friends text NOT NULL AFTER view

Decomposing & Explaining Used Query

If you read this HowTo from begining, you remember that we debugged friends column error in prefix_community_users table which is the error pointed in the output our site gave us. We collected the data we need such are missing column and broken table, but how we know what query to use.? Well, if you didn't clicked on the link we provided just few lines above, here is the brief explanation of the query 

ALTER TABLE table_name
ADD column_name datatype
AFTER after_column


  • ALTER TABLE table_name - This tells the MySQL server what table to alter
  • ADD column_name datatype - This tells what column to add and what datatype this column will be. This is where exporting the table to see its structure comes handy
  • AFTER after_column - This tells after what existing table column to add new column. If you want to add the new column at the end of the table, you can simply ignore this part of query
Retrieved from "http://documentation.jomsocial.com/index.php?title=How_to_Debug_1054_Database_Error%3F&oldid=2128"