Upgrade issue : Invalid query: SQLSTATE[42000]: Syntax error or access violation: 1064

[qcybb]

I'm trying to upgrade to the latest version that was just released. When I visit the setup.php page, it shows this :

The user has all permissions to the database.

Everything seems fine... attempting to create/update database structure

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: 1845; target version: 1846

  (If the update doesn't work, run setup.php?debug=1 to see the detailed error messages and SQL queries.)
updating to version 1846 (MySQL)...

Something went wrong while trying to apply database updates, a message should be logged - check PHP's error_log (/var/log/php-fpm/example_error.log)

The log file shows :

[04-Dec-2022 20:53:32 America/Vancouver] Invalid query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT vacation_notification_pkey' at line 1 caused by ALTER TABLE `vacation_notification` DROP CONSTRAINT vacation_notification_pkey
[04-Dec-2022 20:53:32 America/Vancouver] Couldn't perform PostfixAdmin database update via upgrade.php - DEBUG INFORMATION: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT vacation_notification_pkey' at line 1<br/> Check your error_log for the failed query Trace: #0 /usr/local/www/postfixadmin/public/upgrade.php(1997): db_query('ALTER TABLE `va...')
#1 /usr/www/postfixadmin/public/upgrade.php(208): upgrade_1846_mysql()
#2 /usr/www/postfixadmin/public/upgrade.php(154): _do_upgrade(1845)
#3 /usr/www/postfixadmin/public/setup.php(347): require_once('/usr/local/www/...')
#4 {main}

[amitrnavik]

Facing the same issue on installing.
using php 7.0.33 mysql 5.7.40 OS centos7 apache 2.4
after setting database details and other details in config.inc.php while accessing setup link http://doamin/postfix/setup.php

[stefanomarty]

The error is caused by the "DROP CONSTRAINT" statement in public/upgrade.php, which is not supported by MySQL.

I'm a bit confused because this error was fixed on Jan 25 by 66c7141

but it looks like the last v.3.3.12 is released from the postfixadmin_3.3 branch at this commit:
53bb238

Pls note that postfixadmin_3.3 branch is 83 commits ahead and 482 commits behind master.

Before merging I think it's better asking to @DavidGoodwin which is the correct branch to upgrade from.

[qcybb]

I upgraded to v3.3.1.2 found here :

https://github.com/postfixadmin/postfixadmin/releases

[stefanomarty]

I know, I did the same upgrade by checking out the last tag and found the same error! :-)

As a quick fix you could change just this line in public/upgrade.php from:
db_query("ALTER TABLE $vacation_notification DROP CONSTRAINT vacation_notification_pkey");
to:
db_query("ALTER TABLE $vacation_notification DROP FOREIGN KEY vacation_notification_pkey");

but I'd wait instructions about which of the two branches is ok to use, because there are many other commits on master and I don't know how the affect this last release.

[DavidGoodwin]

Sorry, it's a testing failure.

I use MariaDB locally, so everything "just worked" for me. I should probably pull my finger out and add in a docker-compose.yml / MySQL dependency in the unit tests so we have some better coverage.

I've added the missing commit to postfixadmin_3.3 - I think it probably makes sense to have a 3.3.13 release which would include the collation and this fix.

[damnms]

will this be a hot-fix release (3.3.13), so expected to be released in the next days?

[DavidGoodwin]

@damnms yeah - see https://github.com/postfixadmin/postfixadmin/commits/postfixadmin_3.3 and 19b6f96