New Ad broken – can’t enable, can’t add to group

[Christopher O’Reilly]

Hi there,

This new ad is stuck broken. It cannot be added to more than one group at a time, and cannot be enabled. Various settings won’t save properly despite it saying it was saved.

https://www.hotspringsnc.org/wp-admin/admin.php?page=adrotate

[Arnan de Gans]

Sounds like database issue 🤔
Do you see any errors in the dashboard?
Does it happen to other adverts? Or just this one?

[Christopher O’Reilly]

Only this new one, but i cannot create other new ones.

It has been buggy before but we managed to get things to work and now we are only having an issue adding new ads.

[Arnan de Gans]

Please check the servers error_log file. There are probably a bunch of errors in there mentioning AdRotate.
That’ll tell us what’s wrong and how to proceed.

[Christopher O’Reilly]

et on value of type null in /nas/content/live/hstaadmin/wp-content/plugins/amp-cache/ampforwp-cache.php on line 598
[06-Apr-2022 00:34:14 UTC] WordPress database error Duplicate entry ‘0’ for key ‘PRIMARY’ for query INSERT INTO wp_adrotate_linkmeta ( ad , group , user , schedule ) VALUES (‘0′, ’16’, ‘0’, ‘0’) /* From [www.hotspringsnc.org/wp-admin/admin.php?page=adrotate] in [/nas/content/live/hstaadmin/wp-content/plugins/adrotate-pro/adrotate-manage-publisher.php:554] */ made by require_once(‘wp-load.php’), require_once(‘wp-config.php’), require_once(‘wp-settings.php’), do_action(‘init’), WP_Hook->do_action, WP_Hook->apply_filters, adrotate_insert_input
[06-Apr-2022 00:34:14 UTC] WordPress database error Duplicate entry ‘0’ for key ‘PRIMARY’ for query INSERT INTO wp_adrotate_linkmeta ( ad , group , user , schedule ) VALUES (‘0′, ’17’, ‘0’, ‘0’) /* From [www.hotspringsnc.org/wp-admin/admin.php?page=adrotate] in [/nas/content/live/hstaadmin/wp-content/plugins/adrotate-pro/adrotate-manage-publisher.php:554] */ made by require_once(‘wp-load.php’), require_once(‘wp-config.php’), require_once(‘wp-settings.php’), do_action(‘init’), WP_Hook->do_action, WP_Hook->apply_filters, adrotate_insert_input
[06-Apr-2022 00:34:14 UTC] WordPress database error Duplicate entry ‘0’ for key ‘PRIMARY’ for query INSERT INTO wp_adrotate_linkmeta ( ad , group , user , schedule ) VALUES (‘0′, ’18’, ‘0’, ‘0’) /* From [www.hotspringsnc.org/wp-admin/admin.php?page=adrotate] in [/nas/content/live/hstaadmin/wp-content/plugins/adrotate-pro/adrotate-manage-publisher.php:554] */ made by require_once(‘wp-load.php’), require_once(‘wp-config.php’), require_once(‘wp-settings.php’), do_action(‘init’), WP_Hook->do_action, WP_Hook->apply_filters, adrotate_insert_input

[Arnan de Gans]

Hmm yes. Those are faulty records in the database.
Not sure how that happened though.

Anyway, to fix it you can log in to your PHPMyAdmin (or what have you) and look for the wp_adrotate_linkmeta table.
There will be a bunch of records/rows in there. All records where the ‘ad’ column is 0 are wrong and should be deleted.
Based on your errors, there should be at-least 3.

Of-course take great care in making changes like this as deleting too much or the wrong thing may cause other issues.

If you think that’s too complicated or you’re uncomfortable editing the database like this you can send a message from your dashboard via the Support page and we’ll work it out in a more private manner.

[Christopher O’Reilly]

There was only one entry with 0.

but there were many duplicates for the ads.

See this table export. I already deleted the one entry which had 0 in the ad column.

— phpMyAdmin SQL Dump
— version 4.9.10
— https://www.phpmyadmin.net/
—
— Host: pod-100585.wpengine.com:13306
— Generation Time: Apr 07, 2022 at 01:37 AM
— Server version: 5.7.36-39-log
— PHP Version: 7.2.24-0ubuntu0.18.04.11

SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”;
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = “+00:00”;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

—
— Database: wp_hstaadmin
—

— ——————————————————–

—
— Table structure for table wp_adrotate_linkmeta
—

CREATE TABLE wp_adrotate_linkmeta (
id mediumint(8) UNSIGNED NOT NULL,
ad int(5) UNSIGNED NOT NULL DEFAULT ‘0’,
group int(5) UNSIGNED NOT NULL DEFAULT ‘0’,
user int(5) UNSIGNED NOT NULL DEFAULT ‘0’,
schedule int(5) UNSIGNED NOT NULL DEFAULT ‘0’
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

—
— Dumping data for table wp_adrotate_linkmeta
—

INSERT INTO wp_adrotate_linkmeta ( id , ad , group , user , schedule ) VALUES
(3, 3, 0, 0, 3),
(4, 4, 0, 0, 3),
(6, 6, 0, 0, 3),
(7, 7, 0, 0, 3),
(8, 8, 0, 0, 3),
(9, 9, 0, 0, 3),
(10, 10, 0, 0, 3),
(11, 11, 0, 0, 3),
(12, 12, 0, 0, 3),
(13, 13, 0, 0, 3),
(14, 14, 0, 0, 3),
(15, 15, 0, 0, 3),
(28, 11, 3, 0, 0),
(29, 12, 5, 0, 0),
(30, 13, 5, 0, 0),
(31, 5, 6, 0, 0),
(32, 9, 6, 0, 0),
(33, 12, 6, 0, 0),
(35, 11, 8, 0, 0),
(36, 4, 9, 0, 0),
(37, 7, 9, 0, 0),
(38, 13, 11, 0, 0),
(39, 3, 16, 0, 0),
(40, 4, 16, 0, 0),
(41, 5, 16, 0, 0),
(42, 6, 16, 0, 0),
(43, 7, 16, 0, 0),
(44, 8, 16, 0, 0),
(45, 9, 16, 0, 0),
(46, 10, 16, 0, 0),
(47, 11, 16, 0, 0),
(48, 12, 16, 0, 0),
(49, 13, 16, 0, 0),
(50, 14, 16, 0, 0),
(51, 15, 16, 0, 0),
(52, 3, 17, 0, 0),
(53, 4, 17, 0, 0),
(54, 5, 17, 0, 0),
(55, 6, 17, 0, 0),
(56, 7, 17, 0, 0),
(57, 8, 17, 0, 0),
(58, 9, 17, 0, 0),
(59, 10, 17, 0, 0),
(61, 12, 17, 0, 0),
(63, 14, 17, 0, 0),
(64, 15, 17, 0, 0),
(65, 3, 18, 0, 0),
(66, 4, 18, 0, 0),
(67, 5, 18, 0, 0),
(68, 6, 18, 0, 0),
(69, 7, 18, 0, 0),
(70, 8, 18, 0, 0),
(71, 9, 18, 0, 0),
(72, 10, 18, 0, 0),
(73, 12, 18, 0, 0),
(74, 14, 18, 0, 0),
(75, 15, 18, 0, 0),
(76, 3, 2, 0, 0),
(77, 4, 2, 0, 0),
(78, 5, 2, 0, 0),
(79, 6, 2, 0, 0),
(80, 7, 2, 0, 0),
(81, 8, 2, 0, 0),
(82, 9, 2, 0, 0),
(83, 10, 2, 0, 0),
(84, 12, 2, 0, 0),
(85, 14, 2, 0, 0),
(86, 15, 2, 0, 0),
(87, 16, 0, 0, 3),
(90, 16, 7, 0, 0),
(91, 16, 16, 0, 0),
(95, 3, 0, 0, 4),
(96, 4, 0, 0, 5),
(97, 5, 0, 0, 6),
(98, 6, 0, 0, 7),
(99, 7, 0, 0, 8),
(100, 8, 0, 0, 9),
(101, 9, 0, 0, 10),
(102, 10, 0, 0, 11),
(103, 11, 0, 0, 12),
(104, 12, 0, 0, 13),
(105, 13, 0, 0, 14),
(106, 14, 0, 0, 15),
(107, 15, 0, 0, 16),
(108, 16, 0, 0, 17),
(109, 3, 0, 0, 18),
(110, 4, 0, 0, 19),
(111, 5, 0, 0, 20),
(112, 6, 0, 0, 21),
(113, 7, 0, 0, 22),
(114, 8, 0, 0, 23),
(115, 9, 0, 0, 24),
(116, 10, 0, 0, 25),
(117, 11, 0, 0, 26),
(118, 12, 0, 0, 27),
(119, 13, 0, 0, 28),
(120, 14, 0, 0, 29),
(121, 15, 0, 0, 30),
(122, 16, 0, 0, 31),
(123, 3, 0, 0, 32),
(124, 4, 0, 0, 33),
(125, 5, 0, 0, 34),
(126, 6, 0, 0, 35),
(127, 7, 0, 0, 36),
(128, 8, 0, 0, 37),
(129, 9, 0, 0, 38),
(130, 10, 0, 0, 39),
(131, 11, 0, 0, 40),
(132, 12, 0, 0, 41),
(133, 13, 0, 0, 42),
(134, 14, 0, 0, 43),
(135, 15, 0, 0, 44),
(136, 16, 0, 0, 45),
(137, 17, 0, 0, 46),
(138, 17, 1, 0, 0),
(139, 18, 0, 0, 47),
(140, 18, 17, 0, 0),
(141, 18, 18, 0, 0),
(142, 18, 2, 0, 0),
(143, 17, 16, 0, 0),
(144, 18, 16, 0, 0),
(145, 3, 0, 0, 48),
(146, 4, 0, 0, 49),
(147, 5, 0, 0, 50),
(148, 6, 0, 0, 51),
(149, 7, 0, 0, 52),
(150, 8, 0, 0, 53),
(151, 9, 0, 0, 54),
(152, 10, 0, 0, 55),
(153, 11, 0, 0, 56),
(154, 13, 0, 0, 57),
(155, 14, 0, 0, 58),
(156, 15, 0, 0, 59),
(157, 17, 0, 0, 60),
(158, 18, 0, 0, 61);

—
— Indexes for dumped tables
—

—
— Indexes for table wp_adrotate_linkmeta
—
ALTER TABLE wp_adrotate_linkmeta
ADD PRIMARY KEY ( id );
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

[Christopher O’Reilly]

I tried deleting all the ones with 0 in group, and it just broke all the ads, so I restored the table.

[Christopher O’Reilly]

But unfortunately now they all remain broken :/

[Christopher O’Reilly]

scratch that, the database recovery ultimately allowed them to function after being restored.

Still haven’t resolved the issue with creating new ads though.

[Christopher O’Reilly]

seems related:
https://stackoverflow.com/questions/18688694/error-duplicate-entry-0-for-key-primary

perhaps the table got broken somehow and the incrementation is having trouble. why is it trying to start at 0 all the time?

[Christopher O’Reilly]

I also get this error when trying to create a new ad, and it just goes back to the old one:
[07-Apr-2022 02:18:02 UTC] WordPress database error Duplicate entry ‘0’ for key ‘PRIMARY’ for query INSERT INTO wp_adrotate ( title , bannercode , thetime , updated , author , imagetype , image , tracker , show_everyone , desktop , mobile , tablet , os_ios , os_android , os_other , type , weight , autodelete , budget , crate , irate , state_req , cities , states , countries ) VALUES (”, ”, ‘1649283482’, ‘1649283482’, ‘oreillyc’, ‘dropdown’, ”, ‘N’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ’empty’, ‘6’, ‘N’, ‘0’, ‘0’, ‘0’, ‘N’, ‘a:0:{}’, ‘a:0:{}’, ‘a:0:{}’) /* From [www.hotspringsnc.org/wp-admin/admin.php?page=adrotate&view=addnew] in [/nas/content/live/hstaadmin/wp-content/plugins/adrotate-pro/dashboard/publisher/adverts-edit.php:15] */ made by do_action(‘toplevel_page_adrotate’), WP_Hook->do_action, WP_Hook->apply_filters, adrotate_manage_adverts, include(‘/plugins/adrotate-pro/dashboard/publisher/adverts-edit.php’)

[Christopher O’Reilly]

Well, I managed to manually configure the ad in the database, and resolve the errors so it displays, but the Primary key errors are still showing up all over.

Can you help resolve those errors so we can edit ads normally?

[Arnan de Gans]

Must be an issue with the indexes then. Do both those tables have a PRIMARY KEY index for id?
I can’t reproduce it on my test site and the install script adds the index just fine.

Anyway, if your table has no such index, please create one or re-install the plugin so that the install script can create it along with the new tables. Re-installing of-course will loose all current AdRotate data.

ALTER TABLE wp_adrotate_linkmeta ADD PRIMARY KEY (id);
ALTER TABLE wp_adrotate ADD PRIMARY KEY (id);

Perhaps reset the index:

ALTER TABLE wp_adrotate AUTO_INCREMENT = 1;
Where 1 is the next to be created id. SO if there are 10 records now, it should be 11.

Did you edit the database recently? Perhaps restored it? Or did maintenance?
I’ve never seen the indexes mess up before…

[Christopher O’Reilly]

They do have primary keys, but updating the adrotate auto_increment to 19 (we currently have 18 adds)
ALTER TABLE wp_adrotate AUTO_INCREMENT = 19;

didn’t resolve the issue. I still can’t edit ads effectively through the wordpress backend and when creatin a new add it just keeps getting stuck on the add with id 0.

We have been using AdRotate for many years. At some point I think we had to restore some information through the database.

Do you think it’d be possible to repair the database, or export and re-import ads, or do all of our ads need to be manually recreated to fix this issue?

[Arnan de Gans]

So weird… I’m not really a database expert and if the ‘simple’ solutions don’t work i quickly run out ideas.
If it is indeed an issue with a messed up index, perhaps deleting it and creating a new index helps. Or making a sql dump of the table contents and just creating a new table may just be a more sure fix. Ofcourse import the sql dump afterwards to get your ads back.

Like I said, I’ve never seen an index mess up like this. Certainly not with a small index like this.