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
Hello Tyris,
thank you for the report! (and the possible fix)
What version of postgre are you using?
Never mind, wrong question. ;)
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.
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?
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:
$setTo = isset($column_info['default']) ? $column_info['default'] : ''; $setTo = isset($column_info['default']) ? $column_info['default'] : (strpos($old_info['type'], 'int') !== false ? 0 : '');and:
$smcFunc['db_query']('', '
UPDATE ' . $table_name . '
SET ' . $column_info['name'] . ' = \'' . $setTo . '\'
WHERE ' . $column_info['name'] . ' = NULL',
array(
'security_override' => true,
)
); $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...
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
/me doesn't know much about openID...Norv is the master. :P
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
+?>
Thank you. It should have been fixed, yes. But it seems it was not...
It is now making its way into the new release ;)
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.
Yes, it is fixed in 2.1 ;)