Jump to content

Recommended Posts

14 minutes ago, TomB01 said:

I ended up with entirely different configurations than the default Phoenix.

Try moving everything but the configuration table (and sessions and whos_online). 

1 hour ago, TomB01 said:

Any clue on how to find out other fields that are shared among tables?

Try something like

    SELECT C1.COLUMN_NAME, COUNT(*) AS TOTAL
    FROM INFORMATION_SCHEMA.COLUMNS C1
      INNER JOIN INFORMATION_SCHEMA.COLUMNS C2
        ON C1.COLUMN_NAME = C2.COLUMN_NAME
       AND C1.TABLE_NAME != C2.TABLE_NAME
       AND C1.TABLE_SCHEMA = C2.TABLE_SCHEMA
    WHERE C1.TABLE_SCHEMA='YourDatabase'
    GROUP BY C1.COLUMN_NAME
    HAVING TOTAL > 1

 


Always back up before making changes.

Share this post


Link to post
Share on other sites

I usually just do everything bar the configuration and configuration_group tables (which are the two "controlling" database tables).

Then make sure that all the tables I just brought over have the same columns [and settings] as a core Phoenix.

 


Help shape the future of Phoenix; join the Phoenix Club

Share this post


Link to post
Share on other sites

Excuse me for going back to the well again, but Got SQL? (to list the settings of the tables)  In particular, can you confirm the settings that are important for Phoenix?

Is anyone working on a migrate-to-Phoenix-from-older-OsC Checklist?  That might be a small effort for potentially great reward.  I have these databases backed up six ways to Sunday, multiple copies of the databases,  and an operating sandbox.  So I can mess with parts without breaking anything, but it still seems daunting.

Share this post


Link to post
Share on other sites
47 minutes ago, TomB01 said:

but it still seems daunting.

Tom I would just follow Gary's advice and copy over the entire database, minus the configuration files.  You'll want to keep your Phoenix configuration files.  That is the easiest approach and exactly what I did....you'll be missing a couple of fields but Phoenix will complain about those and you can simply add them.   You'll end up with some data you don't need but it really doesn't matter...in my case I actually wanted that data.  I added extra fields over the years and didn't want to  loose any of that information I had collected.

Dan

Share this post


Link to post
Share on other sites

You could take it as an opportunity to do some house keeping with the db. Get rid of all the crud and slim it down and keep only the absolute necessary bits for the new shop.

It’s amazing how much junk build up in a db over the years.


 

Share this post


Link to post
Share on other sites
1 hour ago, JcMagpie said:

You could take it as an opportunity to do some house keeping with the db. Get rid of all the crud and slim it down and keep only the absolute necessary bits for the new shop.

It’s amazing how much junk build up in a db over the years.

More likely to break it, than slim it down - if I do that.

Share this post


Link to post
Share on other sites
3 hours ago, Dan Cole said:

Tom I would just follow Gary's advice and copy over the entire database, minus the configuration files.  You'll want to keep your Phoenix configuration files.  That is the easiest approach and exactly what I did....you'll be missing a couple of fields but Phoenix will complain about those and you can simply add them.   You'll end up with some data you don't need but it really doesn't matter...in my case I actually wanted that data.  I added extra fields over the years and didn't want to  loose any of that information I had collected.

Dan

OK - did that and promptly got error messages in the Admin and fatal errors in the Store.

I've been working on this, off and on, since Thanksgiving - including about two solid weeks when I was on vacation during Christmas/New Year's.  If I had tried this a month or so ago, I probably would've had a heart attack.  However, I've blown it up so much since then, that things are not phasing me so much anymore.  Using the sandbox helps immensely, though.

So ... the phpAdmin indicated a successful copy of all the tables - excepting the configuration, configuration_group, sessions, and whos_online were not copied. (Copy was done with phpAdmin as mentioned earlier in this thread.) Starting with the Phoenix Admin, it blew up on the Supporters Low Stock Alert and when accessing the Catalog->Categories/Products, not recognizing a couple of fields in the products table.  I researched that by comparing to another Phoenix database and discovered that products_cost and products_reorder_level had to be added.  In this case, I was able to reference the Readme files for the supporters' code (d18 and d20, to be exact) and run the SQL statements again (ALTER TABLE products ADD etc., etc.).

Checking the store, it blew up with error messages on the categories_description and products_description tables.  Turns out, Phoenix added four fields to the categories_description table and three fields to the products_description table.  Copying the Alter statement from above, I ended up with the following that I ran on the database:
 

Quote

 

ALTER TABLE categories_description ADD categories_description TEXT NULL;
ALTER TABLE categories_description ADD categories_seo_description TEXT NULL;
ALTER TABLE categories_description ADD categories_seo_keywords VARCHAR(128) NULL;
ALTER TABLE categories_description ADD categories_seo_title VARCHAR(128) NULL;


ALTER TABLE products_description ADD products_seo_description TEXT NULL;
ALTER TABLE products_description ADD products_seo_keywords VARCHAR(128) NULL;
ALTER TABLE products_description ADD products_seo_title VARCHAR(128) NULL;

 

I'm not sure this was documented anywhere.  At least, I haven't seen it while following the Phoenix threads since T-giving.  I'm coming from 2.3.4, so maybe these fields were in CE Gold or Edge?

Final step was to upload all my existing catalog images and it now appears my sandbox is a working version of my store in Phoenix, with all the supporters add-ons. 🙂

I will keep testing it for a couple of days and try porting everything over to my actual store this weekend.  I sort of hope Gary doesn't have another update between now and then. 😉

Edited by TomB01

Share this post


Link to post
Share on other sites
19 minutes ago, TomB01 said:

Final step was to upload all my existing catalog images and it now appears my sandbox is a working version of my store in Phoenix, with all the supporters add-ons. 🙂

Perfect....wasn't hard was it?  Congratulations.

20 minutes ago, TomB01 said:

I will keep testing it for a couple of days and try porting everything over to my actual store this weekend.  I sort of hope Gary doesn't have another update between now and then. 😉

Just because Gary releases an update doesn't mean that you need to stop what you're doing.  Get your store up and running and come back to that at some future date. 

Dan

Share this post


Link to post
Share on other sites
1 hour ago, Dan Cole said:

Perfect....wasn't hard was it?  Congratulations.

Just because Gary releases an update doesn't mean that you need to stop what you're doing.  Get your store up and running and come back to that at some future date. 

Dan

Yes - no disrespect meant toward Gary.  It's sort of like Santa Claus keeps pushing presents down the chimney, but you're still trying to unwrap the first one. 😀

Share this post


Link to post
Share on other sites
3 hours ago, Dan Cole said:

Just because Gary releases an update doesn't mean that you need to stop what you're doing.  Get your store up and running and come back to that at some future date. 

@TomB01

Exactly this.  This is the main reason why I output updates in bite-size chunks on a fairly regular basis.  

It allows those who want to keep up-to-date the ability to do it in an easy 5 or 10 minutes.  It also allows those who can't or don't want to take that time to come back to it at a later date or even to disregard the point updates entirely and just do an update blowout on each .0 release.

Point being;  take your time. 

If there was anything super important to get done "NOW" (eg if a insecure thing appeared or some show-stopping bug was introduced between versions) I'd be sure to mention that in the relevant version update post to try to get shopowners to get that part of an update done pronto.  


Help shape the future of Phoenix; join the Phoenix Club

Share this post


Link to post
Share on other sites

@burt,

Ha!  Right on cue: you just dropped another supporters code down the chimney!  😉

Share this post


Link to post
Share on other sites
On 1/22/2020 at 5:54 PM, JcMagpie said:

You could take it as an opportunity to do some house keeping with the db. Get rid of all the crud and slim it down and keep only the absolute necessary bits for the new shop.

It’s amazing how much junk build up in a db over the years.

2/3 in my case

Share this post


Link to post
Share on other sites
On 1/22/2020 at 10:51 AM, TomB01 said:

OK - did that and promptly got error messages in the Admin and fatal errors in the Store.

I've been working on this, off and on, since Thanksgiving - including about two solid weeks when I was on vacation during Christmas/New Year's.  If I had tried this a month or so ago, I probably would've had a heart attack.  However, I've blown it up so much since then, that things are not phasing me so much anymore.  Using the sandbox helps immensely, though.

So ... the phpAdmin indicated a successful copy of all the tables - excepting the configuration, configuration_group, sessions, and whos_online were not copied. (Copy was done with phpAdmin as mentioned earlier in this thread.) Starting with the Phoenix Admin, it blew up on the Supporters Low Stock Alert and when accessing the Catalog->Categories/Products, not recognizing a couple of fields in the products table.  I researched that by comparing to another Phoenix database and discovered that products_cost and products_reorder_level had to be added.  In this case, I was able to reference the Readme files for the supporters' code (d18 and d20, to be exact) and run the SQL statements again (ALTER TABLE products ADD etc., etc.).

Checking the store, it blew up with error messages on the categories_description and products_description tables.  Turns out, Phoenix added four fields to the categories_description table and three fields to the products_description table.  Copying the Alter statement from above, I ended up with the following that I ran on the database:

Quote

 

ALTER TABLE categories_description ADD categories_description TEXT NULL;
ALTER TABLE categories_description ADD categories_seo_description TEXT NULL;
ALTER TABLE categories_description ADD categories_seo_keywords VARCHAR(128) NULL;
ALTER TABLE categories_description ADD categories_seo_title VARCHAR(128) NULL;


ALTER TABLE products_description ADD products_seo_description TEXT NULL;
ALTER TABLE products_description ADD products_seo_keywords VARCHAR(128) NULL;
ALTER TABLE products_description ADD products_seo_title VARCHAR(128) NULL;

 

I'm not sure this was documented anywhere.  At least, I haven't seen it while following the Phoenix threads since T-giving.  I'm coming from 2.3.4, so maybe these fields were in CE Gold or Edge?

Final step was to upload all my existing catalog images and it now appears my sandbox is a working version of my store in Phoenix, with all the supporters add-ons. 🙂

I will keep testing it for a couple of days and try porting everything over to my actual store this weekend.  I sort of hope Gary doesn't have another update between now and then. 😉

Tom, Thanks for this, long time OsC user here (going back to MS2.2) and now moving away from OsC 2.3.4, would like to import as much as possible. As others have mentioned I imported the database minus config tables, and ended up with an error that I was able to get rid of by running the code above. All products are shown on the store, however, I'm getting an error when I click on a product at the shop,

 

1054 - Unknown column 'p.products_gtin' in 'field list'

select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, pd.products_url, p.products_price, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id, p.products_gtin from products p, products_description pd where p.products_status = '1' and p.products_id = '1637' and pd.products_id = p.products_id and pd.language_id = '3'

[TEP STOP]

 

Any thoughts to get past this? 

Thank you.

 

 

 

 

 

 

 

222.png

Edited by Krisz1

Share this post


Link to post
Share on other sites
1 hour ago, Krisz1 said:

Any thoughts to get past this? 

You need to add that field to your products table.  That field was missing in my database at one point too.   Back up your database and then try adding it.  I did so using...

ALTER TABLE `products` ADD `products_gtin` CHAR(14) NULL AFTER `products_ordered`; 

Dan

 

Share this post


Link to post
Share on other sites
2 hours ago, Dan Cole said:

You need to add that field to your products table.  That field was missing in my database at one point too.   Back up your database and then try adding it.  I did so using...

ALTER TABLE `products` ADD `products_gtin` CHAR(14) NULL AFTER `products_ordered`; 

Dan

 

Thank you !

Share this post


Link to post
Share on other sites

Dan is correct and glad you found the solution.

To others - if this will help - take a look at that error message that Krisz1 got:

Quote

1054 - Unknown column 'p.products_gtin' in 'field list'

select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, pd.products_url, p.products_price, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id, p.products_gtin from products p, products_description pd where p.products_status = '1' and p.products_id = '1637' and pd.products_id = p.products_id and pd.language_id = '3'

I'm sure this is like explaining 2+2 to some of you, but when I saw similar messages, I keyed on the "Unknown column" part.  That tells you there's a field (column) missing in the database table the follow-on SQL statement is trying to access.  Then I looked in the SQL "Select" statement for the "from" word.  The name that comes after the "from" word in the SQL statement is the database table.  You will also see the "products_gtin" field name in the collection of field names that the "Select" statement is trying to gather.  So in this case, "products p" looks like the source table for the SQL statement.  I don't know enough about SQL to know if the comma after that is specifying another table.  I also don't know what the "p" means at the end of "products," or at the beginning of the field (column) and table names, but that doesn't matter.  I'm going to check the "products" table, first. 

With those partial bits of information, go to the phpAdmin (or whatever client you're using) and find the "products" table in the database.  Look at the structure for the "products" table in 2.3.4 and then look at the structure for the "products" table in Phoenix.  In phpAdmin, there's an actual tab for "Structure."  That should show you the extra fields (columns) existing in 2.3.4 that you need to add to Phoenix.  You should see the "products_gtin" field in the 2.3.4 "products" table (in this example).  Develop a similar SQL "Alter" statement as above, adjusting/editing for the variable type (TEXT, VARCHAR(##), etc.).  Run that in the SQL window in phpAdmin on the database (don't touch the 2.3.4 one), then re-load the Phoenix store page that caused the error.  It should go away.  Or, at least the error changes to something else that you can also research similarly.  Eventually, you should arrive at no errors when you re-load the store's page in question.

Interestingly, I don't have a "products_gtin" field in my "products" table - either 2.3.4 or Phoenix.  That's probably because I never installed the same add-ons that you guys did.  So, maybe everyone arguing against a do-all script is correct.  I had a horrible experience in the past trying to use an all-at-once SQL script for porting an OsC database (coming from MS2.2 to 2.3.4).  Granted, it's not a single button-push, but maybe it really is better to encourage the logic and method of manually copying the database.

Remember to back up, back up, back up - both before and after. 😉  This is very rudimentary, but hope it helps ...

Edited by TomB01

Share this post


Link to post
Share on other sites

While the SQL 'select' statement is written as one long sentence, I like to format it into several lines, making it easier for me to read. So, I'll format it like this:

select p.products_id,
       pd.products_name,
       pd.products_description,
       p.products_model,
       p.products_quantity,
       p.products_image,
       pd.products_url,
       p.products_price,
       p.products_tax_class_id,
       p.products_date_added,
       p.products_date_available,
       p.manufacturers_id,
       p.products_gtin
from products p,
     products_description pd
where p.products_status = '1' 
  and p.products_id = '1637' 
  and pd.products_id = p.products_id 
  and pd.language_id = '3'

This breaks the 'select' statement into its three components:

Select .... this lists the fields it wants to select.

From .... this lists the tables in which to find the fields. Note that the table names are followed by a letter or two. This is the same letter preceding the fields in the Select list, letting the Select command know which table the field is in

Where .... this lists the conditions for the search. Only select the records if these conditions are true.

And, as @TomB01 pointed out, there error is saying that the Select function is asking for a field that does not exist in the appropriate table.

HTH

Malcolm


Get the latest Responsive osCommerce CE (community edition) here .

Share this post


Link to post
Share on other sites
27 minutes ago, TomB01 said:

I also don't know what the "p" means at the end of "products," or at the beginning of the field (column) and table names, but that doesn't matter.

It can help to start reading this helpful site: https://www.w3schools.com/sql/sql_intro.asp

Regarding the "p" ... it could be every letter ..  But it kind of relates the columns to the correct table. Like eg: 

 SELECT  x.column_1, x.column_2 FROM table x

 

Hope this helps!

 

Edited by azpro

Share this post


Link to post
Share on other sites
2 hours ago, azpro said:

It can help to start reading this helpful site: https://www.w3schools.com/sql/sql_intro.asp

Regarding the "p" ... it could be every letter ..  But it kind of relates the columns to the correct table. Like eg: 


 SELECT  x.column_1, x.column_2 FROM table x

 

Hope this helps!

 

So, Select "p.field1, p.field2. pd.field1, pdfield2 from tablename1 p, tablename2 pd;" is using an arbitrary character(s) to distinguish between different tables in the same Select statement?  I guess ArtcoInc is showing that, too, the way he expanded the SQL statement.

Cool! 

Share this post


Link to post
Share on other sites

@TomB01

45 minutes ago, TomB01 said:

So, Select "p.field1, p.field2. pd.field1, pdfield2 from tablename1 p, tablename2 pd;" is using an arbitrary character(s) to distinguish between different tables in the same Select statement?  I guess ArtcoInc is showing that, too, the way he expanded the SQL statement.

Cool! 

Typical convention uses the table name to come up the the 'arbitrary' character(s). So, the table 'products' would use the letter 'p', the table 'products_description' would use the letters 'pd', etc.

Malcolm


Get the latest Responsive osCommerce CE (community edition) here .

Share this post


Link to post
Share on other sites

Incidentally, you can also use the full table name, e.g.

SELECT table1.field2, table2.field1, table3.field3 FROM table1, table2, table3 WHERE 

unless you are doing a sub-select, which creates a virtual table that has no name (and thus needs the alias). 

While the aliases can be arbitrary, it is helpful if you can be consistent with what people have previously done.  E.g. p2c for products_to_categories. 


Always back up before making changes.

Share this post


Link to post
Share on other sites

Turns out, I needed this, too:

ALTER TABLE `products` ADD `products_gtin` CHAR(14) NULL AFTER `products_ordered`; 

I just hadn't gone far enough in my testing.  This is an additional field that was added in Phoenix (or sometime after 2.3.4), but is ordered before two other fields in the structure list.  Thus, the "AFTER 'products_ordered'.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×