1.4.6, pgsql, 'move thread' gives SQL error

[pierce]

when I just tried to 'move thread', i got the following SQL error...

Code:

MyBB SQL Error

MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
    22P02 - ERROR: invalid input syntax for integer: ""
Query:
    SELECT COUNT(p.pid) AS posts, u.uid FROM mybb_posts p LEFT JOIN mybb_users u ON (u.uid=p.uid) WHERE tid='' GROUP BY u.uid ORDER BY posts DESC

I selected the thread in the forum view, chose move threads, clicked Go(1), on the next screen selected the target forum (a new empty one), and hit Move Threads, and got that error.

using Postgres 8.3.7 on CentOS 5.3

[Pirata Nervo]

I don't know why $tid is not valid for you.

PHP Code:

$this->move_thread($tid, $thread_options['movethread'], 'redirect', $time); 


Should be pass a valid thread id. At least it does for me. Try moving a different thread

Edit:
I am not using Postgre SQL but the input data is the same I guess. I haven't tracked down all code but from what I understood, once you execute that action ('move thread' through moderation.php - inlinethread), it runs the execute function which is part of the class custom moderation which will then execute the function move_thread which is part of the class moderation.

[pierce]

this is still happening to me.

i tried to instrument the move code in class_moderation and class_error to print a preset string after the sql error, but it didn't work... I don't think I ..........

*OH* its bombing in move_threads, not move_thread. ok, moving instrumentation to class_moderation.php->move_threads()...

$tids and $tidlist seem fine.

Code:

$query1 = $db->query("
                                SELECT COUNT(p.pid) AS posts, u.uid
                                FROM ".TABLE_PREFIX."posts p
                                LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=p.uid)
                                WHERE tid='{$thread['tid']}'
                                GROUP BY u.uid
                                ORDER BY posts DESC
                    ");

that, however, appears wrong. pgsql is seeing that as WHERE tid=''

I don't get it, the query that fetches the list of $thread doesn't have tid as a field.

Code:

$query = $db->simple_select("threads", "fid, visible, replies, unapprovedposts", "tid IN ($tid_list)");
                while($thread = $db->fetch_array($query))
                {

and, now I find -another- error in move_thread() in class_moderation... the query at line 903 or thereabouts... PostgreSQL 8.3 is pretty strict about SQL standard compliance, and complains that the grouping in that query doesn't encompass p.visible...

I fixed it as follows...

Code:

switch($db->type)
                {
                        case "pgsql":
                                $query = $db->query("
                                        SELECT COUNT(p.pid) AS posts, u.uid, p.visible
                                        FROM ".TABLE_PREFIX."posts p
                                        LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=p.uid)
                                        WHERE tid='$tid'
                                        GROUP BY u.uid,p.visible
                                        ORDER BY posts DESC
                                ");
                                break;
                        default;
                                $query = $db->query("
                                        SELECT COUNT(p.pid) AS posts, u.uid, p.visible
                                        FROM ".TABLE_PREFIX."posts p
                                        LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=p.uid)
                                        WHERE tid='$tid'
                                        GROUP BY u.uid
                                        ORDER BY posts DESC
                                ");
                                break;
                }

[pierce]

bump...

this is still outstanding in 1.4.8 ... move_thread needs that SQL fix I mention in the past post (adding p.visible to the group by clause), while move_threads is still not seeing a $tid value in its arguments.

[Ryan Gordon]

No need to bump it - If it's in this forum we are aware of it.

[Pirata Nervo]

Can you try printing $thread['tid'] ?

[pierce]

I just created a NEW test forum on this same machine, totally stock virgin 1.4.8 on PG 8.3.7 and CentOS 5.3, and was easily able to reproduce the move threads problem. http://mybb.freescruz.com ... if you want to register and privately identify yourself to me, i'll enable you as a moderator to test this.

I added a 1 message thread to "My Forum", then in the forum view selected the thread, and used 'inline thread moderation' to 'move threads', then chose another forum and hit 'ok' and got the same SQL error.

pirata: $tids was apparently empty at the entry to move_threads() in class_moderation.php, I didn't drill down any deeper.

Hey, what -is- a reasonable way to instrument this php code and display diagnostics? its not being called in a place where i can just print stuff... last time around, I created a $hack that i filled with string values, then added to the SQL error handler to print as an 'extra' ...not very effective.

---

ok, more instrumentation on move_threads() in class_moderation.php... $tids was valid, array with single value 2 in the test case on my test forum.... inside the while($thread = $db->fetch_array($query)), $thread is...

Code:

Array ( [fid] => 2 [visible] => 1 [replies] => 0 [unapprovedposts] => 0 )

I do -not- see a TID value here at all. in fact, the query is ...

Code:

$query = $db->simple_select("threads", "fid, visible, replies, unapprovedposts", "tid IN ($tid_list)");

so, $thread['tid'] is, of course, null. which is the error I'm getting.

I added ,tid to the select clause of that simple_select() and voila, now its working.

[Nickman]

Corresponding Ticket: http://dev.mybboard.net/issues/414