Skip to content

Fix foreign key parsing broken by MariaDB system versioning #20346

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
Maria-Berta wants to merge 1 commit into
base: QA_5_2
Choose a base branch
from
+154 −0
Open

Fix foreign key parsing broken by MariaDB system versioning #20346

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
14 changes: 14 additions & 0 deletions libraries/classes/ConfigStorage/Relation.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -445,6 +445,20 @@ public function getForeigners($db, $table, $column = '', $source = 'both')
$tableObj = new Table($table, $db);
$show_create_table = $tableObj->showCreate();
if ($show_create_table !== '') {
// Strip MariaDB system versioning clauses that confuse the SQL parser.
// PERIOD FOR SYSTEM_TIME and ROW START/END columns are not relevant
// for foreign key detection and cause parsing failures.
// See: https://github.com/phpmyadmin/phpmyadmin/issues/20095
$show_create_table = (string) preg_replace(
'/^\s*`?\w+`?\s+\w+(?:\(\d+\))?\s+GENERATED ALWAYS AS ROW (?:START|END)[^\n]*,?\n?/im',
'',
$show_create_table
);
$show_create_table = (string) preg_replace(
'/^\s*PERIOD\s+FOR\s+SYSTEM_TIME\s*\([^)]*\),?\n?/im',
'',
$show_create_table
);
$parser = new Parser($show_create_table);
$stmt = $parser->statements[0];
$foreign['foreign_keys_data'] = [];
Expand Down
140 changes: 140 additions & 0 deletions test/classes/ConfigStorage/RelationTest.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2244,4 +2244,144 @@ public function testRenameTableEscaping(): void

$this->assertAllQueriesConsumed();
}

/**
* Test that getForeigners correctly parses foreign keys from a system-versioned table.
* System-versioned tables include PERIOD FOR SYSTEM_TIME and GENERATED ALWAYS AS ROW
* START/END columns in SHOW CREATE TABLE output, which previously broke the SQL parser.
*
* @see https://github.com/phpmyadmin/phpmyadmin/issues/20095
*/
public function testGetForeignersWithSystemVersionedTable(): void
{
$showCreateTable = "CREATE TABLE `orders` (\n"
. " `id` int(11) NOT NULL,\n"
. " `customer_id` int(11) DEFAULT NULL,\n"
. " `ts` timestamp(6) GENERATED ALWAYS AS ROW START,\n"
. " `te` timestamp(6) GENERATED ALWAYS AS ROW END,\n"
. " PRIMARY KEY (`id`,`te`),\n"
. " KEY `fk_customer` (`customer_id`),\n"
. " PERIOD FOR SYSTEM_TIME (`ts`, `te`),\n"
. " CONSTRAINT `fk_customer` FOREIGN KEY (`customer_id`)"
. " REFERENCES `customers` (`id`)\n"
. ') ENGINE=InnoDB WITH SYSTEM VERSIONING';

// Strip GENERATED ALWAYS AS ROW START/END columns
$processed = (string) preg_replace(
'/^\s*`?\w+`?\s+\w+(?:\(\d+\))?\s+GENERATED ALWAYS AS ROW (?:START|END)[^\n]*,?\n?/im',
'',
$showCreateTable
);
// Strip PERIOD FOR SYSTEM_TIME clause
$processed = (string) preg_replace(
'/^\s*PERIOD\s+FOR\s+SYSTEM_TIME\s*\([^)]*\),?\n?/im',
'',
$processed
);

// After stripping, the parser should find the foreign key
$parser = new \PhpMyAdmin\SqlParser\Parser($processed);
$stmt = $parser->statements[0];
self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\CreateStatement::class, $stmt);

$foreignKeys = $stmt->getForeignKeys();
self::assertCount(1, $foreignKeys);
self::assertSame('fk_customer', $foreignKeys[0]->constraint);
self::assertSame(['customer_id'], $foreignKeys[0]->indexList);
self::assertSame('customers', $foreignKeys[0]->refTableName);
}

/**
* Test that PERIOD FOR SYSTEM_TIME clause is stripped before parsing.
*
* @see https://github.com/phpmyadmin/phpmyadmin/issues/20095
*/
public function testStripPeriodForSystemTime(): void
{
$showCreateTable = "CREATE TABLE `orders` (\n"
. " `id` int(11) NOT NULL,\n"
. " `customer_id` int(11) DEFAULT NULL,\n"
. " PERIOD FOR SYSTEM_TIME (`ts`, `te`),\n"
. " CONSTRAINT `fk_customer` FOREIGN KEY (`customer_id`)"
. " REFERENCES `customers` (`id`)\n"
. ') ENGINE=InnoDB';

$processed = (string) preg_replace(
'/^\s*PERIOD\s+FOR\s+SYSTEM_TIME\s*\([^)]*\),?\n?/im',
'',
$showCreateTable
);

self::assertStringNotContainsString('PERIOD FOR SYSTEM_TIME', $processed);

$parser = new \PhpMyAdmin\SqlParser\Parser($processed);
$stmt = $parser->statements[0];
$foreignKeys = $stmt->getForeignKeys();
self::assertCount(1, $foreignKeys);
self::assertSame('fk_customer', $foreignKeys[0]->constraint);
}

/**
* Test that GENERATED ALWAYS AS ROW START/END columns are stripped before parsing.
*
* @see https://github.com/phpmyadmin/phpmyadmin/issues/20095
*/
public function testStripGeneratedAlwaysAsRowStartEnd(): void
{
$showCreateTable = "CREATE TABLE `orders` (\n"
. " `id` int(11) NOT NULL,\n"
. " `ts` timestamp(6) GENERATED ALWAYS AS ROW START,\n"
. " `te` timestamp(6) GENERATED ALWAYS AS ROW END,\n"
. " CONSTRAINT `fk_customer` FOREIGN KEY (`customer_id`)"
. " REFERENCES `customers` (`id`)\n"
. ') ENGINE=InnoDB';

$processed = (string) preg_replace(
'/^\s*`?\w+`?\s+\w+(?:\(\d+\))?\s+GENERATED ALWAYS AS ROW (?:START|END)[^\n]*,?\n?/im',
'',
$showCreateTable
);

self::assertStringNotContainsString('ROW START', $processed);
self::assertStringNotContainsString('ROW END', $processed);
}

/**
* Test that non-versioned tables are unaffected by the system versioning fix.
*
* @see https://github.com/phpmyadmin/phpmyadmin/issues/20095
*/
public function testGetForeignersWithNonVersionedTable(): void
{
$showCreateTable = "CREATE TABLE `orders` (\n"
. " `id` int(11) NOT NULL,\n"
. " `customer_id` int(11) DEFAULT NULL,\n"
. " PRIMARY KEY (`id`),\n"
. " CONSTRAINT `fk_customer` FOREIGN KEY (`customer_id`)"
. " REFERENCES `customers` (`id`)\n"
. ') ENGINE=InnoDB';

// Apply the same pre-processing — should not alter a non-versioned table
$processed = (string) preg_replace(
'/^\s*`?\w+`?\s+\w+(?:\(\d+\))?\s+GENERATED ALWAYS AS ROW (?:START|END)[^\n]*,?\n?/im',
'',
$showCreateTable
);
$processed = (string) preg_replace(
'/^\s*PERIOD\s+FOR\s+SYSTEM_TIME\s*\([^)]*\),?\n?/im',
'',
$processed
);

// String should be unchanged
self::assertSame($showCreateTable, $processed);

// Foreign key should still be found
$parser = new \PhpMyAdmin\SqlParser\Parser($processed);
$stmt = $parser->statements[0];
$foreignKeys = $stmt->getForeignKeys();
self::assertCount(1, $foreignKeys);
self::assertSame('fk_customer', $foreignKeys[0]->constraint);
}
}