Advertisement:

Author Topic: Unique Key Constraint Errors SMF 2.0.5, pgsql 9.2.4  (Read 44263 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: 67,721
    • Arantor 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.
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 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 #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 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 #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,617
  • 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 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 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 #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 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 #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,617
  • 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 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 #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!