upgrade from 1.6 to 1.7 with a more than 30G database

[psauleda]

Hello everyone.

I am trying to upgrade from version 1.6 to 1.7 a prestashop with a database with more than 30 gigabytes.

I have installed the 1click update, and I have launched it by console with the cli-update.

The problem is that has been doing the backup for more than a week, and still not finished.

In the folder [shop]/[admin]/autoupgrade/backup/V1.6... there are many files with the extension .sql.bz2 but in total they barely occupy 1.2G. From there to compressing the rest of the database, it may take several more weeks...

In the github of the project ( https://github.com/PrestaShop/autoupgrade ) it mentions that there is an --action parameter to specify from which step of the process to start the upgrade, but it simply points to the code in githup: https://github .com/PrestaShop/autoupgrade/tree/dev/classes/TaskRunner/Upgrade

I haven't been able to find more documentation about this parameter, does anyone know how it works?

I'm working on a copy of the store, so I don't need the backup to roll-back if the upgrade fails.

Thanks in advance!

[endriu107]

Probably most of data in your database is statistics, connections etc. You can clear those tables before update. You can cleare it manually or use module there is few available like "tidy" developed by @Daresh

[psauleda]

Hello.

Forgot to mention I already look at that, our bigger tables are not from statistics, are from products: product_lang, sparts, product, product_shop, search_index and search_shop are with difference the biggest tables.

 

[endriu107]

Tables you can clear is:

`ps_connections` ,`ps_connections_page` ,`ps_connections_source` ,`ps_guest` ,`ps_log` ,`ps_statssearch` ,`ps_pagenotfound` ,`ps_search_index` ,`ps_search_word` ,`ps_page_viewed` ,`ps_smarty_cache`

[psauleda]

Thanks again, but my biggest tables are:

ps_product_lang 16G

sparts 2.7G

ps_product 2.6G

 

while ps_connectins, ps_log, etc are only few megas, so it doesn't really makes a difference.

[endriu107]

How many active products you have in shop?

Table sparts is not native so you can check is this from some module or script, maybe it is not relevant for shop.

[psauleda]

On 3/18/2022 at 5:43 PM, endriu107 said:

How many active products you have in shop?

Table sparts is not native so you can check is this from some module or script, maybe it is not relevant for shop.

The shop has more tan 17.000.000 products.

About sparts table, it's from a custom module.

[WisQQ]

On 3/18/2022 at 10:52 AM, psauleda said:

Hello everyone.

I am trying to upgrade from version 1.6 to 1.7 a prestashop with a database with more than 30 gigabytes.

I have installed the 1click update, and I have launched it by console with the cli-update.

The problem is that has been doing the backup for more than a week, and still not finished.

In the folder [shop]/[admin]/autoupgrade/backup/V1.6... there are many files with the extension .sql.bz2 but in total they barely occupy 1.2G. From there to compressing the rest of the database, it may take several more weeks...

In the github of the project ( https://github.com/PrestaShop/autoupgrade ) it mentions that there is an --action parameter to specify from which step of the process to start the upgrade, but it simply points to the code in githup: https://github .com/PrestaShop/autoupgrade/tree/dev/classes/TaskRunner/Upgrade

I haven't been able to find more documentation about this parameter, does anyone know how it works?

I'm working on a copy of the store, so I don't need the backup to roll-back if the upgrade fails.

Thanks in advance!

I'd suggest you to make a manual backup of your site and database, then install it on your local machine using xamp or wamp. Set almost no limits on the configs and then try to upgrade it. Also dont use BACKUP option during 1click upgrade, because as you saw it takes forever to finish doing backup. It will speed up upgrading process and you will have your backup anyway. 

Edited March 21, 2022 by WisQQ (see edit history)

[psauleda]

21 hours ago, WisQQ said:

I'd suggest you to make a manual backup of your site and database, then install it on your local machine using xamp or wamp. Set almost no limits on the configs and then try to upgrade it. Also dont use BACKUP option during 1click upgrade, because as you saw it takes forever to finish doing backup. It will speed up upgrading process and you will have your backup anyway. 

I tried, but still got a timeout error while upgrading database:

All files upgraded. Now upgrading database...
[Ajax / Server Error for action upgradeDb] textStatus: "error " errorThrown:"Gateway Timeout " jqXHR: "
504 Gateway Time-out

 

on php i've got

max_execution_time = -1

 

and on apache:

TimeOut 150000

KeepAliveTimeOut 10000

 

So i don't know why the timeout in a few minutes...

 

there's no way of doing it with the cli but without doing the backup?

 

 

[WisQQ]

7 hours ago, psauleda said:

I tried, but still got a timeout error while upgrading database:

All files upgraded. Now upgrading database...
[Ajax / Server Error for action upgradeDb] textStatus: "error " errorThrown:"Gateway Timeout " jqXHR: "
504 Gateway Time-out

 

on php i've got

max_execution_time = -1

 

and on apache:

TimeOut 150000

KeepAliveTimeOut 10000

 

So i don't know why the timeout in a few minutes...

 

there's no way of doing it with the cli but without doing the backup?

 

 

I'm not sure about that, but it might be sql server connection timeout.
Also working on a backup is much safer and doing it on your localhost gives you more resource to use. Do you know on which part of database it returns error?

What i would do is to try and do upgrade on a backup site with database without data from these large tables. Check if it works, then try to import data into those tables with exported table data. 
Before you do that check the structure of both tables from your old prestashop and new one.

These is just my guess, so be careful before you do this on your proper site.

Hope this gives you some ideas.

Edited March 22, 2022 by WisQQ (see edit history)

[Nickz]

A 30 GB Database better clean that up before. Old data, refused customers etc all not needed.

[psauleda]

19 hours ago, WisQQ said:

I'm not sure about that, but it might be sql server connection timeout.
Also working on a backup is much safer and doing it on your localhost gives you more resource to use. Do you know on which part of database it returns error?

What i would do is to try and do upgrade on a backup site with database without data from these large tables. Check if it works, then try to import data into those tables with exported table data. 
Before you do that check the structure of both tables from your old prestashop and new one.

These is just my guess, so be careful before you do this on your proper site.

Hope this gives you some ideas.

The last instruction logged was an ALTER TABLE on a 2,6G table:

[2022-03-22 13:14:50] <div class="upgradeDbOk">[OK] SQL 1.7.0.0 ALTER TABLE `ps_product` CHANGE `available_date` `available_date` DATE NULL DEFAULT NULL</div>
[2022-03-22 13:17:22] <div class="upgradeDbOk">[OK] SQL 1.7.0.0 ALTER TABLE `ps_product` ADD `show_condition` TINYINT(1) NOT NULL DEFAULT '0' AFTER `available_date`</div>

 

So, probably it's gonna be easier to start with a clean site and export the data from the 1.6 shop and import it on the new site.

 

 

 

[Nickz]

21 hours ago, psauleda said:

So, probably it's gonna be easier to start with a clean site and export the data from the 1.6 shop and import it on the new site.

sure is also cleaner