Advertisement:

Author Topic: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4  (Read 44255 times)

Offline markf

  • Semi-Newbie
  • *
  • Posts: 20
Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« on: October 01, 2013, 07:59:01 AM »
Hi, I reported this on PortaMX forums.

They've suggested raising it here.

At the core of it, it looks like
Code: [Select]
$smcFunc['db_insert']('replace', ... isn't working correctly. Instead of doing the replace, it's doing an insert and throwing Unique Key Constraint violations.

Anything I can do to fix this?

Cheers,
Mark

- - - - As reported to PortaMx

I'm getting a lot of errors on a pretty default install of portaMx.
My setup is SMF 2.0.5 and PortaMx 1.51-ecl.
For backend i'm using Postgresql 9.2.4 on FreeBSD.

I have a completely uncustomized SMF, just a couple of themes added, however I'm getting a lot of duplicate key errors trying to go to the various PortaMx admin tabs.

The main one is when i go to the portaMx tab:

Code: [Select]
ERROR: duplicate key value violates unique constraint "smf_portamx_settings_uidx"
DETAIL: Key (varname)=(liveinfo) already exists.
File: /usr/local/www/smf/Sources/PortaMx/AdminCenter.php
Line: 764

I found i can temporarily get around it by deleting the liveinfo row from the database, but after a couple of clicks around the admin pages, i can't go back without re-deleting the row.

I'm a java developer with some php experience, had a look at the source where the problem is, but i can't work out why the replace method of db_insert isn't actually doing either a delete/insert or an update but instead it's clearly trying to do an straight insert, and hence the duplicate key error.

I'm also seeing the same problem trying to set visibility of blocks on the site.
At first it just looked like the UI wasn't accepting my "update" or "update all" clicks in the blocks UI config, but if i get into the main configuration for a block (e.g. 'statistics' by clicking "click to edit this block") and try and update the user groups that are allowed to view the blocks, it comes back with a similar error on unique constraints, this time:

Code: [Select]
ERROR: duplicate key value violates unique constraint "smf_portamx_blocks_pkey"
DETAIL: Key (id)=(3) already exists.
File: /usr/local/www/smf/Sources/PortaMx/AdminBlocks.php
Line: 493

Are there any known issues with running on Postgresql 9.2, as I suspect there are d/b issues at work (similar to the main SMF install erroring because of the quoting change to \' vs '' in pgsql's change in 9+).
« Last Edit: October 01, 2013, 04:11:33 PM by markf »

Offline ziycon

  • Support Specialist
  • SMF Hero
  • *
  • Posts: 2,650
  • Gender: Male
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #1 on: October 01, 2013, 09:03:40 AM »
Off the top of my head a replace consists of two parts, a delete and insert so you would be right in saying that it looks to be doing an insert. If the replace isn't removing the record your looking to replace, you will of course get a duplicate key violation when it goes to insert the replacement record.
Is there any locks on the table your doing the replace on?

Offline markf

  • Semi-Newbie
  • *
  • Posts: 20
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #2 on: October 01, 2013, 10:10:51 AM »
No, i can see no locks on the database:

Code: [Select]
blocked_pid | blocked_user | blocking_pid | blocking_user | blocked_statement
-------------+--------------+--------------+---------------+-------------------
(0 rows)


Offline emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #3 on: October 01, 2013, 10:57:04 AM »
Probably because the query is wrong: the last parameter used should not be the name of the variable inserted, but the name of the index, so in that case I think: 'uidx'.

So, if you replace
Code: [Select]
array('liveinfo')with:
Code: [Select]
array('uidx')it should work.

On a side note:
https://github.com/SimpleMachines/SMF2.1/blob/release-2.1/Sources/Subs-Db-postgresql.php#L697
what the code is doing is exactly: delete and insert new.


Take a peek at what I'm doing! ;D



Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

Offline markf

  • Semi-Newbie
  • *
  • Posts: 20
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #4 on: October 01, 2013, 11:59:44 AM »
Probably because the query is wrong [nofollow]: the last parameter used should not be the name of the variable inserted, but the name of the index, so in that case I think: 'uidx' [nofollow].

So, if you replace
Code: [Select]
array('liveinfo')with:
Code: [Select]
array('uidx')it should work.

On a side note:
https://github.com/SimpleMachines/SMF2.1/blob/release-2.1/Sources/Subs-Db-postgresql.php#L697 [nofollow]
what the code is doing is exactly: delete and insert new.

Thanks for the links, i've just been searching for what SMF is doing.
I'm not sure, however, your analysis is correct.

The replace is:

Code: [Select]
$smcFunc['db_insert']('replace', '{db_prefix}portamx_settings',
array(
'varname' => 'string',
'config' => 'string'
),
array(
'liveinfo',
serialize($info),
),
array('liveinfo')
);

The function signature is:
Code: [Select]
function smf_db_insert($method = 'replace', $table, $columns, $data, $keys, $disable_trans = false, $connection = null)
So the table is '{db_prefix}portamx_settings', columns are "varname, config", data is "liveinfo, <serialized liveinfo>", and the keys are "liveinfo".

The uidx is the constraint name for the primary key that's being violated, i.e. that there's already a row with liveinfo as the key.

I'm going to look at this deeper tonight. I put debug on my instance and tried to click the part that's breaking and only saw an INSERT happening, no delete. Also, the mod is pretty established, so I think that kind of error would have been picked up pretty quickly.

My current theory is that the delete isn't happening at lines 717-728 and i'll add debug to find out why later.

Offline emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #5 on: October 01, 2013, 12:06:15 PM »
Code: [Select]
// A key? That's what we were looking for.
if (in_array($columnName, $keys))
So the name of the column, okay, not the name of the index, but for sure not the name of the variable you are inserting.


Take a peek at what I'm doing! ;D



Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

Offline markf

  • Semi-Newbie
  • *
  • Posts: 20
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #6 on: October 01, 2013, 04:03:05 PM »
well spotted. changing to varname fixes the issue, i've posted back to my original bug report at http://portamx.com/3379/postgresql-duplicate-key-errors/ [nofollow]

At least i should be able to fix the other issue now too.

Offline emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #7 on: October 01, 2013, 04:14:45 PM »
Good, thanks for reporting back! ;D


Take a peek at what I'm doing! ;D



Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

Offline feline

  • SMF Hero
  • ******
  • Posts: 1,617
  • Gender: Female
    • Product developer site
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #8 on: October 02, 2013, 05:29:38 AM »
We have a lot of insert - replace queries ...
So the solution is that the third array must hold the col name they have unique or primary index?
Correct ? ???

in this case these exist query must work:
Code: [Select]
$smcFunc['db_insert']('replace', '
{db_prefix}portamx_settings',
array(
'varname' => 'string',
'config' => 'string'
),
array(
'liveinfo',
serialize($info),
),
array('liveinfo')
);

Table structure:
Code: [Select]
'portamx_settings' => array(
// column defs
array(
array('name' => 'varname', 'type' => 'varchar', 'size' => '80', 'default' => '', 'null' => false),
array('name' => 'config', 'type' => 'text', 'null' => false),
),
// index defs
array(
array('type' => 'unique', 'name' => 'uidx', 'columns' => array('varname')),
),
// options
array()
),

Any remarks?
« Last Edit: October 02, 2013, 05:47:09 AM by feline »
PortaMx has released the official release of PortaMx-Forum 1.2 with a built in SEF engine, a integrated Portal and full support for the EU Cookie Law.
Follow us on our Community


Offline markf

  • Semi-Newbie
  • *
  • Posts: 20
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #9 on: October 02, 2013, 05:55:54 AM »
Looking at

http://wiki.simplemachines.org/smf/Db_insert
https://github.com/SimpleMachines/SMF2.1/blob/release-2.1/Sources/Subs-Db-postgresql.php#L697 [nofollow]

the 3rd array are the primary key columns in the table, so that any columns you use in array 1 are matched for the delete's "WHERE" clause (lines 711/712 and 721 to 724).

Although I'll let the SMF guys confirm my reading of it.

It looks like you have it correct in https://github.com/PortaMx/PortaMx-1.51-ecl/blob/master/Sources/PortaMx/AdminCenter.php#L395 [nofollow] as an example.

Offline feline

  • SMF Hero
  • ******
  • Posts: 1,617
  • Gender: Female
    • Product developer site
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #10 on: October 02, 2013, 08:40:37 AM »
The Insert "replace" routine for Postgres is definitive buggy (my meanings)...
I'v tested this and see that the delete row function can't work ..

The created delete query looks like this:
delete from tablename where id = {int:0}

The data for the insert and delete query looks like this:
$data[0] => [id]=26 => [more]=xx
so the smfFunc('delete') can't find the id in the data array.
 
The correct query for delete must look as follow:
delete from tablename where id = {int:id}
Now the row is correct deleted and the insert works.

So I think the "insert-replace" for postgres must be coded as follow:
Code: [Select]
// PostgreSQL doesn't support replace: we implement a MySQL-compatible behavior instead
if ($method == 'replace')
{
$count = 0;
$where = '';
foreach ($columns as $columnName => $type)
{
// Are we restricting the length?
if (strpos($type, 'string-') !== false)
$actualType = sprintf($columnName . ' = SUBSTRING({string:%1$s}, 1, ' . substr($type, 7) . '), ', $columnName);
else
$actualType = sprintf($columnName . ' = {%1$s:%2$s}, ', $type, $columnName);

// A key? That's what we were looking for.
if (in_array($columnName, $keys))
$where .= (empty($where) ? '' : ' AND ') . substr($actualType, 0, -2);
$count++;
}

// Make it so.
if (!empty($where) && !empty($data))
{
foreach ($data as $k => $entry)
{
$smcFunc['db_query']('', '
DELETE FROM ' . $table .
' WHERE ' . $where,
$entry, $connection
);
}
}
}

Any comment on this?
PortaMx has released the official release of PortaMx-Forum 1.2 with a built in SEF engine, a integrated Portal and full support for the EU Cookie Law.
Follow us on our Community


Offline emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #11 on: October 02, 2013, 01:42:41 PM »
We have a lot of insert - replace queries ...
So the solution is that the third array must hold the col name they have unique or primary index?
Correct ? ???
And in your query the **column** name is liveinfo? (Or maybe varname?)


Take a peek at what I'm doing! ;D



Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

Offline feline

  • SMF Hero
  • ******
  • Posts: 1,617
  • Gender: Female
    • Product developer site
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #12 on: October 02, 2013, 01:46:25 PM »
In the posted example the 3nd array have the colname ( 'id' ) while that is the indexed col...
PortaMx has released the official release of PortaMx-Forum 1.2 with a built in SEF engine, a integrated Portal and full support for the EU Cookie Law.
Follow us on our Community


Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 67,714
    • Arantor on GitHub
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #13 on: October 02, 2013, 01:50:28 PM »
So you're passing a 'key' of liveinfo into the insert function but don't apparently have a column or a key called that and wonder why it doesn't work? Just trying to make sense of this.
To assume is to hope that those who came before had the presence of mind and capacity to implement the dreams of those who would come after.

You either die a hero or live long enough to see yourself become the villain. It seems you have chosen which, and now I must do the same.

Offline emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #14 on: October 02, 2013, 03:57:19 PM »
Table structure:
Code: [Select]
'portamx_settings' => array(
// column defs
array(
array('name' => 'varname', 'type' => 'varchar', 'size' => '80', 'default' => '', 'null' => false),
array('name' => 'config', 'type' => 'text', 'null' => false),
),
// index defs
array(
array('type' => 'unique', 'name' => 'uidx', 'columns' => array('varname')),
),
// options
array()
),
So if I'm not wrong the table should look like:
Code: [Select]
_________________________
|  varname   |   config  |
_________________________
| something1 |   value   |
| something2 |   value   |
| something3 |   value   |
| something4 |   value   |
| something5 |   value   |
| something6 |   value   |
| liveinfo   | serial($) |
_________________________

Am I wrong?
No?
Then, the column name is liveinfo or varname?

If the column name is varname, then your query should be:
Code: [Select]
$smcFunc['db_insert']('replace', '{db_prefix}portamx_settings',
array(
'varname' => 'string',
'config' => 'string'
),
array(
'liveinfo',
serialize($info),
),
array('varname')
);
(of course the documentation is wrong).

We have a lot of insert - replace queries ...
There are 55 insert replace in SMF alone, I'm pretty sure that if the code was *so* buggy (in that specific point) it wouldn't even install on postgre. ;)


Take a peek at what I'm doing! ;D



Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

Offline markf

  • Semi-Newbie
  • *
  • Posts: 20
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #15 on: October 02, 2013, 04:18:44 PM »
the problem isn't actually with the varname/liveinfo example, that's a string and works if you change to varname as i've seen on my site.

however, in feline's example, the id value (an int) doesn't work if you pass it in as "array('id')", there's a error saying "the value 0 is not in the database", so feline's example with the id column needs to also be looked at, but i haven't had time to further this tonight.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 67,714
    • Arantor on GitHub
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #16 on: October 02, 2013, 04:19:57 PM »
I'm still thinking that it's because it's being used wrong...

Does it occur anywhere with a base SMF install? Or is it only inside the PortaMX files?
To assume is to hope that those who came before had the presence of mind and capacity to implement the dreams of those who would come after.

You either die a hero or live long enough to see yourself become the villain. It seems you have chosen which, and now I must do the same.

Offline emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #17 on: October 02, 2013, 05:27:23 PM »
Are you able to move topics from one board to another?
https://github.com/SimpleMachines/SMF2.1/blob/release-2.1/Sources/MoveTopic.php#L479

Are the boards marked as read properly?
Are you able to collapse and uncollapse categories?
Or even more simple: are you able to save *any* setting stored in the settings table?
https://github.com/SimpleMachines/SMF2.1/blob/release-2.1/Sources/Subs.php#L469

If so, the db-layer works.


Take a peek at what I'm doing! ;D



Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

Offline markf

  • Semi-Newbie
  • *
  • Posts: 20
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #18 on: October 02, 2013, 05:48:29 PM »
i think i've bought an elephant to the party by installing with postgresql.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 67,714
    • Arantor on GitHub
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #19 on: October 02, 2013, 05:54:40 PM »
Partly, but SMF itself seems to work fine - sounds like the problem is PortaMX...
To assume is to hope that those who came before had the presence of mind and capacity to implement the dreams of those who would come after.

You either die a hero or live long enough to see yourself become the villain. It seems you have chosen which, and now I must do the same.