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.

One thought on “PowerDNS MySQL INT limit reached.

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>