Comments - The table have issue. select is empty but can't insert more data

Please provide more information - table structure, sample of failing insert, selects etc.

Hi Gilfillan, I have 2 table:

CREATE TABLE `table2` ( `DSID` INT(11) NOT NULL, `PollingPeriod` INT(11) NOT NULL, `MinPeriod` INT(11) NOT NULL DEFAULT '120', `Enabled` TINYINT(4) NOT NULL DEFAULT '1', `Version` INT(11) NOT NULL DEFAULT '0', PRIMARY KEY (`DSID`), CONSTRAINT `FK_Table2Table1DSID` FOREIGN KEY (`DSID`) REFERENCES `table1` (`DSID`) ON DELETE CASCADE ) COLLATE='latin1_swedish_ci' ENGINE=InnoDB

CREATE TABLE "table1" ( "DSID" INT NOT NULL, "Dataset" VARCHAR(8) NOT NULL, "Collector" VARCHAR(32) NULL DEFAULT NULL, "resType" INT NOT NULL, "StatTablePrefix" VARCHAR(8) NULL DEFAULT NULL, "datasetDescription" VARCHAR(80) NULL DEFAULT NULL, "datasetWeight" INT NOT NULL DEFAULT '0', "typeGroupID" INT NULL DEFAULT NULL, "summarySectionTitle" VARCHAR(128) NULL DEFAULT ' ', "DatasetType" TINYINT NULL DEFAULT NULL, "NoAutoInsert" BIT NOT NULL DEFAULT b'0', "NoAutoName" BIT NOT NULL DEFAULT b'0', "SpeedUnit" VARCHAR(8) NULL DEFAULT NULL, "AggrOrder" INT NULL DEFAULT '2', "PrimaryTrend" VARCHAR(32) NULL DEFAULT NULL, "ShortDescription" VARCHAR(16) NULL DEFAULT NULL, "QueryViewFlag" BIT NOT NULL DEFAULT b'0', "ResourceDSID" INT NULL DEFAULT NULL, "ConfigData" BIT NOT NULL DEFAULT b'0', "devAddrType" INT NOT NULL DEFAULT '0', PRIMARY KEY ("DSID") )

- The table1 storage some configuration information - After TRUNCATE TABLE the table2. I immediately inserted additional data for this table. but the DUP PRIMARY KEY error is reported. although when I SELECT TABLE and the result returns no lines. But I think somewhere in the database still caches the data cache of this table

I want to ask what is the problem with table2? Can we check what is happening in table2? And how to solve the problem?

Thanks, Bang Hoang

I still can't repeat this issue. Do you have sample SQL that will repeat the problem, as well as version information?

Hi lan Gilfillan, Can you share the status of this ticket?

I have run the query: TRUNCATE TABLE table2;

Then, immediately run the insert statement with another session: INSERT INTO UniversalDatasetTable (DSID, Enabled, PollingPeriod, MinPeriod, SnmpVersionNumber) VALUES (10300, 1, 900, 60, 0); The message returned is DUP PRIMARY KEY.

My mariaDB is: mysql Ver 15.1 Distrib 10.1.22-MariaDB, for Linux (x86_64) using readline 5.1

Hi Gilfillan, Do you know why it happened? Can you explain the root cause ? Thanks advance !

After TRUNCATE TABLE. I immediately inserted additional data for this table. but the DUP PRIMARY KEY error is reported. although when I SELECT TABLE and the result returns no lines. But I think somewhere in the database still caches the data cache of this table. Please help to explain the root cause. Thanks