Upgrade from 3.2 to 3.3.8 failed: database upgrade.php ignores error SQLSTATE[42S22]: Column not found: 1054 Unknown column 'password_expiry' in 'field list'

[klemens-u]

We had the same error as in #413. We upgraded to Ubuntu 20.04 with MySQL 8.0.

A deep dive showed the following problem:

The database migration script upgrade.php seems to have insufficient error handling. It should stop execution when an error occurs, but it continued silently in our case.

Details: https://github.com/postfixadmin/postfixadmin/blob/master/public/upgrade.php#L1991 executes the following SQL:
ALTER TABLE domain ADD COLUMN password_expiry int DEFAULT 0;

In our case it failed with:
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1

The reason:

select * from domain;
+----------------------------+--------------------------------+---------+-----------+----------+-------+-----------+----------+---------------------+---------------------+--------+
| domain                     | description                    | aliases | mailboxes | maxquota | quota | transport | backupmx | created             | modified            | active |
+----------------------------+--------------------------------+---------+-----------+----------+-------+-----------+----------+---------------------+---------------------+--------+
| ALL                        |                                |       0 |         0 |        0 |     0 |           |        0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |      1 |

Solution for incorrect datetime value:
UPDATE domain SET created='2000-01-01',modified='2000-01-01' WHERE domain='ALL';
Please add this as a database migration!

Solution to re-run the migration:
update config set value=1841;

Run upgrade.php again in your browser:
https://example.com/postfixadmin/upgrade.php?debug=1
The migration should work now.

Summary:

• Please fix the migrations script so that it stops on errors. Do not continue silently!
• Please add a migration which fixes legacy incorrect datetime values

Thank you for creating and maintaining postfixadmin!

[klemens-u]

btw: the following helped me to fix the problem:

show traces with xdebug:
apt install php-xdebug

enable PHP error display:

vi /etc/php/7.4/apache2/php.ini
    display_errors = On
    display_startup_errors = On
systemctl restart apache2

[DavidGoodwin]

thanks for the bug report - I've added 59f0b89 on master, and d654651 in the postfixadmin_3.3 branch.

[lleevveell66]

Hey... hmm.. So, I guess I just figured out that this was my issue, thanks to this bug report and #413 . But, now I am sort of stuck, afraid to make a move.

I have been using this thing, getting angry and annoyed with it showing blank pages, figuring something was wrong with my PHP, but still seemingly working to create new aliases (which is all I really use it for), only to stop working altogether, recently. And, like a burned-out IT admin, I just have been hopping into the database and insert'ing new aliases by hand to keep things moving, until today when I decided to hunt the problem down.

So, I now have a database which is in an odd state (thanks to this issue in the code, but also thanks to my procrastination).

Could we get more detail about what the DB would look like before and after fixing this? Is my DB totally hosed, now? Or, is this just a matter of poking one or two columns into a table or two and populating them with something reasonable, by hand?

How could a guy fix this manually, after ... waaay after... installation, is my question.

[DavidGoodwin]

@lleevveell66 I guess finding out what state your database is in, would be a good start.

Have you tried running :

UPDATE domain SET created='2000-01-01',modified='2000-01-01' WHERE domain='ALL';

and then :

ALTER TABLE domain ADD COLUMN password_expiry int DEFAULT 0;

and :

update config set value=1841;

and then visiting upgrade.php in a web browser?

[lleevveell66]

Thank you! I had done the

UPDATE domain SET created='2000-01-01',modified='2000-01-01' WHERE domain='ALL';

part, but not the

ALTER TABLE domain ADD COLUMN password_expiry int DEFAULT 0;
update config set value=1841;

parts. I just ran those, and I can list aliases, make a new alias, and everything, again!

(How important is it that I run "upgrade.php", now? Can I get away without it?)

[DavidGoodwin]

You should run upgrade.php.
upgrade.php should be non-destructive.

PostfixAdmin may break if the database schema is out of sync with the code.

[lleevveell66]

Ok. Thanks. Does this sound like it has worked?:

CREATE TABLE IF NOT EXISTS `config` ( `id` int(11) not null auto_increment primary key, `name` VARCHAR(20) /*!40100 CHARACTER SET latin1 COLLATE latin1_general_ci */ NOT NULL DEFAULT '', `value` VARCHAR(20) /*!40100 CHARACTER SET latin1 COLLATE latin1_general_ci */ NOT NULL DEFAULT '', UNIQUE name ( `name` ) ) COMMENT = 'PostfixAdmin settings'

Updating database:

- old version: 1841; target version: 1843
  (If the update doesn't work, run setup.php?debug=1 to see the detailed error messages and SQL queries.)

updating to version 1842 (all databases)...

field already exists: mailbox.password_expiry

field already exists: domain.password_expiry
  done

updating to version 1843 (all databases)...

field already exists: fetchmail.src_port
  done 

[DavidGoodwin]

Yes that looks good.