Forums / General / Clustering database id column sizes too small

Clustering database id column sizes too small

Previous topic

General

Next topic

Author	Message
Bruce Morrison	Monday 12 March 2007 6:59:30 pm I've been running a clustered site for about six months and found that the use of mediumInt(8) for the id & masterid columns is too small. The issue was noticed when the client could no longer upload images and the cause was tracked back to the auto increment values being the max possible values for the column defination (16777215) I've changed the columns to bigints (max value 18446744073709551615) with the following sql: ALTER TABLE `ezdbfile_data` CHANGE `id` `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE `ezdbfile_data` CHANGE `masterid` `masterid` BIGINT UNSIGNED NOT NULL DEFAULT '0'; ALTER TABLE `ezdbfile` CHANGE `id` `id` BIGINT( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT; http://issues.ez.no/IssueView.php?Id=10438 Cheers Bruce My Blog: http://www.stuffandcontent.com/ Follow me on twitter: http://twitter.com/brucemorrison Consolidated eZ Publish Feed : http://friendfeed.com/rooms/ez-publish

Author

Message

Bruce Morrison

Monday 12 March 2007 6:59:30 pm

I've been running a clustered site for about six months and found that the use of mediumInt(8) for the id & masterid columns is too small.

The issue was noticed when the client could no longer upload images and the cause was tracked back to the auto increment values being the max possible values for the column defination (16777215)

I've changed the columns to bigints (max value 18446744073709551615) with the following sql:

ALTER TABLE `ezdbfile_data` CHANGE `id` `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `ezdbfile_data` CHANGE `masterid` `masterid` BIGINT UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `ezdbfile` CHANGE `id` `id` BIGINT( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT;

http://issues.ez.no/IssueView.php?Id=10438

Cheers
Bruce

My Blog: http://www.stuffandcontent.com/
Follow me on twitter: http://twitter.com/brucemorrison
Consolidated eZ Publish Feed : http://friendfeed.com/rooms/ez-publish