Change in datatype?

Question

I have a table on which rows are inserted on hourly basis. The row contains id, installation, product_id and hit_allowed. all of them were mediumint in the beginning. but few days back id reached it limit and I had to change the datatype to bigint.

But since then the script from which these rows are populated, it has stopped populating product_id. No changes in the script were made. Can anyone tell me if I will have to change the datatype for it too ?

Answer 1

Please run the following query

SELECT product FROM mytable PROCEDURE ANALYSE();

PROCEDURE ANALYSE() will tell to you the the min value, max value, and needed datatype.

If the product_id is mediumint and the highest value you have for product_id is 16777215, you've hit the max. Change to INT UNSIGNED not BIGINT.

ALTER TABLE mytable MODIFY COLUMN product_id INT UNSIGNED NOT NULL;

UPDATE 2012-06-13 16:06 EDT

You could try importing the change differently.

CREATE TABLE pb_stat_rule_new LIKE pb_stat_rule;
ALTER TABLE pb_stat_rule_new MODIFY COLUMN product_id INT UNSIGNED NOT NULL;
INSERT INTO pb_stat_rule_new SELECT * FROM pb_stat_rule;
ALTER TABLE pb_stat_rule RENAME pb_stat_rule_old;
ALTER TABLE pb_stat_rule_new RENAME pb_stat_rule;

Try out the new file. If things still do not work, you can undo this:

ALTER TABLE pb_stat_rule RENAME pb_stat_rule_zap;
ALTER TABLE pb_stat_rule_old RENAME pb_stat_rule;
DROP TABLE pb_stat_rule_zap;