function sortSmileyTable() incompatible with postgresql

[habakuk]

Using smf 2.0rc5
MacOSX, Apache 2.x, PHP 5.3.x
PostgreSQL 8.3.x

Whenever I tried to add a new smiley set and wanted to check the "Enable customized smileys" checkbox in the "Administration Center > Smileys and Message Icons > Settings", I got back a DB error complaining about a wring type cast - expected an integer and got a string.

I spent a good deal of time trying to find that bugger... :-) And here it is:

The function sortSmileyTable() has this part:

Code Select Expand

$smcFunc['db_query']('alter_table_smileys', '
ALTER TABLE {db_prefix}smileys
ORDER BY temp_order DESC',
array(
'db_error_skip' => true,
)
);

However, postgreSQL doesn't accept ALTER and ORDER in the same call. There is no way to permanently order a table.

Once I got rid of that part, I was able to enable the customized smileys option.

Since this part of the SMF code isn't working at all, I consider this being a bug.

I'm happy to provide more details if required.

cheers
®

[Oldiesmann]

What specific error are you getting? The "alter_table_smileys" part at the beginning of that statement tells SMF to basically ignore that query when we're using PostgreSQL.

Code Select Expand

'alter_table_smileys' => array(
'~(.+)~' => '',
),

[habakuk]

Code Select Expand

ERROR: current transaction is aborted, commands ignored until end of transaction block
File: /Library/Apache2/htdocs/blahblah/forum/Sources/Subs.php
Line: 2994


And the postgres log says:

Code Select Expand

ERROR:  [b]invalid input syntax for integer: ""[/b]
STATEMENT: 
[b] UPDATE tgeedu_smileys
SET temp_order = ''
WHERE temp_order = NULL
ERROR:  current transaction is aborted, commands ignored until end of transaction block
[/b]STATEMENT: 
INSERT INTO tgeedu_log_errors("id_member", "log_time", "ip", "url", "message", "session", "error_type", "file", "line")
VALUES
(1, 1306445537, SUBSTRING('84.73.140.134', 1, 16), SUBSTRING('?action=admin;area=smileys;save;sa=settings', 1, 65534), SUBSTRING('Datenbankfehler: ERROR:  invalid input syntax for integer: ""', 1, 65534), '86d67e34f01e24eae2d988ae18a4c062', 'database', SUBSTRING('/Library/Apache2/htdocs/blahblah/forum/Sources/ManageSmileys.php', 1, 255), 1753)
ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT: 
UPDATE tgeedu_log_activity
SET
hits = hits + 1
WHERE date = '2011-05-26'
ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT: 
INSERT INTO tgeedu_log_errors("id_member", "log_time", "ip", "url", "message", "session", "error_type", "file", "line")
VALUES
(1, 1306445537, SUBSTRING('84.73.140.134', 1, 16), SUBSTRING('?action=admin;area=smileys;save;sa=settings', 1, 65534), SUBSTRING('Datenbankfehler: ERROR:  current transaction is aborted, commands ignored until end of transaction block', 1, 65534), '86d67e34f01e24eae2d988ae18a4c062', 'database', SUBSTRING('/Library/Apache2/htdocs/blahblah/forum/Sources/Subs.php', 1, 255), 2994)
ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT: 
INSERT INTO tgeedu_log_activity("hits", "date")
VALUES
(1, '2011-05-26')


DIsabling the sort code solves the said problem and I can edit, change, order and use my smiley set just fine.

cheers
®

[Oldiesmann]

Looks like PostgreSQL doesn't like LENGTH(code) (setting temp_order to the length of whatever's in the "code" column). Since we're not running that ALTER TABLE ... ORDER BY query in PostgreSQL, we can also just skip the one before it...

Sources/Subs-Db-Postgresql.php

Find

Code Select Expand

);

if (isset($replacements[$identifier]))

Add before that

Code Select Expand

'set_smiley_order' => array(
'~(.+)~' => '',
),

That will cause SMF to skip that query as well. I've fixed this in SVN, so it will be fixed for 2.0 final.

[habakuk]

Learned something new today, thanks!    I'll go ahead and give that fix a go.

cheers
®