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 ?
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;
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;
