Autoupgrade module issues with customization of SQL columns

[ioweb-gr]

Prerequisites

• I understand and accept the project's code of conduct.
• I have already searched in existing issues and found no previous report of this bug.

Describe the bug and add attachments

We have an eshop which was migrated from prestashop 1.6 to 1.7.5.2 (latest version at that point). We're now at a spot when we try to upgrade for the past 10 months using the autoupgrade module and no matter what we tried, there are always a million database issues. To give you an example, to get to a half working state I had to execute manually queries like these

Queries

ALTER TABLE `ps_admin_filter`
    ADD `filter_id` INT NOT NULL;
truncate ps_admin_filter;

ALTER TABLE ps_tab
    ADD COLUMN route_name VARCHAR(256) DEFAULT NULL AFTER class_name;
ALTER TABLE `ps_link_block_shop` ADD `position` INT(11) NOT NULL DEFAULT '0' AFTER `id_shop`;
ALTER TABLE `ps_layered_category` ADD `controller` VARCHAR(255) AFTER `id_shop`;


ALTER TABLE ps_hook
    ADD active TINYINT(1) UNSIGNED DEFAULT 1 NOT NULL AFTER description;
ALTER TABLE `ps_employee`
    ADD `has_enabled_gravatar` TINYINT(1) NOT NULL DEFAULT '0' AFTER `reset_password_validity`;
ALTER TABLE ps_log
    ADD id_lang INT(10) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `ps_log`
    ADD `id_shop` INT(11) UNSIGNED NOT NULL DEFAULT '1' AFTER `id_lang`;
ALTER TABLE ps_log
    ADD id_shop_group INT(10) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `ps_log`
    ADD `in_all_shops` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER `id_lang`;
alter table ps_log
    modify id_shop int(11) unsigned default 1 null;
alter table ps_log
    modify id_shop_group int unsigned default 0 null;
ALTER TABLE `ps_product_attribute` ADD `mpn` VARCHAR(255) AFTER `upc`;


## Fix problems with nulls

UPDATE ps_order_detail SET id_order_invoice = '0' WHERE id_order_invoice IS NULL;
UPDATE ps_order_detail SET product_reference = '' WHERE product_reference IS NULL;
UPDATE ps_order_detail SET product_supplier_reference = '' WHERE product_supplier_reference IS NULL;
UPDATE ps_order_detail SET product_isbn = '' WHERE product_isbn IS NULL;
UPDATE ps_order_detail SET product_upc = '' WHERE product_upc IS NULL;
UPDATE ps_order_detail SET product_mpn = '' WHERE product_mpn IS NULL;
UPDATE ps_order_detail SET download_hash = '' WHERE download_hash IS NULL;
UPDATE ps_order_detail SET product_ean13 = '' WHERE product_ean13 IS NULL;

UPDATE ps_orders SET gift_message = '' WHERE gift_message IS NULL;
UPDATE ps_orders SET shipping_number = '' WHERE shipping_number IS NULL;

UPDATE ps_order_payment SET transaction_id = '' WHERE transaction_id IS NULL;
UPDATE ps_order_payment SET card_number = '' WHERE card_number IS NULL;
UPDATE ps_order_payment SET card_brand = '' WHERE card_brand IS NULL;
UPDATE ps_order_payment SET card_expiration = '' WHERE card_expiration IS NULL;
UPDATE ps_order_payment SET card_holder = '' WHERE card_holder IS NULL;

UPDATE ps_order_carrier SET tracking_number = '' WHERE tracking_number IS NULL;
UPDATE ps_order_carrier SET id_order_invoice = '0' WHERE id_order_invoice IS NULL;
UPDATE ps_order_carrier SET weight = '0' WHERE weight IS NULL;

UPDATE ps_product SET ean13 = '' WHERE ean13 IS NULL;
UPDATE ps_product SET isbn = '' WHERE isbn IS NULL;
UPDATE ps_product SET upc = '' WHERE upc IS NULL;
# UPDATE ps_product SET mpn = '' WHERE mpn IS NULL;
UPDATE ps_product SET unity = '' WHERE unity IS NULL;
UPDATE ps_product SET reference = '' WHERE reference IS NULL;
UPDATE ps_product SET supplier_reference = '' WHERE supplier_reference IS NULL;
UPDATE ps_product SET location = '' WHERE location IS NULL;
UPDATE ps_product SET redirect_type = '301-category' WHERE redirect_type = '';
/*UPDATE ps_product SET available_date = '2020-12-01' WHERE available_date IS NULL;*/
UPDATE ps_product SET available_date = '0000-00-00' WHERE available_date IS NULL;
UPDATE ps_product SET cache_default_attribute = '0' WHERE cache_default_attribute IS NULL;


UPDATE ps_product_shop SET unity = '' WHERE unity IS NULL;
UPDATE ps_product_shop SET redirect_type = '301-product' WHERE redirect_type = '';
/*UPDATE ps_product_shop SET available_date = '2020-12-01' WHERE available_date IS NULL;*/
UPDATE ps_product_shop SET available_date = '0000-00-00' WHERE available_date IS NULL;
UPDATE ps_product_shop SET cache_default_attribute = '0' WHERE cache_default_attribute IS NULL;
UPDATE ps_product_shop SET low_stock_threshold = '0' WHERE low_stock_threshold IS NULL;

update ps_address set dni = '' where dni is null;
update ps_address set other = '' where other is null;
update ps_address set address2 = '' where address2 is null;
update ps_address set company = '' where company is null;
update ps_address set vat_number = '' where vat_number is null;
UPDATE ps_address SET phone = '' WHERE phone IS NULL;
UPDATE ps_address SET phone_mobile = '' WHERE phone_mobile IS NULL;

update ps_customer set company = '' where company is null;
update ps_customer set siret = '' where siret is null;
update ps_customer set ape = '' where ape is null;
update ps_customer set ip_registration_newsletter = '' where ip_registration_newsletter is null;
update ps_customer set website = '' where website is null;
update ps_customer set note = '' where note is null;

update ps_order_invoice set note = '' where note is null;

update ps_order_state set module_name = '' where module_name is null;
update ps_product_attribute
set ean13 = '-'
where ean13 = '' or ean13 is null;

#rename column active to enabled in ps_tab table
alter table ps_tab change active enabled tinyint(1) unsigned default 1 not null;
alter table ps_tab change enabled active tinyint(1) unsigned default 1 not null;
update ps_tab set enabled = 0 where class_name in ('pk_menu','pk_sizeguide');
update ps_tab set enabled = 0 where class_name like '%jx%';

Upon deep search I noticed that all these queries are in one or more ways added by the autoupgrade module and exist in one form or the other in the folder

prestashop\autoupgrade\upgrade\php
prestashop\autoupgrade\upgrade\sql

As to why it fails, the reason is simple

For example in my case on file

1.7.6.0.sql

There is the command

ALTER TABLE `PREFIX_currency` ADD `numeric_iso_code` varchar(3) NOT NULL DEFAULT '0' AFTER `iso_code`;
ALTER TABLE `PREFIX_currency` ADD `precision` int(2) NOT NULL DEFAULT 6 AFTER `numeric_iso_code`;
ALTER TABLE `PREFIX_currency` ADD KEY `currency_iso_code` (`iso_code`);

/* Localized currency information */
CREATE TABLE `PREFIX_currency_lang` (
    `id_currency` int(10) unsigned NOT NULL,
    `id_lang` int(10) unsigned NOT NULL,
    `name` varchar(255) NOT NULL,
    `symbol` varchar(255) NOT NULL,
    PRIMARY KEY (`id_currency`,`id_lang`)
  ) ENGINE=ENGINE_TYPE DEFAULT CHARSET=utf8;

But on my database the table exists and the columns are already there from the first migration I suppose.

These queries are not fault tolerant and they expect a very very predefined structure of the database making it impossible to upgrade

There are other cases like

INSERT INTO PREFIX_hook_alias (name, alias) VALUES
('displayAdminOrderTop', 'displayInvoice'),
('displayAdminOrderSide', 'displayBackOfficeOrderActions'),
('actionFrontControllerInitAfter', 'actionFrontControllerAfterInit')
;

which throw a duplicate error because they should be

INSERT IGNORE INTO `PREFIX_hook_alias` (`name`, `alias`) VALUES
  ('displayAdminOrderTop', 'displayInvoice'),
  ('displayAdminOrderSide', 'displayBackOfficeOrderActions'),
  ('actionFrontControllerInitAfter', 'actionFrontControllerAfterInit')
;

or delete / replace or update the values if the references exist.

There are also inserts from select which are prone to break because if a custom module has added a single column in the select table, the column count won't match and they fail to execute.

INSERT INTO `PREFIX_cms_lang` SELECT
`entity`.`id_cms`, `entity`.`id_lang`, 1, `entity`.`meta_title`, `entity`.`head_seo_title`, `entity`.`meta_description`, `entity`.`meta_keywords`, `entity`.`content`, `entity`.`link_rewrite`
FROM `PREFIX_cms_lang` entity
LEFT JOIN `PREFIX_cms_lang` entity2 ON `entity2`.`id_shop` = 1 AND `entity`.`id_cms` = `entity2`.`id_cms`
WHERE `entity2`.`id_shop` IS NULL;

This query pre-assumes that no user has customized the columns. Well in real cases modules will add columns to tables. Users will need to add columns to tables to extend functionality, sync with ERPs etc etc.

Unless the main goal of prestashop is to never install third party modules or develop anything this will always cause issues.

And the list of course goes on and on and on and on and on.

To be honest as a last resort I started executing all the files manually one by one and realized that I could get my site to a semi-working state. However this is not all that there is, even the statements from the upgrade/php folder are not executed.

So now I have to find and execute manually all the php scripts one by one to see if I can get to a working state.

This is a very serious issue impacting a lot of users and I've been searching solutions for errors all throughout the forums pointing to the queries that need to be executed, and guess what. Every single one of them, was already in the autoupgrade module but never executed. The only logical explanation is that when an sql command fails for any reason, the autoupgrade module fails to notice it and crashes or finishes it's job ignoring them.

This is in no way proper solution and makes prestashop un-upgradeable. I can provide access to a sandbox to the prestashop team if you guys want to see what a real case scenario looks like and why this module is a total disaster for real-life situations.

In reality the module needs to be refactored to work better.

As this will always require manual work, the least it can do is point us to the problems. I have a suggestion and I would like to know if it's feasible, planned, or if there's any interest at all from the platform maintainers to address this issue.

• When a script is executed there needs to be a pointer when it fails. During autoupgrade, it should mention that X script failed to execute successfully whether sql or php. Ideally we would need to know the exact line which caused the error. All commands in the sql / php scripts could be sequentially executed and output possible errors in execution so that the final result we can get a list of failed queries that we can adjust manually. This can be direct output or an error.log

• Separate schema changes from data changes. If the schema is not correct you shouldn't migrate data. Trying to execute an insert while the add column statement failed because it's duplicate may not always work as the column data type might be different than expected.

• Additional columns in the schema unless specifically deleted on purpose during an upgrade script, should be left untouched and ignored as it could result in data loss

• Insert queries should explicitly set the columns to insert values to and not rely on the columns existing on the table because their count might be different than the select column count.

• Alter table statements which add columns should use an add column if not exists statement instead to bypass other issues with previous executions of the autoupgrade module or previous migrations or other cases where the column already exists in the table for one reason or the other.

• There needs to be schema proper schema comparison for each column description compared to the final target schema. For example it could point out that the schema expects table column X to be unsigned int(10) but it's unsinged int

These are all issues I faced during upgrade that I'm suggesting should be fixed to provide a seemless upgrade.

I come from a Magento 2 background and I'll tell you that they solved this by abstracting table definition in XML files and now all newest version XML files are parsed, and the ORM handles the migrations of the columns. During setup:db-schema:upgrade, among other things the expected schema is collected as an abstract definition and the ORM verifies it against the existing schema. It decides what columns need to be updated, created or deleted based on the schema definition.

Migration to Doctrine should have solved this issue because it would make it much more simple to abstract everything.

Expected behavior

Autoupgrade doesn't break as easily.

Steps to reproduce

1. Install version 1.7.5.2
2. Add a few extra columns in the tables that would be updated by the sql script upgrade/sql/1.7.6.2.sql
3. Try to use autoupgrade module
4. Everything executes but the site stops working

PrestaShop version(s) where the bug happened

1.7.5.2 1.7.8.9

PHP version(s) where the bug happened

7.3 7.4

If your bug is related to a module, specify its name and its version

autoupgrade latest version from repo

Your company or customer's name goes here (if applicable).

IOWEB TECHNOLOGIES

[ioweb-gr]

Also is it possible to execute the php upgrade scripts manually as standalone scripts ?

[Hlavtox]

@ioweb-gr Before other maintainers of the project express their words, my 5 cents.

We should make those queries as modification-resistant as possible, but we can't expect what people do with the tables. Best practice is to not modify the default tables at all, custom data should be put to custom tables.

However, you are doing this upgrade only once, so I think the best way is just to modify the upgrade scripts to your needs. Make a test copy of your store and just try and fix, try and fix. You will eventually make the upgrade work fine, then you can forget it.

[ioweb-gr]

Hi @Hlavtox, until the next upgrade, where the same issue will come up for any additional columns.

I would like to point out that even though in most cases using a separate table is good, I have to disagree for simple one-to-one mapping of rows by a separate table key to ERP id, as this would bring a penalty when we would have to join tables like clean up the data that's no longer in the ERP.

As demonstrated in other discussion, we already suffer from the poor performance of the features_lang table with over 500k rows. Imagine having to join that table against an additional table and the performance hit to syncrhonize these data. Why we would need to have this field? Because they manage everything via their ERP and they have to have clear view on the shop itself of where some data is coming from.

I do think that you cannot cover all cases, but these are very simple cases.

In our case, 90% of the issues came from tables which aleady exist since our migration from 1.6 to 1.7 so it's not like we added them directly. That means simly adding if not exists in the queries at the very least. Even if we don't compare datatypes that's a first step.

All migration tools at that time fetched the structure and the data from the previous database and replicated on the new one as the autoupgrade tool wasn't upgrading from 1.6 to 1.7. The only tools available were -> rebuild your shop -> migrate data and schema. Now we will carry this problem forever with the system making us seriously consider abandoning the platform due to that but it would be a pity for all the work we've done.

In any case proper logging of the failures and the queries that failed would allows us to work through them instead of guessing.
This would be a long term solution even if we have to manually adjust them. Currently when we upgrade, it says it successfully upgraded the db when it hasn't really executed the queries. This is defiinitely a bug.

The features requested are vital for being developer-friendly and assist in the upgrade, but the bug itself should be eliminated. THe autoupgrade module can't report that you upgraded the DB, without actually upgrading it. It's totally misleading

[Hlavtox]

I agree with everything you say!

If you can, it would be the best to make a PR for autoupgrade module that would bring more info on what fails. So we know what to do.

Then, improve those queries one by one so they do the same things, but work with custom database columns. 👍

[ioweb-gr]

I can work on improving some of the queries that I saw causing us issues so it's a start. Would the prestashop maintainers be interested in implementing the logic of making the autoupgrade more verbose as to what it's executing and whether it's successful or not ?

The ones that caused me the most issues are

modules/autoupgrade/upgrade/sql/1.7.8.0.sql
modules/autoupgrade/upgrade/sql/1.7.6.6.sql
modules/autoupgrade/upgrade/sql/1.7.6.2.sql
modules/autoupgrade/upgrade/sql/1.7.7.0.sql
modules/autoupgrade/upgrade/sql/1.7.6.0.sql

The hard part for me is trying to execute the upgrade scripts from

modules/autoupgrade/upgrade/php as not all of them have a version reference and other than

-rw-rw-rw- 1 sys_staging psacln 4.0K Apr  5 11:11 ps_1760_copy_data_from_currency_to_currency_lang.php
-rw-rw-rw- 1 sys_staging psacln 1.3K Apr  5 11:11 ps_1760_update_configuration.php
-rw-rw-rw- 1 sys_staging psacln 2.4K Apr  5 11:11 ps_1760_update_tabs.php
-rw-rw-rw- 1 sys_staging psacln 3.2K Apr  5 11:11 ps_1761_update_currencies.php
-rw-rw-rw- 1 sys_staging psacln 2.1K Apr  5 11:11 ps_1763_update_tabs.php
-rw-rw-rw- 1 sys_staging psacln 1.9K Apr  5 11:11 ps_1770_preset_tab_enabled.php
-rw-rw-rw- 1 sys_staging psacln 3.1K Apr  5 11:11 ps_1770_update_charset.php
-rw-rw-rw- 1 sys_staging psacln 3.2K Apr  5 11:11 ps_1770_update_order_status_colors.php
-rw-rw-rw- 1 sys_staging psacln 1.7K Apr  5 11:11 ps_1771_update_customer_note.php
-rw-rw-rw- 1 sys_staging psacln 1.8K Apr  5 11:11 ps_1780_add_feature_flag_tab.php

It's making it really hard to understand what else I need to execute to upgrade from 1.7.5.2 to 1.7.8.9 as a start.

Also I'm not sure how I can execute them directly from cli even if I include them as direct scripts because the FrontController is blocking me. Any insight on that?

e.g.

<?php
/**
 * Standalone script to execute ps_1760_copy_data_from_currency_to_currency_lang()
 */

// Define the PrestaShop root directory
define('_PS_ROOT_DIR_', __DIR__ . '/');

// Include PrestaShop bootstrap file
require _PS_ROOT_DIR_ . 'config/config.inc.php';

// Include necessary PrestaShop classes
require _PS_ROOT_DIR_ . 'init.php';

require _PS_ROOT_DIR_ . 'modules/autoupgrade/upgrade/php/ps_1760_copy_data_from_currency_to_currency_lang.php';

ps_1760_copy_data_from_currency_to_currency_lang();

will yield

PHP Fatal error:  Uncaught Symfony\Component\Debug\Exception\ContextErrorException: Notice: Undefined index: REQUEST_METHOD in /example.com/httpdocs/classes/controller/FrontController.php:809
Stack trace:
#0 /example.com/httpdocs/classes/controller/FrontController.php(317): FrontControllerCore->sslRedirection()
#1 /example.com/httpdocs/init.php(31): FrontControllerCore->init()
#2 /example.com/httpdocs/custom-upgrader.php(13): require('/upgrade.stagin...')
#3 {main}
  thrown in /example.com/httpdocs/classes/controller/FrontController.php on line 809

Fatal error: Uncaught Symfony\Component\Debug\Exception\ContextErrorException: Notice: Undefined index: REQUEST_METHOD in /example.com/httpdocs/classes/controller/FrontController.php:809
Stack trace:
#0 /example.com/httpdocs/classes/controller/FrontController.php(317): FrontControllerCore->sslRedirection()
#1 /example.com/httpdocs/init.php(31): FrontControllerCore->init()
#2 /example.com/httpdocs/custom-upgrader.php(13): require('/upgrade.stagin...')
#3 {main}
  thrown in /example.com/httpdocs/classes/controller/FrontController.php on line 809

[Hlavtox]

@ioweb-gr You should be able to run the scripts manually by running http://<shop_domain>/modules/autoupgrade/upgrade/upgrade.php

more info - https://devdocs.prestashop-project.org/8/basics/keeping-up-to-date/upgrade/

[ioweb-gr]

It seems that the path is wrong for the vendor libraries

<?php
/**
 * Copyright since 2007 PrestaShop SA and Contributors
 * PrestaShop is an International Registered Trademark & Property of PrestaShop SA
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * that is bundled with this package in the file LICENSE.md.
 * It is also available through the world-wide-web at this URL:
 * https://opensource.org/licenses/OSL-3.0
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@prestashop.com so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade PrestaShop to newer
 * versions in the future. If you wish to customize PrestaShop for your
 * needs please refer to https://devdocs.prestashop.com/ for more information.
 *
 * @author    PrestaShop SA and Contributors <contact@prestashop.com>
 * @copyright Since 2007 PrestaShop SA and Contributors
 * @license   https://opensource.org/licenses/OSL-3.0 Open Software License (OSL 3.0)
 */

use PrestaShop\PrestaShop\Core\Foundation\Filesystem\FileSystem;
use PrestaShopBundle\Install\Upgrade;

// Although no arguments execute the script, you can get some help if requested.
if (isset($argv) && is_array($argv) && in_array('--help', $argv)) {
    displayHelp();
    exit(0);
}

$filePrefix = 'PREFIX_';
$engineType = 'ENGINE_TYPE';
define('PS_IN_UPGRADE', 1);

// remove old unsupported classes
@unlink(__DIR__ . '/../../classes/db/MySQL.php');

if (isset($_GET['adminDir']) && $_GET['adminDir'] && !defined('_PS_ADMIN_DIR_')) {
    define('_PS_ADMIN_DIR_', base64_decode($_GET['adminDir']));
}

require_once __DIR__ . '/../../vendor/autoload.php';
require_once __DIR__ . '/../../config/defines.inc.php';
require_once __DIR__ . '/../../config/autoload.php';
Upgrade::migrateSettingsFile();
require_once __DIR__ . '/../classes/datas.php';
require_once dirname(__FILE__) . '/../init.php';
require_once _PS_CONFIG_DIR_ . 'bootstrap.php';

$logDir = _PS_ROOT_DIR_ . '/var/logs/' . _PS_ENV_ . '/';
@mkdir($logDir, FileSystem::DEFAULT_MODE_FOLDER, true);

$upgrade = new Upgrade($logDir, dirname(dirname(__FILE__)) . '/');
if (isset($_GET['autoupgrade']) && $_GET['autoupgrade'] == 1) {
    $upgrade->setInAutoUpgrade(true);
}

if (isset($_GET['deactivateCustomModule']) && $_GET['deactivateCustomModule'] == '1') {
    $upgrade->setDisableCustomModules(true);
}

if (isset($_GET['updateDefaultTheme']) && $_GET['updateDefaultTheme']
    && 'classic' === _THEME_NAME_) {
    $upgrade->setUpdateDefaultTheme(true);
}

if (isset($_GET['adminDir']) && $_GET['adminDir']) {
    $upgrade->setAdminDir(base64_decode($_GET['adminDir']));
}

if (isset($_GET['idEmployee'])) {
    $upgrade->setIdEmployee($_GET['idEmployee']);
}

if (isset($_GET['changeToDefaultTheme']) && $_GET['changeToDefaultTheme'] == 1) {
    $upgrade->setChangeToDefaultTheme(true);
}

if (function_exists('date_default_timezone_set')) {
    date_default_timezone_set('Europe/Paris');
}

if (isset($_GET['action']) && method_exists($upgrade, 'do' . $_GET['action'])) {
    $action = 'do' . $_GET['action'];
    $upgrade->$action();
} else {
    $upgrade->run();
}

$result = '<?xml version="1.0" encoding="UTF-8"?>';
if (!$upgrade->hasFailure()) {
    if (!isset($_GET['action']) || 'UpgradeComplete' === $_GET['action']) {
        Configuration::updateValue('PS_HIDE_OPTIMIZATION_TIPS', 0);
        Configuration::updateValue('PS_NEED_REBUILD_INDEX', 1);
        Configuration::updateValue('PS_VERSION_DB', _PS_INSTALL_VERSION_);
    }

    $result .= '<action result="ok" id="">' . "\n";
    foreach ($upgrade->getInfoList() as $info) {
        $result .= $info . "\n";
    }

    foreach ($upgrade->getWarningList() as $warning) {
        $result .= $warning . "\n";
    }
} else {
    foreach ($upgrade->getFailureList() as $failure) {
        $result .= $failure . "\n";
    }
}

if ($upgrade->getInAutoUpgrade()) {
    header('Content-Type: application/json');
    echo json_encode([
        'nextQuickInfo' => $upgrade->getNextQuickInfo(),
        'nextErrors' => $upgrade->getNextErrors(),
        'next' => $upgrade->getNext(),
        'nextDesc' => $upgrade->getNextDesc(),
        'warningExists' => $upgrade->hasWarning(),
    ]);
} else {
    header('Content-Type: text/xml');
    echo $result;
}

/**
 * displays the help
 */
function displayHelp()
{
    echo <<<'EOF'
PrestaShop upgrade

This script can be called directly and is used by the 1-click upgrade module. It ouputs xml in the first case and json data for the module.
It is mainly used for the database migration of your shop. Logs will be registered in your var/logs/<env> folder.
------------------
Options
--help               Display this message

EOF;
}

Warning: require_once(/var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/modules/autoupgrade/upgrade/../../vendor/autoload.php): failed to open stream: No such file or directory in /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/modules/autoupgrade/upgrade/upgrade.php on line 47

Fatal error: require_once(): Failed opening required '/var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/modules/autoupgrade/upgrade/../../vendor/autoload.php' (include_path='.:/opt/phpbrew/php/phpbrew7_4_32_fdev/lib/php/pear') in /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/modules/autoupgrade/upgrade/upgrade.php on line 47

I guess all it's paths need to be modified to make it work. The correct vendor dir to use is the one included in the autoupgrade module or the PS_ROOT_DIR?

[ioweb-gr]

Even after fixing the paths the file

require_once DIR . '/../classes/datas.php';

Does not exist. I can see it's dynamically created at

/admin_dev/autoupgrade/latest/install/classes/datas.php

[ioweb-gr]

Even after fixing all paths

Notice: Trying to get property 'locale' of non-object in /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/Context.php on line 399

Warning: Use of undefined constant _DB_PREFIX_ - assumed '_DB_PREFIX_' (this will throw an Error in a future version of PHP) in /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/Configuration.php on line 155

Warning: Use of undefined constant _DB_SERVER_ - assumed '_DB_SERVER_' (this will throw an Error in a future version of PHP) in /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/db/Db.php on line 219

Warning: Use of undefined constant _DB_USER_ - assumed '_DB_USER_' (this will throw an Error in a future version of PHP) in /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/db/Db.php on line 219

Warning: Use of undefined constant _DB_PASSWD_ - assumed '_DB_PASSWD_' (this will throw an Error in a future version of PHP) in /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/db/Db.php on line 219

Warning: Use of undefined constant _DB_NAME_ - assumed '_DB_NAME_' (this will throw an Error in a future version of PHP) in /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/db/Db.php on line 219

Fatal error: Uncaught PrestaShopException: Link to database cannot be established: SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: Temporary failure in name resolution in /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/db/DbPDO.php:137 Stack trace: #0 /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/db/Db.php(330): DbPDOCore->connect() #1 /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/db/Db.php(241): DbCore->__construct() #2 /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/config/alias.php(47): DbCore::getInstance() #3 /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/config/alias.php(52): pSQL() #4 /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/Configuration.php(155): bqSQL() #5 /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/Configuration.php(209): ConfigurationCore::loadConfiguration() #6 in /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/db/DbPDO.php on line 137

[matks]

Hello @ioweb-gr I have updated the title of this (very interesting) discussion.

I am not saying it's not a huge issue, but the title of the issues aims to

• provide informations about its content
• make it easier for people to find it using keywords

So I renamed it with that idea 😄 "Huge issue" does not help these 2 goals

[ioweb-gr]

@ioweb-gr You should be able to run the scripts manually by running http://<shop_domain>/modules/autoupgrade/upgrade/upgrade.php
more info - https://devdocs.prestashop-project.org/8/basics/keeping-up-to-date/upgrade/

Therefore it's not possible to do this way

@matks not a problem :)

[ioweb-gr]

however @matks I think the title is misleading now as it doesn't gather the whole range of the issues depicted here.

For example without any additional column this script will always fail

\ps_1770_preset_tab_enabled

<?php
/**
 * Copyright since 2007 PrestaShop SA and Contributors
 * PrestaShop is an International Registered Trademark & Property of PrestaShop SA
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * that is bundled with this package in the file LICENSE.md.
 * It is also available through the world-wide-web at this URL:
 * https://opensource.org/licenses/OSL-3.0
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@prestashop.com so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade PrestaShop to newer
 * versions in the future. If you wish to customize PrestaShop for your
 * needs please refer to https://devdocs.prestashop.com/ for more information.
 *
 * @author    PrestaShop SA and Contributors <contact@prestashop.com>
 * @copyright Since 2007 PrestaShop SA and Contributors
 * @license   https://opensource.org/licenses/OSL-3.0 Open Software License (OSL 3.0)
 */

/**
 * Preset enabled new column in tabs to true for all (except for disabled modules)
 */
function ps_1770_preset_tab_enabled()
{
    //First set all tabs enabled
    $result = Db::getInstance()->execute(
        'UPDATE `' . _DB_PREFIX_ . 'tab` SET `enabled` = 1'
    );

    //Then search for inactive modules and disable their tabs
    $inactiveModules = Db::getInstance()->executeS(
        'SELECT `name` FROM `' . _DB_PREFIX_ . 'module` WHERE `active` != 1'
    );
    $moduleNames = [];
    foreach ($inactiveModules as $inactiveModule) {
        $moduleNames[] = $inactiveModule['name'];
    }
    if (count($moduleNames) > 0) {
        $result &= Db::getInstance()->execute(
            'UPDATE `' . _DB_PREFIX_ . 'tab` SET `enabled` = 0 WHERE `module` IN (' . implode(',', $moduleNames) . ')'
        );
    }

    return $result;
}

If you check here, the name of each module is not enclosed in single quotes creating an sql query like this for example

Unknown column 'jscomposer' in 'where clause'

UPDATE ps_tab SET enabled = 0 WHERE module IN (jscomposer)

So anyone upgrading higher than version 1.7.7.0 would experience a catastrophic failure because of subsequent errors not executing. I guess that's why I found so many issues reported in the forums about this

This is not related to any customization

Take this also into example

modules/autoupgrade/upgrade/php/ps_1770_update_charset.php

<?php
/**
 * Copyright since 2007 PrestaShop SA and Contributors
 * PrestaShop is an International Registered Trademark & Property of PrestaShop SA
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * that is bundled with this package in the file LICENSE.md.
 * It is also available through the world-wide-web at this URL:
 * https://opensource.org/licenses/OSL-3.0
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@prestashop.com so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade PrestaShop to newer
 * versions in the future. If you wish to customize PrestaShop for your
 * needs please refer to https://devdocs.prestashop.com/ for more information.
 *
 * @author    PrestaShop SA and Contributors <contact@prestashop.com>
 * @copyright Since 2007 PrestaShop SA and Contributors
 * @license   https://opensource.org/licenses/OSL-3.0 Open Software License (OSL 3.0)
 */
function ps_1770_update_charset()
{
    $adminFilterTableExists = $adminFilterFilterIdExists = $moduleHistoryTableExists = $translationTableExists = false;

    try {
        $adminFilterTableExists = (bool) Db::getInstance()->executeS(
            'SELECT count(*) FROM ' . _DB_PREFIX_ . 'admin_filter'
        );
        if ($adminFilterTableExists) {
            $adminFilterFilterIdExists = (bool) Db::getInstance()->executeS(
                'SELECT count(filter_id) FROM ' . _DB_PREFIX_ . 'admin_filter'
            );
        }
    } catch (Exception $e) {
    }

    try {
        $moduleHistoryTableExists = (bool) Db::getInstance()->executeS(
            'SELECT count(*) FROM ' . _DB_PREFIX_ . 'module_history'
        );
    } catch (Exception $e) {
    }

    try {
        $translationTableExists = (bool) Db::getInstance()->executeS(
            'SELECT count(*) FROM ' . _DB_PREFIX_ . 'translation'
        );
    } catch (Exception $e) {
    }

    $result = true;

    if ($adminFilterTableExists) {
        if ($adminFilterFilterIdExists) {
            $result &= Db::getInstance()->execute(
                'UPDATE ' . _DB_PREFIX_ . '`admin_filter` SET `filter_id` = SUBSTRING(`filter_id`, 1, 191)'
            );
            $result &= Db::getInstance()->execute(
                'ALTER TABLE ' . _DB_PREFIX_ . '`admin_filter` CHANGE `filter_id` `filter_id` VARCHAR(191) NOT NULL'
            );
        }
        $result &= Db::getInstance()->execute(
            'ALTER TABLE `PREFIX_admin_filter` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci'
        );
    }

    if ($moduleHistoryTableExists) {
        $result &= Db::getInstance()->execute(
            'ALTER TABLE `PREFIX_module_history` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci'
        );
    }

    if ($translationTableExists) {
        $result &= Db::getInstance()->execute(
            'ALTER TABLE `PREFIX_translation` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci'
        );
    }

    return (bool) $result;
}

This will throw an error because the query that gets executed

Table 'db_upgrade.PREFIX_admin_filter' doesn't exist<br /><br /><pre>ALTER TABLE PREFIX_admin_filter CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci</pre>

Doesn't get it's prefix replaced.

If you notice half queries have the DB_PREFIX concatenated inthe string , and half of them have them inside the query itself.

I doubt this executes correctly in any update scenario either

Hence why this issue is general about the autoupgrade procedure which is a total disaster due to not having any abstraction causing these errors in the first place or a test scenario to cover them or anything to prevent someone from writing these codes

[ioweb-gr]

Another example

modules/autoupgrade/upgrade/php/ps_1761_update_currencies.php

This one can never execute in PHP context as prior to it's execution Prestashop needs to be initialized. This cannot happen because to initialize it, the currencies must be there. It's an unresolvable circular dependency basically

[matks]

however @matks I think the title is misleading now as it doesn't gather the whole range of the issues depicted here.

OK but then what do I write 😄 ? "General problem with autoupgrade module"?

The title must give a quick hint about the github issue content 😉

[ioweb-gr]

I know right? :)

The problem is this is an issue with multiple sub-issues, it should be used to gather problems together in my opinion.

THis way it can be safely ignored because it hints that only if you customize the columns there's an issue. In reality it's not.

How about

[EPIC] Autoupgrade fails to upgrade when any sql command fails to execute due to poor implementations?

Then we can gather them up in one place, create sub-issues and process them in PRs

[hibatallahAouadni]

@PrestaShop/committers could someone please try to reproduce the issue 🙏 and change the labels ;)

[Hlavtox]

• ps_1770_preset_tab_enabled - already fixed
• ps_1770_update_charset - Fix minor issues in upgrade process autoupgrade#620
• INSERT INTO PREFIX_hook_alias - Fix minor issues in upgrade process autoupgrade#620

The rest is not clear or result of previous failed upgrades.

[Hlavtox]

@ioweb-gr Closing this for now, if you encounter some other issues on customized shops, please let me know. We can improve it so it's more stable. :-)