MariaDB error on upgrade to 4.2.0 release

[pitoco02]

Found this error during migration proccess:

== 20250121120000 UpsertCoreEmergencyAlertInModule: migrating
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘AS newRow
ON DUPLICATE KEY UPDATE
enabled = new…’ at line 2 in C:\Files\CMS\MYCMS.COM\vendor\robmorgan\phinx\src\Phinx\Db\Adapter\PdoAdapter.php:192
Stack trace:
#0 C:\Files\CMS\MYCMS.COM\vendor\robmorgan\phinx\src\Phinx\Db\Adapter\PdoAdapter.php(192): PDO->exec()
#1 C:\Files\CMS\MYCMS.COM\vendor\robmorgan\phinx\src\Phinx\Db\Adapter\AdapterWrapper.php(157): Phinx\Db\Adapter\PdoAdapter->execute()
#2 C:\Files\CMS\MYCMS.COM\vendor\robmorgan\phinx\src\Phinx\Migration\AbstractMigration.php(199): Phinx\Db\Adapter\AdapterWrapper->execute()
#3 C:\Files\CMS\MYCMS.COM\db\migrations\20250121120000_upsert_core_emergency_alert_in_module.php(34): Phinx\Migration\AbstractMigration->execute()
#4 C:\Files\CMS\MYCMS.COM\vendor\robmorgan\phinx\src\Phinx\Migration\Manager\Environment.php(108): UpsertCoreEmergencyAlertInModule->change()
#5 C:\Files\CMS\MYCMS.COM\vendor\robmorgan\phinx\src\Phinx\Migration\Manager.php(388): Phinx\Migration\Manager\Environment->executeMigration()
#6 C:\Files\CMS\MYCMS.COM\vendor\robmorgan\phinx\src\Phinx\Migration\Manager.php(359): Phinx\Migration\Manager->executeMigration()
#7 C:\Files\CMS\MYCMS.COM\vendor\robmorgan\phinx\src\Phinx\Console\Command\Migrate.php(122): Phinx\Migration\Manager->migrate()
#8 C:\Files\CMS\MYCMS.COM\vendor\symfony\console\Command\Command.php(298): Phinx\Console\Command\Migrate->execute()
#9 C:\Files\CMS\MYCMS.COM\vendor\symfony\console\Application.php(1040): Symfony\Component\Console\Command\Command->run()
#10 C:\Files\CMS\MYCMS.COM\vendor\symfony\console\Application.php(301): Symfony\Component\Console\Application->doRunCommand()
#11 C:\Files\CMS\MYCMS.COM\vendor\robmorgan\phinx\src\Phinx\Console\PhinxApplication.php(70): Symfony\Component\Console\Application->doRun()
#12 C:\Files\CMS\MYCMS.COM\vendor\symfony\console\Application.php(171): Phinx\Console\PhinxApplication->doRun()
#13 C:\Files\CMS\MYCMS.COM\vendor\robmorgan\phinx\bin\phinx(28): Symfony\Component\Console\Application->run()
#14 C:\Files\CMS\MYCMS.COM\vendor\bin\phinx(119): include(‘…’)
#15 {main}

i’m using MariaDB 10.7.4

Had to change the migration file \db\migrations\20250115120000_add_national_weather_service_connector_migration.php from

$this->execute('
    INSERT INTO `module` (`moduleId`, `enabled`, `previewEnabled`, `defaultDuration`, `settings`)
    VALUES (\'core-emergency-alert\', \'1\', \'1\', \'60\', NULL) AS newRow
    ON DUPLICATE KEY UPDATE
        `enabled` = newRow.enabled,
        `previewEnabled` = newRow.previewEnabled,
        `defaultDuration` = newRow.defaultDuration,
        `settings` = newRow.settings;
');

To

 public function change(): void
    {
        // Insert or update core-emergency-alert into the module table
        $this->execute('
            INSERT INTO `module` (`moduleId`, `enabled`, `previewEnabled`, `defaultDuration`, `settings`)
			VALUES (\'core-emergency-alert\', \'1\', \'1\', \'60\', NULL)
			ON DUPLICATE KEY UPDATE
				`enabled` = VALUES(`enabled`),
				`previewEnabled` = VALUES(`previewEnabled`),
				`defaultDuration` = VALUES(`defaultDuration`),
				`settings` = VALUES(`settings`);
		');

Since MariaDB does not allow AS newRow

[alex]

MariaDB isn’t officially supported, so I would swap to MySQL 8.0 which is the supported database engine.

[pitoco02]

Hi Alex!
Thanks for the answer.

Apart from this change I posted above, everything is running 100% fine with MariaDB.
This was the first time I saw an incompatibility of this type

[alex]

Sure that’s fine. We don’t develop or test with it, and as you’ve found the syntax support is different so you’re more likely to hit issues if you use an alternative database to the one we recommend. Thanks for sharing your solution though

[pitoco02]

I’ll keep an eye on this and prepare a migration plan to MySQL 8 as recommended.

Thanks!

[alex]

I had the dev team take a look at this.

The syntax you propose causes a deprecation warning in MySQL 8.0, and that’s why we’ve moved to the AS syntax.

They’ve reworked that migration to avoid the ON DUPLICATE completely and so that should be fixed for 4.2.1.