Duplicate entry errors

Difficulty: Advanced | Updated: Jan 17th, 2013
Only applies to installations made prior to version 3.8. All previous versions affected.
Do NOT blindly increase the integer size! Read a manual first – MySQL Numeric Types!

If your site is particularly busy and handles a lot of traffic you might run into this (or very similar) error message at some point:

WordPress database error Duplicate entry '16777215' for key 'PRIMARY' for query INSERT INTO wp_adrotate_tracker (ipaddress,timer,bannerid,stat,useragent) VALUES ('xxx.xxx.xxx.xxx','1358187611','59','i','')

This basically means your database is full.

Why this happens

When I designed AdRotates database model 4-5 years ago the plugin was meant as a sort of stopgap to quickly post small adverts. With this in mind I thought it smart to use a small integer for ID numbers. Because smaller numbers take less space to store. Over time this turns out to be a problem.

Solving the issue

In order to solve this issue you need to raise the capacity.
You can do this by logging in to your PHPMyAdmin panel and run the following queries:

ALTER TABLE `wp_adrotate_stats` CHANGE `id` `id` BIGINT(9) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `wp_adrotate_tracker` CHANGE `id` `id` BIGINT(9) UNSIGNED NOT NULL AUTO_INCREMENT;

Running queries on your database is at your own risk. You should always have a backup!

After running these 2 queries the ID cap is effectively raised a maximum of 16777215 (16 million something) to 709551615 (700 million something). This should be plenty for the foreseeable future.

If you run into the issue again, or expect to run into it again within a few months simply run the queries again, but with a higher number for the bigint() data type.

For example:

ALTER TABLE `wp_adrotate_stats` CHANGE `id` `id` BIGINT(12) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `wp_adrotate_tracker` CHANGE `id` `id` BIGINT(12) UNSIGNED NOT NULL AUTO_INCREMENT;

Running queries on your database is at your own risk. You should always have a backup!

The effect will be immediate, you do not need to disable AdRotate while doing this and you should not loose any of your data.

Make sure your income is reliable!

With fast and reliable hosting! AJdG Solutions recommends and uses MediaTemple for WordPress hosting! When your money is in advertising you do not want downtime, ever! Check out the MediaTemple website and talk to their representatives for more information!