Cannot change "custom smiley" setting with POSTGRESQL DB.

Started by Tyris, March 11, 2012, 08:06:50 AM

Previous topic - Next topic

Tyris

From the page index.php?action=admin;area=smileys;sa=settings
If Enable customized smileys is selected when you click save then you get the following error.

ERROR: invalid input syntax for integer: ""
File: /home/wwwa/webapps/smf/Sources/ManageSmileys.php
Line: 1753


Code in question:
$smcFunc['db_add_column']('{db_prefix}smileys', array('name' => 'temp_order', 'size' => 8, 'type' => 'mediumint', 'null' => false));

Changing it to:
$smcFunc['db_add_column']('{db_prefix}smileys', array('name' => 'temp_order', 'size' => 8, 'type' => 'mediumint', 'null' => true));
Resolves the issue.

It seems that the temp column that is getting created is getting upset because the rows do not have a value for that column, so it tries to use default of "", which is unacceptable (as is null).

Cheers,
Tyris

emanuele

Hello Tyris,

thank you for the report! (and the possible fix)

What version of postgre are you using?
Never mind, wrong question. ;)


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.

emanuele

I think this is a more general error in DbPackages-postgresql.php:
$setTo = isset($column_info['default']) ? $column_info['default'] : '';
So, if default is not set it sets the default to '' that is not valid for integer.
$setTo = isset($column_info['default']) ? $column_info['default'] : (strpos($old_info['type'], 'int') !== false ? 0 : '');

Additionally the query that follow is wrong because "= NULL" doesn't work, it must be:

$smcFunc['db_query']('', '
UPDATE ' . $table_name . '
SET ' . $column_info['name'] . ' = \'' . $setTo . '\'
WHERE ' . $column_info['name'] . ' IS NULL',
array(
'security_override' => true,
)
);


Fix commited, moving to fixed bugs.


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.

Tyris

Aaa,
I had a look in DbPackages-postgresql.php but couldn't find the ['default'] bit.. which I thought odd since I *tried* passing 'default' => 0 to the call and it worked but then threw a separate error (to do with unexpected null values - so no doubt related to the next query being broken).
Are you able to provide a link to the changeset (are they public?) or paste the details of it so I can apply manually? (Not that my fix didn't work.. but you know :p )

Glad to see SMF devs are still so quick to jump on problems though (its been 6 years since I last posted here... haha)

Would I be correct in guessing not a lot of people use POSTGRESQL for SMF?

emanuele

Quote from: Tyris on March 11, 2012, 10:10:31 AM
Aaa,
I had a look in DbPackages-postgresql.php but couldn't find the ['default'] bit.. which I thought odd since I *tried* passing 'default' => 0 to the call and it worked but then threw a separate error (to do with unexpected null values - so no doubt related to the next query being broken).
Strange, passing a default 0 should work...no idea...

Quote from: Tyris on March 11, 2012, 10:10:31 AM
Are you able to provide a link to the changeset (are they public?) or paste the details of it so I can apply manually? (Not that my fix didn't work.. but you know :p )
The changes I commit are really only those I posted in the previous message, in the file DbPackages-postgresql.php:
Code (find) Select
$setTo = isset($column_info['default']) ? $column_info['default'] : '';
Code (replace with) Select
$setTo = isset($column_info['default']) ? $column_info['default'] : (strpos($old_info['type'], 'int') !== false ? 0 : '');

and:
Code (find) Select
$smcFunc['db_query']('', '
UPDATE ' . $table_name . '
SET ' . $column_info['name'] . ' = \'' . $setTo . '\'
WHERE ' . $column_info['name'] . ' = NULL',
array(
'security_override' => true,
)
);

Code (replace with) Select
$smcFunc['db_query']('', '
UPDATE ' . $table_name . '
SET ' . $column_info['name'] . ' = \'' . $setTo . '\'
WHERE ' . $column_info['name'] . ' IS NULL',
array(
'security_override' => true,
)
);

Nothing else! ;D

Quote from: Tyris on March 11, 2012, 10:10:31 AM
Glad to see SMF are still so quick to jump on problems though (its been 6 years since I last posted here... haha)
Well...that means everything worked as expected! :D (or you didn't use SMF for 6 years... :() :P

Quote from: Tyris on March 11, 2012, 10:10:31 AM
Would I be correct in guessing not a lot of people use POSTGRESQL for SMF?
ehm...yes, you are correct...


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.

Tyris

I my bad; I thought you were just quoting the code; not quoting the fix ^^;; (I should have looked closer!)

Quote from: emanuele on March 11, 2012, 10:22:47 AM
Well...that means everything worked as expected! :D (or you didn't use SMF for 6 years... :() :P
I haven't really needed to deploy/manage a forum for quite a while actually... but there's really only one choice when I needed to again ;)
I actually looked at a few django solutions (coz the site I'll be integrating is python/django), but there is nothing even remotely mature enough; and turns out I can fairly easily integrate the authentication system. Shame the openID support for SMF is so lacking tho :p

emanuele

* emanuele doesn't know much about openID...Norv is the master. :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.

markf

Did this fix ever make it into a release?

I've done a clean 2.0.9 install, and the error is still there trying to set the "Enable customized smileys" option.

ERROR: invalid input syntax for integer: ""
LINE 3: SET temp_order = ''
^
File: /usr/local/www/smf/Sources/ManageSmileys.php
Line: 1754


and can see in DbPackages-postgresql.php it still has the old code without the fixes you mention in this thread.

I can confirm the fixes still work, here's a full diff


--- Sources/DbPackages-postgresql.php.001 2015-04-16 17:24:33.110751130 +0100
+++ Sources/DbPackages-postgresql.php 2015-04-16 17:28:32.298751092 +0100
@@ -368,11 +368,11 @@
if (!$column_info['null'])
{
// We have to set it to something if we are making it NOT NULL.
- $setTo = isset($column_info['default']) ? $column_info['default'] : '';
+ $setTo = isset($column_info['default']) ? $column_info['default'] : (strpos($old_info['type'], 'int') !== false ? 0 : '');
$smcFunc['db_query']('', '
UPDATE ' . $table_name . '
SET ' . $column_info['name'] . ' = \'' . $setTo . '\'
- WHERE ' . $column_info['name'] . ' = NULL',
+ WHERE ' . $column_info['name'] . ' IS NULL',
array(
'security_override' => true,
)
@@ -743,4 +743,4 @@
return $indexes;
}

-?>
\ No newline at end of file
+?>


margarett

Thank you. It should have been fixed, yes. But it seems it was not...

It is now making its way into the new release ;)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Illori

Quote from: margarett on April 16, 2015, 05:54:01 PM
Thank you. It should have been fixed, yes. But it seems it was not...

It is now making its way into the new release ;)

i think it was fixed for 2.1, and not considered for 2.0 at the time.

margarett

Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Advertisement: