Prestashop: SQL Error when using one click upgrade

[Prestaworks]

Describe the bug

I used one click upgrade to upgrade from an earlier 1.7 version to the latest. Now I can't access some pages in BO because there are something wrong in the DB.

Expected behavior

No missing tables and able to access all pages.

Additional information

The following error is logged, and indicate something is bad with the upgrade scripts.

[prestashop-issue-bot[bot]]

Thanks for opening this issue! We will help you to keep its state consistent

[SimonGrn]

Hello,

please follow the template and explain your process exactly.
What was the PS original version and what is the target version ? What PHP version are you using ?

[Prestaworks]

Hi Simon,

Sorry I don't remember what version it was. But check the SQL scripts in the upgrade module, it seems to be missing setting an auto_increment on the table ps_admin_filter.

[SimonGrn]

We test a lot of upgrades for every release and we didn't encounter this problem, so that's why I'm asking you that, because we need to rule out a problem in your own environment.

[Prestaworks]

Hi Simon,

okej thats good to head. I can send the upgradelog
https://we.tl/t-zKBPXQ7wqE
it shows the upgrades that was made on that env. Maybe it gives a clue?

[SimonGrn]

We use GitHub issues only to discuss about bugs and new features in the PrestaShop project. If you have questions about using PrestaShop or third-party modules, or if you need help with your shop, please consider one of our support plans.

Alternatively, you can also ask for help in the community forums or in the public Slack channel.

Thank you

[jayala]

Was having a similar issue after upgrading to 1.7.6.9 from 1.7.5.1. Somehow filter_id field didn't exist and the constraint was wrong.

I fixed it by installing a new prestashop on the same version and comparing the table structure, then I noticed the difference.

Changes I applied:

alter table `ps_admin_filter` change filter_id filter_id varchar(255) not null;
ALTER TABLE ps_admin_filter DROP CONSTRAINT admin_filter_search_idx;
ALTER TABLE ps_admin_filter ADD CONSTRAINT admin_filter_search_id_idx UNIQUE KEY (employee,shop,controller,`action`,filter_id);

Regards

[Prestaworks]

@SimonGrn I thought you said you had tested this?

[SimonGrn]

@jayala we need more information about your configuration: what database do you use (MySQL, MariaDB) ? What version of PHP ? Did you use the one-click upgrade module ? With the ZIP archive or from the official channel ?

Thank you

[SimonGrn]

@Prestaworks so the whole QA team stopped working on PR validation, issue management, and specifications to test an upgrade from 1.7.5.1 to 1.7.6.9. I hope this will satisfy you.
Here is my video, using 1-click upgrade and the official channel: https://drive.google.com/file/d/1I88z4kE_Fuzsj4hBFlribDkeP9aCtcDo/view?usp=sharing

Here is the video from another QA tester : https://drive.google.com/file/d/1SAvuyaGpTf4Az6UukY4fSS0ebee2h8Fh/view?usp=sharing (after the upgrade she had an error connecting to the BO, clearing the cache solved the problem).

The smooth process has been confirmed by 4 people including me.

I'm sorry but your problem almost surely lies in your configuration, a modification you made, or something unexpected. This is why I redirected you to the support. Unless we're able to pinpoint a clear issue originating in the Core itself, we will not be able to help you in Github.

Edit: a third video from a third QA tester : https://drive.google.com/file/d/1utIhNH6i_mdjjSuVeR9SvsHxErKU8q0j/view (first part) then https://drive.google.com/file/d/1aptgTrgwFT7Tqg_kiiXZMhLZmv4Fdg4R/view (second part).

[Prestaworks]

@SimonGrn well, there are now another user with the same problem. You have the solution from him on the issue and the entire upgrade log that I provided. Maybe the easy way then would be to just check what file is supposed to alter those tables? maybe there is an issue there? maybe the alter table code for that part require a specific mysql version? or maybe it's becuase the table has data in it that is bad for some reason? I have also tested two upgrades today from 1.7.5.1 -> 1.7.6.9 one from clean install and one where the admin_filter table had some data in it. Both worked fine.
I used one click upgrade module v4.10.1
Maybe this issue is in another version of the module, or maybe there is some specific needs on the database version.
Can you point me to the code in the module where these alter tables are supposed to be? then I can take a quick look also. Other than that I have not seen this issue other than one time, and it was easy to fix when it happened.

And yes, I am very satisfied that you take it seriously when there are two users reporting an issue with the database after doing an upgrade. It is VERY important that these things work well if shops are going to be able to upgrade to newer versions.

[SimonGrn]

We are not able to reproduce the problem and we cannot use more time to investigate. Either somebody is able to give us precise steps to reproduce the issue 100% of the time, or we will no longer be able to work on it, sadly.
This is a matter of priority and balance of team effort, working in an Open Source project that belongs to the community and not only the PrestaShop company. As a reminder, if an issue is very important for you, you are welcome to work on it or even hire somebody to help you, then help the whole project by providing the fix. This is the essence of Open Source.

Thank you for your understanding.

[eternoendless]

The schema for ps_admin_filter is managed by Doctrine using the AdminFilter entity. The only SQL alters for that table are done in 1.7.7.0 (I searched for PREFIX_admin_filter), the rest is managed by Doctrine schema update. As far as I can tell something else removed the auto increment in your project's schema, because this has remained unchanged since 1.7.0.0.

[jayala]

Well, this was the DDL of ps_admin_filter before the update:

CREATE TABLE `ps_admin_filter` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee` int(11) NOT NULL,
  `shop` int(11) NOT NULL,
  `controller` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `action` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `filter` longtext COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `admin_filter_search_idx` (`employee`,`shop`,`controller`,`action`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

And this was the DDL of the fresh 1.7.6.9 version I downloaded from the official channel:

CREATE TABLE `ps_admin_filter` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee` int(11) NOT NULL,
  `shop` int(11) NOT NULL,
  `controller` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `action` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `filter` longtext COLLATE utf8_unicode_ci NOT NULL,
  `filter_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `admin_filter_search_id_idx` (`employee`,`shop`,`controller`,`action`,`filter_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I don't know if this problem comes from the one click upgrade, since this database was from an theme I bought from the Prestashop Store.

But given the case that this happened to another user, I thought I was important to take this to you.

MariaDB: 10.4.14-MariaDB-1:10.4.14+maria~bionic
PHP: PHP 7.2.24-1+ubuntu18.04.1+deb.sury.org+1
1-Click upgrade: YES, version 4.10.1