Caused by: java.sql.SQLException: Duplicate entry '74' for key 2

[conquest drfence]

I have two databases, a production and a test. When I try and insert rows that contain the same song_id and sortie_id I get a sql exception stating that I have a duplicate entry for the sortie_id column. However when I do this in my test db instance it works as I expect.

For example this insert would work:

insert into song_stats (song_id, sortie_id, total_plays) values (471620, 51, 389);

This one would fail:

insert into song_stats (song_id, sortie_id, total_plays) values (571620, 51, 400);

Below are the show create schema lines for both dbs and they look identical. Is the command line not showing me some constraints that are set on this production db? From what I understand the KEY keyword is only telling mysql to use an index for that column and there is not actually a constraint.

Here are actual inserted rows via the test instance:

select * from song_stats;
+----+-----------+---------+-------------+---------------------+
| id | sortie_id | song_id | total_plays | last_update |
+----+-----------+---------+-------------+---------------------+
| 7 | 8 | 7 | 56292 | 2008-12-21 17:26:16 |
| 8 | 8 | 8 | 26798 | 2008-12-21 17:26:16 |
| 9 | 9 | 7 | 56292 | 2008-12-21 17:27:08 |
| 10 | 9 | 8 | 26798 | 2008-12-21 17:27:08 |
| 11 | 10 | 7 | 56292 | 2008-12-21 22:35:46 |
| 12 | 10 | 8 | 26798 | 2008-12-21 22:35:46 |
| 13 | 10 | 7 | 100 | 2008-12-21 22:52:36 |
+----+-----------+---------+-------------+---------------------+

I'm running mysql 5.0.45 on CentOS.


production db

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| song_stats | CREATE TABLE `song_stats` (
`id` int(11) NOT NULL auto_increment,
`sortie_id` int(11) NOT NULL,
`song_id` int(11) NOT NULL,
`total_plays` int(9) NOT NULL,
`last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `song_id` (`song_id`),
KEY `sortie_id` (`sortie_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 |
+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> quit



test db

mysql> show create table song_stats;
+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| song_stats | CREATE TABLE `song_stats` (
`id` int(11) NOT NULL auto_increment,
`sortie_id` int(11) NOT NULL,
`song_id` int(11) NOT NULL,
`total_plays` int(9) NOT NULL,
`last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `song_id` (`song_id`),
KEY `sortie_id` (`sortie_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 |
+--------------------+--------------------------------------



Edited 1 time(s). Last edit at 12/22/2008 11:23AM by conquest drfence.