cloudstack-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Prachi Damle <Prachi.Da...@citrix.com>
Subject RE: take a look at your foreign key, it may exceed mysql limtion
Date Sat, 19 Jan 2013 02:14:29 GMT
>>>But for mysql in rhel5.x, the default mysql only allows 1000 bytes for each foreign
key

This seems to be the limitation of MyISAM engine. Using InnoDB does not cause this issue.

-----Original Message-----
From: Frank Zhang [mailto:Frank.Zhang@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.

Mime
View raw message