Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
add comment

1 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;
share|improve this answer
    
"db_name.pb_stat_rule.product_id","0","16777215","1","8","3346985","1148373","2‌​6621.3943","45543.8722","MEDIUMINT(8) UNSIGNED" This is the output of the query –  vinny Jun 13 '12 at 18:43
    
but how do I know what value has it currently reached ? –  vinny Jun 13 '12 at 18:45
    
It just suggested the datatype to be MEDIUMINT UNSIGNED. The max is 16777215. That means it cannot go further. You must change it to INT UNSIGNED. –  RolandoMySQLDBA Jun 13 '12 at 18:52
    
the problem is id was a primary key and product_id is not, the maximum value thats in the column is 394874. So, how would it affect it ? There is another column named installation which is still populating the data ! Thanks for a quick reply –  vinny Jun 13 '12 at 19:04
    
You cannot assign new values greater than 16777215. If you have values for product_id greater than 16777215 to assign, you must change it. –  RolandoMySQLDBA Jun 13 '12 at 19:12
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.