[BETA2] Upgrade Script 1.4.x to 1.5.1.2 - NO PRODUCT OPTIONS

Qphoria wrote:First go here:
http://www.hookedonphonics.com/learn-to ... KgodTlTolg

Which version do I need ? Pre-K, Kindergarten, 1st grade or 2nd grade

jty wrote:

Qphoria wrote:First go here:
http://www.hookedonphonics.com/learn-to ... KgodTlTolg

Which version do I need ? Pre-K, Kindergarten, 1st grade or 2nd grade

Best get them all for opencart reading

I'm going through the process of upgrading a 1.4.x site to 1.5 (luckily I've got a test site setup, since it is one hell of an upheaval for a non-major release number change) and the script is useful, but the lack of product options really isn't.

Luckily, I'm a dev, so I've had a compare of the databases. Basically, all that happened is that the databases were normalised so that you've now got "options" as separate entities from "product options". That allows re-use, which is a good idea. I'm just not sure why the conversion script missed those options, since a reasonable sized store could have quite a few products that use them.

Yeah, it won't be neat and pretty and the options wouldn't be consolidated, but it looks like quite a simple set of seven INSERT INTO...SELECT... queries should do the job and leave you in a better position than not having them at all.

Once I've written the queries and run them then I'll post them here, although I'll have to pull my data from a backup as the upgrade process seems to have lost all of the data rather than keeping it safe somewhere but not using it.

IBBoard wrote:Once I've written the queries and run them then I'll post them here, although I'll have to pull my data from a backup as the upgrade process seems to have lost all of the data rather than keeping it safe somewhere but not using it.

If you could / would do that: Thanks!

IBBoard wrote:Yeah, it won't be neat and pretty and the options wouldn't be consolidated, but it looks like quite a simple set of seven INSERT INTO...SELECT... queries should do the job and leave you in a better position than not having them at all.

I think it's more complicated than you're anticipating, because the old options data is split differently in the new tables. I'd be surprised if you got it working in seven INSERT statements, but if you do, I'll be impressed!

All of the data is there. Nothing has been lost, it has just been split up. I've got the notes and mappings right in front of me, I just need to write the queries and test it.

It will need some defaults adding in places, and what I'm planning won't consolidate matching options (e.g. 50 duplicates of "Size" with values S, M and L), but IMO what you'll end up with should be infinitely preferable to just going "you get all of your data copied, except options - you've got to re-enter all of that yourself".

Once all of the data is across then consolidating should be possible, but I'll take it one step at a time (especially when my wife's store has a few alternate spellings, and all options being called "size" whether they're for rings or belts).


[edit] Still not quite working on it yet. I have just realised that 7 INSERT statements is a bit optimistic, though. That'll work if the data was in differently named tables, but I guess it won't be, so it'll take a few more queries but the generally idea is there

Is there a free tool to compare 2 databases? I made a copy of my livesystem, upgraded it and now im setting up the settings, the templates and extensions. But the original database wont stay at the same status. Which means i want to make this version live, i will setup maintenance mode and then would need to find out what is changed and copy this to the new database.

Making the upgrade again and all settings, templates and so on probably would take very long.

So is there such a comparetool?

Thanks!
Sebastian

Just a quick note to say that I'm almost there with the product options stuff. Only issues at the moment seem to be around the pre-parsing the script does with the SQL. The statements themselves should run fine, but one gets corrupted (merged with the following statement) and others don't appear to execute in their entirety (columns aren't added/modified). Other than that, the "get the data, even if it isn't consolidated" querying has been quite simple.

SebastianJu wrote:Is there a free tool to compare 2 databases? I made a copy of my livesystem, upgraded it and now im setting up the settings, the templates and extensions. But the original database wont stay at the same status. Which means i want to make this version live, i will setup maintenance mode and then would need to find out what is changed and copy this to the new database.

Making the upgrade again and all settings, templates and so on probably would take very long.

So is there such a comparetool?

Thanks!
Sebastian

This is what I use to create upgrade patches for each version, including the sql part of the beta2 upgrade script.
http://www.mysqldiff.org/

It is the only tool I found that converts differences into modify and alter statements. It has worked perfectly thus far for adding/removing columns. But you must be very familiar with what should be merged and what shouldn't be.. And it is a little quirky to setup if you don't know how to work with databases.. So YMMV.

Thanks... that looks interesting...

Okay, so my query count was a bit optimistic (and based on incorrect assumptions) but here is a fixed version that a) copies all of the product options across (but doesn't consolidate them yet) and b) fixes a couple of bugs, thereby removing the warnings. Unfortunately I haven't been able to test it yet as I just managed to rm -rf /path/to/opencart instead of rm -rf /path/to/opencart/install. Everything looks okay in the database, though.

NOTE: If you ran earlier versions of this script from Qphoria and ignored the warnings then you'll have lost your history of who used which coupons with which order. Whether that is important to you is another matter.


[Edit] Hold that thought. Now that I've fixed the warnings with the checks before changes to the coupon_history table, it does insert the data *but* it appears to be incomplete. I've got two coupons instead of three, and all coupon histories show a coupon_id of 0. I'll either fix that bug later or let someone else do it. For now I've got to rebuild the work I lost.

IBBoard wrote: NOTE: If you ran earlier versions of this script from Qphoria and ignored the warnings then you'll have lost your history of who used which coupons with which order. Whether that is important to you is another matter.

14x didn't track coupon history so there was nothing to save. Coupon history was added in 1.5.x

Excellent script thank you!

I am now on Version 1.5.1.3.

I noticed compared to my clean 1.5.1.3 install there are less status options in the "PayPal standard" payment module.

EG - In the "Expired Status:" drop down there is no "Expired" option as there is in my clean install.

Is this related to the upgrade process/this script?

Hi, this has worked great BUT when I click on a product I get:

Error: Unknown column 'a.attribute_group_id' in 'on clause'
Error No: 1054
SELECT ag.attribute_group_id, agd.name FROM oc_product_attribute pa LEFT JOIN oc_attribute a ON (pa.attribute_id = a.attribute_id) LEFT JOIN oc_attribute_group ag ON (a.attribute_group_id = ag.attribute_group_id) LEFT JOIN oc_attribute_group_description agd ON (ag.attribute_group_id = agd.attribute_group_id) WHERE pa.product_id = '172' AND agd.language_id = '1' GROUP BY ag.attribute_group_id ORDER BY ag.sort_order, agd.name

any ideas?

fred: I've just looked and the table should be created by the upgrade script, including that column. The only way that column wouldn't exist is if you already had a table by that name. I'm not sure what might cause that, other than existing MODs.

Qphoria: Sorry to disagree, but it looks to me like there is something to track. Your comment in the SQL (I'm assuming you wrote all of the SQL) even says as much: If I look at my 1.4.x database then the order table does have a column called "coupon_id", and a very small number have an ID in there that matches a coupon in the coupon table.

Four of the errors/warnings when I ran the code were from the "SHOW COLUMN" statement bailing because it took "oc_order" as the table name, stripped the table prefix (I don't use one) and then errored because "order" was used as a table name but wasn't quoted and so it clashed with the keyword. That then made the get_num_rows return 0, which caused that INSERT INTO SELECT statement not to be run. If I fixed it by putting quotes in the one relevant query (see attached diffs for all of my changes) and removed all error suppression then I got data in that table (albeit not useful data yet - only 15 rows, none of which are actually uses of a coupon) and didn't get any errors.

I still need to do some tweaking until I'm happy with my modified version. If you want to take the changes (including the code to remove warnings) then by all means take it. I don't know whether your script was meant to be GPLed, the same as the main code base, but I'm happy for my changes to be GPLed. If you don't want to take the changes then they're already on the forum and I'm happy for anyone else to make use of them if they want my changes and are happy to accept that (for now) the options are copied but not consolidated.

ok, so what do i need to do?

If I remove the current database, add the old one from version 1.4.9.4 then add the update script again will that work?

right I have deleted the upgrade database, installed the database from 1.4.9.4 and then re done upgrade script and its still showing the error, please help...

The quick-fix (which ignores the underlying problem, whatever causes it) is to go to the database using something like phpMyAdmin and add a column called "attribute_group_id" to the table "attribute" which has a type "int" and doesn't allow nulls.

To try and work out what the problem is, go back to the 1.4 database, look at it in phpMyAdmin or similar and tell us what columns you can see in the attribute table. Telling us any MODs you added might help as well. If it isn't there then tell us if there were any errors or warnings during the upgrade process.

OK I have php my admin, in attribute there is currently:

attribute_id ]
sort_order

how do I add attribute_group_id?

If that's with your old 1.4 database (before you've done the upgrade) then the following comes with warnings. I've not looked at how the database *actually* changed over time, only the changes that Qphoria's script makes. It tries to create that table from scratch, so it shouldn't exist (by my reasoning).

Your options are:

1) run the query "ALTER TABLE attribute ADD attribute_group_id int(11) NOT NULL AFTER attribute_id;" through phpMyAdmin's "Query" section and hope that there aren't any other bits of table that are there that the upgrade isn't expecting

or

2) Drop the entire table and let the upgrade recreate it, and then hope that you don't have any data in there (or don't care about losing it) and that there aren't any other bits of table that are there that the upgrade isn't expecting

Unfortunately, once you get to the "not quite in the vanilla state that the script expects" position then it is a bit of trial and error to get it working using automated means.