First up: I'm not looking for a way to fix the dreaded tablespace exists InnoDB error found here, rather I'm looking for a way to prevent it! Over the past few weeks we've had a table dissapear from our database at random, with no way to re-create it (because it gives a tablespace exists error). We've narrowed it down to the following table:
CREATE TABLE product_localised (
id INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
product_id INT (10) UNSIGNED NOT NULL,
language_id INT (10) UNSIGNED NOT NULL,
slug VARCHAR (255) COLLATE utf8_unicode_ci NOT NULL,
title TEXT COLLATE utf8_unicode_ci,
description TEXT COLLATE utf8_unicode_ci,
description_short TEXT COLLATE utf8_unicode_ci,
custom_startselect_content TEXT COLLATE utf8_unicode_ci,
created_at TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
updated_at TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
deleted_at TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY product_localised_product_id_language_id_unique (`product_id`, `language_id`),
KEY product_localised_language_id_foreign (`language_id`),
CONSTRAINT product_localised_language_id_foreign FOREIGN KEY (`language_id`) REFERENCES languages (`id`),
CONSTRAINT product_localised_product_id_foreign FOREIGN KEY (`product_id`) REFERENCES products (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 46727 DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;
The question: can someone look at the table's structure and keys and tell if there's anything wrong with it?!
We're hosting our database on Amazon's RDS and tried MySQL 5.5 and 5.6, with innodb_file_per_table enabled.
We also tried MySQL 5.5 on Ubuntu Server 14.04 LTS (using Vagrant) and MySQL 5.6 on Windows using xampp.
In response to questions asked below:
Consider removing id and using (product_id, language_id) as the PK. And surely you don't need 2 billion languages, so consider shrinking language_id to SMALLINT UNSIGNED:
- We prefer single column primary keys as it makes using our ORM a lot easier
- We don't have a million languages, but it's simply our auto increment setup we use throughout our project, surely this shouldn't cause any problems?
