Are composite primary keys okay in a clustered environment?
Based on http://support.severalnines.com/entries/21692388-Limitations-in-Galera-Cluster-for-MySQL as well as lore from co-workers, I am aware that I need to have a primary key in each table I define. So far, I've defined the first field in the record as 'integer primary key autoincrement' and called it rowid.
Now I'd like to define a table with no rowid, but with the primary key defined as the combination of a text field and an int. My question is whether Galera clustering using the InnoDB storage engine will be just as happy with this as it seems to be with an "integer primary key autoincrement" field.
Reading through the discussion about http://support.severalnines.com/entries/20668841-Having-trouble-importing-data-into-a-Galera-cluster, I see that the original poster had a composite field in his database definition and no comment was made about that, which seems like implicit permission to do what I want, but I thought it might be useful to have an explicit answer since I haven't been able to find any information about this particular question despite a lot Googling and searching in the forums and knowledge base here.
The table definition I'd like to use is:
create table history (
plugin text,
runtime int,
errors int,
primary key (plugin, runtime)
);
Thanks in advance for any insight into the issue.
-
Hi,
yes it is okay, but your example will fail with:
ERROR 1170 (42000): BLOB/TEXT column 'plugin' used in key specification without a key length
You must index a part of the blob/text, like this look for 512 below, which will use the first 512B of the blob/text:
create table history ( plugin text, runtime int, errors int, primary key (plugin(512), runtime) );
BR
johan
Please sign in to leave a comment.
Comments
2 comments