• Welcome to Simple Machines Community Forum. Please login or sign up.
September 18, 2021, 01:16:42 PM

News:

Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord


Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4

Started by markf, October 01, 2013, 07:59:01 AM

Previous topic - Next topic

markf

hopefully just a misreading of the API for pgsql, that we can work out and improve both sides by fixing.

Arantor

Here's the thing... what SMF does works for SMF. If it didn't work, we'd fix it. But right now I haven't seen anything to indicate that SMF *itself* is misbehaving.
No good deed goes unpunished
All helpful urges should be circumvented

feline

October 03, 2013, 05:27:59 AM #22 Last Edit: October 03, 2013, 05:38:24 AM by feline
I tested that in a debugger ...
here the query we tested:

$smcFunc['db_insert']('replace', '
{db_prefix}portamx_blocks',
array(
'id' => 'int',
'side' => 'string',
'pos' => 'int',
'active' => 'int',
'cache' => 'int',
'blocktype' => 'string',
'acsgrp' => 'string',
'config' => 'string',
'content' => 'string',
),
$block,
array('id')
);

the indexed col is id.
I'v set a breakpoint just before the delete query starts.
In the attached images you can see the data in the variables.
Image1 is the original code (not work)
Image2 is the modificated code they I post (works)

and this code we use:
// PostgreSQL doesn't support replace: we implement a MySQL-compatible behavior instead
if ($method == 'replace')
{
$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);
}

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


Please try it self ...

emanuele

Not technically an SMF bug: it is intended.
The array of data is not supposed to be an associative array, but an array with numerical indexes.

So, for example in AdminCategories.php, the $category array should be:
// get all data
$category = array(
$_POST['id'],
PortaMx_makeSafe($_POST['name']),
$_POST['parent'],
$_POST['level'],
$_POST['catorder'],
(!empty($_POST['acsgrp']) ? implode(',', $_POST['acsgrp']) : ''),
(!empty($_POST['artsort']) ? implode(',', $_POST['artsort']) : ''),
pmx_serialize($_POST['config']),
);


Still in PortaMx most of the 'replace' are broken (in Postgre) because they pass the wrong argument. ;)

Then I tried to install PortaMx (1.51 from github) but all the queries updating settings miss the index so they fail:
https://github.com/PortaMx/PortaMx-1.51-ecl/blob/master/install/dbinstall.php#L333
https://github.com/PortaMx/PortaMx-1.51-ecl/blob/master/install/dbinstall.php#L452
https://github.com/PortaMx/PortaMx-1.51-ecl/blob/master/install/dbinstall.php#L593
https://github.com/PortaMx/PortaMx-1.51-ecl/blob/master/install/dbinstall.php#L631
https://github.com/PortaMx/PortaMx-1.51-ecl/blob/master/install/dbinstall.php#L641

to fix, replace the last array with:
array('varname')
or
array('variable')

Though, out of curiosity: why are you adding hooks with an insert and not through add_integration_function and why are you updating the SMF settings table with an insert and not with updateSettings?


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.

feline

Well .. I found the problem ..
the SMF insert_replace data array MUST have a numeric index (see Image22).
Because we use a data array with a colname index (see Image11), the Postgres delete fails.

That a numeric index is a MUST HAVE is not documented on SMF ..

AND .. a correct programmed function can handle both (my meanings) .. but I'm not a SMF developer  :laugh:


feline

We just have updated the GIT repository for PortaMx 1.51 ecl (https://github.com/PortaMx/PortaMx-1.51-ecl)
Notice:
update all db insert-replace because SMF don't support string indexes in the data array (pew)

emanuele



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.

markf

Thanks Feline, I'll update it later on. I trust this is just a code change so no d/b changes?

feline


markf

Great work Feline, the site's working with all the updates, no more d/b errors.

Thanks!

Advertisement: