Advertisement:

Author Topic: Cannot change "custom smiley" setting with POSTGRESQL DB.  (Read 6139 times)

Offline Tyris

  • Sophist Member
  • *****
  • Posts: 1,306
  • Gender: Male
    • Shana Project
Cannot change "custom smiley" setting with POSTGRESQL DB.
« on: March 11, 2012, 08:06:50 AM »
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.

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

Code in question:
Code: [Select]
$smcFunc['db_add_column']('{db_prefix}smileys', array('name' => 'temp_order', 'size' => 8, 'type' => 'mediumint', 'null' => false));
Changing it to:
Code: [Select]
$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

Offline emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
Re: Cannot change "custom smiley" setting with POSTGRESQL DB.
« Reply #1 on: March 11, 2012, 08:56:34 AM »
Hello Tyris,

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

What version of postgre are you using?
Never mind, wrong question. ;)
« Last Edit: March 11, 2012, 09:04:47 AM by emanuele »


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 emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
Re: Cannot change "custom smiley" setting with POSTGRESQL DB.
« Reply #2 on: March 11, 2012, 09:52:07 AM »
I think this is a more general error in DbPackages-postgresql.php:
Code: [Select]
$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.
Code: [Select]
$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:
Code: [Select]
$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.
« Last Edit: March 11, 2012, 09:58:35 AM by emanuele »


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 Tyris

  • Sophist Member
  • *****
  • Posts: 1,306
  • Gender: Male
    • Shana Project
Re: Cannot change "custom smiley" setting with POSTGRESQL DB.
« Reply #3 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).
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?
« Last Edit: March 11, 2012, 10:17:08 AM by Tyris »

Offline emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
Re: Cannot change "custom smiley" setting with POSTGRESQL DB.
« Reply #4 on: March 11, 2012, 10:22:47 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...

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

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

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.

Offline Tyris

  • Sophist Member
  • *****
  • Posts: 1,306
  • Gender: Male
    • Shana Project
Re: Cannot change "custom smiley" setting with POSTGRESQL DB.
« Reply #5 on: March 11, 2012, 10:38:57 AM »
I my bad; I thought you were just quoting the code; not quoting the fix ^^;; (I should have looked closer!)

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

Offline emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
Re: Cannot change "custom smiley" setting with POSTGRESQL DB.
« Reply #6 on: March 11, 2012, 11:17:22 AM »
* 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.

Offline markf

  • Semi-Newbie
  • *
  • Posts: 20
Re: Cannot change "custom smiley" setting with POSTGRESQL DB.
« Reply #7 on: April 16, 2015, 12:29:39 PM »
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.

Code: [Select]
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

Code: [Select]
--- 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
+?>

Offline margarett

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,761
  • Gender: Male
Re: Cannot change "custom smiley" setting with POSTGRESQL DB.
« Reply #8 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 ;)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

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

Offline Illori

  • Project Manager
  • SMF Master
  • *
  • Posts: 48,077
Re: Cannot change "custom smiley" setting with POSTGRESQL DB.
« Reply #9 on: April 16, 2015, 06:01: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.

Offline margarett

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,761
  • Gender: Male
Re: Cannot change "custom smiley" setting with POSTGRESQL DB.
« Reply #10 on: April 16, 2015, 06:06:03 PM »
Yes, it is fixed in 2.1 ;)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

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