wallabag: Migration from 2.3.8 to 2.4.2 fails

Environment

  • Version: 2.3.8
  • Installation: Yunohost package
  • PHP version: 7.4
  • OS: Debian
  • Database: MySQL

What steps will reproduce the bug?

This happens on an update with the yunohost package but this is related to a SQL migration so I thing this is a wallabag issue, or at least you might be able to help?

  1. Install yunohost package (current version = 2.3.8 )
  2. Try to upgrade with yunohost app upgrade wallabag2 -u https://github.com/YunoHost-Apps/wallabag2_ynh/tree/testing

Applying migration 20190510141130 throws some SQL errors when dropping some foreign keys:

2021-04-22 06:42:07,936: DEBUG -   ++ migrating 20190510141130
2021-04-22 06:42:07,936: DEBUG - 
2021-04-22 06:42:08,036: DEBUG -      -> ALTER TABLE `oauth2_access_tokens` DROP FOREIGN KEY FK_368A4209A76ED395
2021-04-22 06:42:08,038: WARNING - 06:42:07 ERROR     [console] Error thrown while running command "--no-interaction --env prod doctrine:migrations:migrate". Message: "An exception occurred while executing 'ALTER TABLE `oauth2_access_tokens` DROP FOREIGN KEY FK_368A4209A76ED395':
2021-04-22 06:42:08,038: DEBUG - Migration 20190510141130 failed during Execution. Error An exception occurred while executing 'ALTER TABLE `oauth2_access_tokens` DROP FOREIGN KEY FK_368A4209A76ED395':
2021-04-22 06:42:08,038: WARNING - SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP FOREIGN KEY `FK_368A4209A76ED395`; check that it exists" ["exception" => Doctrine\DBAL\Exception\DriverException { …},"command" => "--no-interaction --env prod doctrine:migrations:migrate","message" => """  An exception occurred while executing 'ALTER TABLE `oauth2_access_tokens` DROP FOREIGN KEY FK_368A4209A76ED395':\n  \n  SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP FOREIGN KEY `FK_368A4209A76ED395`; check that it exists  """]
2021-04-22 06:42:08,039: DEBUG - SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP FOREIGN KEY `FK_368A4209A76ED395`; check that it exists
2021-04-22 06:42:08,039: WARNING - In AbstractMySQLDriver.php line 110:
2021-04-22 06:42:08,040: WARNING -   An exception occurred while executing 'ALTER TABLE `oauth2_access_tokens` DROP FOREIGN KEY FK_368A42
2021-04-22 06:42:08,040: WARNING -   09A76ED395':
2021-04-22 06:42:08,041: WARNING -   SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP FOREIGN KEY `FK_368A4209A76ED395`
2021-04-22 06:42:08,041: WARNING -   ; check that it exists
2021-04-22 06:42:08,042: WARNING - In PDOConnection.php line 91:
2021-04-22 06:42:08,042: WARNING -   SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP FOREIGN KEY `FK_368A4209A76ED395`
2021-04-22 06:42:08,042: WARNING -   ; check that it exists
2021-04-22 06:42:08,043: WARNING - In PDOConnection.php line 86:
2021-04-22 06:42:08,043: WARNING -   SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP FOREIGN KEY `FK_368A4209A76ED395`
2021-04-22 06:42:08,043: WARNING -   ; check that it exists

I tried listing the foreign keys but I might have the wrong SQL query:

MariaDB [wallabag2]> SELECT    TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE   REFERENCED_TABLE_SCHEMA = 'wallabag2' AND   REFERENCED_TABLE_NAME = 'oauth2_access_tokens';
Empty set (0.027 sec)

MariaDB [wallabag2]> 

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Reactions: 1
  • Comments: 45 (44 by maintainers)

Most upvoted comments

I’ll try to look at it this evening

@Kdecherf I tried your commands, with no error raised.

Here is the log : https://pastebin.com/c5rsQX45 (link valid for 6 months)

Now I am trying to upgrade to v2.4.3.

edit: It worked ! 🎉

I’m closing this issue since a workaround is available, for the record: https://github.com/wallabag/wallabag/issues/5233#issuecomment-1041863310

@bruce-willis probably because you first installed it at version 2.3.0 or later

@nicofrand:

try others to do the upgrade and see…

I’ve just updated from yunohost master branch to 2.4.3 without any problems

install log

sudo yunohost app upgrade wallabag2 -u https://github.com/YunoHost-Apps/wallabag2_ynh/tree/2.4.3
Info: Now upgrading wallabag2...
Info: [....................] > Loading installation settings...
Info: [....................] > Checking version...
Info: [++++++++++..........] > Backing up the app before upgrading (may take a while)...
Info: [##########..........] > Ensuring downward compatibility...
Info: [##########+.........] > Making sure dedicated system user exists...
Info: [###########++.......] > Upgrading source files...
Info: [#############.......] > Upgrading NGINX web server configuration...
Info: [#############++.....] > Upgrading dependencies...
Info: [###############.....] > Upgrading PHP-FPM configuration...
Info: The service fail2ban has correctly executed the action reload-or-restart.
Info: [###############++++.] > Reconfiguring wallabag...
Info: [###################.] > Reloading NGINX web server...
Info: [####################] > Upgrade of wallabag2 completed
Success! wallabag2 upgraded
Success! Upgrade complete

yunohost update logs

Let me know if you need any further info

If the IDs are random (due to :create) then @lapineige you should have different key names than the ones I provided. Could you check that’s the case?

No, FK and UNIQ names are derived from a CRC32 of the column name^1. By default we have a table prefix value of wallabag_. On yunohost package the prefix was removed. So the “random” name should be consistent across all yunohost installs.

It seems that doctrine:schema:create was used during wallabag:install in pre 2.3.0 versions, which could explain why you have “broken” installs. EDIT: the call was replaced with doctrine:migrations:migrate in 2680b0bc8c9044b19b80a596f0005a1051b4ee54

I’ll reproduce it locally and I’ll try to find a sql statement to clean up that mess. However I don’t think I’ll include it in the wallabag codebase.

Would it be possible for you to play custom statements on your installs?

Okay, I may found the culprit, will confirm it

I didn’t find anything useful in wallabag2_ynh with git rev-list --all | xargs git grep doctrine: 😦

Ok I found the reproduction path, I need to find the best way to handle this corner case now

I don’t think so, it may be related to the way wallabag is installed in the first place.

I’m trying to reproduce the issue on my side

Managing migration for indexes is really a pain…

Since mariadb 10.0.2 IF EXISTS can be used for DROP FOREIGN KEY: https://jira.mariadb.org/browse/MDEV-318 Maybe this would be enough?

Hi!

I did not try, there was no fix as far as I know.

@Kdecherf Could you guide us towards a way to fix this please? Maybe something like https://github.com/wallabag/wallabag/blob/master/app/DoctrineMigrations/Version20190510141130.php should be done?

Also, should 20190510141130 migration be edited (running another migration after won’t work)?