Bug Database Error

[Mick]

Hi there,

Everyone seems to be getting this MYSQL error when viewing the arcade.

Database error in vBulletin 4.2.3:

Invalid SQL:
SELECT u.*, a.avatarpath, NOT ISNULL(c.userid) AS hascustomavatar, c.dateline AS avatardateline, c.width AS avwidth, c.height AS avheight, c.height_thumb AS avheight_thumb, c.width_thumb AS avwidth_thumb, c.filedata_thumb, SUM(s.timefinish - s.timestart) AS total FROM dbtech_vbarcade_session AS s LEFT JOIN user AS u ON (u.userid = s.userid) LEFT JOIN dbtech_vbarcade_game AS g ON (g.gameid = s.gameid) LEFT JOIN avatar AS a ON (a.avatarid = u.avatarid) LEFT JOIN customavatar AS c ON (c.userid = u.userid) WHERE g.enabled = 1 AND s.record > 2 GROUP BY s.userid ORDER BY total DESC LIMIT 3;

MySQL Error   : BIGINT UNSIGNED value is out of range in '(`vbulletin`.`s`.`timefinish` - `vbulletin`.`s`.`timestart`)'
Error Number  : 1690
Request Date  : Tuesday, November 3rd 2015 @ 08:24:00 AM
Error Date    : Tuesday, November 3rd 2015 @ 08:24:00 AM
Script        : https://www.digitalworldz.co.uk/arcade.php
Referrer      : https://www.digitalworldz.co.uk/index.php
IP Address    : 
Username      : 
Classname     : vB_Database_MySQLi
MySQL Version : 5.5.44-MariaDB

Cheers
Mick

 

[Fillip H.]

Can you find what the timefinish and timestart columns are for those records?

 

[Mick]

Can you find what the timefinish and timestart columns are for those records?

Which records?

I looked in the Table: dbtech_vbarcade_session

And found the timestart and finish but there are like 100000 records

I am pretty sure it relates to this code in the arcade.php

		$typestats = array(
			'champs' => "SELECT s.*, d.*, u.*, g.*$avatar[0] FROM " . TABLE_PREFIX . "dbtech_vbarcade_session AS s INNER JOIN " . TABLE_PREFIX . "dbtech_vbarcade_data AS d ON (d.local_winner = s.sessionid) LEFT JOIN " . TABLE_PREFIX . "user AS u ON (u.userid = s.userid) LEFT JOIN " . TABLE_PREFIX . "dbtech_vbarcade_game AS g ON (g.gameid = s.gameid)$avatar[1] WHERE g.enabled = 1 AND s.record = 4 ORDER BY s.timefinish DESC LIMIT " . $vbulletin->options['dbtech_vbarcade_topstats'],
			'scores' => "SELECT s.*, u.*, g.*$avatar[0] FROM " . TABLE_PREFIX . "dbtech_vbarcade_session AS s LEFT JOIN " . TABLE_PREFIX . "user AS u ON (u.userid = s.userid) LEFT JOIN " . TABLE_PREFIX . "dbtech_vbarcade_game AS g ON (g.gameid = s.gameid)$avatar[1] WHERE g.enabled = 1 AND s.record = 4 ORDER BY s.timefinish DESC LIMIT " . $vbulletin->options['dbtech_vbarcade_topstats'],
			'trophies' => "SELECT u.*$avatar[0], COUNT(*) AS total FROM " . TABLE_PREFIX . "dbtech_vbarcade_session AS s INNER JOIN " . TABLE_PREFIX . "dbtech_vbarcade_data AS d ON (d.local_winner = s.sessionid) LEFT JOIN " . TABLE_PREFIX . "user AS u ON (u.userid = s.userid) LEFT JOIN " . TABLE_PREFIX . "dbtech_vbarcade_game AS g ON (g.gameid = s.gameid)$avatar[1] WHERE g.enabled = 1 AND s.record > 2 GROUP BY s.userid ORDER BY total DESC LIMIT " . $vbulletin->options['dbtech_vbarcade_topstats'],
			'longest' => "SELECT u.*$avatar[0], SUM(s.timefinish - s.timestart) AS total FROM " . TABLE_PREFIX . "dbtech_vbarcade_session AS s LEFT JOIN " . TABLE_PREFIX . "user AS u ON (u.userid = s.userid) LEFT JOIN " . TABLE_PREFIX . "dbtech_vbarcade_game AS g ON (g.gameid = s.gameid)$avatar[1] WHERE g.enabled = 1 AND s.record > 2 GROUP BY s.userid ORDER BY total DESC LIMIT " . $vbulletin->options['dbtech_vbarcade_topstats']
		);

EDIT:
I commented out the code above, and then turned off general stats and it works again but looks weird???

https://www.digitalworldz.co.uk/arcade.php


Cheers Fillip H.

Mick

 

[Fillip H.]

I think the issue is that one of the returned rows has a timestart higher than timefinish.

Try running a query like this:

select * from dbtech_vbarcade_session where timestart > timefinish

and see if that returns any rows.

 

[Mick]

Hi Fillip H.,

Yes there is about 15,000 records?

Showing rows 14600 - 14624 (14625 total, Query took 0.0108 seconds.)

Can I do anything to fix it?

Or should I delete the sessions?

 

[Fillip H.]

Can you show me an example result set before you do that?

 

[Fillip H.]

Hello Mick,

This ticket has now been closed with the status No Response.

We hope your issue or question has been addressed to your satisfaction. If not, please feel free to re-open it by clicking this link.

If you have any further issues or questions, please feel free to start a new support ticket via the button at the top of every page.

Thank you!