SQL Syntax Error with 'row_number' Column Name During Installation

[fiodyr]

SQL Syntax Error with 'row_number' Column Name During Installation

Description

During AtroCore installation process on Linux server with MariaDB, there's a SQL syntax error occurring due to the use of the reserved word 'row_number' without backticks in table creation queries. This error prevents successful database initialization and blocks the installation process.

Error Message

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 'row_number INT DEFAULT NULL, created_at DATETIME DEFAULT NULL, modified_at DA...' at line 1

Steps to Reproduce

1. Set up a Linux server with MariaDB
2. Start AtroCore installation process
3. Fill in database credentials and other required information
4. During the installation, when system tries to create database structure
5. Observe error in logs when trying to create import_job_log table

Current SQL Query

CREATE TABLE import_job_log ( id VARCHAR(36) NOT NULL, name VARCHAR(255) DEFAULT NULL, deleted TINYINT(1) DEFAULT '0', type VARCHAR(10) DEFAULT 'create', entity_name VARCHAR(100) DEFAULT NULL, entity_id VARCHAR(36) DEFAULT NULL, message LONGTEXT DEFAULT NULL, row_number INT DEFAULT NULL, ... )

Temporary Solution
Navigate to file:
.\your-project\vendor\atrocore\core\app\Atro\Core\Utils\Database\Schema\Schema.php
Find the rebuild() function and modify it by adding one line before SQL execution:
public function rebuild(): bool
{
// get queries
$queries = $this->getDiffQueries(false);
// run rebuild
$result = true;
foreach ($queries as $sql) {
$GLOBALS['log']->info('SCHEMA, Execute Query: ' . $sql);
// Add backticks around reserved word 'row_number'
$sql = preg_replace('/\b(row_number)\b(?!)/', '$1`', $sql); // <-- Add this line
try {
$result &= (bool)$this->connection->executeQuery($sql);
} catch (\Exception $e) {
$GLOBALS['log']->alert('Rebuild database fault: ' . $e);
$result = false;
}
}
$this->createSystemUser();
return $this->eventManager
->dispatch('Schema', 'afterRebuild', new Event(['result' => (bool)$result, 'queries' => $queries]))
->getArgument('result');
}
This modification adds backticks around the reserved word 'row_number' in SQL queries before execution, preventing the syntax error.

Permanent Solution Needed
While the temporary fix works, a permanent solution should be implemented at the schema definition level to properly handle reserved words in column names.

Additional Context
This error affects the installation/update process
The issue occurs because 'row_number' is a reserved word in MariaDB/MySQL
Similar issues might occur with other reserved words used as column names

Environment
AtroCore version: 1.11.26
MariaDB version: 11.1.6-MariaDB

Impact
This issue prevents successful database rebuilding and blocks the installation/update process.