How To Successfully Import Sql From Previous Version?

[Walrus]

I've installed a test store in order to resolve some template problems with the live store, so that I can play with it without having to close the live store.

 

I used the prefix ps2 for the new SQL database so as not to clash with the current database tables when I import.

 

Having exported the current database from the live store, I then imported it into the test store's database (using phpMyAdmin). The imported database has the prefix ps, so no problems importing. 

 

However, in Admin, I don't see anything from the imported database. I would expect to see the products, features, customer accounts, etc to display. I logged out and back in but it made no difference.

 

What am I doing wrong?

 

TIA.

[tdr170]

In the settings.inc.php file you have to change the prefix      define('_DB_PREFIX_', 'ps2_');

 

root files/config/settings.inc.php

 

When using a test store for testing new modules layout changes or what ever just use the same prefix this way you can import between different shops.

This is a very good practice to go with and the way I always do it, when I get a new module or create a new theme I always test on a test site until I am confident that it will install and configure without error.

I actually have several test sites and all use the same prefix.

 

 

One more thing when you export a database choose custom and then add drop table if exist you will be able to import a database to an existing database and just over wright it, always backup the database before importing new. 

Edited January 30, 2016 by tdr170 (see edit history)

[Walrus]

I've never understood this drop option or why you cannot say over write when a table already exists, instead of stopping an import dead in its tracks. I think you may have given me the info I have been looking for. To be sure, do you mean select "Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER statement" when I EXPORT database?

 

If I change prefix to 'ps_' to match the imported database's prefix, how do I get rid of all of the new 'ps2_' tables? I tried deleting new database, recreating it in cPanel, then importing the live store's exported database, but then couldn't log in, so had to start all over again. I now have lots of redundant tables. There's no way that I can see in phpMyAdmin to select multiple tables and then choose drop them.

 

Update: changing prefix to ps_ has same effect as deleting database then importing the ps_ prefixed database. Grrr! I now cannot login as I get employee doesn't exist (am using same admin settings for test and live store and employee - superAdmin - does exist). The login page also isn't formatted. Reverting to ps2_ prefix fixes login issues.

 

Thanks for the help, much appreciated.

Edited January 31, 2016 by Walrus (see edit history)

[tdr170]

First I will start with the tables issue, click on the main title of the database this will open a page on the right where you can see all tables if you look you will see on the left side of the right box there are little checkboxes instead on selecting 1 by 1 scroll to bottom and you will see a check box with select all click on that and it will check all tables, then from the drop down select drop. You may need to do this more then once as prestashop creates about 270 tables for an install, as you can see in my database section only 250 are shown per page.

 

 

 

 

So now lets fix the prefix issue, get this program Notepad++ https://notepad-plus-plus.org/ once you have it installed open the database with it with the prefix of ps2_ the first database table will be ps2_access use your mouse and highlight just the prefix ps2_, from the menu select Search, Find then go to the replace tab, you should see ps2_ in the top test area in the bottom put ps_ be sure you have this correct and have the underscore with both. Now on the right menu select replace all. Now you have this database with the ps_ prefix just like the original.

 

 

 

 

Now go to your database and create a second database you can use the same username and password as the first to make it easier they have different names so no worry about cross over you now you have 2 databases.

In the original database import the original stores tables back so the store is live again.

Import the test stores database to the new test store database.

 

You will now need to correct the settings.inc.php of the test store that originally had the ps2_ prefix , change the _DB_NAME_ and _DB_PREFIX_ to the new database names the database info.

 

 

Got this so far.

 

 

 

So now we want to use Live store database for the test store basically a duplicate, export the live store database and add the drop tables if exist, should look like image below do not select crate database.

 

 

 

 

 

Now this is done but I can not login to the test store anymore I get employee does not exist.

the easiest way to correct this is to go to the live stores files and in the settings.inc.php file we talked so much about you will need to copy the Keys in the bottom section.

You will need to copy this and in the test store replace those keys with these, now you can login and have a duplicate test site.

 

(do not use this just example)

define('_COOKIE_KEY_', '70cRbIhENo9yhdVCl2BeapeBdKLTC4wcNScHbi6ZQZqbtMvcOuuo30Sb');

define('_COOKIE_IV_', 'SbnZSTq1');

define('_RIJNDAEL_KEY_', '4Q98vRmu52UP1tVIDMVYwU02k6KP72Rl');

define('_RIJNDAEL_IV_', 'VpwZjiIgNTQkt1e1ToYTpM==');

 

 

Once logged in you will need to change the URL in Preferences, SEO & URLs to the URL, the domain will be the same but the url is different, if live store was in root you will see this

 /    

so test site is in folder names test you would change to  

/test/

 

 

Try these steps and let me know how it goes I think from here you could probably figure things out.

 

 

Edited February 2, 2016 by tdr170 (see edit history)

[Walrus]

Thanks for taking the time for a detailed reply. Have been too busy to respond. I had hoped just importing the database would be enough but seems not so without lots of work.

 

I will try starting with another clean install, exporting the live store database again but this time by telling it to drop duplicate tables on import though, then importing it.