[MDEV-5324] UPDATE and PRIMARY KEY Created: 2013-11-20  Updated: 2013-11-21  Resolved: 2013-11-21

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.33a-galera
Fix Version/s: None

Type: Bug Priority: Blocker
Reporter: Antoine Pous Assignee: Elena Stepanova
Resolution: Not a Bug Votes: 0
Labels: update
Environment:

Linux Debian Wheezy x64 - OVH dedicated server


 Description   

Create this table :

CREATE TABLE `user_session` (
`php_id` varchar(26) NOT NULL,
`session_id` varchar(32) NOT NULL DEFAULT '',
`session_data` text,
`session_time` int(11) NOT NULL,
PRIMARY KEY (`php_id`,`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Add any values and try to update them :

UPDATE user_session SET session_id = :session_id, session_data = :session_data, session_time = :session_time WHERE php_id = :php_id

i get always this error :

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'xxxx' for key 'PRIMARY'

This is illogical, i try to update an existing line, i should not have this error.

 Comments   
Comment by Elena Stepanova [ 2013-11-20 ]

Hi,

What do :session_id, etc signify? Where do you execute the statement from?

Could you please provide an example with real data, e.g. what you have in the table and what you are trying to update it to?

In general, there is nothing strange about this error. Lets say you have this data in the table:

MariaDB [test]> select * from user_session;
------------------------------------------+

php_id session_id session_data session_time

------------------------------------------+

a a a 2147483647
a b b 2147483647

------------------------------------------+
2 rows in set (0.00 sec)

Then the following update will indeed cause the error:

MariaDB [test]> update user_session set session_id = 'a' where php_id = 'a';
ERROR 1062 (23000): Duplicate entry 'a-a' for key 'PRIMARY'

This is because you are updating several (two) rows at once, trying to modify them the way that their primary key becomes identical.

But of course, it all depends on the data in the table and in the query.

Comment by Antoine Pous [ 2013-11-21 ]

Hi,

thank you for your answer, i'm going wrong, this is not a bug.

Sorry for the mistake :/

Generated at Thu Feb 08 07:03:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.