Should we use service_id, provider_id instead? Anthony
> -----Original Message----- > From: Frank Zhang [mailto:frank.zh...@citrix.com] > Sent: Friday, January 18, 2013 4:21 PM > To: cloudstack-dev@incubator.apache.org > Subject: take a look at your foreign key, it may exceed mysql limtion > > Some of our DB schema creates long foreign keys which exceed max length > of foreign key allowed by mysql. For mysql default in 6.2 that allows > longer key so we don't see the issue. > But for mysql in rhel5.x, the default mysql only allows 1000 bytes for > each foreign key, then the DB deployment will fail. I know ACS build > will only support rhel6.x, but please still note you > may break the rule some time. > > For example: > > CREATE TABLE `ntwk_offering_service_map` ( > `id` bigint unsigned NOT NULL auto_increment, > `network_offering_id` bigint unsigned NOT NULL COMMENT > 'network_offering_id', > `service` varchar(255) NOT NULL COMMENT 'service', > `provider` varchar(255) COMMENT 'service provider', > `created` datetime COMMENT 'date created', > PRIMARY KEY (`id`), > CONSTRAINT `fk_ntwk_offering_service_map__network_offering_id` > FOREIGN KEY(`network_offering_id`) REFERENCES `network_offerings`(`id`) > ON DELETE CASCADE, > UNIQUE (`network_offering_id`, `service`, `provider`) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > > The key ' UNIQUE (`network_offering_id`, `service`, `provider`)' > contains service(255) + provider(255), so there is at least 500 chars. > Please note since mysql 5.x, varchar is calculated by chars not by > bytes, so with uft8 encoding which is usually mult-bytes sequence, 500 > chars definitely beyond 1000 bytes limit.
