Topic: Attn r04r: Database Error!

FuckAlms !vX8K53rFBI started this discussion 12 years ago #38,586

> Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1
> Query: UPDATE topics SET replies = replies - 1 WHERE id = ?
> File: /includes/database.class.php
> Line: 72
> Backtrace:

Array

(

    [0] => Array

        (

            [file] => /action.php

            [line] => 515

            [function] => db_exec

            [class] => db

            [object] => db Object

                (

                    [db_link:protected] => Resource id #3

                    [special_values:protected] => Array

                        (

                            [0] => NOW()

                            [1] => NULL

                            [2] => UNIX_TIMESTAMP()

                        )



                    [query_id:protected] => Resource id #14

                    [prefix:protected] => 

                    [db_username:protected] => (hidden)

                    [db_password:protected] => (hidden)

                )



            [type] => ->

            [args] => Array

                (

                    [0] => UPDATE topics SET replies = replies - 1 WHERE id = ?

                    [1] => 204

                )



        )



)

Despite throwing an error, the query still deletes all the posts like it's supposed to. What needs to change in order to make it work smoothly?

HaikerensGuide !dBGi/iH4eY joined in and replied with this 12 years ago, 23 seconds later[^] [v] #515,185

shut up

Anonymous C joined in and replied with this 12 years ago, 2 minutes later, 3 minutes after the original post[^] [v] #515,186

@previous (HaikerensGuide !dBGi/iH4eY)

> shut up

r04r joined in and replied with this 12 years ago, 8 minutes later, 11 minutes after the original post[^] [v] #515,188

I assume this isn't about Minichan because of the mention of MariaDB instead of MySQL. So is this a general question where something broke on your own installation? A bit more context would be useful. I'm guessing the error you're seeing is because the code doesn't make use of transactions (yes its gross) and it first deletes the reply from the reply table, then decrements the reply counter in the topic table.

I'm not really sure why this query would produce an error, it looks like an ordinary prepared statement to me. Especially odd because the deletion itself also makes use of a similar prepared statement (in the version I have here at least), which executed successfully prior to this failed statement. So indeed assuming this is for another site/your work I am willing to help, but a bit more context and info on what has been changed would be helpful.

Also. If you're going to be rewriting things... For the love of god just make it use of PDO instead of mysql_query and all the string hackery I've done.

FuckAlms !vX8K53rFBI (OP) replied with this 12 years ago, 13 minutes later, 24 minutes after the original post[^] [v] #515,195

@previous (r04r)
It's for OtakuTalk. For whatever reason trying to 'delete all posts' from the link on the IP page causes this error but it works fine from the ID page. The spambots never keep their ID, but they do make multiple hits from a single IP, so wiping by IP rather than ID is preferable. Since you mentioned decrementing the reply counter, it's probably noteworthy that the reply counter has never actually gone down when I delete posts.

I'm not really looking to rewrite stuff, I just want to make it stop throwing an error at me and take me back to the IP or ID page with a header notice, the way the other actions do.

r04r replied with this 12 years ago, 1 minute later, 25 minutes after the original post[^] [v] #515,196

@previous (FuckAlms !vX8K53rFBI)
Alright, so did you make any changes anywhere? If so it would help if you could at least upload a copy of action.php & database.class.php somewhere. I'm quite busy today so I would prefer looking at this tomorrow btw.

(Edited 1 minute later.)

FuckAlms !vX8K53rFBI (OP) replied with this 12 years ago, 22 minutes later, 47 minutes after the original post[^] [v] #515,209

@previous (r04r)
I don't believe any changes were made to those particular files; both are timestamped Feb 2012.
database.class.php
action.php

There's no rush if you've got other stuff to do.

r04r replied with this 12 years ago, 1 minute later, 49 minutes after the original post[^] [v] #515,210

@previous (FuckAlms !vX8K53rFBI)
Alright, weird, I'll take another look tomorrow (if I remember, if I never reply pls do cite me again at some point to yell at me)

Anonymous E joined in and replied with this 12 years ago, 5 minutes later, 55 minutes after the original post[^] [v] #515,212

The other similar calls in that file use a %1 rather than a ? for the parameter you nerd.

r04r replied with this 12 years ago, 3 minutes later, 59 minutes after the original post[^] [v] #515,213

@previous (E)
Right on, that's probably it. Guess that's a bug that is still in the older code, but doesn't/didn't exist in the minichan codebase. I really should do a new "release" sometime, but will first have to dig through the code and move all newly introduced "secrets" to the config file.

edit: Note that %1 is a concept that I introduced in the database.class.php and this gets translated to ? under the hood, but some array shuffling happens and the parameter probably didn't get included in the prepared statement because database.class.php only recognizes %s. This was done to mirror the better PDO behavior and make writing queries easier, but sadly at the time PDO was not available on most shared webhosts. I guess this specific call got missed and never got updated before the release that @OP got was made.

Really, this code is awful by todays standards (It was awful back then, but less so). I really need to finish my rewrite someday.

(Edited 5 minutes later.)

FuckAlms !vX8K53rFBI (OP) replied with this 12 years ago, 8 minutes later, 1 hour after the original post[^] [v] #515,215

@515,212 (E)
@previous (r04r)
Awesome, it worked!

So if it never threw an error when wiping all posts from an ID page, and counters never went down when deleting that way either, does it mean the error was being suppressed or is the decrement missing from the function used by the ID page?

r04r replied with this 12 years ago, 3 minutes later, 1 hour after the original post[^] [v] #515,216

@previous (FuckAlms !vX8K53rFBI)
I'm not sure. I should note that the nukes have always been a little bit weird, and I'm not even sure if they're enabled on minichan. We barely use them, since (bot)spam is minimal with a captcha for first post. I assume wiping posts from the id page triggers nuke_id which starts at line 405 in your action.php. Looking briefly at that I would think it should, so I'm not sure what is happening there.

r04r double-posted this 12 years ago, 6 minutes later, 1 hour after the original post[^] [v] #515,217

@515,215 (FuckAlms !vX8K53rFBI)
@previous (r04r)
I stared at it for another minute, and I think I spotted the bug. Line 453, that $id at the end there should probably be $parent_id

This bug also does not exist in minichan's current codebase, even though nuke_id is forcefully disabled in the code (because I thought it was too powerful of a tool for most moderators at the time). I will definitely look into making another release I guess so there's at least something to compare to.

(Edited 1 minute later.)

Anonymous F joined in and replied with this 12 years ago, 1 hour later, 2 hours after the original post[^] [v] #515,237

maagd

r04r replied with this 12 years ago, 4 days later, 4 days after the original post[^] [v] #516,469

@515,215 (FuckAlms !vX8K53rFBI)
did you see
@515,217 (r04r)
just making sure

FuckAlms !vX8K53rFBI (OP) replied with this 12 years ago, 1 hour later, 4 days after the original post[^] [v] #516,480

@previous (r04r)
Yeah, that fix also worked, I believe.