Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.6.12
-
Ubuntu 22.04
Description
Recently we upgraded mariadb from 10.3 to 10.6 and we are facing a weird problem.
Although there is a unique key on 3 columns (`table_hash`,`table_id`,`meta_key`) there are multiple records with same data.
It didn't happen when we were on mariadb 10.3.
*Table structure:*
```
CREATE TABLE `ordini_meta` (
|
`meta_id` int(11) NOT NULL, |
`table_hash` bigint(64) NOT NULL DEFAULT 0, |
`table_id` int(11) NOT NULL DEFAULT 0, |
`meta_key` varchar(50) DEFAULT NULL, |
`meta_value` longtext DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
|
|
ALTER TABLE `ordini_meta`
|
ADD PRIMARY KEY (`meta_id`),
|
ADD UNIQUE KEY `unique_index` (`table_hash`,`table_id`,`meta_key`),
|
ADD KEY `table_hash` (`table_hash`),
|
ADD KEY `table_id` (`table_id`),
|
ADD KEY `meta_key` (`meta_key`);
|
|
ALTER TABLE `ordini_meta`
|
MODIFY `meta_id` int(11) NOT NULL AUTO_INCREMENT; |
```
*Example of duplicates*:
Duplicates
Rows in the table are managed with PHP and the query used is:
```
INSERT INTO ordini_meta (table_hash,table_id,meta_key,meta_value) VALUES (%d,%d,%s,%s) ON DUPLICATE KEY UPDATE meta_id=LAST_INSERT_ID(meta_id), meta_value=%s"
|
|
```
*SHOW VARIABLES 10.6(now):*
pastebin
*SHOW VARIABLES 10.3(before upgrade):*
pastebin
DB structure is the same that was in 10.3, unique key was already there before upgrade. In 10.3 there weren't any duplicate rows. We restored the database from a backup(mysqldump) and everyday we need to delete duplicate rows. We also tried to re-create index many times. I suppose I just need to create a bug report
We really don't understand why it happens.
Any help is really appreciated.