Topic: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4

[markf]

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+).

[ziycon]

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?

[markf]

No, i can see no locks on the database:

Code: [Select]

blocked_pid | blocked_user | blocking_pid | blocking_user | blocked_statement
-------------+--------------+--------------+---------------+-------------------
(0 rows)


[emanuele]

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.

[markf]

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.

[emanuele]

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.

[markf]

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.

[emanuele]

Good, thanks for reporting back!

[feline]

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?

[markf]

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.

[feline]

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?

[emanuele]

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

[feline]

In the posted example the 3nd array have the colname ( 'id' ) while that is the indexed col...

[Arantor]

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.

[emanuele]

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.

[markf]

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.

[Arantor]

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?

[emanuele]

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.

[markf]

i think i've bought an elephant to the party by installing with postgresql.

[Arantor]

Partly, but SMF itself seems to work fine - sounds like the problem is PortaMX...