Advertisement:

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

Offline markf

  • Semi-Newbie
  • *
  • Posts: 20
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #20 on: October 02, 2013, 06:05:50 PM »
hopefully just a misreading of the API for pgsql, that we can work out and improve both sides by fixing.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 69,123
    • StoryBB/StoryBB on GitHub
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #21 on: October 02, 2013, 06:09:07 PM »
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.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

Nothing turns to hate so bitter as what once was love.

Offline feline

  • SMF Hero
  • ******
  • Posts: 1,636
  • Gender: Female
    • Product developer site
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #22 on: October 03, 2013, 05:27:59 AM »
I tested that in a debugger ...
here the query we tested:
Code: [Select]
$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:
Code: [Select]
// 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 ...
« Last Edit: October 03, 2013, 05:38:24 AM by feline »
PortaMx has released the version 1.41 of PortaMx-Forum with a built in SEF engine, a integrated Portal and full compatibility with the EU GDPR.
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 #23 on: October 03, 2013, 08:20:47 AM »
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:
Code: [Select]
// 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:
Code: [Select]
array('varname')or
Code: [Select]
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.

Offline feline

  • SMF Hero
  • ******
  • Posts: 1,636
  • Gender: Female
    • Product developer site
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #24 on: October 03, 2013, 09:36:31 AM »
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:

PortaMx has released the version 1.41 of PortaMx-Forum with a built in SEF engine, a integrated Portal and full compatibility with the EU GDPR.
Follow us on our Community


Offline feline

  • SMF Hero
  • ******
  • Posts: 1,636
  • Gender: Female
    • Product developer site
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #25 on: October 03, 2013, 10:29:20 AM »
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)
PortaMx has released the version 1.41 of PortaMx-Forum with a built in SEF engine, a integrated Portal and full compatibility with the EU GDPR.
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 #26 on: October 03, 2013, 10:36:52 AM »
The install is still broken. :P


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 #27 on: October 03, 2013, 10:47:04 AM »
Thanks Feline, I'll update it later on. I trust this is just a code change so no d/b changes?

Offline feline

  • SMF Hero
  • ******
  • Posts: 1,636
  • Gender: Female
    • Product developer site
Re: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4
« Reply #28 on: October 03, 2013, 11:10:22 AM »
only changes on the PortaMx code...
PortaMx has released the version 1.41 of PortaMx-Forum with a built in SEF engine, a integrated Portal and full compatibility with the EU GDPR.
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 #29 on: October 03, 2013, 03:19:34 PM »
Great work Feline, the site's working with all the updates, no more d/b errors.

Thanks!