Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.9.5, 10.10.6, 10.11.5, 11.0.3
-
None
-
None
-
Using mariadb:11.0.3-jammy docker image
Description
Insert into a table containing FK constraints of UUID type fails on 11.0.3 and upwards (tested on mariadb:11.0.3-jammy, mariadb:11.0.4-jammy, mariadb:11.1.3-jammy, mariadb:11.2.2-jammy, mariadb:11.3.1-rc-jammy) and works on 11.0.2 (tested on mariadb:11.0.2-jammy and mariadb:11.0.1-rc-jammy).
There is no other difference besides the mariadb version. The table into which the new record is inserted is empty. The insert fails no matter how many UUID FK there are - all of them fail even if testing one by one. There is valid data matching in the referenced tables and the referenced UUID columns are PK in the respective tables. All tables are CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci . Having the FK nullable or not does not affect the described behaviour.
Adding FK on INT works in the given table. Even with log_warning=9 there is nothing in the error.log related to this. The server doesnt crash.
The table definition is:
===============================
CREATE TABLE `PricingAdjustments` ( |
`pricingAdjustmentUuid` uuid NOT NULL, |
|
`garageUuid` uuid NOT NULL, |
`vehicleTypeGroupUuid` uuid DEFAULT NULL COMMENT 'If null it is for all vehicles in the garage', |
`pricingAdjustmentName` varchar(100) DEFAULT NULL, |
`pricingAdjustmentFromDate` datetime DEFAULT NULL, |
`pricingAdjustmentToDate` datetime DEFAULT NULL, |
`pricingAdjustmentWeekDay` set('monday','tuesday','wednesday','thursday','friday','saturday','sunday') DEFAULT NULL, |
`pricingAdjustmentTransfer` decimal(6,2) NOT NULL COMMENT 'Percentage', |
`pricingAdjustmentGarageDistance` decimal(6,2) NOT NULL COMMENT 'percentage', |
`pricingAdjustmentPassengerDistance` decimal(6,2) NOT NULL COMMENT 'percentage', |
`pricingAdjustmentHourly` decimal(6,2) NOT NULL COMMENT 'percentage', |
`pricingAdjustmentMinHours` tinyint(3) UNSIGNED NOT NULL, |
`pricingAdjustmentDaily` decimal(6,2) NOT NULL COMMENT 'percentage', |
`pricingAdjustmentCreatedAt` datetime(6) NOT NULL, |
`pricingAdjustmentCreatedBy` uuid NOT NULL, |
`pricingAdjustmentUpdatedAt` datetime(6) NOT NULL, |
`pricingAdjustmentUpdatedBy` uuid NOT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; |
 |
 |
ALTER TABLE `PricingAdjustments` |
ADD PRIMARY KEY (`pricingAdjustmentUuid`), |
ADD UNIQUE KEY `pricingAdjustmentName` (`pricingAdjustmentName`), |
ADD KEY `PricingAdjustmentTimePeriodIndex` (`pricingAdjustmentFromDate`,`pricingAdjustmentToDate`), |
ADD KEY `pricingAdjustmentWeekDay` (`pricingAdjustmentWeekDay`), |
ADD KEY `PricingAdjustmentsVehicleTypeGroupUuidFK` (`vehicleTypeGroupUuid`), |
ADD KEY `PricingAdjustmentsCreatedByFK` (`pricingAdjustmentCreatedBy`), |
ADD KEY `PricingAdjustmentsUpdatedByFK` (`pricingAdjustmentUpdatedBy`), |
ADD KEY `PricingAdjustmentsGarageUuidFK` (`garageUuid`); |
 |
ALTER TABLE `PricingAdjustments` |
ADD CONSTRAINT `PricingAdjustmentsCreatedByFK` FOREIGN KEY (`pricingAdjustmentCreatedBy`) REFERENCES `Roles` (`roleUuid`), |
ADD CONSTRAINT `PricingAdjustmentsGarageUuidFK` FOREIGN KEY (`garageUuid`) REFERENCES `Garages` (`garageUuid`), |
ADD CONSTRAINT `PricingAdjustmentsUpdatedByFK` FOREIGN KEY (`pricingAdjustmentUpdatedBy`) REFERENCES `Roles` (`roleUuid`), |
ADD CONSTRAINT `PricingAdjustmentsVehicleTypeGroupUuidFK` FOREIGN KEY (`vehicleTypeGroupUuid`) REFERENCES `VehicleTypeGroups` (`vehicleTypeGroupUuid`); |
And one of the related table is:
 |
 |
CREATE TABLE `Roles` ( |
`roleUuid` uuid NOT NULL, |
`roleName` varchar(100) NOT NULL, |
`orgUuid` uuid DEFAULT NULL, |
`roleType` enum('user','team','tag','capability','departmental','privilege') NOT NULL, |
`roleCreatedAt` datetime(6) NOT NULL, |
`roleCreatedBy` uuid NOT NULL, |
`roleUpdatedAt` datetime(6) NOT NULL, |
`roleUpdatedBy` uuid NOT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; |
 |
ALTER TABLE `Roles` |
ADD PRIMARY KEY (`roleUuid`), |
ADD UNIQUE KEY `roleNameOrgUuidIndex` (`roleName`,`orgUuid`) USING BTREE, |
ADD KEY `RolesOrgUuidFK` (`orgUuid`), |
ADD KEY `RolesRoleCreatedByFK` (`roleCreatedBy`), |
ADD KEY `RolesRoleUpdatedByFK` (`roleUpdatedBy`), |
ADD KEY `roleTypeIndex` (`roleType`), |
ADD KEY `roleCreatedAtIndex` (`roleCreatedAt`), |
ADD KEY `roleUpdatedAtIndex` (`roleUpdatedAt`); |
 |
ALTER TABLE `Roles` |
ADD CONSTRAINT `RolesOrgUuidFK` FOREIGN KEY (`orgUuid`) REFERENCES `Organizations` (`orgUuid`), |
ADD CONSTRAINT `RolesRoleCreatedByFK` FOREIGN KEY (`roleCreatedBy`) REFERENCES `Roles` (`roleUuid`), |
ADD CONSTRAINT `RolesRoleUpdatedByFK` FOREIGN KEY (`roleUpdatedBy`) REFERENCES `Roles` (`roleUuid`); |
The existing record in the Roles table is:
 |
INSERT INTO `Roles` (`roleUuid`, `roleName`, `orgUuid`, `roleType`, `roleCreatedAt`, `roleCreatedBy`, `roleUpdatedAt`, `roleUpdatedBy`) VALUES |
('1ee9ab52-a1df-6410-bc7a-0242ac150006', 'admin2', '1ee9ab52-9131-6eba-80df-0242ac150006', 'user', '2023-12-14 19:15:38.142972', '1ee9ab51-ca65-614c-9259-0242ac150006', '2023-12-14 19:15:38.143016', '1ee9ab51-ca65-614c-9259-0242ac150006'); |
|
And the failing insert is:
INSERT INTO PricingAdjustments( |
pricingAdjustmentUuid,
|
garageUuid,
|
vehicleTypeGroupUuid,
|
pricingAdjustmentName,
|
pricingAdjustmentFromDate,
|
pricingAdjustmentToDate,
|
pricingAdjustmentWeekDay,
|
pricingAdjustmentTransfer,
|
pricingAdjustmentGarageDistance,
|
pricingAdjustmentPassengerDistance,
|
pricingAdjustmentHourly,
|
pricingAdjustmentMinHours,
|
pricingAdjustmentDaily,
|
pricingAdjustmentCreatedAt,
|
pricingAdjustmentCreatedBy,
|
pricingAdjustmentUpdatedAt,
|
pricingAdjustmentUpdatedBy
|
)
|
VALUES( |
'1ee9e538-c714-6422-ad9d-0242ac15000c', |
'1ee9ab52-a55a-619e-a4af-0242ac150006', |
'1ee9ab51-cdff-650a-acfa-0242ac150006', |
'Jan Feb weekends 23-24 2', |
'2024-12-01 10:00:00.000000', |
'2025-01-30 10:00:00.000000', |
'saturday,sunday', |
'6.6', |
'7.8', |
'4.7', |
'5', |
4,
|
'6', |
'2023-12-19 09:46:57.077296', |
'1ee9ab52-a1df-6410-bc7a-0242ac150006', |
'2023-12-19 09:46:57.077318', |
'1ee9ab52-a1df-6410-bc7a-0242ac150006' |
)
|
===============================
Inserting records in other tables with UUID FKs works. Dropping and recreating the affected table does not resolve the issue. The referenced tables are "CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;" too.
Changing the FKs to other tables works (again using UUIDs). But Im able to reproduce this issue with very small and simple tables - again all good on 11.0.2 and fails on 11.0.3. It seems it matters to which table the FK is, not the source table definition. With certain referenced tables it works and with others it doesnt. The worse is that with other tables referencing for example the Roles table given above - it works OK.
Attachments
Issue Links
- is caused by
-
-
- Closed
-