Skip to content

Problem with ALTER table during Update #21948

New issue
New issue
Closed
#21975
Closed
Problem with ALTER table during Update#21948
#21975
Assignees
PierreRambaud
Labels
1.7.7.0Affects versionsBOCategory: Back OfficeBugType: BugDatabaseLabel: Which BO under menu is concernedFixedResolution: issue closed because fixedMajorSeverity: major bug > https://build.prestashop.com/news/severity-classificationMust-haveKanban prioritization: issue must be included in next versionPR availableSolution: issue is being addressed
Milestone
 
1.7.7.0
@ghost

Description

@ghost
ghost
opened on Nov 17, 2020

Describe the bug

Problem with ALTER table during Update

Steps to Reproduce

Make an update 1769 --> 1770

Additional information

Linux #799710 SMP Wed Jul 10 08:31:28 UTC 2019 x86_64

Version du logiciel serveur : Apache

Version de PHP : 7.2.34
Limite de mémoire : 2048M
Temps maximal d'exécution : 18000
Taille max. pour envoi de fichiers : 10M
info_outline Informations sur la base de données

Version de MySQL : 10.1.45-MariaDB-0+deb9u1
Serveur MySQL : localhost
Nom MySQL : 176
Utilisateur MySQL : 176
Préfixe des tables : ps_
Moteur MySQL : InnoDB
Connecteur MySQL : DbPDO

SQL 1.7.7.0 1071 in ALTER TABLE `ps_admin_filter` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci: Specified key was too long; max key length is 767 bytes
SQL 1.7.7.0 1071 in ALTER TABLE `ps_alias` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci: Specified key was too long; max key length is 767 bytes
SQL 1.7.7.0 1071 in ALTER TABLE `ps_authorization_role` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci: Specified key was too long; max key length is 767 bytes
SQL 1.7.7.0 1071 in ALTER TABLE `ps_module_preference` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci: Specified key was too long; max key length is 767 bytes
SQL 1.7.7.0 1071 in ALTER TABLE `ps_smarty_lazy_cache` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci: Specified key was too long; max key length is 767 bytes
SQL 1.7.7.0 1071 in ALTER TABLE `ps_tab_module_preference` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci: Specified key was too long; max key length is 767 bytes
SQL 1.7.7.0 1071 in ALTER TABLE `ps_admin_filter` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci: Specified key was too long; max key length is 767 bytes
SQL 1.7.7.0 1071 in ALTER TABLE `ps_alias` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci: Specified key was too long; max key length is 767 bytes
SQL 1.7.7.0 1071 in ALTER TABLE `ps_authorization_role` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci: Specified key was too long; max key length is 767 bytes
SQL 1.7.7.0 1071 in ALTER TABLE `ps_module_preference` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci: Specified key was too long; max key length is 767 bytes
SQL 1.7.7.0 1071 in ALTER TABLE `ps_smarty_lazy_cache` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci: Specified key was too long; max key length is 767 bytes
SQL 1.7.7.0 1071 in ALTER TABLE `ps_tab_module_preference` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci: Specified key was too long; max key length is 767 bytes

Activity

matks

matks commented on Nov 17, 2020

@matks
matks
on Nov 17, 2020
Contributor

Hi @okom3pom let's explore this topic together 😄

So if I get correctly

  • in PS 1.7.7.0 we converted columns to utf8mb4 character set
  • when performing the update on your MySQL server you get error messages
  • this is because max key length is reached

So it would be because of the size of your database 🤔 ? ping @PierreRambaud

Can you tell us the size of these tables in your server (count + KB size) ?

ghost

ghost commented on Nov 17, 2020

@ghost
ghost
on Nov 17, 2020

It's a new install only demo product :)

i think porblem is mysql version or maria db version, i found a lot of topic with this issue

hibatallahAouadni
added
1.7.7.0Affects versions
BOCategory: Back Office
DatabaseLabel: Which BO under menu is concerned
Waiting for devStatus: action required, waiting for tech feedback
on Nov 17, 2020
hibatallahAouadni
mentioned this in 2 issues on Nov 17, 2020
PierreRambaud

PierreRambaud commented on Nov 17, 2020

@PierreRambaud
PierreRambaud
on Nov 17, 2020
Contributor

Hi,
It's not because of the size of the database, but the size of the fields. I just don't understand how it is possible to reach more than 767 in ps_admin_filter for example 🤔
Did you add indexes on fields, or did you change something?

ghost

ghost commented on Nov 17, 2020

@ghost
ghost
on Nov 17, 2020

@PierreRambaud it's a new install without change

ghost

ghost commented on Nov 18, 2020

@ghost
ghost
on Nov 18, 2020

Here the differences :

176 :
structure176

177 :
structure177

If i run :

ALTER TABLE `ps_admin_filter` CHANGE `filter_id` `filter_id` VARCHAR(191) NOT NULL;

Before

ALTER TABLE `ps_admin_filter` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

It's ok, il check all tables with the error and it's the same 255 and 191

I also found a lot of duplicate querys, the best is to remove in different PR ?

Regards

ghost

ghost commented on Nov 18, 2020

@ghost
ghost
on Nov 18, 2020

Something like this, fixing for me but i don't know if a row is greater than 191 ( cache_id ? )

ALTER TABLE `PREFIX_admin_filter` CHANGE `filter_id` `filter_id` VARCHAR(191) NOT NULL;
ALTER TABLE `PREFIX_alias` CHANGE `alias` `alias` VARCHAR(191) NOT NULL;
ALTER TABLE `PREFIX_authorization_role` CHANGE `slug` `slug` VARCHAR(191) NOT NULL;
ALTER TABLE `PREFIX_module_preference` CHANGE `module` `module` VARCHAR(191) NOT NULL;
ALTER TABLE `PREFIX_smarty_lazy_cache` CHANGE `cache_id` `cache_id` VARCHAR(191) NOT NULL;
ALTER TABLE `PREFIX_hook_tab_module_preference` CHANGE `module` `module` VARCHAR(191) NOT NULL;

@PierreRambaud

PierreRambaud
added
Must-haveKanban prioritization: issue must be included in next version
on Nov 18, 2020
PierreRambaud

PierreRambaud commented on Nov 18, 2020

@PierreRambaud
PierreRambaud
on Nov 18, 2020
Contributor

Yes, there are a lot of duplicates, look like a rebase has failed :(
Thanks for your report. We will fix it today:)

PierreRambaud
added this to the 1.7.7.0 milestone on Nov 18, 2020

17 remaining items

Loading
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

1.7.7.0Affects versionsBOCategory: Back OfficeBugType: BugDatabaseLabel: Which BO under menu is concernedFixedResolution: issue closed because fixedMajorSeverity: major bug > https://build.prestashop.com/news/severity-classificationMust-haveKanban prioritization: issue must be included in next versionPR availableSolution: issue is being addressed

Type

No type

Projects

No projects

Milestone

Relationships

None yet

    Development

    Participants

    @PierreRambaud@Progi1984@matks@marionf@hibatallahAouadni

    Issue actions
      Problem with ALTER table during Update · Issue #21948 · PrestaShop/PrestaShop