Return DB errors from $smcFunc

Started by tjbalon, May 05, 2021, 03:33:08 PM

Previous topic - Next topic

tjbalon

Afternoon all  8)

We've added steam integration into our website, and regularly poll steam for updates in batches of 100 users (max getsummaries limit from valve). Currently, the script we use is failing to update steam profiles. I have narrowed it down to the query:
     
$smcFunc['db_query']('', 'UPDATE {db_prefix}members SET steampic = {string:steampic}, steamname = {string:steamname}, steamstatus = {string:steamstatus} WHERE id_member = {int:id_member}', array(
                   'steampic' => $dbUser['steampic'],
                    'steamname' => $dbUser['steamname'],
                    'steamstatus' => $dbUser['steamstatus'],
                    'id_member' => $dbUser['id_member']
            ));


In our error log, the output is:
Quote
   <div id="fatal_error">
      <div class="cat_bar">
         <h3 class="catbg">
            Database Error
         </h3>
      </div>
      <div class="windowbg">
         <span class="topslice"><span></span></span>
         <div class="padding">Please try again.  If you come back to this error screen, report the error to an administrator.</div>
         <span class="botslice"><span></span></span>
      </div>
   </div>
   <div class="centertext"><a href="javascript:history.go(-1)">Back</a></div>

My ASSUMPTION is that it is due to some type of DB column collation since there are so many special characters that can be set in a steam profile. We are using utf8_general_ci. My problem currently is that I cannot see what the specific MySQL error is from $smcFunc so I'm kinda scratching my head. I really don't want to have to re-write to a native PDO for PHP to see what the error is. Error is not in the error log, as we are including outside the website as an external script that just runs via cron.

I saw the documentation for $smcFunc talks about something called using 'db_error' but this doesn't look like what I need.

I tried logging all queries on my sql server, I see some of the query but unfortunately cuts off  :laugh:

shawnb61

In 2.0, $smcFunc['db_error']() should return the standard mysqli error number, e.g., 1054 for unknown column.  I do think that would be helpful - just do a search for the number returned. 

$smcFunc should be logging an error in the SMF error log as well under most circumstances.

Sometimes, when I'm baffled and need more DB-related error info, I will do a print_r on both the connection and the result objects, e.g., something along the lines of:
$sql = '<your query...>';

$result = $smcFunc['db_query']('', $sql, array('db_error_skip' => true), $db_connection);

echo '<br>error info:<br>';
$err = $smcFunc['db_error']();
print_r($err);

echo '<br>conn info:<br>';
print_r($db_connection);

echo '<br>result info:<br>';
print_r($result);


If needed, write it to a file.  That will pretty much always give you what you're looking for in terms of DB info.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

tjbalon

Quote from: shawnb61 on May 05, 2021, 04:55:03 PM
In 2.0, $smcFunc['db_error']() should return the standard mysqli error number, e.g., 1054 for unknown column.  I do think that would be helpful - just do a search for the number returned. 

$smcFunc should be logging an error in the SMF error log as well under most circumstances.

Sometimes, when I'm baffled and need more DB-related error info, I will do a print_r on both the connection and the result objects, e.g., something along the lines of:
$sql = '<your query...>';

$result = $smcFunc['db_query']('', $sql, array('db_error_skip' => true), $db_connection);

echo '<br>error info:<br>';
$err = $smcFunc['db_error']();
print_r($err);

echo '<br>conn info:<br>';
print_r($db_connection);

echo '<br>result info:<br>';
print_r($result);


If needed, write it to a file.  That will pretty much always give you what you're looking for in terms of DB info.

Thanks shawn, what should I set here for $db_connection? I assume I can make some ambiguous thing like $tmp then call db_error on $tmp to see what the error was?

It looks as if my script is dying after I make the call. (I did solve the issue but this will be good to learn for future debugging).


        if ($changed) {
            logMsg(1, 'Information for: ' . $dbUser['id_member'] . '. Updating database with latest information.');
            print_r($dbUser);
            $smcFunc['db_query']('', 'UPDATE {db_prefix}members SET steampic = {string:steampic}, steamname = {string:steamname}, steamstatus = {string:steamstatus} WHERE id_member = {int:id_member}', array(
                    'steampic' => $dbUser['steampic'],
                    'steamname' => $dbUser['steamname'],
                    'steamstatus' => $dbUser['steamstatus'],
                    'id_member' => $dbUser['id_member']
            ));
        }


Issue is if there is an utf8mb4 character in any of those parameters, it errors out. I've just upgraded to MariaDB 10.5, thus the new found issues. I went through and changed the collation in those area(s) as well as the Settings.php coalition. I've read this is a bit unsuggested, but I will monitor my changes closely.

How would I debug this instance of db_query? Set a $db_connection after the query then call for the error? I think the script dies right after this is called, but I may be wrong. I tried putting it in a try/catch, same issue.

shawnb61

Every session with your database needs a database connection.  That connection object is created upon connect/init and used on all subsequent calls.  When using standard $smcFunc calls, it is usually in $db_connection, which is a global variable. 

If you look at the $smcFunc code (e.g., in Subs_Db_mysql.php, ~lines 1187+), you will see that most calls allow for passing of a connection as an argument (if, for example, you need multiple connections) or falling back onto the $db_connection global. 

In your example, I'm pretty sure you just need to expose the $db_connection global and use it.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

tjbalon

Quote from: shawnb61 on May 05, 2021, 05:22:45 PM
Every session with your database needs a database connection.  That connection object is created upon connect/init and used on all subsequent calls.  When using standard $smcFunc calls, it is usually in $db_connection, which is a global variable. 

If you look at the $smcFunc code (e.g., in Subs_Db_mysql.php, ~lines 1187+), you will see that most calls allow for passing of a connection as an argument (if, for example, you need multiple connections) or falling back onto the $db_connection global. 

In your example, I'm pretty sure you just need to expose the $db_connection global and use it.

Thank you, will try that!

Advertisement: