Mysql duplicate entry for key primary

Configuration
1

I have been receiving this error for quite sometime. How to fix it? My mysql is external.

mysql-duplicates
mysql-duplicates1613×158 101 KB

2

I’ve recreated a new db. Restarted HA, but the error of duplicate entry still exist. I have also ran these lines for each tables.

SET foreign_key_checks = 0;
TRUNCATE table_name; //Enter each of the table names 
SET foreign_key_checks = 1;
3

I am at a lost and do not know what to do to fix this error. I have tried numerous times dropping all tables and recreating in mysql. Right off the bat, the duplicate error message pops up.

How do I start from scratch? Does HA retained any entries along side with Mysql?

This is the table structure I use to upload to DB.

-- phpMyAdmin SQL Dump
-- version 4.9.4
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Nov 03, 2020 at 02:26 AM
-- Server version: 5.6.41-84.1
-- PHP Version: 7.3.6

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 */;

--
--

-- --------------------------------------------------------

--
-- Table structure
--

CREATE TABLE `events` (
  `event_id` int(11) NOT NULL,
  `event_type` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `event_data` text COLLATE utf8_unicode_ci,
  `origin` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `time_fired` datetime DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `context_id` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL,
  `context_user_id` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL,
  `context_parent_id` char(36) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `recorder_runs` (
  `run_id` int(11) NOT NULL,
  `start` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  `closed_incorrect` tinyint(1) DEFAULT NULL,
  `created` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `schema_changes` (
  `change_id` int(11) NOT NULL,
  `schema_version` int(11) DEFAULT NULL,
  `changed` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `states` (
  `state_id` int(11) NOT NULL,
  `domain` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `entity_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `attributes` text COLLATE utf8_unicode_ci,
  `event_id` int(11) DEFAULT NULL,
  `last_changed` datetime DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `context_id` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL,
  `context_user_id` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL,
  `old_state_id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


ALTER TABLE `events`
  ADD PRIMARY KEY (`event_id`),
  ADD KEY `ix_events_context_user_id` (`context_user_id`),
  ADD KEY `ix_events_context_id` (`context_id`),
  ADD KEY `ix_events_time_fired` (`time_fired`),
  ADD KEY `ix_events_context_parent_id` (`context_parent_id`),
  ADD KEY `ix_events_event_type_time_fired` (`event_type`,`time_fired`);

ALTER TABLE `recorder_runs`
  ADD PRIMARY KEY (`run_id`),
  ADD KEY `ix_recorder_runs_start_end` (`start`,`end`);

ALTER TABLE `schema_changes`
  ADD PRIMARY KEY (`change_id`);

ALTER TABLE `states`
  ADD PRIMARY KEY (`state_id`),
  ADD KEY `ix_states_event_id` (`event_id`),
  ADD KEY `ix_states_last_updated` (`last_updated`),
  ADD KEY `ix_states_entity_id_last_updated` (`entity_id`,`last_updated`);


--
-- AUTO_INCREMENT
--
ALTER TABLE `events`
  MODIFY `event_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `recorder_runs`
  MODIFY `run_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `schema_changes`
  MODIFY `change_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `states`
  MODIFY `state_id` int(11) NOT NULL AUTO_INCREMENT;
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 */;
4

I am running into the same issue, I also have an external mysql server with a duplicate key issue in events.

5

This is an ongoing issue I am having and it’s driving me nuts. If it’s not this error, it is the foreign key.

I thought I have fixed the first by deleting all the tables and restart HA. That is restart HA and while HA is restarting. Drop all tables in mysql leaving only an empty database name. HA will create the tables once booted up. The error seems to go awhile, but after a few days, I start receiving an new error. Something about foreign key.This below sql cmd suppose to fix it, but it does not.

SET foreign_key_checks = 0;
TRUNCATE table_name; //Enter each of the table name
SET foreign_key_checks = 1;

So, I have drop the tables once again, restarted Ha. It has been a day. No errors yet other than the usual once in awhile lost connection to mysql which I think is my host provider.

(2013, ‘Lost connection to MySQL server during query’)

6

I actually just performed a Host restart instead of just a HA restart (that will give me more time to truncate the tables) and truncated all tables and I don’t seem to have the issue anymore.

Very strange issue, it seemed like HA was keeping some other book-keeping in the recorder.

Let’s hope the issue doesn’t come back :crossed_fingers: