Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.11, 11.4, 11.8.3, 11.8
-
None
Description
Regular users do not see anything in `information_schema.referential_constraints`, so deployment process tries to add the foregn keys and fails with error 'already exists'.
LATEST FOREIGN KEY ERROR
------------------------
2025-11-24 16:42:48 0x7efbe46806c0 Error in foreign key constraint creation for table `frontend`.`#sql-alter-52b-a`.
A foreign key constraint of name `frontend`.`attrib_namespace_modifiable_bies_ibfk_1`
already exists. (Note that internally InnoDB adds 'databasename'
in front of the user-defined constraint name.)
This is galera cluster 11.8.3 with one primary node and 2 standby nodes. But I don't think that the problem is galera-related. The issue happens on all 3 nodes (and was likely propagated by mariadbbackup when restoring nodes, which happens relatively often).
When I log in as root user - it sees all content in information_schema.referential_constraints properly.
EDIT: there was another user - 'obsreadonly'. It didn't see the content as well , but apparently it was missing REFERENCES privilege. After granting REFERENCES - 'obsreadonly' started seeing the content of referential_constraints. I tried to revoke REFERENCE from 'obs' and then grant it back, but it didn't help.
We have similar instance of the same version and similar configuration (and identical user permissions) where the problem doesn't happen
Please suggest a solution or workaround
> select count(*) from information_schema.referential_constraints; |
+----------+ |
| count(*) | |
+----------+ |
| 0 |
|
+----------+ |
1 row in set (0.012 sec) |
|
|
> SELECT count(*) FROM information_schema.innodb_sys_foreign; |
+----------+ |
| count(*) | |
+----------+ |
| 108 |
|
+----------+ |
1 row in set (0.001 sec) |
|
|
> show create table flags\G |
*************************** 1. row ***************************
|
Table: flags |
Create Table: CREATE TABLE `flags` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`status` enum('enable','disable') NOT NULL, |
`repo` varchar(255) DEFAULT NULL, |
`project_id` int(11) DEFAULT NULL, |
`package_id` int(11) DEFAULT NULL, |
`architecture_id` int(11) DEFAULT NULL, |
`position` int(11) NOT NULL, |
`flag` enum('useforbuild','sourceaccess','binarydownload','debuginfo','build','publish','access','lock') NOT NULL, |
PRIMARY KEY (`id`), |
KEY `index_flags_on_flag` (`flag`), |
KEY `architecture_id` (`architecture_id`), |
KEY `index_flags_on_package_id` (`package_id`), |
KEY `index_flags_on_project_id` (`project_id`), |
CONSTRAINT `flags_ibfk_3` FOREIGN KEY (`architecture_id`) REFERENCES `architectures` (`id`), |
CONSTRAINT `flags_ibfk_4` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`), |
CONSTRAINT `flags_ibfk_5` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`), |
CONSTRAINT `flags_ibfk_6` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`), |
CONSTRAINT `flags_ibfk_7` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`) |
) ENGINE=InnoDB AUTO_INCREMENT=56765561 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC |
1 row in set (0.001 sec) |
|
|
> show grants;
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Grants for obs@% | |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| GRANT SELECT, PROCESS ON *.* TO `obs`@`%` IDENTIFIED BY PASSWORD '*xxxxxxxxxxxx' WITH MAX_USER_CONNECTIONS 305 | |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `frontend`.* TO `obs`@`%` | |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
2 rows in set (0.001 sec) |
|
|
> select version(); |
+--------------------+ |
| version() |
|
+--------------------+ |
| 11.8.3-MariaDB-log |
|
+--------------------+ |
1 row in set (0.001 sec) |
|
|
> show engine innodb status\G
|
*************************** 1. row ***************************
|
Type: InnoDB
|
Name: |
Status:
|
=====================================
|
2025-11-26 14:51:11 0x7f01e7a306c0 INNODB MONITOR OUTPUT |
=====================================
|
Per second averages calculated from the last 33 seconds |
-----------------
|
BACKGROUND THREAD
|
-----------------
|
srv_master_thread loops: 20 srv_active, 1171352 srv_idle
|
srv_master_thread log flush and writes: 1171070 |
----------
|
SEMAPHORES
|
----------
|
------------------------
|
LATEST FOREIGN KEY ERROR |
------------------------
|
2025-11-24 16:42:48 0x7efbe46806c0 Error in foreign key constraint creation for table `frontend`.`#sql-alter-52b-a`. |
A foreign key constraint of name `frontend`.`attrib_namespace_modifiable_bies_ibfk_1` |
already exists. (Note that internally InnoDB adds 'databasename' |
in front of the user-defined constraint name.) |
Note that InnoDB's FOREIGN KEY system tables store |
constraint names as case-insensitive, with the |
MariaDB standard latin1_swedish_ci collation. If you |
create tables or databases whose names differ only in |
the character case, then collisions in constraint |
names can occur. Workaround: name your constraints |
explicitly with unique names. |
------------
|
TRANSACTIONS
|