MySQL Bugs: #29884: Duplicate key problem

Bug #29884	Duplicate key problem
Submitted:	19 Jul 2007 0:00	Modified:	20 Jul 2007 8:39
Reporter:	John Walker	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	5.0.27-community-nt	OS:	Windows (XP SP2 fully updated)
Assigned to:		CPU Architecture:	Any
Tags:	duplicate key

Description:
When trying to add a single record to a very simple but empty table, I kept encountering the error "Duplicate entry '127.0.0.1' for key username (I was using an IP address as a key).

I was generating the query via PHP (5.16). However when I tried the same query on phpMyAdmin (2.10) I got the same error message.

Eventually, after trying a number of less severe solutions (flushing the table, deleting all records) I deleted and then recreated the table. The problem went away.

How to repeat:
Not sure. Has happened again yet.

Suggested fix:
Value left in indexes after records have been deleted?

Thank you for the bug report. Could you please test with the latest released
version 5.0.45 and provide the table create statement and insert commands
as test case. Thanks in advance.

Not being completely at ease with installing new server versions I'd rather take a week or so to get to that but here is the CREATE code (below)

I now notice that I had a second UNIQUE key defined (usercode) and it is possible this value was not properly defined in the SQL statement. This might create the problem but it seems odd if that is the case, that the error did not come up in the several tests since I recreated the database, all using a single SQL, missing a value for usercode.

I'm afraid that I cannot faithfully recreate the original version of the SQL statement values so you might as well insert working values for the other variables into, or, if you can wait a week or so, I will try to get back to this and test it to see if I can somehow recreate the error with these new clues.

  $sql = "INSERT INTO active_users     (username,password,userid,userlevel,usercode,email,dtnew) 
VALUES ('127.0.0.1','$password','$userid','$userlevel','',".time().")";
  
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- phpMyAdmin SQL Dump
-- version 2.10.2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Jul 18, 2007 at 05:26 PM
-- Server version: 5.0.27
-- PHP Version: 5.1.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

-- 
-- Database: `ged`
-- 

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

-- 
-- Table structure for table `users`
-- 

CREATE TABLE `users` (
  `username` varchar(30) character set latin1 NOT NULL,
  `password` varchar(32) character set latin1 default NULL,
  `userid` varchar(32) character set latin1 default NULL,
  `usercode` varchar(5) character set latin1 NOT NULL,
  `userlevel` tinyint(1) unsigned NOT NULL,
  `email` varchar(50) character set latin1 default NULL,
  `dtnew` timestamp NULL default NULL,
  `dtmod` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`username`),
  UNIQUE KEY `ucodex` (`usercode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Thank you for the feedback.

I could not repeat described behaviour nor with version 5.0.27, neither with current version and provided CREATE TABLE  and INSERT statements. Also INSERT statement contained error, so I had to modify it.

Please try to repeat bug in your environment and provide us information how we can do it too.

When I noticed the error in my SQL, I was initially mortified to think that this "bug" might in fact have just been my error. But on further thought it seemed unlikely as, using the same SQL statement, there was a problem before the recreate of the table and no problem after.

But I do not think that this is an easily reproduceable bug. I suspect that as a result of an odd combination of table column modifications and tests, at some point an index entry didn't get deleted when the corresponding record was deleted (this may not be the actual explanation but it seems like one that would fit the facts).

I will make an effort to update my database version and test with the new one when I can get back to this. But I suspect that if this one has not been fixed in the newer version, you might have to wait until the occasional similar report adds more information.

John,

thank you for the feedback.

Statement "INSERT INTO active_users     (username,password,userid,userlevel,usercode,email,dtnew) 
VALUES ('127.0.0.1','$password','$userid','$userlevel','',".time().")";

contains 7 fields and 6 values, so this statement can be correct only if one of your variables contains "', '". This is not MySQL bug.

So to repeat described behaviour we need real statement MySQL server got. Without PHP variables, but with their values. Until we get it we could not repeat and fix the bug.

When you will be able to provide such a statement, please provide it and reopen the report.