database restore problems - milestone release

[endee]

Help- I broke it!!

Just installed the new release, I probably went about it the wrong way, having problems with sql db.

 

Old install was fairly new- early Jan03.

 

Did a complete new installation.

Restored backed up DB, everything OK, no errors reported, all the info is there.

 

However when I go into "customers>orders>edit> I get the error

 

"1054 - Unknown column 'orders_status_id' in 'field list'

 

select orders_status_id, date_added, customer_notified, comments from orders_status_history where orders_id = '39' order by date_added

 

[TEP STOP]"

 

Some incompatibility between the 2 databases?

 

Any help much appreciated, how do I get the info from the old db into the right format for the new one? SQL still terrifies me!!

 

Neil

 

Neil

[Waza04]

What I think the problem is:

 

You have Customer Status installed by ELARI -

 

But you have not inserted the sql file(s) the came with the install with this mod.

 

Warren

[endee]

I don't think I do.

 

Got more errors now in checkout!!

 

"1054 - Unknown column 'orders_status_id' in 'field list'

 

insert into orders_status_history (orders_id, orders_status_id, date_added, customer_notified, comments) values ('43', '1', now(), '1', '')

 

[TEP STOP]"

 

I think what I need to do is use the new db and import the data from the old one.

Or at least the 'orders_status_id' in 'field list' - which seems to be causing the problem.

How difficult would that be (remembering my SQL phobia!).

Any useful websites with beginner SQL info?

 

Neil

[minglou]

i also got same problem,

 

orders

orders_products

orders_products_attributes

orders_products_download

orders_status

orders_status_history

orders_total

 

everything elso works well, beside the orders...

and i found there are 9 additonal fields in orders

 

billing_name

billing_company

billing_street_address

billing_suburb

billing_city

billing_postcode

billing_state

billing_country

billing_address_format_id

 

since i used older version of oscommerce, how can i restore from old DB to MS1 DB ( only orders got problems )?

 

thank you so much if anybody can help!!

[pages]

Me also I got the same problem.

Damned, I naver had a problem installing/upgrading TEP or OSC even it was releases or CVS files ! But now, folowing the install script to upgrade from a cvs (jan/2003) to official MS1, I was not so lucky.

 

First, after upgrading I got an "cupgrade complete with succes" messages, but it was followed bu some lines of Mysql errors. Then when playing around, I got

 

"1054 - Unknown column 'orders_status_id' in 'field list' 



insert into orders_status_history (orders_id, orders_status_id, date_added, customer_notified, comments) values ('43', '1', now(), '1', '')

 

 

It seems the upgrade of orders history doesn't works so well. It looks for something like orders_status_id field in orders_status_history which does not exist in this table.

 

Yhe question is how to fix it ? Is 'orders_status_id' a missed field or do we have to rename the 'orders_status_history_id' field or do something else.

 

And what do we have to fix in the install/upgrade script ?

[CC]

You guys should have checked the forums before you tried this.

 

It isnt as straight forward as just a quick upgrade.

Especially if you are trying this on a live server, or a server with mods on.

 

Best thing you can do is search the forum for answers or wait until someone has done this correctly and has documented it.

 

If yo have a test server, try and accomplish this as a test and then document it for the other users that need it.

 

CC.

[Mark Evans]

But now, folowing the install script to upgrade from a cvs (jan/2003) to official MS1, I was not so lucky.

 

The upgrade script is only to be used when upgrading 2.1 to 2.2-MS1 not from CVS to CVS.

 

You can see database changes as they happen by looking at this file

 

http://cvs.sourceforge.net/cgi-bin/viewcvs.../oscommerce.sql

[pages]

Thanks a lot Mark,

 

I thought that running the upgrade sould have no effect where no changes needed, and just make it where differences with the installed version were found. Anyway... (perhaps this sould be a feature in next upgrade scripts to avoid people using cvs versions have to risk data looses)

 

Is there now a way to move contents to the new orders_status_history table or do we have to do it manually or build our own script ?

 

And "f?licitations ? toute l'?quipe" for this great release !

[Paul_w]

I managed to upgrade the database from a January snapshot by using a File compare on the sql scripts from MS1 and the original snapshot. You can then use phpMyAdmin to make the changes to the database manually. I think it was only 3 minor changes needed. This seems to have worked without problem.

 

I would recommend backing up your original DB first of course!

[Paul_w]

You can fix the checkout problems by running these MySQL commands to partially update the database to to MS1 version

 

ALTER TABLE orders_status_history DROP new_value;

ALTER TABLE orders_status_history DROP old_value;

ALTER TABLE orders_status_history ADD orders_status_id int(5) NOT NULL default '0';

ALTER TABLE orders_status_history ADD comments text;

 

As I said before please backup first! and watch out for word-wrap on the above.

 

I have a sql text file that will update the database to the MS1 version from a january snapshot ~13th Jan. This will only make all the required modifications and will not overwrite any data already existing in the database. Let me know if this is any use to you.

[pages]

Thank you Paul, but in the meanwhile I had to resolve the problem by myself. Il found your message while I was doing it so it was reassuring to me to know that I was in the right way.

 

I changed tables like you, but I didn't drop the "new_value" field, I just renamed it to "orders_status_id", so I kept the date that were allready there.

 

So it looks like this:

 

ALTER TABLE orders_status_history CHANGE new_value orders_status_id INT (5);

ALTER TABLE orders_status_history DROP old_value; 

ALTER TABLE orders_status_history ADD comments text;

 

Then I applied this script to move the data (just "comments" field from ancient "orders" table to new to "orders_status_history" table:

<?php

$server = "";//your servers's name

$login = "";//your login

$pass "";//your password

$dbasename ="";//your databasename

MYSQL_CONNECT($server, $login, $pass) or die ("Unable to connect to server");

   mysql_select_db("$dbasename") or die ("Unable to select database");



   $result = mysql_query('select orders_id, orders_status, comments from orders where comments not like ""');

while(list($orders_id, $orders_status, $comments) = mysql_fetch_row($result)) {

    if (mysql_query('update orders_status_history set comments="$comments" where orders_id="$orders_id" and orders_status_id="$orders_status"')) {

	 echo "orders_id=$orders_id and orders_status=$orders_status updated with success<br>";

 } else {

	 echo "<font color="red">There is a problem</font> near orders_id=$orders_id and orders_status=$orders_status NOT updated<br>";

 }

}

?>

 

At the end I deleted comments field in orders "table"

ALTER TABLE orders DROP comments;

 

It works for me but comments with special caracters or with apostroph inside may not move, so be carefull that all data moved correctly befor drop the comments. And of course make a backup of your data before doing anything.

 

Hope it'll be usefull to someone, and thanks again for Mark's and Paul's contributions to this discussion.

[Paul_w]

Glad you got things working, after reading this thread this morning I went back throught the sql files and realised I had missed a fair few new updates/additions to the configuration table (That'll teach me to do this at ridiculous hours of the night :!: ).

 

I reckon on around 25 changes to the database from snapshot ~13th Jan.

[ukracer]

But now, folowing the install script to upgrade from a cvs (jan/2003) to official MS1, I was not so lucky.

 

The upgrade script is only to be used when upgrading 2.1 to 2.2-MS1 not from CVS to CVS.

 

You can see database changes as they happen by looking at this file

http://cvs.sourceforge.net/cgi-bin/viewcvs.../oscommerce.sql

 

Can you clarify I have understood this right?

 

I have major problem with getting a contrib working in a post nov snapshot BUT I cant use the script to update because I have too new a version. If I had installed the version 2.1 I could have updated with the minimum of fuss??

 

Surely I cant have understood this right!