[Bug] | OJS 3.5.0 Upgrade Fails with SQL Syntax Error 1064 on MariaDB/MySQL due to Incorrect Default Value Quoting

[thuanhuynhh]

Valid Title

• I have updated the title to accurately reflect the bug description

Description

When upgrading from OJS 3.4.0.9 to OJS 3.5.0 on my server using MariaDB, the process error with a PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064. The error is caused by the migration script I9425_SeparateUIAndSubmissionLocales.php, which incorrectly generates an ALTER TABLE statement. It reads an existing default value for a locale column (e.g., 'en') and wraps it in an additional set of quotes, resulting in an invalid SQL syntax (DEFAULT ''en'' instead of the correct DEFAULT 'en').

Steps to Reproduce

1. Run upgrade tool: php tools/upgrade.php upgrade.

Expected Result

Skip this bug and the process should complete successfully

Actual Result

`ERROR: Upgrade failed: DB: 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 '''' at line 1 in /home/jha-demo/htdocs/demo.jha.vn/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php:565
Stack trace:
#0 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(565): PDO->prepare()
#1 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(812): Illuminate\Database\Connection->Illuminate\Database{closure}()
#2 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(779): Illuminate\Database\Connection->runQueryCallback()
#3 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(560): Illuminate\Database\Connection->run()
#4 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(118): Illuminate\Database\Connection->statement()
#5 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(564): Illuminate\Database\Schema\Blueprint->build()
#6 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(406): Illuminate\Database\Schema\Builder->build()
#7 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(361): Illuminate\Database\Schema\Builder->table()
#8 /home/lib/pkp/classes/migration/upgrade/v3_5_0/I9425_SeparateUIAndSubmissionLocales.php(115): Illuminate\Support\Facades\Facade::__callStatic()
#9 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Collections/Traits/EnumeratesValues.php(261): PKP\migration\upgrade\v3_5_0\I9425_SeparateUIAndSubmissionLocales->PKP\migration\upgrade\v3_5_0{closure}()
#10 /home/lib/pkp/classes/migration/upgrade/v3_5_0/I9425_SeparateUIAndSubmissionLocales.php(107): Illuminate\Support\Collection->each()
#11 /home/lib/pkp/classes/install/Installer.php(445): PKP\migration\upgrade\v3_5_0\I9425_SeparateUIAndSubmissionLocales->up()
#12 /home/lib/pkp/classes/install/Installer.php(298): PKP\install\Installer->executeAction()
#13 /home/lib/pkp/classes/install/Installer.php(207): PKP\install\Installer->executeInstaller()
#14 /home/lib/pkp/classes/cliTool/UpgradeTool.php(97): PKP\install\Installer->execute()
#15 /home/lib/pkp/classes/cliTool/UpgradeTool.php(70): PKP\cliTool\UpgradeTool->upgrade()
#16 /home/tools/upgrade.php(22): PKP\cliTool\UpgradeTool->execute()
#17 {main}

Next Illuminate\Database\QueryException: 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 '''' at line 1 (Connection: mysql, SQL: alter table email_templates_default_data modify locale varchar(28) not null default ''en'') in /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php:825
Stack trace:
#0 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(779): Illuminate\Database\Connection->runQueryCallback()
#1 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(560): Illuminate\Database\Connection->run()
#2 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(118): Illuminate\Database\Connection->statement()
#3 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(564): Illuminate\Database\Schema\Blueprint->build()
#4 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(406): Illuminate\Database\Schema\Builder->build()
#5 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(361): Illuminate\Database\Schema\Builder->table()
#6 /home/lib/pkp/classes/migration/upgrade/v3_5_0/I9425_SeparateUIAndSubmissionLocales.php(115): Illuminate\Support\Facades\Facade::__callStatic()
#7 /home/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Collections/Traits/EnumeratesValues.php(261): PKP\migration\upgrade\v3_5_0\I9425_SeparateUIAndSubmissionLocales->PKP\migration\upgrade\v3_5_0{closure}()
#8 /home/lib/pkp/classes/migration/upgrade/v3_5_0/I9425_SeparateUIAndSubmissionLocales.php(107): Illuminate\Support\Collection->each()
#9 /home/lib/pkp/classes/install/Installer.php(445): PKP\migration\upgrade\v3_5_0\I9425_SeparateUIAndSubmissionLocales->up()
#10 /home/lib/pkp/classes/install/Installer.php(298): PKP\install\Installer->executeAction()
#11 /home/lib/pkp/classes/install/Installer.php(207): PKP\install\Installer->executeInstaller()
#12 /home/lib/pkp/classes/cliTool/UpgradeTool.php(97): PKP\install\Installer->execute()
#13 /home/lib/pkp/classes/cliTool/UpgradeTool.php(70): PKP\cliTool\UpgradeTool->upgrade()
#14 /home/tools/upgrade.php(22): PKP\cliTool\UpgradeTool->execute()
#15 {main}`

Environment Details

No response

Application Version

3.5.0.0

Logs

No response

Additional Information

No response

[asmecher]

@thuanhuynhh, this is odd since our CI testing includes PostgreSQL upgrades (e.g. this log) that should cover your case; what do you get for the following with your OJS 3.4.0-x database, prior to running the upgrade?

\d email_templates_default_data

[Rofypkr]

We also encounter errors during upgrading from #3.4 to #3.5. In case we drop the column, it generates it automatically even if we try upgrading with a fresh setup.

Errors occurred during installation:

A database error has occurred: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'redirect_context_id' (Connection: mysql, SQL: alter table site rename column redirect to redirect_context_id)
Errors occurred during installation:

A database error has occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'redirect' in 'site' (Connection: mysql, SQL: alter table site modify redirect bigint null)

[asmecher]

@Rofypkr, I've moved your support forum post on the same subject here:

https://forum.pkp.sfu.ca/t/error-on-upgrade-to-ojs-3-5/96116

It's best to just post each issue once; otherwise it might divide our support efforts.

[churongcon]

@thuanhuynhh, this is odd since our CI testing includes PostgreSQL upgrades (e.g. this log) that should cover your case; what do you get for the following with your OJS 3.4.0-x database, prior to running the upgrade?

\d email_templates_default_data

I have upgraded from 3.4.0-9 to 3.5.0 via command line and see this error. Also see error like @Rofypkr if i trying to run upgrade command line again

[ffoerster]

Just to confirm - i also ran into this issue upgrading a 3.4.0-8 database to 3.5.0-0. Although on MariaDB 10.2.

SHOW CREATE TABLE site; on the pre-upgrade database shows:

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| site  | CREATE TABLE `site` (
  `site_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `redirect` bigint(20) NOT NULL DEFAULT 0,
  `primary_locale` varchar(14) NOT NULL,
  `min_password_length` tinyint(4) NOT NULL DEFAULT 6,
  `installed_locales` varchar(255) NOT NULL DEFAULT 'en',
  `supported_locales` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`site_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[asmecher]

Hi all -- I originally misread this as affecting PostgreSQL, but even stranger, it looks like it's affecting MySQL/MariaDB. This is very odd, as 99% of our team uses that configuration, and did significant testing on the upgrade process without encountering this.

Diving into the underlying toolset, it looks like Laravel uses an information_schema query to get column information, and thus the defaults that are causing the problem.

For example:

select column_name as `name`, data_type as `type_name`, column_type as `type`, collation_name as `collation`, is_nullable as `nullable`, column_default as `default`, column_comment as `comment`, generation_expression as `expression`, extra as `extra` from information_schema.columns where table_schema = 'YOUR_DATABASE_NAME_HERE' and table_name = 'email_templates_default_data' order by ordinal_position asc;

(Replace YOUR_DATABASE_NAME_HERE with your database name.)
In my MariaDB installation, I get:

+---------------------------------+-----------+-----------------+--------------------+----------+---------+-----------------------------------+------------+----------------+
| name                            | type_name | type            | collation          | nullable | default | comment                           | expression | extra          |
+---------------------------------+-----------+-----------------+--------------------+----------+---------+-----------------------------------+------------+----------------+
| email_templates_default_data_id | bigint    | bigint unsigned | NULL               | NO       | NULL    |                                   |            | auto_increment |
| email_key                       | varchar   | varchar(255)    | utf8mb3_general_ci | NO       | NULL    | Unique identifier for this email. |            |                |
| locale                          | varchar   | varchar(14)     | utf8mb3_general_ci | NO       | en      |                                   |            |                |
| name                            | varchar   | varchar(255)    | utf8mb3_general_ci | NO       | NULL    |                                   |            |                |
| subject                         | varchar   | varchar(255)    | utf8mb3_general_ci | NO       | NULL    |                                   |            |                |
| body                            | text      | text            | utf8mb3_general_ci | YES      | NULL    |                                   |            |                |
+---------------------------------+-----------+-----------------+--------------------+----------+---------+-----------------------------------+------------+----------------+

Can someone who is affected by this issue run the same query? I suspect you'll see 'en' in the default column, rather than the en that I'm getting.

[BartChris]

MariaDB on Debian 12 (10.11.6-MariaDB-0+deb12u1), instance running on OJS 3.4.0.9: outputs:

| name                            | type_name | type                | collation          | nullable | default | comment | expression | extra          |
|---------------------------------|-----------|---------------------|--------------------|----------|---------|---------|------------|----------------|
| email_templates_default_data_id | bigint    | bigint(20) unsigned | NULL               | NO       | NULL    |         | NULL       | auto_increment |
| email_key                       | varchar   | varchar(255)        | utf8mb4_general_ci | NO       | NULL    |         | NULL       |                |
| locale                          | varchar   | varchar(255)        | utf8mb4_general_ci | NO       | 'en'    |         | NULL       |                |
| subject                         | varchar   | varchar(255)        | utf8mb4_general_ci | NO       | NULL    |         | NULL       |                |
| body                            | text      | text                | utf8mb4_general_ci | YES      | NULL    |         | NULL       |                |
| name                            | varchar   | varchar(255)        | utf8mb4_general_ci | YES      | NULL    |         | NULL       |                |

In Doctrine DBAL Github the following is stated/quoted:

"In order to allow expressions as default values and distinguish them from literals, Mariadb now quotes default values in information_schema.column table."

doctrine/dbal#2825 (comment)

(see: https://mariadb.com/docs/release-notes/mariadb-community-server-release-notes/old-releases/release-notes-mariadb-10-2-series/mariadb-1027-release-notes#notable-changes)

It looks like there was a change starting with MariaDB 10.2.7+ in how default values are handled. In all current versions of MariaDB the default value is always quoted. I’m wondering if upgrading MariaDB doesn’t automatically update the existing table metadata in the information schema. That could explain why some systems show quoted defaults ('en') and others (those whose databases where maybe created before the breaking change in MariaDB) do not. The code therefor definitely has to adapt to the behavior in current MariaDB databases.

Edit:
The Laravel Schema toolset does not seem to have special handling for MariaDB default columns like Doctrine DBAL.
https://github.com/doctrine/dbal/blob/b37d160498ea91a2382a2ebe825c4ea6254fc0ec/src/Schema/MySQLSchemaManager.php#L268

[asmecher]

Thanks for the details, everyone. While I will prep a fairly easy fix for the next OJS/OMP/OPS build, I'm pretty sure this is a good opportunity for Laravel to better encapsulate DBMS behaviour, so I've filed a couple of issues on that project:

• String column defaults are not properly escaped laravel/framework#56124
• MariaDB column defaults may or may not be quoted laravel/framework#56125