Violated Constraint in nightlies

[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"

[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

[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?

[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

[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

[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.

[garvinhicking]

Hi!

Seems to work well on MySQL. I just committed this patch:

http://svn.berlios.de/viewcvs/serendipi ... 71&r2=1689

Regards,
Garvin