Page 1 of 1
Violated Constraint in nightlies
Posted: Mon May 07, 2007 4:34 pm
by Evil Azrael
Hi
I get the following error since the Nightly version of 2007-05-03. It happens when saving a new entry. Looks like it uses an generated id multiple times.
Code: Select all
Sitemap Update an www.google.com gemeldet.
Warning: pg_query() [function.pg-query]: Query failed: ERROR: duplicate key violates unique constraint "serendipity_references_pkey" in /www/blog.evilazrael.de/include/db/postgres.inc.php on line 210
Error in INSERT INTO serendipity_references (id,name,link,entry_id) values ('279', 'google', 'http://www.google.de', '27')
ERROR: duplicate key violates unique constraint "serendipity_references_pkey"
Re: Violated Constraint in nightlies
Posted: Tue May 08, 2007 9:37 am
by garvinhicking
Hi!
Can you log the full SQL that is being send to pgsql when an entry is saved?
The INSERT should only happen after a DELETE statement has been issued that removes the previously declared entry in serendipity_references.
Regards,
Garvin
Posted: Tue May 08, 2007 4:18 pm
by Evil Azrael
I put the full sql log of a "save new entry" session at
http://evilazrael.net/pg_error.log . I hope this helps you.
Just for curiosity, is it normal that the user information is retrieved multiple times?
Posted: Wed May 09, 2007 10:56 am
by garvinhicking
Hi!
First off - I don't have a running pgsql installation, so most of the things are only tested with mysql and assume to be also working on pgsql. So you might need to help me out with pgsql specifics.
Thanks for your logfile, it was quite helpful. User information is retrieved multiple times bevause of multiple requests - a click on "Save" results in 2 HTTP calls, the basic page itself and then also the iframe contents where actual saving is done.
Now, the problem seems to be this query:
DELETE FROM serendipity_references WHERE entry_id=29 AND type = ''
This one does NOT seem to delete the duplicate of a reference that is later inserted:
INSERT INTO serendipity_references (id, entry_id, name, link) VALUES(281, 29, 'Last.fm', '
http://www.lastfm.de')
The question is, why does the last SQL query report "duplicate column" even though all references with entry_id=29 should be deleted before that? Does pgsql maybe have a problem with "AND type = ''"? Do the rows get deleted if you execute that query manually with a pgsql frontend? What is the 'type' column set to of the lastfm-entry?
Regards,
Garvin
Posted: Wed May 09, 2007 10:59 am
by Evil Azrael
I will check that this evening, need to get to work. i can help you with pgsql and provide you an account on my homeserver if you want. -> ICQ
Posted: Wed May 09, 2007 8:14 pm
by Evil Azrael
The problem is rather easy. The value in the type column is NULL by default and it is not set, when a value is inserted which specifies no type. '' (empty string) and NULL are two distinct values. I think mysql does not distinguish these two values.
Replacing "type = ''" with "type IS NULL" works, i hope this does not break compatibility with mysql.
Posted: Thu May 10, 2007 12:07 pm
by garvinhicking
Hi!
Seems to work well on MySQL.
I just committed this patch:
http://svn.berlios.de/viewcvs/serendipi ... 71&r2=1689
Regards,
Garvin