• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

[Powergamers] SQL error: "MYSQL ERROR: BIGINT UNSIGNED value is out of range" (1690)

L

ligus

New Member
Joined
Apr 26, 2010
Messages
253
Reaction score
0
Hello, I'm trying to use this script: http://otland.net/threads/gesior-acc-maker-tfs-0-3-5-powergamers-and-online-time-stats.44762/
But there is problem with this line:
Code: 
db.executeQuery("UPDATE `players` SET `exphist7`=players.exphist6, `exphist6`=players.exphist5, `exphist5`=players.exphist4, `exphist4`=players.exphist3, `exphist3`=players.exphist2, `exphist2`=players.exphist1, `exphist1`=players.experience-players.exphist_lastexp, `exphist_lastexp`=players.experience;")

I have these errors:
Code: 
mysql_real_query(): UPDATE `players` SET 
`exphist7`=players.exphist6, `exphist6`=players.exphist5, `exphist5`=players.exphist4,
`exphist4`=players.exphist3, `exphist3`=players.exphist2, `exphist2`=players.exphist1, 
`exphist1`= players.experience-players.exphist_lastexp, `exphist_lastexp`=players.experience; 
- MYSQL ERROR: BIGINT UNSIGNED value is out of range in 
'(`ot`.`players`.`experience` - `ot`.`players`.`exphist_lastexp`)' (1690)

I've changed column exphist_lastexp in DB - I've set attribute Unsigned but it haven't helped.
How can it be fixed?
 
Sort by date Sort by votes
"ALTER TABLE `table_name` ALTER COLUMN `column_name` int"

I'm not gonna look through that stuff but try setting the column returning that error to int.
 
Upvote 0 Downvote
Are you talking about exphist_lastexp?


Edit:
ALTER TABLE `players` ALTER COLUMN `exphist_lastexp` int;


#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int' at line 1
 
Upvote 0 Downvote
you need to add these in your database
Code: 
ALTER TABLE `players` ADD `exphist_lastexp` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `exphist1` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `exphist2` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `exphist3` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `exphist4` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `exphist5` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `exphist6` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `exphist7` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetimetoday` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetime1` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetime2` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetime3` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetime4` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetime5` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetime6` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetime7` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetimeall` BIGINT( 255 ) NOT NULL DEFAULT '0';

and add this after

Code: 
UPDATE `players` SET `exphist_lastexp`=`players`.`experience`


as add in your query database:
follow these steps as shown in the images

1:
http://prntscr.com/3rpm5z
3rpm5z

2:

http://prntscr.com/3rpngi
3rpngi

3:
http://prntscr.com/3rpohi
3rpohi


4:
http://prntscr.com/3rpp4s
3rpohi

performs the same steps with the following

works perfectly
Screenshot_17.png
;):p
 
Upvote 0 Downvote
Thanks but I've already done it.
When script is working then there is such error:
Code: 
mysql_real_query(): UPDATE `players` SET
`exphist7`=players.exphist6, `exphist6`=players.exphist5, `exphist5`=players.exphist4,
`exphist4`=players.exphist3, `exphist3`=players.exphist2, `exphist2`=players.exphist1,
`exphist1`= players.experience-players.exphist_lastexp, `exphist_lastexp`=players.experience;
- MYSQL ERROR: BIGINT UNSIGNED value is out of range in
'(`ot`.`players`.`experience` - `ot`.`players`.`exphist_lastexp`)' (1690)
 
Upvote 0 Downvote
mmm ok try with this

open server/ file .../data/globalevents/scripts
Create new file history.lua
Open history.lua and put:

Code: 
function onThink(interval, lastExecution) 
    if (tonumber(os.date("%d")) ~= getGlobalStorageValue(23456)) then 
        setGlobalStorageValue(23456, (tonumber(os.date("%d")))) 
        db.executeQuery("UPDATE `players` SET `onlinetime7`=players.onlinetime6, `onlinetime6`=players.onlinetime5, `onlinetime5`=players.onlinetime4, `onlinetime4`=players.onlinetime3, `onlinetime3`=players.onlinetime2, `onlinetime2`=players.onlinetime1, `onlinetime1`=players.onlinetimetoday, `onlinetimetoday`=0;") 
        db.executeQuery("UPDATE `players` SET `exphist7`=players.exphist6, `exphist6`=players.exphist5, `exphist5`=players.exphist4, `exphist4`=players.exphist3, `exphist3`=players.exphist2, `exphist2`=players.exphist1, `exphist1`=players.experience-players.exphist_lastexp, `exphist_lastexp`=players.experience;") 
    end 
    db.executeQuery("UPDATE `players` SET `onlinetimetoday`=players.onlinetimetoday+60, `onlinetimeall`=players.onlinetimeall+60 WHERE `online` = 1;") 
    return TRUE 
end

and now you include this in your globalevent.xml
Code: 
<globalevent name="history" interval="60" event="script" value="history.lua"/>


now if you are using one version new than gesior try this script and copy all lines

Code: 
<?php
if(!defined('INITIALIZED'))
    exit;

/*
Script for OTSes that use MySQL (globalevent, type: think, interval: 60 or 60000 [tfs 0.4]):
function onThink()
    if (tonumber(os.date("%d")) ~= getGlobalStorageValue(23456)) then
        setGlobalStorageValue(23456, (tonumber(os.date("%d"))))
        db.executeQuery("UPDATE `players` SET `onlinetime7`=`onlinetime6`, `onlinetime6`=`onlinetime5`, `onlinetime5`=`onlinetime4`, `onlinetime4`=`onlinetime3`, `onlinetime3`=`onlinetime2`, `onlinetime2`=`onlinetime1`, `onlinetime1`=`onlinetimetoday`, `onlinetimetoday`=0;")
        db.executeQuery("UPDATE `players` SET `exphist7`=`exphist6`, `exphist6`=`exphist5`, `exphist5`=`exphist4`, `exphist4`=`exphist3`, `exphist3`=`exphist2`, `exphist2`=`exphist1`, `exphist1`=`experience`-`exphist_lastexp`, `exphist_lastexp`=`experience`;")
    end
    db.executeQuery("UPDATE `players` SET `onlinetimetoday`=`onlinetimetoday`+60, `onlinetimeall`=`onlinetimeall`+60 WHERE `online` = 1;")
end

MySQL schema:
ALTER TABLE `players` ADD `exphist_lastexp` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `exphist1` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `exphist2` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `exphist3` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `exphist4` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `exphist5` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `exphist6` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `exphist7` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `onlinetimetoday` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `onlinetime1` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `onlinetime2` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `onlinetime3` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `onlinetime4` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `onlinetime5` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `onlinetime6` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `onlinetime7` BIGINT( 20 ) NOT NULL DEFAULT '0',
ADD `onlinetimeall` BIGINT( 20 ) NOT NULL DEFAULT '0';
*/
$limit = 50;
$type = $_REQUEST['type'];
function coloured_value($valuein)
{
    $value2 = $valuein;
    while(strlen($value2) > 3)
    {
        $value .= '.'.substr($value2, -3, 3);
        $value2 = substr($value2, 0, strlen($value2)-3);
    }
    $value = $value2.$value;
    if($valuein > 0)
        return '<font color="green">+'.$value.'</font>';
    elseif($valuein < 0)
        return '<font color="red">'.$value.'</font>';
    else
        return '<font color="black">'.$value.'</font>';
}
if(empty($type))
    $players = $SQL->query('SELECT * FROM ' . $SQL->tableName('players') . ' WHERE ' . $SQL->fieldName('group_id') . ' < 2 ORDER BY ' . $SQL->fieldName('experience') . '-' . $SQL->fieldName('exphist_lastexp') . ' DESC LIMIT ' . $limit)->fetchAll();
elseif($type == "sum")
    $players = $SQL->query('SELECT * FROM ' . $SQL->tableName('players') . ' WHERE ' . $SQL->fieldName('group_id') . ' < 2 ORDER BY ' . $SQL->fieldName('exphist1') . '+' . $SQL->fieldName('exphist2') . '+' . $SQL->fieldName('exphist3') . '+' . $SQL->fieldName('exphist4') . '+' . $SQL->fieldName('exphist5') . '+' . $SQL->fieldName('exphist6') . '+' . $SQL->fieldName('exphist7') . '+' . $SQL->fieldName('experience') . '-' . $SQL->fieldName('exphist_lastexp') . ' DESC LIMIT ' . $limit)->fetchAll();
elseif($type >= 1 && $type <= 7)
    $players = $SQL->query('SELECT * FROM ' . $SQL->tableName('players') . ' WHERE ' . $SQL->fieldName('group_id') . ' < 2 ORDER BY ' . $SQL->fieldName('exphist' . (int) $type) . ' DESC LIMIT '.$limit)->fetchAll();
$main_content .= '<CENTER><H2>Ranking of powergamers</H2></CENTER><BR><TABLE BORDER="0" CELLPADDING="4" CELLSPACING="1" WIDTH="100%"><TR BGCOLOR="'.$config['site']['vdarkborder'].'"><TD style="color:white"><B>Rank</B></TD><TD style="color:white"><B>Name</B></TD>';
if($type == "sum")
    $main_content .= '<TD bgcolor="red"><b><center><a href="?subtopic=powergamers&type=sum">7 Days sum</a></center></B></TD>';
else
    $main_content .= '<TD bgcolor="yellow"><b><center><a href="?subtopic=powergamers&type=sum">7 Days sum</a></center></B></TD>';
for($i = 7; $i >= 2; $i--)
{
    if($type == $i)
        $main_content .= '<TD bgcolor="red"><b><center><a href="?subtopic=powergamers&type='.$i.'">'.$i.' Days Ago</a></center></B></TD>';
    else
        $main_content .= '<TD bgcolor="yellow"><b><center><a href="?subtopic=powergamers&type='.$i.'">'.$i.' Days Ago</a></center></B></TD>';
}
if($type == 1)
    $main_content .= '<TD bgcolor="red"><b><center><a href="?subtopic=powergamers&type=1">1 Day Ago</a></center></B></TD>';
else
    $main_content .= '<TD bgcolor="yellow"><b><center><a href="?subtopic=powergamers&type=1">1 Day Ago</a></center></B></TD>';
if(empty($type))
    $main_content .= '<TD bgcolor="red"><b><center><a href="?subtopic=powergamers">Today</a></center></B></TD>';
else
    $main_content .= '<TD bgcolor="yellow"><b><center><a href="?subtopic=powergamers">Today</a></center></B></TD>';
$main_content .= '</TR>';
foreach($players as $player)
{
    if(!is_int($number_of_rows / 2)) { $bgcolor = $config['site']['darkborder']; } else { $bgcolor = $config['site']['lightborder']; } $number_of_rows++;
    $main_content .= '<tr bgcolor="'.$bgcolor.'"><td align="center">'.$number_of_rows.'. </td>';
    if($player['online'] == 1)
        $main_content .= '<td><a href="?subtopic=characters&name='.urlencode($player['name']).'"><b><font color="green">'.htmlspecialchars($player['name']).'</font></b></a>';
    else
        $main_content .= '<td><a href="?subtopic=characters&name='.urlencode($player['name']).'"><b><font color="red">'.htmlspecialchars($player['name']).'</font></b></a>';
    $main_content .= '<br />'.$player['level'].' '.htmlspecialchars(Website::getVocationName($player['vocation'], $player['promotion'])).'</td><td align="right">'.coloured_value($player['exphist1'] + $player['exphist2'] + $player['exphist3'] + $player['exphist4'] + $player['exphist5'] + $player['exphist6'] + $player['exphist7'] + $player['experience'] - $player['exphist_lastexp']).'</td>';
    $main_content .= '<td align="right">'.coloured_value($player['exphist7']).'</td><td align="right">'.coloured_value($player['exphist6']).'</td><td align="right">'.coloured_value($player['exphist5']).'</td><td align="right">'.coloured_value($player['exphist4']).'</td><td align="right">'.coloured_value($player['exphist3']).'</td><td align="right">'.coloured_value($player['exphist2']).'</td><td align="right">'.coloured_value($player['exphist1']).'</td><td align="right">'.coloured_value($player['experience']-$player['exphist_lastexp']).'</td></tr>';
}
$main_content .= '</TABLE>';
 
Upvote 0 Downvote
Try to replace the query:
Code: 
db.executeQuery("UPDATE `players` SET `exphist7` = players.exphist6, `exphist6` = players.exphist5, `exphist5` = players.exphist4, `exphist4` = players.exphist3, `exphist3` = players.exphist2, `exphist2` = players.exphist1, `exphist1` = GREATEST(players.experience - players.exphist_lastexp, 0), `exphist_lastexp` = players.experience;")
 
Upvote 0 Downvote
Still the same problem:
Code: 
mysql_real_query(): UPDATE `players` SET `exphist7` = players.exphist6, `exphist6` = players.exphist5, `exphist5` =
players.exphist4, `exphist4` = players.exphist3, `exphist3` = players.exphist2, `exphist2` = players.exphist1, `exphist1` = GREATE
ST(players.experience - players.exphist_lastexp, 0), `exphist_lastexp` = players.experience; - MYSQL ERROR: BIGINT UNSIGNED value
is out of range in '(`ot`.`players`.`experience` - `ot`.`players`.`exphist_lastexp`)' (1690)
 
Upvote 0 Downvote
are you dont understand. each square in db sql can Constantin a few chars. (the same like bp, can just Constantin 20 items). any way did you must set data type in there column. example double_long ofc if this is only for int value ;)
 
Upvote 0 Downvote
paste you db table code i'll se

//edit.

PHP: 
ALTER TABLE `players` ADD `exphist_lastexp` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `exphist1` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `exphist2` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `exphist3` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `exphist4` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `exphist5` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `exphist6` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `exphist7` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetimetoday` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetime1` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetime2` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetime3` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetime4` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetime5` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetime6` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetime7` BIGINT( 255 ) NOT NULL DEFAULT '0',
ADD `onlinetimeall` BIGINT( 255 ) NOT NULL DEFAULT '0';
You need now to set all bigint space from 20 to 255

//edit 2
PHP: 
UNSIGNED BIGINT(20)
- at the last you can replace with unsinged bigint(20) -[291a2d25bddc15fc77485e120529f42ca685f6f4]. any way i think this is bad idea cuz your site can slow so much.
 
Last edited:
Upvote 0 Downvote

Similar threads

Gesior.pl
GlobalEvent [TFS 1.x] Power Gamers and Online Time for Gesior2012/MyAAC
Replies
1
Views
100
netbe
N
shakal1994
  • Question
PHP znote acc powergamers
Replies
17
Views
718
mano368
M
Back
Top