Copy Shopdata: Script for upgrading and fixing buggy shops by copying business data and images

[eVRECIA]

After writing copy_shopdata.php these errors:

1 accessory 38
Fatal error: Uncaught mysqli_sql_exception: SELECT command denied to user 'db109971xnova'@'10.10.51.16' for table `db109971xbiopla`.`ps_accessory` in /www/b/i/u109971/public_html/admin500bwd3vsijqzrprxtm/Prestools_Suite/copy_shopdata_functions.inc.php:7 Stack trace: #0 /www/b/i/u109971/public_html/admin500bwd3vsijqzrprxtm/Prestools_Suite/copy_shopdata_functions.inc.php(7): mysqli_query(Object(mysqli), 'INSERT INTO `db...') #1 /www/b/i/u109971/public_html/admin500bwd3vsijqzrprxtm/Prestools_Suite/copy_shopdata_functions.inc.php(587): dbxquery(Object(mysqli), 'INSERT INTO `db...') #2 /www/b/i/u109971/public_html/admin500bwd3vsijqzrprxtm/Prestools_Suite/copy_shopdata_functions.inc.php(458): copy_table_direct('accessory', 'ps_', '`id_product_1`,...', Array) #3 /www/b/i/u109971/public_html/admin500bwd3vsijqzrprxtm/Prestools_Suite/copy_shopdata.php(228): copy_table('accessory') #4 {main} thrown in /www/b/i/u109971/public_html/admin500bwd3vsijqzrprxtm/Prestools_Suite/copy_shopdata_functions.inc.php on line 7

[Nickz]

Denied that should be a permission related error.

[eVRECIA]

I am a beginner in working with databases. What error related to permission?

[tasPuika]

Quote

denied to user 'db109971xnova'@'10.10.51.16' for table `db109971xbiopla`.`ps_accessory`

as your MySQL root you must grant permissions via something like (this is too wide for real life):

grant all on `db109971xbiopla`.* to 'db109971xnova'@'10.10.51.16' 

                                                                                                                                        

[eVRECIA]

But why only for the table ps_accessory,  if in the database are all tables from PS 8.1.6.

[musicmaster]

Rights problems usually happen when copying from one mysql installation to another. I don't have a solution for that, except for the suggestion to copy the database to the target installation and use that as the basis for copy_shopdata,

[gregor5]

We are about to try upgrading an old 1.6.1.24 Prestashop version using your script. We tried with 1 click upgrade but it doesn't work.

Would you recommend we try and do a fresh installation of the same Prestashop version, transfer tables, and try again with 1-click or can we go straight from 1.6 to 8.1 (or 1.7)?

Edited July 18, 2024 by gregor5 (see edit history)

[Nickz]

2 hours ago, gregor5 said:

can we go straight from 1.6 to 8.1 (or 1.7)?

You cannot, you need to go step by step thru the entire tree.
Why do you wish to update. No themes you like? Do clients complain about the optic? 

Most shops look alike when opened with a mobile phone.

[musicmaster]

4 hours ago, gregor5 said:

We are about to try upgrading an old 1.6.1.24 Prestashop version using your script. We tried with 1 click upgrade but it doesn't work.

Would you recommend we try and do a fresh installation of the same Prestashop version, transfer tables, and try again with 1-click or can we go straight from 1.6 to 8.1 (or 1.7)?

I would suggest a fresh 8.1 installation and then going straight from the old shop to that version.

Let me know when you notice any problem.

[Biscot]

Hello, an update for prestashop 8.2?, because when I try I get this "Prestashop 8.2 is not yet supported by Prestools."

[musicmaster]

11 minutes ago, Biscot said:

Hello, an update for prestashop 8.2?, because when I try I get this "Prestashop 8.2 is not yet supported by Prestools."

Download the latest version of Prestools.

[Biscot]

ok it works

[luismfbsilva061980]

Good morning,

I'm trying to use copy_data, but something here is malfunctioning.
Presta 8.1.7 on the same server and different folders, different DBs.

I just need to transfer the customers, addresses and orders, but I get this error:

SELECT command denied to user 'xxxx_xxxxxx'@'localhost' for table `xxxx_xxxxxx1`.`ps_address`

MySQL error 1142: SELECT command denied to user 'xxxx_xxxxxx'@'localhost' for table `xxxx_xxxxxx1`.`ps_address`

 

Any suggestions?

[musicmaster]

It looks like those databases have different logins and that gives problems.

If that is the case you have two options:

 - you can copy one of the databases or allocate rights so that both can be reached with the same server-user-password combination

 - you can set the $copy_method variable to row. But that will make the process much slower.

[luismfbsilva061980]

16 minutes ago, musicmaster said:

It looks like those databases have different logins and that gives problems.

If that is the case you have two options:

 - you can copy one of the databases or allocate rights so that both can be reached with the same server-user-password combination

 - you can set the $copy_method variable to row. But that will make the process much slower.

So what it tells me is that I have to put the old database with the same user and pass as the new database, right?

[musicmaster]

yes

[ipaelo]

HI,

 

I am trying to update a 1.6.1.24 to 8.2.  I have a problem when the script find a NULL.  I have downloaded latest version of copydata.

Column 'color' cannot be null

MySQL error 1048: Column 'color' cannot be null
Generated by URL '/Backoffice/herramientas/copy_shopdata.php'
with Query 'INSERT INTO `ps_8`.`ps_attribute` (`id_attribute`,`id_attribute_group`,`color`,`position`) VALUES ('51','10',NULL,'42')'

May someone help me, please? 

 

[Jose Lorenzini]

  

Hello Ipaelo

replace NULL with '#eee' or any color RGB u want .

INSERT INTO `ps_attribute` (`id_attribute`, `id_attribute_group`, `color`, `position`) VALUES ('51', '10', '#FFFFFF', '42');

 

other option is trying to update the color field so then u make it accepting NULL

ALTER TABLE `ps_attribute` MODIFY `color` VARCHAR(255) NULL;

set a default atttribute :
 

ALTER TABLE `ps_attribute` MODIFY `color` VARCHAR(255) NOT NULL DEFAULT '#000000';

and try your script again:

 'INSERT INTO `ps_8`.`ps_attribute` (`id_attribute`,`id_attribute_group`,`color`,`position`) VALUES ('51','10',NULL,'42')'

 

saludos

jose lorenzini

[Nickz]

On 11/19/2024 at 2:07 PM, ipaelo said:

 

I am trying to update a 1.6.1.24 to 8.2.

you should have updated step by step.
Or alternatively create a new shop in the version you wish to use.

[musicmaster]

On 11/19/2024 at 3:07 PM, ipaelo said:

HI,

 

I am trying to update a 1.6.1.24 to 8.2.  I have a problem when the script find a NULL.  I have downloaded latest version of copydata.

Column 'color' cannot be null

MySQL error 1048: Column 'color' cannot be null
Generated by URL '/Backoffice/herramientas/copy_shopdata.php'
with Query 'INSERT INTO `ps_8`.`ps_attribute` (`id_attribute`,`id_attribute_group`,`color`,`position`) VALUES ('51','10',NULL,'42')'

May someone help me, please?

It works for me: NULL is automatically converted into an empty string. It looks like your database has stricter settings. Maybe it is a Mysql vs. MariaDB difference.

Edit: There is now a new version of the package that you can download

 

Edited November 23, 2024 by musicmaster (see edit history)

[Renato Strafatta]

Hi Musicmaster, my old prestashop was infected by a malware on TinyMCE plugin and I cannot get into admin... I installed a fresh new prestashop and I want to get the products and other pages and info from the old database... is that possible with your tool??? can you help me??

[musicmaster]

2 hours ago, Renato Strafatta said:

Hi Musicmaster, my old prestashop was infected by a malware on TinyMCE plugin and I cannot get into admin... I installed a fresh new prestashop and I want to get the products and other pages and info from the old database... is that possible with your tool??? can you help me??

Yes, that is possible. You only need to fill in the database credentials in the config file.

You can use Copy_shopdata_imghub.php to copy the image directory.

[musicmaster]

The script got a major upgrade.

[musicmaster]

Prestools got an upgrade that changed the way the variables in the settings file are named. This affects Copy_shopdata too. If you download Copy_shopdata from now on you must make sure that your Prestools is at least at version 1.36.

[salouso]

Hi

I have the same problem some above have mentioned.
I am trying to copy db from PS 1.7.6.7 to 8.2.0

First table worked fine, on second table I get this: see attached image. Can't add code directly, because silly Prestashop forum blocks messages that contain SQL code 🤣
Red marks are to hide DB and Domain Info.

Above you suggested to assign same db user to both db's. I am working with Plesk and this is not allowed. I need one user per DB. Or am I misunderstanding your solution?

 

Thanks, Sarah

[musicmaster]

Did you try to set "$copy_mode" in the config file to "row"?

[ferszcik]

On 2/10/2025 at 6:50 PM, musicmaster said:

Did you try to set "$copy_mode" in the config file to "row"?

I had the same issue as my db's were on different servers. Switching to "row" made it work. But now I have a question about next step as I am unable to log in into new shop (to which I copied data):

- copy the security strings (Ryndael keys, Cookie keys) from the settings.inc.php (PS 1.6) or
parameters.php (PS 1.7+) of the old shop to the same file on the new shop. Without this you won't
be able to login. The Ryndael keys are only used in Prestashop 1.6.

 

I am moving data from presta 1.6.1.23 to 8.2.1 so how should I approach the passwords and cookie keys topic?

[ferszcik]

Ok, I answered my own question.

I was looking at wrong file, I took cookie key from setting.inc.php (1.6.1.23) and replaced it in parameters.php (8.2.1) and login works again

[dandumit]

Hello,

I need now to upgrade from 1.7.6 to 8.2 . please tell me is there any previous experience on this ?

Thank you,

Daniel

[dandumit]

and second question if I can migrate from a multistore shop to a simple no multistore one

 

[Knowband Plugins]

4 hours ago, dandumit said:

I need now to upgrade from 1.7.6 to 8.2 . please tell me is there any previous experience on this ?

 

You can use 1-click upgrade module to achieve the same. Just take the back of DB & FIle & do the upgrade. 

Upgrading the staging first would be better. 

 

Due to PHP compatibility, first you need to perform the upgrade to 1.7.8 (Using PHP 7.2). Once upgraded to 1.7.8, update the PHP version to 7.4 then perform the 8.2 migration. 

 

Refer to the PHP version compatibility with Prestashop.

 

 

1 hour ago, dandumit said:

and second question if I can migrate from a multistore shop to a simple no multistore one

 

Yes, Simply disable the multistore feature. 

[musicmaster]

11 hours ago, dandumit said:

I need now to upgrade from 1.7.6 to 8.2 . please tell me is there any previous experience on this ?

Yes, there is and it worked.

7 hours ago, dandumit said:

and second question if I can migrate from a multistore shop to a simple no multistore one

Everything is copied. So you would need to start with a multistore, copy the data and then delete the shops that you no longer want. Among the Prestools cleanup routines there is one that will delete remainders of the deleted shops.

[dandumit]

@musicmaster It seems that your module will rescue me again.

 

I get many warnings that he cannot read name of indexes :

Notice: Undefined index: index_name in /pts/copy_shopdata_functions.inc.php on line 326

Notice: Undefined index: index_name in /pts/copy_shopdata_functions.inc.php on line 340

Notice: Undefined index: non_unique in/pts/copy_shopdata_functions.inc.php on line 342

 

I had a look on script but I cannot figure why those warnings.

 

And finally I have an error and stops on: 

MySQL error 1062: Duplicate entry '2-3-001Ω' for key 'ps_search_word.id_lang'
Generated by URL '/pts/copy_shopdata.php'
with Query ps_search_word` (`id_lang`,`id_shop`,`id_word`,`word`) SEL `id_lang`,`id_shop`,`id_word`,`word` FROM ps_search_word

 

What should I do here ?   I have tried to rebuild search index on old shop but still problem persists.

I had a look on source table but I cannot find this duplicate.

Thank you,

Daniel

 

 

[musicmaster]

5 hours ago, dandumit said:

I get many warnings that he cannot read name of indexes :

Notice: Undefined index: index_name in /pts/copy_shopdata_functions.inc.php on line 326

Notice: Undefined index: index_name in /pts/copy_shopdata_functions.inc.php on line 340

Notice: Undefined index: non_unique in/pts/copy_shopdata_functions.inc.php on line 342

These are fieldnames from the information_schema.statistics table. I have those from the MariaDb version. I wonder whether you have Mysql instead and there is some difference. Can you check that?

 

5 hours ago, dandumit said:

And finally I have an error and stops on: 

MySQL error 1062: Duplicate entry '2-3-001Ω' for key 'ps_search_word.id_lang'
Generated by URL '/pts/copy_shopdata.php'
with Query ps_search_word` (`id_lang`,`id_shop`,`id_word`,`word`) SEL `id_lang`,`id_shop`,`id_word`,`word` FROM ps_search_word

I suspect that the Ohm sign gives problems. Note that you may be moving between slightly different character sets (like utf8mbt4_general and utf8_unicode) and that can result in this kind of errors. The easiest solution is to set $skip_search_tables in the config table to true and re-index the new shop when you are ready.

Edited March 30 by musicmaster (see edit history)

[dandumit]

YEs it's Mysql . I have requested specifically to be Mysql. Despite warnings script it's running.

I have rebuild again search index in source DB and finally was working.

Thank you,

Daniel

[musicmaster]

3 minutes ago, dandumit said:

YEs it's Mysql . I have requested specifically to be Mysql. Despite warnings script it's running.

I have rebuild again search index in source DB and finally was working.

Thank you,

Daniel

Good to hear it works.

The code with the warnings checks the indexation and gives warnings when the databases have different indexation for some table. So it is not mission critical.

Could you help me by providing an overview of the fields that this information_schema statistics table has under Mysql?

[dandumit]

7 hours ago, musicmaster said:

Could you help me by providing an overview of the fields that this information_schema statistics table has under Mysql?

I would be very glad. Just tell me what to do. I have put a debug echo , I have run manually the sql , runs ok , not exactly why the script gives that warning.

[musicmaster]

11 hours ago, dandumit said:

I would be very glad. Just tell me what to do. I have put a debug echo , I have run manually the sql , runs ok , not exactly why the script gives that warning.

Could you try the attached version of copy_shopdata_functions.inc.php? (you need to unzip it as Prestashop doesn't like .php extensions)

It should produce debug text when the warning occurs. I would like to see that debug text.

copy_shopdata_functions.inc.zip

[ciroco05]

Dear Musicmaster,

thank you for this script!
I'm tried to update a 1.6.1.4 to 8.2 and at first run the update works great.

At moment I've only a trouble  about the product images and copy_shopdata_imghub

Due my shop was update in the past from very old version (1.4) the image set is:

product Id = 1003 
base Images in img/p = 1003-1602, 1003-1603, 1003-1604 and so on all in root o img/p

while the script expect the imgaes in img/p/1/6/0/2 and so on

For this reason, I can't use the utility copy_shopdata_imghub and I can't regenerate with image-regenerate.php (Prestools)

Do you have any suggest for this?

[musicmaster]

You will need to write a little script to do this for you.

Maybe such a function is included in older versions of Prestashop, but now all support for this format has ended.

I can write it for you but then there will be some costs. 

[ciroco05]

Dear Musicmaster,

yes of course.
please send a pm with detail

 

[Ray UK]

I Know PS9 has only just come out, but will this excellent plugin be updated for PS9 any time soon

[musicmaster]

On 6/30/2025 at 1:13 PM, Ray UK said:

I Know PS9 has only just come out, but will this excellent plugin be updated for PS9 any time soon

You can use it with PS9. PS9 has no big changes in the business data. I have added only one minor fix for it.

[Ray UK]

On 7/4/2025 at 3:02 PM, musicmaster said:

You can use it with PS9. PS9 has no big changes in the business data. I have added only one minor fix for it.

When I try to use it I just get the message

Prestashop 8.3 is not yet supported by this Prestools version. Make sure you download the latest version.

(Im using a clean PS9 and have downloaded the script from your site today)

Edited July 6 by Ray UK (see edit history)

[Ray UK]

OK so I just changed this from v8.3 and its now loading. Is that all that is needed or will there be issues

define('_PS_VERSION_',$version);
	if (_PS_VERSION_ >= "9.1")
	  die("Prestashop 9.1 is not yet supported by this Prestools version. Make sure you download the latest version.");
  }

 

[musicmaster]

3 hours ago, Ray UK said:

OK so I just changed this from v8.3 and its now loading. Is that all that is needed or will there be issues

define('_PS_VERSION_',$version);
	if (_PS_VERSION_ >= "9.1")
	  die("Prestashop 9.1 is not yet supported by this Prestools version. Make sure you download the latest version.");
  }

That is the right way. It should work. 

Please let me know when you find any problem.

Edited July 6 by musicmaster (see edit history)

[Ray UK]

OK so i just tried the copy_shopdata and get the following error.

Doing the following language transformations: 1=>3
Initialization skipped because it had already run
Copying tables

1 accessory 80
Index differences: 
Extra in old: accessory_product (id_product_1,id_product_2), 
Extra in new: PRIMARY (id_product_1,id_product_2),
Fatal error: Uncaught mysqli_sql_exception: Table '**********.wvsds_accessory_orig' doesn't exist in /home/******/public_html/[domain]/admin_dir/prestools/copy_shopdata_functions.inc.php:554 Stack trace: #0 /home//******//public_html/[domain]/admin_dir/prestools/copy_shopdata_functions.inc.php(554): mysqli_query(Object(mysqli), 'select 1 from `...') #1 /home//******//public_html/[domain]/admin_dir/prestools/copy_shopdata.php(271): copy_table('accessory') #2 {main} thrown in /home//******//public_html/[domain]/admin_dir/prestools/copy_shopdata_functions.inc.php on line 554

This is going from 8.2.1 to 9

PHP 8.1 (also tried 8.4)

Edited July 7 by Ray UK (see edit history)

[musicmaster]

Thank you for reporting the problem.

I uploaded a new version. Can you check it?

[Ray UK]

1 hour ago, musicmaster said:

Thank you for reporting the problem.

I uploaded a new version. Can you check it?

I’m trying to reply but blocked from the forum lol

Edited July 7 by Ray UK (see edit history)

[Ray UK]

Ok so looks like they blocked my hardware id of my computer.  I’m on my phone now 

[Ray UK]

I have emailed you music master as it’s difficult posting the code from the phone 

[Ray UK]

[Ray UK]

looking at that it looks as if the target username is trying to access the source db. 

[Ray UK]

The forum wont let me post the code (in the <> block) thats in the image above

If I try to paste that code I get the error saying 

"This comment can no longer be edited. It may have been moved or deleted, or too much time may have passed since it was posted for it to be edited."

Edited July 7 by Ray UK (see edit history)

[musicmaster]

The problems are now solved with the latest version of Copy_shopdata.

[musicmaster]

On 3/30/2025 at 10:36 AM, dandumit said:

I get many warnings that he cannot read name of indexes :

Notice: Undefined index: index_name in /pts/copy_shopdata_functions.inc.php on line 326

Notice: Undefined index: index_name in /pts/copy_shopdata_functions.inc.php on line 340

Notice: Undefined index: non_unique in/pts/copy_shopdata_functions.inc.php on line 342

 

I had a look on script but I cannot figure why those warnings.

 

And finally I have an error and stops on: 

MySQL error 1062: Duplicate entry '2-3-001Ω' for key 'ps_search_word.id_lang'
Generated by URL '/pts/copy_shopdata.php'
with Query ps_search_word` (`id_lang`,`id_shop`,`id_word`,`word`) SEL `id_lang`,`id_shop`,`id_word`,`word` FROM ps_search_word

 

It looks like Mysql is using INDEX_NAME instead of index_name in MariaDB.  That doesn't give problems with the query as field names are not case sensitive. However, it gives a problem when you retrieve a database row into an array: associative array indexes are case sensitive. I have uploaded a new version that I hope will fix the problem.

[musicmaster]

I uploaded the latest version: 0.79.

Most improvements in the latest versions focus on handling modules:

 - after a copy_shopdata run you will now see a list of module tables with the option to copy them. 

 - with copy_shopdata_confhub you can now copy configuration table settings for specific modules

 - in Prestools a new function Module Mapper has been added that tells you which database tables and configuration table prefixes belong to the modules that you installed. This is a reconstruction with a text search so it is not 100% accurate.

 - It has been made easier to compare shops with Module-compare as it can now use the configuration of copy_shopdata. So the comparison will now always be up-to-date and you no longer need to download and upload csv files.

[Luca Siviero]

Hello everyone, I need to upgrade a prestashop store from 1.6.1.24 to the latest stable version.
I have never used Prestashop, but I do have some experience in the field.
I don't want to study the entire prestashop schema, so is it possibile to migrate 1.6.1.24 to 8.x? And to 9.x? Is an upgrade to 9.x recommended, or do you advice to stay on 8.x?

Anyway, I came across this tool through an older discussion. I would like to know if there is a specific guide in migrating prestashop from 1.6.1.24 to 8.x with this particular tool.

I would also like to know if I should expect some data loss due to schema incompatibility or if the export is denormalized, then transformed to a compatible schema once uploaded back in a newer version of Prestashop to keep data integrity.

Any advice is very very welcome, since it's my first time working with prestashop. Thanks! Any external source of information or guide would be great!

[musicmaster]

5 hours ago, Luca Siviero said:

Hello everyone, I need to upgrade a prestashop store from 1.6.1.24 to the latest stable version.
I have never used Prestashop, but I do have some experience in the field.
I don't want to study the entire prestashop schema, so is it possibile to migrate 1.6.1.24 to 8.x? And to 9.x? Is an upgrade to 9.x recommended, or do you advice to stay on 8.x?

Anyway, I came across this tool through an older discussion. I would like to know if there is a specific guide in migrating prestashop from 1.6.1.24 to 8.x with this particular tool.

I would also like to know if I should expect some data loss due to schema incompatibility or if the export is denormalized, then transformed to a compatible schema once uploaded back in a newer version of Prestashop to keep data integrity.

Any advice is very very welcome, since it's my first time working with prestashop. Thanks! Any external source of information or guide would be great!

The software package contains an elaborate manual.

It is possible both to go to the version 8.x and 9.x. In the basic software there will be no data loss.

The big unknown - what you need to find out yourself - is modules. 1.6 modules can not be run with newer versions. So you need to find out which modules are used and which of them store data in the database. For the Prestashop modules there is usually a newer version with a different name. For other modules you will need to ask the maker.

As not all modules may yet have a 9.x version that may become the deciding factor in which version you want to migrate to.