Jump to content

Archived

This topic is now archived and is closed to further replies.

christiansees

v2.2 RC2 Upgrade sql error 1054 - Unknown column

Recommended Posts

Hello All,

I am having a problem with the account_history_info.php page while doing the v2.2 RC2 Upgrade. I keep getting an SQL error. The pages that I have problems with are checkout_success.php account_history_info.php and download.php. The error for account_history_info.php when I leave out the line

alter table orders_products_download add index idx_orders_products_download_orders_id (orders_id);
from the SQL modifications for the upgrade is
1054 - Unknown column 'os.orders_status_id' in 'where clause'

 

select o.orders_status, date_format(o.last_modified, '%Y-%m-%d') as date_purchased_day, opd.download_maxdays, op.products_name, opd.orders_products_download_id, opd.orders_products_filename, opd.download_count, opd.download_maxdays from orders o, orders_products op, orders_products_download opd where o.customers_id = '2' and o.orders_status >= '2' and o.orders_id = '6213' and o.orders_id = op.orders_id and op.orders_products_id = opd.orders_products_id and opd.orders_products_filename != '' and o.orders_status = os.orders_status_id and os.downloads_flag = '1' and os.language_id = '1'

 

[TEP STOP]

I realized that the line of SQL has nothing to do with the error and the error hasn't changed after running that line. What it seems to correspond to is the line 213 of account_history_info.php

  $statuses_query = tep_db_query("select os.orders_status_name, osh.date_added, osh.comments from " . TABLE_ORDERS_STATUS . " os, " . TABLE_ORDERS_STATUS_HISTORY . " osh where osh.orders_id = '" . (int)$HTTP_GET_VARS['order_id'] . "' and osh.orders_status_id = os.orders_status_id and os.language_id = '" . (int)$languages_id . "' and os.public_flag = '1' order by osh.date_added");

but this doesn't make sense either as TABLE_ORDERS_STATUS . "os," is declared, and not missing also order_status_id is a column in table order_status in the database.

I'm stumped :blink: Any help is much appreciated.

Thanks

Christian


On your last day only you will have to approve or disaprove of how your life has been.

Share this post


Link to post
Share on other sites

I checked the dependencies such as /classes/order.php and /includes/header.php and I don't find anything there either that would prevent the file from loading properly.


On your last day only you will have to approve or disaprove of how your life has been.

Share this post


Link to post
Share on other sites

Try checking that all the php5 patches documented in the "Solutions to common problems" below, have been applied to your site


Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

Share this post


Link to post
Share on other sites

Thanks for the suggestion. I did find that catalog/includes/functions/cache.php catalog/admin/file_manager.php SQL update

ALTER TABLE whos_online MODIFY COLUMN last_page_url VARCHAR(255) NOT NULL; Had not been done but updating them did not clear the error. So I am still stumped.

All help is appreciated.

Thanks

Christian


On your last day only you will have to approve or disaprove of how your life has been.

Share this post


Link to post
Share on other sites

Hello All,

I still haven't found a solution to this question. The line that seems to be referenced in account_history_info.php is 213

  $statuses_query = tep_db_query("select os.orders_status_name, osh.date_added, osh.comments from " . TABLE_ORDERS_STATUS . " os, " . TABLE_ORDERS_STATUS_HISTORY . " osh where osh.orders_id = '" . (int)$HTTP_GET_VARS['order_id'] . "' and osh.orders_status_id = os.orders_status_id and os.language_id = '" . (int)$languages_id . "' order by osh.date_added")

But that looks correct as far as I can tell.

Any help is much appreciated.

thanks

Christian


On your last day only you will have to approve or disaprove of how your life has been.

Share this post


Link to post
Share on other sites

Next thing that I tried is changing register globals from on to off. Still no change.

I really could use some help with this.

Thanks

Christian


On your last day only you will have to approve or disaprove of how your life has been.

Share this post


Link to post
Share on other sites

When I check the pages download.php checkout_success.php and account_history_info.php for "left join" as per the suggestion below there are none for those pages. I don't know if there are any in the pages they are dependent on, as I can't tell what pages they are dependent on except for what are in the require statements and they don't have any "left join" either.

Hope to hear back from some one.

Thanks

Christian

 

 

OK, I'm totally confused here about where you are and where you're trying to go. Just to level-set and make sure we're on the same page:

 

2.2 MS2 Milestone 2, 17 August 2006 (060817)

2.2 RC1 Release Candidate 1, summer 2007

2.2 RC2 Release Candidate 2, late 2007

2.2 RC2a Release Candidate 2a, quick patch, February 2008

 

Where are you starting from and where are you going?

 

2.2 MS2 has two major problems when going to PHP 5 and MySQL 5:

 

1) PHP 5 in most cases has register global variables turned OFF by default. MS2 needs them turned ON. Usually you can do this in a server php.ini or httpd.conf settings file. RC1 and later don't use register globals and don't care if they're on or off.

 

2) MySQL 4 and earlier had the comma (,) operator and JOIN operators at the same precedence level. In conformance with the SQL standard, MySQL 5 now has JOIN at a higher precedence than comma. This breaks any code of the form

 

SELECT ... FROM table1 name1, table2 name2 LEFT JOIN ....

 

You need to edit the code to change it to

 

SELECT ... FROM (table1 name1, table2 name2) LEFT JOIN ....

 

Otherwise you will often get an error (1054?) about a field/column not found in the "ON" specifier phrase. As far as I can tell, RC2a has not been fixed.

 

There may well be other "gotchas" out there, but these are the two biggies that break every osC installation when PHP and MySQL are upgraded.


On your last day only you will have to approve or disaprove of how your life has been.

Share this post


Link to post
Share on other sites

Ok I am really stuck here, and sure would appreciate some help.

I have been reading all the posts that seem even remotely relevant, and applying what I find there and still no change.

For example I updated the .htaccess files to switch the register globals setting. As some posts I found said that register globals should be set to Off in the php.ini file. When I did so I lost admin access. However when I set it back on it didn't restore access. So I have to update register globals to on in .htaccess for the admin folder, to get to the admin pages. I did so by uncommenting the line.

php_value register_globals 1

When I uncomment this line for the catalog folder .htaccess file in hopes that it will resolve my error list above there is no change.

Any one who has any clue.

Please reply.

Thanks

Christian


On your last day only you will have to approve or disaprove of how your life has been.

Share this post


Link to post
Share on other sites

Perhaps I should mention that v2.2 RCa is a misnomer and that the actual upgrade that I am doing to v2.2RC2a.

Hope that helps is getting a suggestion of some sort.

Because I have review this error, the data base table, and the line of code, and I can not find any reason that I should be getting this error. I am sure that I am missing some thing likely even something simple. But I can't seem to find what.

Thanks

Christian


On your last day only you will have to approve or disaprove of how your life has been.

Share this post


Link to post
Share on other sites

First, you are looking at the wrong file. It's not account_history_info.php, it is probably either catalog/includes/modules/downloads.php

or

catalog/download.php

And the problem is that your query is missing the folloing part, so check the RC2a instructions for these files:

...opd, " . TABLE_ORDERS_STATUS . " os where...

Share this post


Link to post
Share on other sites

Hello All,

I will restate the problem in hopes of clarity.

After applying the oscommerce-2.2rc2a/upgrade.html changes. Everything seems to be working except when I go to the page catalog/account_history_info.php I get the following error message.

1054 - Unknown column 'os.orders_status_id' in 'where clause'

select o.orders_status, date_format(o.last_modified, '%Y-%m-%d') as date_purchased_day, opd.download_maxdays, op.products_name, opd.orders_products_download_id, opd.orders_products_filename, opd.download_count, opd.download_maxdays from orders o, orders_products op, orders_products_download opd where o.customers_id = '2' and o.orders_status >= '2' and o.orders_id = '6213' and o.orders_id = op.orders_id and op.orders_products_id = opd.orders_products_id and opd.orders_products_filename != '' and o.orders_status = os.orders_status_id and os.downloads_flag = '1' and os.language_id = '1'

[TEP STOP]

 

When I check the code for that page the only line that I find that references those data base calls is 213

 

  $statuses_query = tep_db_query("select os.orders_status_name, osh.date_added, osh.comments from " . TABLE_ORDERS_STATUS . " os, " . TABLE_ORDERS_STATUS_HISTORY . " osh where osh.orders_id = '" . (int)$HTTP_GET_VARS['order_id'] . "' and osh.orders_status_id = os.orders_status_id and os.language_id = '" . (int)$languages_id . "' order by osh.date_added");

 

Following dr_lucas's direction I looked at download.php the only line that I found there that was relevant was

 

  $downloads_query = tep_db_query("select date_format(o.date_purchased, '%Y-%m-%d') as date_purchased_day, opd.download_maxdays, opd.download_count, opd.download_maxdays, opd.orders_products_filename from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS_PRODUCTS_DOWNLOAD . " opd, " . TABLE_ORDERS_STATUS . " os where o.customers_id = '" . $customer_id . "' and o.orders_id = '" . (int)$HTTP_GET_VARS['order'] . "' and o.orders_id = op.orders_id and op.orders_products_id = opd.orders_products_id and opd.orders_products_download_id = '" . (int)$HTTP_GET_VARS['id'] . "' and opd.orders_products_filename != '' and o.orders_status = os.orders_status_id and os.downloads_flag = '1' and os.language_id = '" . (int)$languages_id . "'");

 

For the sake of it I re-uploaded account_history_info.php, download.php, and checkout_success.php, still no joy.

When I check the data base for orders_status_id I get these results.

 SELECT *
FROM `XXXXXX_xxxxx`.`configuration`
WHERE (
`configuration_id` LIKE '%orders_status_id%'
OR `configuration_title` LIKE CONVERT( _utf8 '%orders_status_id%'
USING latin1 )
COLLATE latin1_swedish_ci
OR `configuration_key` LIKE CONVERT( _utf8 '%orders_status_id%'
USING latin1 )
COLLATE latin1_swedish_ci
OR `configuration_value` LIKE CONVERT( _utf8 '%orders_status_id%'
USING latin1 )
COLLATE latin1_swedish_ci
OR `configuration_description` LIKE CONVERT( _utf8 '%orders_status_id%'
USING latin1 )
COLLATE latin1_swedish_ci
OR `configuration_group_id` LIKE '%orders_status_id%'
OR `sort_order` LIKE '%orders_status_id%'
OR `last_modified` LIKE '%orders_status_id%'
OR `date_added` LIKE '%orders_status_id%'
OR `use_function` LIKE CONVERT( _utf8 '%orders_status_id%'
USING latin1 )
COLLATE latin1_swedish_ci
OR `set_function` LIKE CONVERT( _utf8 '%orders_status_id%'
USING latin1 )
COLLATE latin1_swedish_ci
)
LIMIT 0 , 30

 

Also checkout_success.php and download.php are not working as well Any and all help is appreciated.

thanks

Christian


On your last day only you will have to approve or disaprove of how your life has been.

Share this post


Link to post
Share on other sites

Hello All,

So When looking at download.php line 23

  $downloads_query = tep_db_query("select date_format(o.date_purchased, '%Y-%m-%d') as date_purchased_day, opd.download_maxdays, opd.download_count, opd.download_maxdays, opd.orders_products_filename from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS_PRODUCTS_DOWNLOAD . " opd, " . TABLE_ORDERS_STATUS . " os where o.customers_id = '" . $customer_id . "' and o.orders_id = '" . (int)$HTTP_GET_VARS['order'] . "' and o.orders_id = op.orders_id and op.orders_products_id = opd.orders_products_id and opd.orders_products_download_id = '" . (int)$HTTP_GET_VARS['id'] . "' and opd.orders_products_filename != '' and o.orders_status = os.orders_status_id and os.downloads_flag = '1' and os.language_id = '" . (int)$languages_id . "'");

 

At the very end of the line there is the call for the column order_status_id in the order status_table. When I check the order_status table in the data base it is clearly there. So when I check the osCommerce Online Merchant v2.2 RC2a Upgrade Notes in the

 Database changes there are no changes to that column. When I check old versions of the data base this table has alway been there. So despite the new call to it in the update of the download.php page there should be no problem. I don't understand how it is that this page can not refer to that column.

Also as I have followed the instructions very carefully I don't see why others are not having these exact problems.

I mean what does it take to get a little help here?

Thanks


On your last day only you will have to approve or disaprove of how your life has been.

Share this post


Link to post
Share on other sites

Ok I cleared the tep stop 1054 error code.

My current problem is that now that the page is displaying correctly there is no download link? Even on new orders.

Any suggestions?


On your last day only you will have to approve or disaprove of how your life has been.

Share this post


Link to post
Share on other sites

Have you find the solution to this yet? I'm very frustrated by this too so if anyone can help please, I'll be very appreciated!

Share this post


Link to post
Share on other sites

×