Postgresql error - 'db_insert' function as 'Replace'. (SMF2)

[Nathaniel]

Problem:
SMF 2 only.
The $smcFunc['db_insert'] function has a bug when it tries to handle the 'replace' way of inserting data. It sets the array of variables for the UPDATE query with incorrect ids when compared with the array of data that was passed to the function.

Error message returned: "The database value you're trying to insert does not exist: 0"

Running postgre 8.3.8.

Location:
File: Subs-Db-postgresql.php
Function: smf_db_insert
Line: 582

Fix:
Replace this code:

Code Select Expand

$count = 0;
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) . '), ', $count);
else
$actualType = sprintf($columnName . ' = {%1$s:%2$s}, ', $type, $count);

// Has it got a key?
if (in_array($columnName, $keys))
$where .= (empty($where) ? '' : ' AND ') . substr($actualType,0, -2);
else
$updateData .= $actualType;

$count++;
}

With this code:

Code Select Expand

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

// Has it got a key?
if (in_array($columnName, $keys))
$where .= (empty($where) ? '' : ' AND ') . substr($actualType,0, -2);
else
$updateData .= $actualType;
}

The fix above is based on the code used by the Mysql equivilent function for creating a similar array in the Subs-Db-mysql.php file on line 617.

[Something like that]

http://dev.simplemachines.org/mantis/view.php?id=3821


Thanks for pointing out these errors

They ought to make you a Beta Tester.

[Nathaniel]

Thank you for putting it on the bugtracker.

I found these errors while trying to debug the SP installer for SMF2 when using postgresql, it turns out that the only issues were these two bugs, which makes things boring. Got to check SqlLite sometime as well.

I would do beta testing, but I have no time at the moment, got final exams in less than a month.

[Oldiesmann]

Thanks for the report. Will go ahead and fix this one as well

[Oldiesmann]

! SMF's workaround for REPLACE and INSERT IGNORE queries in PostgreSQL caused a database error (Subs-Db-postgresql.php) Revision 9156

[Nathaniel]

Unfortunately its not quite that painless, that fix introduced another issue, which breaks quite a lot of calls to the 'smf_db_insert' function. (like the entire updateSettings function :S)

The change I posted above, forces that function to only accept data that uses the specfic column names (whereas it previously forced you not to pass any). For consistancy with the relevant Mysql function, it should allow both. The fix below should fix that (as well as the fix above).

Find:

Code Select Expand

// Try and update the entries.
if (!empty($updateData))
foreach ($data as $k => $entry)
{
$smcFunc['db_query']('', '
UPDATE ' . $table . '
SET ' . $updateData . '
' . (empty($where) ? '' : ' WHERE ' . $where),
$entry, $connection
);

// Make a note that the replace actually overwrote.
if (smf_db_affected_rows() != 0)
{
unset($data[$k]);
$db_replace_result = 2;
}
}

Replace:

Code Select Expand

// Create an array consisting of only the columns.
$indexed_columns = array_keys($columns);

// Try and update the entries.
if (!empty($updateData))
foreach ($data as $k => $entry)
{
$smcFunc['db_query']('', '
UPDATE ' . $table . '
SET ' . $updateData . '
' . (empty($where) ? '' : ' WHERE ' . $where),
array_combine($indexed_columns, $entry), $connection
);

// Make a note that the replace actually overwrote.
if (smf_db_affected_rows() != 0)
{
unset($data[$k]);
$db_replace_result = 2;
}
}

[Norv]

Can you please tell where exactly can be noticed the behavior reported in the original post?