Export/import only some database tables - customers/orders/products - to new shop version ?

[kevin78]

Hello

 

i am using Prestashop multishop 1.5.6.3

But there are some bugs, and i would like to solve them and in same time, upgrade to version 1.6.1.1

 

So my idea is to create a new database, new shop, clean, fresh and nice

Then, i would like to import all my customers tables, orders tables, suppliers, manufacturers, and categories tables.

 

Could you tell me if is it possible ?

Are these tables are all same between 1.5.6.3 and 1.6.1.1 ?

[musicmaster]

Yes there are quite a few database changes, specially between 1.6.0.14 and 1.6.1.0. You can see which database changes are implemented in the install directory. Under that directory you will find the directory \install\upgrade\sql. There you will find files with names like "1.6.1.0.sql". Each file tells what database changes are implemented from the previous version. In most case this concerns just changing indexes - what isn't usually that interesting, but with 1.6.1.0 (and to a lesser extent 1.6.0.11) you have some real changes.

 

I have a made some tool that tries to do something similar to what you want to do. So you might consider using it as a basis for your efforts:

https://www.prestashop.com/forums/topic/445453-copy-shopdata-script-for-copying-shop-content-for-upgrade/

[kevin78]

Hi Music,

 

thanks a lot.

You script is working between different prestashop version as well ?

I mean, can i use it for exporting date from 1.5.3.1 to 1.6.1.0 ?

[tuk66]

I would suggest to use the 1-Click Upgrade module. It replaces all files and manages the database upgrade. It much better to upgrade this way and then eventually remove unwanted records. You can never know how the data in the new version changes.

[kevin78]

No, my goal is to not use the 1-click upgrade module.

I have too much old modules since prestashop 1.2

And after many upgrades since 1.2, some tables are still existing.

And some errors still persists.

I want to clean all the databases in order to start with a clean shop.

[tuk66]

This is a very difficult task if you want to preserve orders.

[kevin78]

Table ps_orders look like to be the same.

Isn't it ?

 

Other way which could be a solution :

- upgrade my 1.5.3 to 1.6.1, then extract only data tables (customers, orders, categories, products, etc.)

- make a new installation and import these tables

[tuk66]

Download the latest version and look in the /install/upgrade/sql and php directories. All changes are there.

[musicmaster]

Hi Music,

 

thanks a lot.

You script is working between different prestashop version as well ?

I mean, can i use it for exporting date from 1.5.3.1 to 1.6.1.0 ?

Hi Kevin,

 

If you read the documentation of the script (that is in the config file) you will see that it tells you to upgrade the old shop (or a copy of it) first with 1-click upgrade. The goal is to get the database in the correct format. It doesn't matter that the upgraded shop may be unstable. Usually the instability is in the configuration and you leave that behind when you transfer the tables with the script.

 

If for some reason you can't use 1-click upgrade at all the recommended solution is to upgrade to 1.6.0.10 or earlier. That way you stay before the main database changes. You can always upgrade further with 1-click upgrade from there.

 

An example of the differences is that in PS 1.6.1 Prestashop has added a field id_product to the ps_product_attribute_shop table. That is an optimization: the id_product_attribute field in that table is always linked to one product only. But looking it up in another table takes extra time...

 

This script is basically an export and import function with a few extras. That way it can handle changes in indexes. But it will leave new fields like the one mentioned just empty.

 

M

[kevin78]

I checked your script.

But i dont really understand what does it bring exactly.

On your script, you ask to upgrade the old shop with the PS Autoupgrade.

If we use this "normal" upgrade solution, it should upgraded my old shop (1.5.6.3) to the latest version (1.6.1.1).

With all my datas.

 

So what your script will do more than the PS Autoupgrade ?

I have checked all SQL modification from 1.5.6.3 to the 1.6.1.1

And here some modifications which is affected the important data which i need (like tables orders, users, products, categories) :

 

Most modifications start in 1.6.0.1 and 1.6.0.10 - 1.6.1.x :

CREATE TABLE IF NOT EXISTS `PREFIX_order_slip_detail_tax`
ALTER TABLE `PREFIX_tax_rules_group`
UPDATE `PREFIX_tax_rules_group`
ALTER TABLE `PREFIX_order_detail`
UPDATE `PREFIX_category` SET `is_root_category`
ALTER TABLE `PREFIX_orders`

etc.

[musicmaster]

If you have a successful upgrade with 1-click upgrade: congratulations! You don't need my script.

 

The script is meant for those people who after the upgrade are left with an unusable shop. Sometimes upgrades don't even complete. Other times they complete but the upgraded shop is so unstable that it cannot be used. The instability usually is in the configuration and script separates the configuration from the business data.

[kevin78]

Ah yes; now i understand

It is usefull when the 1-click upgrade doesnt work.

 

I will try a 1-click upgrade.

But my original question was to start a new shop v1.6.1.1 and put the tables data from the 1.5.6.3

I think, i will proceed like this :

 

- upgrade my 1.5.6.3 to 1.6.1.1 : i hope it will be OK. If not, i hope it will at least modify correctly the database, in order to export the most important tables (like in your script).

- install the new 1.6.1.1 : i clean it. And i import the exported tables before.

 

I think that is like your script, right ?

[ietax]

sorry I received this error

 

5 old languages; 5 new languages.

Both shops need to have the same languages defined!

 

 

languages ID should be same order?

 

 

thanks

[musicmaster]

sorry I received this error

 

5 old languages; 5 new languages.

Both shops need to have the same languages defined!

 

 

languages ID should be same order?

 

 

thanks

No, after copying the table contents it will correct for the order of the languages.

 

Are you sure you don't have differences in the languages listed? Even ones like between English and English US?

[ietax]

Ok i corrected ID language and it works

 

But... I have access error from backoffice

 

There is one error.

1. The Employee does not exist, or the password provided is incorrect.

thanks

[musicmaster]

Did you change your Ryndael keys in settings.inc.php, as specified in the description?

[kevin78]

Hello Musicmaster,

 

In order to upgrade my shop 1.5.6.3. to the 1.6.1.1 with a "cleaning" step, i am testing your script today, in this way below :

 

1- i will create a new shop 1.5.6.3. and erase all data (with the native prestashop module "cleaner")

2- i will start the the copy_shopdata.php, without any modules, any themplates.

3- i will have a copy of my 1.5.6.3. - new, without modules, etc.

4- i will upgrade with 1-click Upgrade to 1.6.1.1

5- i will add the new templates and modules

 

 

But right now, im at the step 2.

I have this error :

MySQL error 1: Can't create/write to file '/var/www/my-admin/triple/copy_shopdata_address.dtx' (Errcode: 13)

 

I think it is a permission error.

Could you tell me which user is used for creating the dtx files please ?

 

 

Edit : i change the persmissions to 777 (with 775 it was still not enough).

It is working. All datas have been correctly transfered but i have one problem : special character has not been applied.

Like : é - è - à

Edited October 22, 2015 by kevin78 (see edit history)

[musicmaster]

Glad you made it one step further.

 

Unfortunately I am too busy for the moment to look at the the accent problem. I did a quick look on internet and found some terms that might help, but I haven't the time to spend testing them now:

SET collation_connection = 'utf8_general_ci';

SET CHARACTER SET 'utf8';

LOAD DATA INFILE '/path/to/file' INTO TABLE imported_table CHARACTER SET 'latin1'

[kevin78]

Sorry to disturb you...

but where should i put these codes ?

In your copy_shopdata.php file ?

 

For example, in the line 311 :

 

$query = "LOAD DATA INFILE '".$tablefile."' INTO TABLE "._DB_PREFIX_.$table."";

 

Or line 347 :

 

$query = "LOAD DATA INFILE '".$tablefile."' INTO TABLE "._DB_PREFIX_.$table."";

[musicmaster]

You could try changing both lines into

$query = "LOAD DATA INFILE '".$tablefile."' INTO TABLE "._DB_PREFIX_.$table." CHARACTER SET 'utf8'";

The other two commands are MySQL commands. You could add them near the top of copy-shopdata.php after the link to the old database has been opened. It will look something like:

  $query = "SET collation_connection = 'utf8_general_ci'";
  $result = mysqli_query($oldconn, $query);
  $query = "SET CHARACTER SET 'utf8'";
  $result = mysqli_query($oldconn, $query);
  $query = "SET collation_connection = 'utf8_general_ci'";
  $result = mysqli_query($conn, $query);
  $query = "SET CHARACTER SET 'utf8'";
  $result = mysqli_query($conn, $query);

[kevin78]

Thnks again.

I made this modification only :

 

$query = "LOAD DATA INFILE '".$tablefile."' INTO TABLE "._DB_PREFIX_.$table." CHARACTER SET 'utf8'";

 

And everything looks like to be ok.

Im on multishop. And it is working also, just need to make some settings and everything look like to be ok.

Now, i have a "new" 1.5.6.3 without any module, without any template, but with all datas.

I will upgrade to 1.6.1.1 tomorrow.

 

 

Edit : upgrade to 1.6.1.1 perfectly without any problem, and solve some bug i had with the last 1.5.6.3

i will continue my test this week, and go to production next week

Edited October 22, 2015 by kevin78 (see edit history)

[ietax]

Hi, and sorry again.

 

I used your script and I did not receive errors. but i cannot access on BO.

I set user and pw but I have same page.

I used Ryndael keys and cookie keys from old version.

 

 

thanks a lot.

[musicmaster]

Did you update the ps_shop_url table and set the correct domain and subdirectory?

[ietax]

I tried to do it on server, not in local. but I have this error

 

Parse error: syntax error, unexpected '>' in /home/**/**/admin*****/triple/copy_shopdata_functions.inc.php on line 132

 

 

I have configurated correct DB data into copy_shopdata_config

 

thanks

[ietax]

I found an error on copy_shopdata_functions.inc on line 123

 

$query = "LOAD DATA INFILE '".$tablefile."' INTO TABLE "._DB_PREFIX_.$table." CHARACTER SET 'utf8'";";

 

should be

 

$query = "LOAD DATA INFILE '".$tablefile."' INTO TABLE "._DB_PREFIX_.$table." CHARACTER SET 'utf8'";

 

 

 

But I have another error

 

 

 

 

5 old languages; 5 new languages. Language check ok.

 

 

Notice: Undefined variable: exim in /**/**/**/admin**/triple/copy_shopdata.php on line 70

 

Notice: Undefined variable: exim in /**/**/**/admin**/triple/copy_shopdata.php on line 71

 

Warning: mkdir(): No such file or directory in /**/**/**/admin**/triple/copy_shopdata.php on line 71

 

accessory 0

address 42484 2

MySQL error 1045: 

Generated by Query 'SELECT `id_address`,`id_country`,`id_state`,`id_customer`,`id_manufacturer`,`id_supplier`,`id_warehouse`,`alias`,`company`,`lastname`,`firstname`,`address1`,`address2`,`postcode`,`city`,`other`,`phone`,`phone_mobile`,`vat_number`,`dni`,`date_add`,`date_upd`,`active`,`deleted` INTO OUTFILE '/**/**/**/admin**/triple/copy_shopdata_address.dtx' FROM ps_address'

[musicmaster]

That exim part can be excluded. So that is not a problem. I am working on a new version but I need to test it some more so I can't upload it before tomorrow.

 

Mysql error 1045 is a rights error. You don't have enough rights to create that file. It means that either you should get more rights or you should copy that shop from the server to your local host and use copy_shopdata from there.

Edited October 30, 2015 by musicmaster (see edit history)

[ietax]

thanks, how can I set rights for execute it on server?

 

i shlould set it to a triple folder only? thanks

[musicmaster]

The script writes it to the Triple folder, so it is there that you need the rights. How - and whether - you can assign those rights - depends on your provider. You need to ask them.