Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.8, 12.3, 13.1, 12.3.3
-
None
-
Linux x86_64
Description
When mysql.servers has a primary key on (Server_name, Port), two rows can share one Server_name. On FLUSH PRIVILEGES, MariaDB accepts this layout and silently replaces the live foreign-server definition with the duplicate row even though both persistent rows remain.
This occurs when mysql.servers is reloaded with a primary key on (Server_name, Port), allowing two persistent rows to share a Server_name.
The reproducer modifies the mysql.servers system table, so it requires an account permitted to modify that table and manage foreign servers. Save the following as case.sql:
DROP SERVER IF EXISTS remote1; |
|
|
CREATE SERVER remote1 |
FOREIGN DATA WRAPPER mysql OPTIONS (HOST 'baseline.example'); |
|
|
RENAME TABLE mysql.servers TO mysql.servers_saved; |
CREATE TABLE mysql.servers LIKE mysql.servers_saved; |
ALTER TABLE mysql.servers |
DROP PRIMARY KEY, |
ADD PRIMARY KEY (Server_name, Port); |
INSERT INTO mysql.servers |
SELECT * FROM mysql.servers_saved; |
INSERT INTO mysql.servers |
(Server_name, Host, Db, Username, Password, Port, Socket, Wrapper, Owner, Options) |
VALUES
|
('remote1', 'duplicate.example', '', '', '', 1, '', 'mysql', '', '{}'); |
|
|
SELECT 'before_flush' AS phase, COUNT(*) AS persistent_rows |
FROM mysql.servers |
WHERE Server_name='remote1'; |
SHOW CREATE SERVER remote1; |
|
|
FLUSH PRIVILEGES; |
|
|
SELECT 'after_flush' AS phase, COUNT(*) AS persistent_rows |
FROM mysql.servers |
WHERE Server_name='remote1'; |
SHOW CREATE SERVER remote1; |
Save the persistent-state check as verify.sql:
SELECT Server_name, Host, Port, Wrapper, Options |
FROM mysql.servers |
WHERE Server_name='remote1' |
ORDER BY Port; |
SHOW CREATE SERVER remote1; |
Run both files in order with a privileged MariaDB client:
mariadb -uroot < case.sql |
mariadb -uroot < verify.sql
|
The captured MariaDB 12.3.3 result showed two rows before and after the reload. Before FLUSH PRIVILEGES, the cached definition still used baseline.example; afterward, SHOW CREATE SERVER returned an empty options list:
phase persistent_rows
|
before_flush 2
|
Server Create Server
|
remote1 CREATE SERVER `remote1` FOREIGN DATA WRAPPER `mysql` OPTIONS (`HOST` 'baseline.example');
|
phase persistent_rows
|
after_flush 2
|
Server Create Server
|
remote1 CREATE SERVER `remote1` FOREIGN DATA WRAPPER `mysql` OPTIONS ();
|
The verification query showed that both persistent definitions were still present while the cached definition remained the empty one:
Server_name Host Port Wrapper Options
|
remote1 duplicate.example 1 mysql {}
|
remote1 baseline.example 3306 mysql {"HOST": "baseline.example"}
|
Server Create Server
|
remote1 CREATE SERVER `remote1` FOREIGN DATA WRAPPER `mysql` OPTIONS ();
|
The malformed key permits distinct rows with the same logical server identity. MariaDB should reject that table layout before replacing the live cache; it should not choose one duplicate definition and silently change the meaning of an existing server.
Restore the original system table after collecting the result:
DROP TABLE IF EXISTS mysql.servers; |
RENAME TABLE mysql.servers_saved TO mysql.servers; |
FLUSH PRIVILEGES; |
DROP SERVER IF EXISTS remote1; |
Attachments
Issue Links
- relates to
-
MDEV-15696 Implement SHOW CREATE SERVER
-
- Closed
-
-
-
- Closed
-