Repository: ampache/ampache (language: php)

Issue:
Database upgrade failure during upgrade from Ampache 4.2.3 to 5.0.0

## Description

Database upgrade failure during upgrade from Ampache 4.2.3 to 5.0.0

### Describe the bug

While in the process of upgrading from Ampache 4.2.3 to 5.0.0 the upgrade procedure asked to upgrade the database from 400012 to 500015 as usual. However the upgrade procedure seemed to fail. BTW, one cannot really see what is going on because the only sympton is that one is at the database upgrade page again, with no clear indication that the database upgrade procedure failed.
After attempting to upgrade the database again the the upgrade goes fine. So actually for me it is not a problem since now I have a working and running Ampache server, but I just wanted to report it since there might be something fishy during the upgrade procedure. 
The logs pasted bellow show that the problem is with the podcast table. However in this server I don't actually have any podcast configured.
This server has been upgraded several times, always using official Ampache versions, so it could be that the database contained some relics of older versions. Apparently it tries to remove columns "catalog" from the podcast_episode table, but that column didn't exist in the database when Ampache was running with version 4.2.3. 

I have pasted the logs below

### To reproduce

Steps to reproduce the behavior:

1. Use the latest Ampache 5.0.0 release to upgrade a existing 4.2.3 Ampache server
2. Connect to the Ampache server and follow the instructions to upgrade the database.

### Expected behavior
<!-- What you believe should have happened -->

### Screenshots
<!-- If applicable, add screenshots to help explain your problem. -->

## Environment

* Ampache version: 5.0.0 
* Web server + version: apache 2.4.48
* Server operating system: Raspbian 11
* Client operating system: Debian 11

### Client type

* Web

## Logs

2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: starting
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: checking versions
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 400012
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 400013
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 400014
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 400015
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 400016
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 400017
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 400018
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 400019
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 400020
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 400021
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 400022
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 400023
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 400024
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500000
2021-10-02T02:05:19+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500001
2021-10-02T02:05:20+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500002
2021-10-02T02:05:20+02:00 [] (Ampache\Module\System\Dba) -> Error_query SQL: ALTER TABLE `podcast_episode` DROP COLUMN `catalog`;
2021-10-02T02:05:20+02:00 [] (Ampache\Module\System\Dba) -> Error_query MSG: ["42000",1091,"Can't DROP COLUMN `catalog`; check that it exists"]
2021-10-02T02:05:20+02:00 [] (Ampache\Module\System\Dba) -> Database connection...
2021-10-02T02:05:20+02:00 [] (Ampache\Module\System\Dba) -> Error_query SQL: ALTER TABLE `podcast_episode` DROP COLUMN `catalog`;
2021-10-02T02:05:20+02:00 [] (Ampache\Module\System\Dba) -> Error_query MSG: ["42000",1091,"Can't DROP COLUMN `catalog`; check that it exists"]
2021-10-02T02:05:20+02:00 [] (Ampache\Module\System\Dba) -> Database connection...
2021-10-02T02:05:20+02:00 [] (Ampache\Module\System\Dba) -> Error_query SQL: ALTER TABLE `podcast_episode` DROP COLUMN `catalog`;
2021-10-02T02:05:20+02:00 [] (Ampache\Module\System\Dba) -> Error_query MSG: ["42000",1091,"Can't DROP COLUMN `catalog`; check that it exists"]
2021-10-02T02:05:20+02:00 [] (Ampache\Module\System\Dba) -> Database connection...
2021-10-02T02:05:20+02:00 [] (Ampache\Module\System\Dba) -> Error_query SQL: ALTER TABLE `podcast_episode` DROP COLUMN `catalog`;
2021-10-02T02:05:20+02:00 [] (Ampache\Module\System\Dba) -> Error_query MSG: ["42000",1091,"Can't DROP COLUMN `catalog`; check that it exists"]
2021-10-02T02:05:20+02:00 [] (Ampache\Module\System\Dba) -> Database connection...
2021-10-02T02:06:11+02:00 [] (Ampache\Module\System\Dba) -> Database connection...
2021-10-02T02:06:11+02:00 [] (Ampache\Module\Application\ApplicationRunner) -> Found handler "Ampache\Module\Application\Update\UpdateAction" for action "update"
2021-10-02T02:06:11+02:00 [] (Ampache\Module\System\Update) -> run_update: starting
2021-10-02T02:06:11+02:00 [] (Ampache\Module\System\Update) -> run_update: checking versions
2021-10-02T02:06:11+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500003
2021-10-02T02:06:13+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500004
2021-10-02T02:06:13+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500005
2021-10-02T02:06:14+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500006
2021-10-02T02:06:14+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500007
2021-10-02T02:06:14+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500008
2021-10-02T02:06:14+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500009
2021-10-02T02:06:14+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500010
2021-10-02T02:06:14+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500011
2021-10-02T02:06:14+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500012
2021-10-02T02:06:14+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500013
2021-10-02T02:06:14+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500014
2021-10-02T02:06:14+02:00 [] (Ampache\Module\System\Update) -> run_update: successfully updated to 500015
2021-10-02T02:06:14+02:00 [] (Ampache\Module\System\Update) -> run_update: starting rebuild_all_preferences
2021-10-02T02:06:14+02:00 [] (Ampache\Module\System\Update) -> run_update: starting



Discussion / hints:
It looks like as part update_500003(), which is in charge of adding the column, the column is first drop:
    /**
     * update_500003
     *
     * add catalog to podcast_episode table
     */
    public static function update_500003()
    {
        $retval = true;
        $sql    = "ALTER TABLE podcast_episode DROP COLUMN catalog;";
        $retval &= Dba::write($sql);
        $sql    = "ALTER TABLE podcast_episode ADD catalog int(11) UNSIGNED NOT NULL DEFAULT '0';";
        $retval &= Dba::write($sql);
...
        return $retval;
    }

So I guess that simply removing the line that drops the column the problem should be fixed. Seeing that code now it becomes clear why the second attempt succeeds, since the next line adds the column, the next time the update is run the column can now be dropped without an error.

Fix patch:
diff --git a/src/Module/System/Update.php b/src/Module/System/Update.php
index 86e4a72ef6..f17f6231a4 100644
--- a/src/Module/System/Update.php
+++ b/src/Module/System/Update.php
@@ -1313,8 +1313,6 @@ public static function update_500002()
     public static function update_500003()
     {
         $retval = true;
-        $sql    = "ALTER TABLE `podcast_episode` DROP COLUMN `catalog`;";
-        $retval &= (Dba::write($sql) !== false);
         $sql    = "ALTER TABLE `podcast_episode` ADD `catalog` int(11) UNSIGNED NOT NULL DEFAULT '0';";
         $retval &= (Dba::write($sql) !== false);
         $sql = "UPDATE `podcast_episode`, (SELECT min(`podcast`.`catalog`) as `catalog`, `podcast`.`id` FROM `podcast` GROUP BY `podcast`.`id`) AS `podcast` SET `podcast_episode`.`catalog` = `podcast`.`catalog` WHERE `podcast_episode`.`catalog` != `podcast`.`catalog` AND `podcast_episode`.`podcast` = `podcast`.`id` AND `podcast`.`catalog` > 0;";


Test patch: