PowerDNS MySQL INT limit reached.
The very non-descriptive error below indicates that you have reached the maximum value for the MySQL INT Data Type.
Communicator thread died because of error: Failed to execute mysql_query, perhaps connection died? Err=-1: Duplicate entry ‘2147483647′ for key 1
What you need to do if this happens to you on your PowerDNS records table is the following:
mysql> alter table records modify id BIGINT AUTO_INCREMENT;
If it happens to your domains table, then things get a bit more complicated and you should do the following:
mysql> alter table records drop foreign key `records_ibfk_1`;
mysql> alter table records modify domain_id BIGINT ;
mysql> alter table domains modify id BIGINT AUTO_INCREMENT;
mysql> alter table records add constraint `records_ibfk_1` FOREIGN KEY (`domain
_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE;
The above worked for me, your results may vary.
- None Found
April 2nd, 2008 at 6:34 pm
Your fix works, Augie, but it’s a) slow and b) requires unnecessary storage space.
Much better:
ALTER TABLE records MODIFY id INT UNSIGNED AUTO_INCREMENT NOT NULL;
The real key there is the “unsigned” – it gives you twice as much storage in the same amount of space. And the query runs nearly simultaneously, as opposed to the hour+ it takes to change the size of a field in a table that long.
Another observation: why are all of the fields in the records table nullable? Best practice is usually to make them non-nullable unless for some reason nulls are really needed (and they’re usually no).
JFWIW.