MySQL 1054 Error produced by account.php

[jackelton]

The above error has appeared on my site when I log in as a user and open the "My Account" page. I can't be too sure as to when this started as I have added a couple of shipping modules to the site. These are working OK and don't seem to be connected to the error. The full error report is:

 

1054 - Unknown column 's.public_flag' in 'where clause'

 

select count(*) as total from orders o, orders_status s where o.customers_id = '3' and o.orders_status = s.orders_status_id and s.language_id = '1' and s.public_flag = '1'

 

[TEP STOP]

 

I've looked at account.php and the sql call is there but I haven't made any changes to that file and it was working prviously. Has anyone had this problem before or know where to start fixing it? The orders table in the database doesn't have the fields referred to in the error report but I can't understand how this file could suddenly have been rewritten.

 

I believe this is the offending query from account.php:

 

$orders_query = tep_db_query("select o.orders_id, o.date_purchased, o.delivery_name, o.delivery_country, o.billing_name, o.billing_country, ot.text as order_total, s.orders_status_name from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_TOTAL . " ot, " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$customer_id . "' and o.orders_id = ot.orders_id and ot.class = 'ot_total' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and s.public_flag = '1' order by orders_id desc limit 3");

 

All help would be appreciated asap as this is a live site. Thanks

:unsure:

 

Er, it's just occurred to me in the country tables (as entered via admin) I've put spaces in between the ISO2 codes (i.e. US, CN not US,CN) - would that be causing a problem? Leaving a comma off the end of the list seems to. I've also checked, optimised and repaired the tables in the database but I don't have permissions to flush them.

[Guest]

Did you recently upgrade to RC1 or RC2? Did you make the database changes? If not you may want to upgrade. This will fix your problem though. Run the following in phpmyadmin.

SQL Database changes

Add indexes to increase performance

Add public and download status flag fields to the orders_status table

Increase payment_method field size on the orders table

Increase the last_page_url field size on the whos_online table

alter table banners add index idx_banners_group (banners_group); alter table banners_history add index idx_banners_history_banners_id (banners_id); alter table currencies add index idx_currencies_code (code); alter table customers add index idx_customers_email_address (customers_email_address); alter table customers_basket add index idx_customers_basket_customers_id (customers_id); alter table customers_basket_attributes add index idx_customers_basket_att_customers_id (customers_id); alter table orders add index idx_orders_customers_id (customers_id); alter table orders_products add index idx_orders_products_orders_id (orders_id); alter table orders_products add index idx_orders_products_products_id (products_id); alter table orders_status_history add index idx_orders_status_history_orders_id (orders_id); alter table orders_products_attributes add index idx_orders_products_att_orders_id (orders_id); alter table orders_products_download add index idx_orders_products_download_orders_id (orders_id); alter table products add index idx_products_model (products_model); alter table products_attributes add index idx_products_attributes_products_id (products_id); alter table reviews add index idx_reviews_products_id (products_id); alter table reviews add index idx_reviews_customers_id (customers_id); alter table specials add index idx_specials_products_id (products_id); alter table zones add index idx_zones_to_geo_zones_country_id (zone_country_id); alter table orders_status add public_flag int DEFAULT '1'; alter table orders_status add downloads_flag int DEFAULT '0'; alter table orders modify payment_method varchar(255) NOT NULL; alter table whos_online modify last_page_url text NOT NULL;

[jackelton]

Did you recently upgrade to RC1 or RC2? Did you make the database changes? If not you may want to upgrade. This will fix your problem though. Run the following in phpmyadmin.

SQL Database changes

Add indexes to increase performance

Add public and download status flag fields to the orders_status table

Increase payment_method field size on the orders table

Increase the last_page_url field size on the whos_online table

alter table banners add index idx_banners_group (banners_group); alter table banners_history add index idx_banners_history_banners_id (banners_id); alter table currencies add index idx_currencies_code (code); alter table customers add index idx_customers_email_address (customers_email_address); alter table customers_basket add index idx_customers_basket_customers_id (customers_id); alter table customers_basket_attributes add index idx_customers_basket_att_customers_id (customers_id); alter table orders add index idx_orders_customers_id (customers_id); alter table orders_products add index idx_orders_products_orders_id (orders_id); alter table orders_products add index idx_orders_products_products_id (products_id); alter table orders_status_history add index idx_orders_status_history_orders_id (orders_id); alter table orders_products_attributes add index idx_orders_products_att_orders_id (orders_id); alter table orders_products_download add index idx_orders_products_download_orders_id (orders_id); alter table products add index idx_products_model (products_model); alter table products_attributes add index idx_products_attributes_products_id (products_id); alter table reviews add index idx_reviews_products_id (products_id); alter table reviews add index idx_reviews_customers_id (customers_id); alter table specials add index idx_specials_products_id (products_id); alter table zones add index idx_zones_to_geo_zones_country_id (zone_country_id); alter table orders_status add public_flag int DEFAULT '1'; alter table orders_status add downloads_flag int DEFAULT '0'; alter table orders modify payment_method varchar(255) NOT NULL; alter table whos_online modify last_page_url text NOT NULL;

 

 

Hi bktrain, thanks for your quick reply.

I'm running v2.2 RC2 from a fresh install but from the code above it looks a different table structure, so I'll give it a go. I'll get back to let you know. Thanks.

[jackelton]

bktrain that's brill. Thanks muchly. It worked a treat. :wub: :P