Hi, we were facing the same issue and my colleague tracked it down: https://serverfault.com/questions/1064579/openstack-only-building-one-vm-per... We have a customized fixed for us which works nicely, but it would surely help to get this fixed in general as it seems to be a reoccurring issue. Zitat von Przemyslaw Basa <przemyslaw.basa@redge.com>:
Hi,
Well i think i figured it out. Following Xena deployment instructions mariadb was installed in version 10.6.5 and it seems to be some kind of bug in this version. Upgrading to 10.6.8 fixed this particular issue for me.
I've checked some older and newer versions (10.5.6, 10.8.3) and problematic query behaves there like in 10.6.8.
Here's how I've done my tests if someone is interested:
% docker run --rm --detach --name mariadb-10.6.5 --env MYSQL_ROOT_PASSWORD=test mariadb:10.6.5 % docker run --rm --detach --name mariadb-10.6.8 --env MYSQL_ROOT_PASSWORD=test mariadb:10.6.8
% docker exec -i mariadb-10.6.5 mysql -u root -ptest < tables_dump.sql % docker exec -i mariadb-10.6.8 mysql -u root -ptest < tables_dump.sql
% docker exec -i mariadb-10.6.5 mysql -u root -ptest -t test < test.sql +----+--------------------------------------+------------+-------------------+---------+----------+------------------+-------+ | id | uuid | generation | resource_class_id | total | reserved | allocation_ratio | used | +----+--------------------------------------+------------+-------------------+---------+----------+------------------+-------+ | 5 | 16f620c0-8c6f-4984-8d58-e2c00d1b32da | 50 | 0 | 128 | 0 | 2 | 13318 | | 5 | 16f620c0-8c6f-4984-8d58-e2c00d1b32da | 50 | 1 | 1031723 | 2048 | 1 | NULL | | 5 | 16f620c0-8c6f-4984-8d58-e2c00d1b32da | 50 | 2 | 901965 | 2 | 1 | NULL | +----+--------------------------------------+------------+-------------------+---------+----------+------------------+-------+
% docker exec -i mariadb-10.6.8 mysql -u root -ptest -t test < test.sql +----+--------------------------------------+------------+-------------------+---------+----------+------------------+-------+ | id | uuid | generation | resource_class_id | total | reserved | allocation_ratio | used | +----+--------------------------------------+------------+-------------------+---------+----------+------------------+-------+ | 5 | 16f620c0-8c6f-4984-8d58-e2c00d1b32da | 50 | 0 | 128 | 0 | 2 | 5 | | 5 | 16f620c0-8c6f-4984-8d58-e2c00d1b32da | 50 | 1 | 1031723 | 2048 | 1 | 13312 | | 5 | 16f620c0-8c6f-4984-8d58-e2c00d1b32da | 50 | 2 | 901965 | 2 | 1 | 1 | +----+--------------------------------------+------------+-------------------+---------+----------+------------------+-------+
% cat tables_dump.sql create database test; connect test;
CREATE TABLE `allocations` ( `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, `resource_provider_id` int(11) NOT NULL, `consumer_id` varchar(36) NOT NULL, `resource_class_id` int(11) NOT NULL, `used` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `allocations_resource_provider_class_used_idx` (`resource_provider_id`,`resource_class_id`,`used`), KEY `allocations_resource_class_id_idx` (`resource_class_id`), KEY `allocations_consumer_id_idx` (`consumer_id`) ) ENGINE=InnoDB AUTO_INCREMENT=547 DEFAULT CHARSET=utf8mb3;
CREATE TABLE `inventories` ( `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, `resource_provider_id` int(11) NOT NULL, `resource_class_id` int(11) NOT NULL, `total` int(11) NOT NULL, `reserved` int(11) NOT NULL, `min_unit` int(11) NOT NULL, `max_unit` int(11) NOT NULL, `step_size` int(11) NOT NULL, `allocation_ratio` float NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_inventories0resource_provider_resource_class` (`resource_provider_id`,`resource_class_id`), KEY `inventories_resource_class_id_idx` (`resource_class_id`), KEY `inventories_resource_provider_id_idx` (`resource_provider_id`), KEY `inventories_resource_provider_resource_class_idx` (`resource_provider_id`,`resource_class_id`) ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb3;
CREATE TABLE `resource_providers` ( `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, `uuid` varchar(36) NOT NULL, `name` varchar(200) DEFAULT NULL, `generation` int(11) DEFAULT NULL, `root_provider_id` int(11) DEFAULT NULL, `parent_provider_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_resource_providers0uuid` (`uuid`), UNIQUE KEY `uniq_resource_providers0name` (`name`), KEY `resource_providers_name_idx` (`name`), KEY `resource_providers_parent_provider_id_idx` (`parent_provider_id`), KEY `resource_providers_root_provider_id_idx` (`root_provider_id`), KEY `resource_providers_uuid_idx` (`uuid`), CONSTRAINT `resource_providers_ibfk_1` FOREIGN KEY (`parent_provider_id`) REFERENCES `resource_providers` (`id`), CONSTRAINT `resource_providers_ibfk_2` FOREIGN KEY (`root_provider_id`) REFERENCES `resource_providers` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3;
INSERT INTO `allocations` VALUES ('2022-07-07 23:08:10',NULL,329,5,'b6da8a02-a96c-464e-a6c4-19c96c83dd44',1,12288),('2022-07-07 23:08:10',NULL,332,5,'b6da8a02-a96c-464e-a6c4-19c96c83dd44',0,4),('2022-07-08 06:26:28',NULL,335,4,'aec7aaea-10df-451b-b2ce-847099ee0110',1,2048),('2022-07-08 06:26:28',NULL,338,4,'aec7aaea-10df-451b-b2ce-847099ee0110',0,2),('2022-07-12 08:53:21',NULL,400,1,'29cf1131-1bb3-4f06-b339-930a4bb055d4',1,16384),('2022-07-12 08:53:21',NULL,403,1,'29cf1131-1bb3-4f06-b339-930a4bb055d4',0,2),('2022-07-14 08:24:27',NULL,538,5,'9681447d-57ec-45c7-af48-63be3c7201da',2,1),('2022-07-14 08:24:27',NULL,541,5,'9681447d-57ec-45c7-af48-63be3c7201da',1,1024),('2022-07-14 08:24:27',NULL,544,5,'9681447d-57ec-45c7-af48-63be3c7201da',0,1); INSERT INTO `resource_providers` VALUES ('2022-07-04 11:59:49','2022-07-13 13:03:08',1,'6ac81bb4-50ef-4784-8a64-9031afeaaa9d','p-os-compute01.openstack.local',50,1,NULL),('2022-07-04 12:00:49','2022-07-13 13:03:07',4,'a324b3b9-f8c8-4279-bf63-a27163fcf792','g-os-compute01.openstack.local',42,4,NULL),('2022-07-04 12:03:57','2022-07-14 08:24:27',5,'16f620c0-8c6f-4984-8d58-e2c00d1b32da','t-os-compute01.openstack.local',50,5,NULL); INSERT INTO `inventories` VALUES ('2022-07-04 11:59:50','2022-07-11 09:24:04',1,1,0,128,0,1,128,1,2),('2022-07-04 11:59:50','2022-07-11 09:24:04',4,1,1,1031723,2048,1,1031723,1,1),('2022-07-04 11:59:50','2022-07-11 09:24:04',7,1,2,901965,2,1,901965,1,1),('2022-07-04 12:01:53','2022-07-04 14:59:53',10,4,0,128,0,1,128,1,2),('2022-07-04 12:01:53','2022-07-04 14:59:53',13,4,1,1031723,2048,1,1031723,1,1),('2022-07-04 12:01:53','2022-07-04 14:59:53',16,4,2,901965,2,1,901965,1,1),('2022-07-04 12:03:57','2022-07-14 07:16:08',17,5,0,128,0,1,128,1,2),('2022-07-04 12:03:57','2022-07-14 07:09:11',20,5,1,1031723,2048,1,1031723,1,1),('2022-07-04 12:03:57','2022-07-14 07:09:11',23,5,2,901965,2,1,901965,1,1);
% cat test.sql SELECT rp.id, rp.uuid, rp.generation, inv.resource_class_id, inv.total, inv.reserved, inv.allocation_ratio, allocs.used FROM resource_providers AS rp JOIN inventories AS inv ON rp.id = inv.resource_provider_id LEFT JOIN ( SELECT resource_provider_id, resource_class_id, SUM(used) AS used FROM allocations WHERE resource_class_id IN (0, 1, 2) AND resource_provider_id IN (5) GROUP BY resource_provider_id, resource_class_id ) AS allocs ON inv.resource_provider_id = allocs.resource_provider_id AND inv.resource_class_id = allocs.resource_class_id WHERE rp.id IN (5) AND inv.resource_class_id IN (0,1,2) ;
Regards, Przemyslaw Basa