Tutorial to Compare and Repair Database Tables between fresh install and upgraded store

[Ulisses Ramos]

After many upgrades since 1.2.x i noticed that i´ve had many differences between a clean install and a upgraded install on the same version

These are the steps to compare and repair the table squeme

 

1- Install a new prestashop with demo products on the domain ( same version as your current store)

 

2- Download and install http://www.dbsolo.com/download.html . Choose your version.

 

3- If your accessing from another computer you have to give access to your root account.

-Go to phpmyadmin / Users and select EDIT PRIVILEGES on the ROOT Account.

-On that menu goto "Change Login Information / Copy User" and on "Host:" select on the dropdown menu "ANY HOST". on the field in front a "%" will appear. Click "GO"

-Backup your Current store database

 

4-Open DBSOLO

-On the menu to add server, configure your server. Choose "MYSQL-JDBC" and enter all your data

-After that connect to your server

 

5- On DBSOLO goto "Tools -> Compare Squemas"

-On the left block choose the new prestashop install database and on the right choose your store.

-Press next 2 times until a proccess of comparison begins.

-Click "Only show object that do not match" on the middle of the menu.

-On the right menu, bellow the list of destination database, you have some buttons. Click on the fourth one from the left that looks like a papyrus script. "View SQL script to syncronize destination with source".

-On the next menu make sure that all is selected except

---"Always use DROP/CREATE for modified tables

---on the next menu uncheck "MISSING" so the tables that exist on the destination like PS_CMS_xxx and do not exist on a new install aren´t deleted.

-On "Show identifiers with" select the first " Quotes " " "

-Press next and a script will appear to match the tables properties from the source( (Fresh installation) to the destination (Your store)

 

This saved me a lot of problems and solved a lot of errors that i had due to table differences.

 

I always upgraded the store the correct way, but some differences always appear.

 

After running the script on PHPMyAdmin on the destination database, if you check the differences again.. you will see that they were corrected.

 

After you finish the comparison, in "PhpMyAdmin -> Users" delete the Root account that can be accessed by % to prevent security issues

Edited October 10, 2012 by oriflameorge (see edit history)

[Rhobur]

Great tutorial, I badly need a such solution!

I followed the steps but when running the generated SQL commands I get the error :

 

-- ps_address

--

ALTER TABLE "ps_address" CHANGE COLUMN "company" "company"VARCHAR( 32 ) NULL COMMENT '';

 

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"ps_address" CHANGE COLUMN "company" "company" VARCHAR(32) NULL COMMENT ''' at line 5

 

 

I am trying to synchronize the fresh 1.5.2.0 to actual 1.4.8.2 DB.

 

Any thoughts of what can I do ?

Regards,

Robert

 

Edit: I don't know why but in the error message there is no space between "company" and VARCHAR; in the generated script however it is correct :

ALTER TABLE "ps_address" CHANGE COLUMN "company" "company" VARCHAR(32) NULL COMMENT '';

DROP INDEX "id_warehouse" ON "ps_address";

ALTER TABLE "ps_address" DROP COLUMN "id_warehouse";

 

EDIT 2: My bad!!! I was selecting to generate the script from the left side instead of the right!

I will test again with the right script this time.

Edited December 7, 2012 by Caprice (see edit history)

[Ulisses Ramos]

ALTER TABLE "ps_address" CHANGE COLUMN "company" "company"VARCHAR( 32 ) NULL COMMENT '';

 

I think that there is a SPACE missing between "company" and VARCHAR

[Rhobur]

@Ulisses : Thank you immensely, pal!

 

After trying for several days with phpMyAdmin synchronization option I was about giving up.

I have managed to update the schema using your method .

There were a bunch of SQL errors when running the generated script and I had to solve them by hand, manually dropping, creating, adding indexes or columns for some (about 6-7 tables) but eventually all was OK.

I have checked the updated DB and all changes seem applied, the DBSOLO app is showing no differences.

The only drawback is that phpMyadmin is still reporting needed synchronization don't know why but I don' really care.

Now I can proceed with the next step, the upgrade from 1.4.8.2 to 1.5.2.0

Have a great weekend, man!

[cmat]

so i am having major issues after upgrade (i.e. only catagories and manufatures show up on FO/BO and maybe 20 items total. when infact i have 1000 +) look here vmotorsports.com. first question which is a dumb one how do you intall a second fresh copy of prestashop with out removeing the first? next is the table ps_products where all the products are held? also the ps_version_db ont he database that the site is using now is 1.4.2.5 and the installed version of prestashop is 1.4.5.1 can that cause a problem? thanx for any info

[paperx]

Hy , i've the same problem too

But I solved

 

- follow ULISSES RAMOS TUTORIAL

 but if u're Linux or mac (unix) user u've to change sintax like i show below:

 

You get this code ( it generte sintax error)
-- ps_advice
DROP TABLE "ps_advice";
-- ps_advice_lang
DROP TABLE "ps_advice_lang";

 

Clean the code

 

-- ps_advice
DROP TABLE ps_advice;
-- ps_advice_lang
DROP TABLE ps_advice_lang;

 

and copy all on SQL

then launch start

 

Hope to solve some of our problem

Greetings