Bug #1659

SQL error on thread copy using custom moderation tool

Added by Andreas Klauer about 3 years ago. Updated over 2 years ago.

Status:ClosedStart date:07/23/2011
Priority:NormalDue date:
Assignee:Tom Moore% Done:

100%

Category:Moderation
Target version:1.6.7
Reproducibility:Always Database Type:
Reported In MyBB Version:1.6.3 Database Version:
PHP Version: SQA assignments:Nathan Malcolm
Browser:

Description

Hi,

creating a mod tool that does nothing but copy threads, it results in an SQL error:

MyBB SQL Error

MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
    1366 - Incorrect integer value: '' for column 'prefix' at row 1
Query:
    UPDATE mybb16_threads SET `prefix`='' WHERE tid IN (5,4,3,2,1) 

Please contact the MyBB Group for support.

This query is issued by inc/class_moderation.php::apply_thread_prefix and originates in inc/class_custommoderation.php

                        if($thread_options['threadprefix'] != '-1')
                        {
                                $this->apply_thread_prefix($tids, $thread_options['threadprefix']); // Update thread prefix
                        }

Either $thread_options should be initialized differently, or apply_thread_prefix() should make sure that the prefix isn't an invalid value.


Related issues

Related to MyBB - Bug #1575: MyBB should force traditional sql_mode Rejected 05/12/2011
Related to MyBB - Bug #1442: Split posts - PgSQL Closed 02/01/2011

History

#1 Updated by Spencer Swords about 3 years ago

I'm not able to reproduce this. Can you post your custom moderation tool setup (the options used for the custom moderation tool)?

#2 Updated by Spencer Swords about 3 years ago

  • SQA assignments set to Spencer

#3 Updated by Andreas Klauer about 3 years ago

The moderation tool was created like so:

Configuration -> Moderator Tools -> Add New Thread Tool

Enter Name
Enter Description
Click Copy Thread -> Select My Forum

Save Thread Tool

No other changes from the defaults.

I can't produce the issue on my other board which is using shared hosting. One and the same query, on one host it causes the error, on another it does not. Investigating more closely I came across this:

http://bugs.mysql.com/bug.php?id=18551

I think that's exactly the issue; prefix is of type integer and the query issued by MyBB tries to set it to empty string instead.

#4 Updated by Andreas Klauer about 3 years ago

Same problem with splitting posts,

MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
    1366 - Incorrect integer value: '' for column 'visible' at row 1
Query:
    INSERT INTO mybb1604_threads (`fid`,`subject`,`icon`,`uid`,`username`,`dateline`,`lastpost`,`lastposter`,`replies`,`visible`,`notes`) VALUES ('2','[split] blubber','0','1','admin','1311699695','1311699735','admin','0','','') 

I think MyBB requires traditional mysql-mode for now...

#5 Updated by Diogo Parrinha almost 3 years ago

I'm not sure which one is better, if changing the SQL mode or making sure we pass an integer when the field is an integer.

        $visible = $db->fetch_field($query, "visible");

        if($destination_tid == 0)
        {
            // Splitting into a new thread
            $thread = get_thread($tid);
            // Create the new thread
            $newsubject = $db->escape_string($newsubject);
            $query = array(
                "fid" => $moveto,
                "subject" => $newsubject,
                "icon" => intval($icon),
                "uid" => intval($thread['uid']),
                "username" => $db->escape_string($thread['username']),
                "dateline" => intval($thread['dateline']),
                "lastpost" => intval($thread['lastpost']),
                "lastposter" => $db->escape_string($thread['lastposter']),
                "replies" => count($pids)-1,
                "visible" => $visible,
                "notes" => ''
            );

Should be:

        $visible = (int)$db->fetch_field($query, "visible");

        if($destination_tid == 0)
        {
            // Splitting into a new thread
            $thread = get_thread($tid);
            // Create the new thread
            $newsubject = $db->escape_string($newsubject);
            $query = array(
                "fid" => $moveto,
                "subject" => $newsubject,
                "icon" => intval($icon),
                "uid" => intval($thread['uid']),
                "username" => $db->escape_string($thread['username']),
                "dateline" => intval($thread['dateline']),
                "lastpost" => intval($thread['lastpost']),
                "lastposter" => $db->escape_string($thread['lastposter']),
                "replies" => count($pids)-1,
                "visible" => $visible,
                "notes" => '0'
            );

Can you please let me know if this fixes it? (the bug you reported in the post above mine)

#6 Updated by Andreas Klauer almost 3 years ago

Diogo Parrinha wrote:

making sure we pass an integer when the field is an integer.

I guess that'd be the only proper fix in the long run, apart from changing the type of the field itself. Finding all occurences related to this is quite a challenge though, if I ran into two without looking there's bound to be more of them.

Can you please let me know if this fixes it? (the bug you reported in the post above mine)

I'm not sure if I understand why you changed notes to 0. notes is a text field. Shouldn't it be intval($visible) instead?

I'd have to change my MySQL config again to test this...

#7 Updated by Diogo Parrinha almost 3 years ago

Ops, not sure what went through my head when I put a 0 in notes.
I made it to typecast $visible when declared. Do you think it is better to typecast it only when the query is going to be executed?
Both work anyway.

But yes, you're right about the "long run". I'm not sure if this will be made into 1.6 so perhaps changing the SQL mode for inserts is better.

2.0 should not have this problem anyway since Yii does it for us.

#8 Updated by Tom Moore over 2 years ago

  • Category set to Moderation
  • Status changed from New to Assigned
  • Assignee set to Tom Moore
  • Target version set to 1.6.6

The second issue does not apply - I believe it was fixed in another issue (#1664).

#9 Updated by Tom Moore over 2 years ago

  • Status changed from Assigned to Resolved
  • % Done changed from 0 to 100

Applied in changeset r5684.

#10 Updated by Nathan Malcolm over 2 years ago

  • Status changed from Resolved to Closed
  • SQA assignments changed from Spencer to Nathan Malcolm

#11 Updated by Tom Moore over 2 years ago

  • Target version changed from 1.6.6 to 1.6.7

Also available in: Atom PDF