Primary key violation when there is absolutely no data in table

Question

I have a Google App Engine Standard Env instance. My application which connects to Google Cloud SQL has the following error -

 Duplicate entry '1' for key 'PRIMARY' Query: INSERT INTO user_profiles
 (uid, name, profile_image_url, favourite_team_id, provider, admin)
 VALUES (?, ?, ?, ?, ?, ?) on duplicate key update name = ?,
 profile_image_url = ?, favourite_team_id = ?, provider = ?, admin = ?

Whats really strange is that the user_profiles table is empty!

select count(*) from eplreflex.user_profiles 

count(*) 
0

Looks like when I dropped and recreated the schema, the mysql instance still somehow has old data in it. What exactly is going on? How do I delete everything including the old data?

Here is the table definition btw

CREATE TABLE `eplreflex`.`user_profiles` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `uid` VARCHAR(200) NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  `profile_image_url` VARCHAR(400) NOT NULL,
  `favourite_team_id` INT NULL,
  `provider` VARCHAR(100) NULL,
  `email` VARCHAR(100) NULL,
  `purchased_coins` double not null default 0,
  `free_coins` double not null default 0,
  `coins_won` double not null default 0,
  `coins_lost` double not null default 0,
  `admin` bool not null default 0,
  `insert_ts` timestamp default current_timestamp,
  `update_ts` timestamp default current_timestamp on update current_timestamp,
  `ads_enabled` bool not null default 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`uid`),
  INDEX (`favourite_team_id` ASC),
  INDEX (`coins_won` ASC),
  CONSTRAINT
    FOREIGN KEY (`favourite_team_id`)
    REFERENCES `eplreflex`.`teams` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT);

Update

So I do a count(*) from table and it returns 0.

I try truncating the table and it gives me

Error Code: 1701. Cannot truncate a table referenced in a foreign key constraint (eplreflex.leaderboard_users, CONSTRAINT leaderboard_users_ibfk_1 FOREIGN KEY (user_id) REFERENCES eplreflex.user_profiles (id))

I select count(*) from leaderboard_users and that also returns zero!!

Something is seriously messed up with Google Cloud Sql.

Answer 1

That is pretty strange. You're not supplying data for the PRIMARY key field (id), which is set sensible to autoincrement, so it shouldn't be possible to get a duplicate key error on that field.

The only possibility I can see is that the auto_increment counter for that field is somehow out of sync. This should also not be possible if you deleted and recreated the table.

You can try the following, to ensure that the counter is set to zero:

ALTER TABLE user_profiles AUTO_INCREMENT=0;

Also, if you want to be 100% sure that the table is empty, and the auto increment counter is reset, issue the following statement:

TRUNCATE TABLE user_profiles;

This will delete all of the data and reset/clear the indexes.

The final possibility to explore would be whether your code is accidentally trying to insert the row twice - however I don't think this is the problem, because that would give you a duplicate key error on the UID filed, not on the ID field.

Answer 2

I had this problem today with a local MySQL database, and it was a trigger which was copying data to another table on create. Removing the trigger avoided the error.

I also tried Simon's suggestions.

It didn't makes sense to me either.