Jump to content
Search In
  • More options...
Find results that contain...
Find results in...
  • Sign Up
  • 0
AnnieRuru

Advance SQL commands

Asked by AnnieRuru

Question

AnnieRuru    958

AnnieRuru
Posted

As usual, I only write advance guides

This guide is a compilation of SQL commands that I have used, or Questions answered on the forum
every single subject here are related to Hercules/Ragnarok Online in some ways, so you won't feel bored reading them XD

Table of Content
1. When to use *escape_sql script command
2. How to build a case-sensitive table
3. Choose a table type, MyISAM or InnoDB ?
3a. How to index a table properly
3b. Why you shouldn't use `char_reg_num_db` table
4. AUTO_INCREMENT
5. How to do IF-ELSE in SQL query ?
5a. How to update multiple rows on different conditions in a single query
6. How to show the current rank of the player
7. INSERT INTO ... SELECT ...
8. Table JOIN vs AS
9. What is the maximum string limit for *query_sql
9a. UNION

This topic is now open to Suggestions, Ideas, Improvements, and Questions ~
I'm sure many of you have some questions since the creation of this topic
You may also post up your tricks if you want to share with us

Alphoccio, Kiel, Rebel and 3 others reacted to this

Share this post

Link to post
Share on other sites

10 answers to this question

Recommended Posts

  • 0

AnnieRuru    958

AnnieRuru
Posted

1. When to use escape_sql script command

 

  ultramage said:

if the message contains " or ' or some such special character, then after it gets combined into a single string, it will not form a correct query

. 

	input .@haha$;
	dispbottom .@haha$;
	dispbottom escape_sql(.@haha$);

it doesn't has much differences, because it only affect 3 special characters
' <- single quotation mark
" <- double quotation mark
\ <- left slash

if I input -> haha"lala'hehe <-
it will return -> haha\"lala\'hehe <-
this is what we call, Escape a character

in hercules script, we also know we can use " symbol in any string input 

	mes "Susan says :\" Today I ate 3 eggs \".";

where in the game client, you can see the " symbol in the npc msg box

let's say I have a sql script like this 

prontera,153,171,5	script	Show Characters	1_F_MARIA,{
	mes "input name, I'll show you all characters name it has on that player's account";
	input .@name$;
	.@nb = query_sql("SELECT `char_id`, `name` FROM `char` WHERE `name` LIKE '"+ .@name$ +"'", .@cid, .@name$);
	if ( !.@nb ) {
		mes "no result";
		close;
	}
	for ( .@i = 0; .@i < .@nb; ++.@i )
		mes .@cid[.@i] +" "+ .@name$[.@i];
	close;
}

this script has a possibility to be hacked
because to perform sql injection, I can enclose the string with quotation mark, then use another sql command to hack

BUT with an escape_sql command, if the user want to enclose the string with quotation mark to hack the script
the escape_sql command escaped the string, the quotation mark the user input will be escaped
thus the script will become impossible to hack

just now that script was for string input 

prontera,153,171,5	script	Show Characters	1_F_MARIA,{
	mes "input account ID, I'll show you all characters name it has on that player's account";
	input .@aid$;
	.@nb = query_sql("SELECT `char_id`, `name` FROM `char` WHERE `account_id` = "+ escape_sql(.@aid$), .@cid, .@name$);
	if ( !.@nb ) {
		mes "no result";
		close;
	}
	for ( .@i = 0; .@i < .@nb; ++.@i )
		mes .@cid[.@i] +" "+ .@name$[.@i];
	close;
}

this is another stupid case.
1. the scripter use string input while the script just needed a number
2. even with escape_sql command over there, there is no quotation mark at all
yes this script also has a risk to be hack

because escape_sql only escape quotation mark.
that hacker don't even have to input quotation mark because it is a number
and an injection query can be sent without any quotation mark input

there are 2 ways to solve this
either use numeric variable for the input command
or enclose that .... 

..... WHERE `account_id` = '"+ escape_sql(.@aid$) +"'", ....

with single quotation mark, when the hacker input a quotation mark will be escaped by escape_sql command

Reference : https://www.w3schools.com/sql/sql_injection.asp

escape_sql command for another thing is
if the player register their names containing ' or ", these characters are escaped
only happens when the server have no restriction on the creation of players name

hercules\conf\char\char-server.conf 

// Manage possible letters/symbol in the name of charater. Control character (0x00-0x1f) are never accepted. Possible values are:
// NOTE: Applies to character, party and guild names.
// 0: no restriction (default)
// 1: only letters/symbols in 'name_letters' option.
// 2: Letters/symbols in 'name_letters' option are forbidden. All others are possibles.
name_option: 1

 

and this was what happened to my SQL dota pvpladder script

  Quote

I'm having this DB error with the script: 

[SQL]: DB error - You have an error in your SQL syntax; check the manual that co
rresponds to your MySQL server version for the right syntax to use near 's Babie
s', 1 , 1 )' at line 1
[Debug]: at ..\src\map\script.c:11553 - insert into ownladder ( guild_id, name,
currentown, highestown ) values ( 98, 'Silo's Babies', 1 , 1 )
[Debug]: Source (NPC): DOTAPVP (invisible/not on a map)

I'm using SVN 12077. But this error only happens when that certain guild would have a kill.

Silo's Babies <-- this is a guild name
you can see the 5th string has a single quotation mark

with escape_sql command, that string will turn into
Silo\'s Babies <-- the quotation mark is escaped when send to sql query

Cabrera, IndieRO, hurtsky and 2 others reacted to this

Share this post

Link to post
Share on other sites
  • 0

AnnieRuru    958

AnnieRuru
Posted

2. How to build a case-sensitive table

this is the answer I found
http://dba.stackexchange.com/questions/15250/how-to-do-a-case-sensitive-search-in-where-clause

by default, the table creation use charset = latin1;
means it couldn't do a case-sensitive search

if you want to do a case-sensitive in a query, use BINARY 

SELECT * FROM `char` WHERE `name` = BINARY('AnnieRuru');

however using BINARY might have performance hit if it is a big table
so its more recommend to convert your SQL table to collate with latin1_general_cs


let's say this is a sample table 

CREATE TABLE `test` (
`id` INT(11) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(23)
)ENGINE = InnoDB;

do an ALTER table syntax 

ALTER TABLE `test` MODIFY COLUMN `name` VARCHAR(23) COLLATE latin1_general_cs;

or just put it into the table creation 

CREATE TABLE `test` (
`id` INT(11) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(23)
)ENGINE = InnoDB DEFAULT CHARSET = latin1 COLLATE latin1_general_cs;

 

Rebel, IndieRO, hurtsky and 1 other reacted to this

Share this post

Link to post
Share on other sites
  • 0

AnnieRuru    958

AnnieRuru
Posted

3. Choose a table type, MyISAM or InnoDB ?

https://stackoverflow.com/questions/20148/myisam-versus-innodb

Before MySQL 5.5,
MyISAM is mostly use for read-heavy + table locking storage engine = such as pvp ladder ( always select ... order by kill desc )
InnoDB is mostly use for write-heavy + row locking storage engine = such as quest script ( select ... from char_id ... only 1 row is retrieve )

After MySQL 5.6, (currently is 8.0)
just stick to InnoDB

there is only 1 reason MyISAM is better than InnoDB
- MyISAM use smaller disk usage than InnoDB

let's take a look at our MyISAM to InnoDB converter
https://github.com/HerculesWS/Hercules/blob/stable/sql-files/tools/convert_engine_innodb.sql
This converter is useful if you are using MySQL 5.6 or above

There are 4 tables that are commented out
the reason is simple, these 4 tables only read once and forgotten when server is live
since MyISAM is good at reading (SELECT) + smaller disk usage, its no use to convert these 4 tables into InnoDB

3a How to index a table properly

http://mysql.rjweb.org/doc.php/index_cookbook_mysql
http://www.dbta.com/Columns/DBA-Corner/Top-10-Steps-to-Building-Useful-Database-Indexes-100498.aspx

a simple thumb of rule, anything that is SELECT .... WHERE `field` = .....
that `field` has to be index

let's take a look at this PVP Ladder script that use Kill/Death ratio 

CREATE TABLE `pvpladder` (
`char_id` INT(11),
`name` VARCHAR(23),
`kills` INT(11),
`death` INT(11),
PRIMARY KEY (`char_id`),
KEY (`kills`, `death`)
) ENGINE = InnoDB;
prontera,155,186,6	script	PVP Ladder	1_F_MARIA,{
	.@nb = query_sql( "SELECT `name`, `kills`/(`death`+1) FROM `pvpladder` WHERE `kills` > 0 ORDER BY `kills`/(`death`+1) DESC LIMIT 10", .@name$, .@ratio$ );
	if ( !.@nb ) {
		mes "no entry";
		close;
	}
	mes "Current Ranking :";
	for ( .@i = 0; .@i < .@nb; ++.@i )
		mes "No."+(.@i +1)+" ["+ .@name$[.@i] +"] "+ .@ratio$[.@i] +" kill";
	close;
OnPCKillEvent:
	if ( killedrid == getcharid(3) ) { // killing self should only increase death count. EG: Grand-cross
		query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 0,1 ) ON DUPLICATE KEY UPDATE `death` = `death` +1";
		end;
	}
	query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 1,0 ) ON DUPLICATE KEY UPDATE `kills` = `kills` +1";
	attachrid killedrid;
	query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 0,1 ) ON DUPLICATE KEY UPDATE `death` = `death` +1";
	end;
}

This kind of query -> ORDER BY kills/death, needs to index them together like this 

KEY (`kills`, `death`)

3b. Why you shouldn't use `char_reg_num_db` table

blame Euphy for spreading this technique

There are 2 reasons why you shouldn't even touch all these variable tables

Reason no.1 This table is sorely meant for server usage
Once these data is loaded, it is process internally, and only save character data according to this configuration

  Reveal hidden contents

Reason no.2 The `value` field is not index !

  Hunting Mission Line 146 said:

query_sql("SELECT char_id AS id, (SELECT `name` FROM `char` WHERE char_id = id),`value` FROM `char_reg_num_db` WHERE `key` = 'Mission_Total' ORDER BY `value` DESC LIMIT 5",.@id,.@name$,.@val);

This line has ORDER BY `value`, try recheck our main.sql file 

CREATE TABLE IF NOT EXISTS `acc_reg_num_db` (
  `account_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  `key` VARCHAR(32) BINARY NOT NULL DEFAULT '',
  `index` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  `value` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`account_id`,`key`,`index`),
  KEY `account_id` (`account_id`)
) ENGINE=MyISAM;

SQL will search through every single line in the `value` field if that column isn't index

Of course you can ... do ALTER table to add KEY to the `value` field
but this table has already optimized in that way for server usage
the more field you index into the table, the more disk usage space it use

Conclusion : If you want to make a custom script, then make a custom table. Leave these table alone !

IndieRO, Quazi, bWolfie and 2 others reacted to this

Share this post

Link to post
Share on other sites
  • 0

AnnieRuru    958

AnnieRuru
Posted (edited)

4. AUTO_INCREMENT 

CREATE TABLE `support_ticket` (
`id` INT(11) AUTO_INCREMENT,
`title` VARCHAR(70),
`message` VARCHAR(255),
PRIMARY KEY (`id`)
) ENGINE = InnoDB;

In this kind of query that has AUTO_INCREMENT, many people do .... 

	$support_ticket_id++;
	query_sql "INSERT INTO `support_ticket` VALUES ( "+ $support_ticket_id +", '"+ escape_sql(.@title$) ...

can be optimize .... using NULL 

	query_sql "INSERT INTO `support_ticket` VALUES ( NULL, '"+ escape_sql(.@title$) ...

can retrieve the last row with 

	query_sql "SELECT MAX(`id`) FROM `support_ticket`", .@id;
//	----- OR -----
	query_sql "SELECT LAST_INSERT_ID()", .@id;

 

Question : This question was asked on eathena forum board

One of my friend touched my custom table and the AUTO_INCREMENT has jump off the value 

|   1   | <data set 1>
|   2   | <data set 2>
|   3   | <data set 3>
| 25854 | <data set 4>
| 25855 | <data set 5>
| 25856 | <data set 6>

I want to make the value return back to normal as shown 

|   1   | <data set 1>
|   2   | <data set 2>
|   3   | <data set 3>
|   4   | <data set 4>
|   5   | <data set 5>
|   6   | <data set 6>

How to do this WITHOUT losing any of the current data ?

Answer: The trick is ... just drop that column and rebuild it 

ALTER TABLE `inventory` DROP COLUMN `id`;
ALTER TABLE `inventory` ADD COLUMN `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

convert the table into MyISAM will process the query much faster

Edited by AnnieRuru
IndieRO, Radian, hurtsky and 1 other reacted to this

Share this post

Link to post
Share on other sites
  • 0

AnnieRuru    958

AnnieRuru
Posted (edited)

5. How to do IF-ELSE in SQL query ?

Question : I have a PVP ladder script that runs on Points system. Each kill plus 1 point and each death minus 1 point.
The problem is, this query will make the points go into negative value if the player is being kill repeatedly 

query_sql "UPDATE `pvp_points` SET `points` = `points` - 1 WHERE `char_id` = "+ getcharid(0);

How do I make the points stop at 0 if the player is already at 0 points ?

 

Answer : 

query_sql "UPDATE `pvp_points` SET `points` = IF(`points` = 0, 0, `points` - 1) WHERE `char_id` = "+ getcharid(0);
query_sql "UPDATE `pvp_points` SET `points` = (CASE WHEN `points` = 0 THEN 0 ELSE `points` - 1 END) WHERE `char_id` = "+ getcharid(0);

 

Explanations:

similar to hercules script language, 

	if (<condition>)
		<execute true condition>;
	else
		<execute false condition>;

in SQL language 

	IF(<condition>, <execute true condition>, <execute false condition>)
CASE WHEN <condition>
	THEN <execute true condition>
	ELSE <execute false condition>
END

 

Reference : https://www.w3schools.com/sql/func_mysql_if.asp

https://stackoverflow.com/questions/63447/how-do-i-perform-an-if-then-in-an-sql-select

5a. How to update multiple rows on different conditions in a single query

This query will update multiple rows on different condition 

UPDATE `pvpladder` SET `points` = 
CASE
	WHEN `char_id` = 150000 THEN `points` +1
	WHEN `char_id` = 150001 THEN `points` -1
END
WHERE `char_id` IN (150000,150001);

Reference : https://stackoverflow.com/questions/20255138/sql-update-multiple-records-in-one-query

Edited by AnnieRuru
IndieRO, hurtsky, Rebel and 1 other reacted to this

Share this post

Link to post
Share on other sites
  • 0

AnnieRuru    958

AnnieRuru
Posted (edited)

6. How to show the current rank of the player

  Reveal hidden contents

Question : This is the part of the script, output as below 

.@nb = query_sql("SELECT `name`, `kills` FROM `pvpladder` ORDER BY `kills` DESC LIMIT 5", .@name$, .@kills);
for ( .@i = 0; .@i < .@nb; .@i++ )
	mes "No."+(.@i+1)+" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills";
No.1 [Alice] ~ 19 kills
No.2 [Emily] ~ 11 kills
No.3 [Irene] ~ 11 kills
No.4 [Brittany] ~ 8 kills
No.5 [Fiona] ~ 7 kills

 2nd place and 3rd place has the same amount of kills, how do I make both of them display as 2nd place like this ? 

No.1 [Alice] ~ 19 kills
No.2 [Emily] ~ 11 kills
No.2 [Irene] ~ 11 kills
No.4 [Brittany] ~ 8 kills
No.5 [Fiona] ~ 7 kills

 

Answer :

Method no.1: Convert the table into InnoDB will return the result faster. Allow to use OFFSET 

.@nb = query_sql("SELECT `name`, `kills`, FIND_IN_SET(`kills`, (SELECT GROUP_CONCAT(`kills` ORDER BY `kills` DESC) FROM `pvpladder`)) FROM `pvpladder` ORDER BY `kills` DESC LIMIT 5", .@name$, .@kills, .@rank);
for ( .@i = 0; .@i < .@nb; ++.@i )
	mes "No."+ .@rank[.@i] +" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills";

Method no.2: This method return result faster than method 1 in huge table. Not allow to use OFFSET 

.@query$  = "SELECT `name`, IF(@d=t.`kills`, @r, @r:=@i), @d:=t.`kills`, @i:=@i+1 ";
.@query$ += "FROM `pvpladder` t, (SELECT @d:=0, @r:=0, @i:=1)q ";
.@query$ += "ORDER BY `kills` DESC LIMIT 5";
.@nb = query_sql(.@query$, .@name$, .@rank, .@kills, .@dummy);
for ( .@i = 0; .@i < .@nb; ++.@i )
	mes "No."+ .@rank[.@i] +" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills";

.

.

Question : How do I show the current ranking of the player ? 

	mes "Your kills -> "+ .@kills;
	mes "Your rank -> "+ .@rank;

Answer : 

query_sql "SELECT `kills`, 1+(SELECT COUNT(1) FROM `pvpladder` t1 WHERE t1.`kills` > t2.`kills`) FROM `pvpladder` t2 WHERE `char_id` = "+ getcharid(0), .@kills, .@rank;

Remember to index the `kills` field

 

Reference : https://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table

.

  Reveal hidden contents

 

Edited by AnnieRuru
Bringer, Radian, Rebel and 1 other reacted to this

Share this post

Link to post
Share on other sites
  • 0

AnnieRuru    958

AnnieRuru
Posted (edited)

7. INSERT INTO ... SELECT ...

Question : This question was asked on eathena forum . Note: at the time the old mail system was unstable and not many server use it
I want to reward my players for supporting my server.
I want to give every player who are still actively login in this year (2010) an item ID 22574 in their storage
How do I achieve this ?

Answer : Run this SQL command when your server is offline 

INSERT INTO `storage` (`account_id`, `nameid`, `amount`, `identify`)
SELECT `account_id`, '22574', '1', '1'
FROM `login`
WHERE DATE(`lastlogin`) >= '2010-01-01' && `account_id` !=1;

The reply from the topic starter gives a feed back, including this 

		Inserted rows: 7738
		Inserted row id: 8244859 (Query took 13.6253 sec)

 

  Reveal hidden contents

 

Edited by AnnieRuru

Share this post

Link to post
Share on other sites
  • 0

AnnieRuru    958

AnnieRuru
Posted

8. Table JOIN vs AS 

CREATE TABLE `pvpladder` (
`char_id` int(11) PRIMARY KEY,
`points` int(11)
) ENGINE = InnoDB;

This table is missing the `name` field. So have to retrieve the `name` from the `char` table.

 

A simple way is using table JOIN 

SELECT `char`.`char_id`, `char`.`name` , `pvpladder`.`points`
FROM `pvpladder` LEFT JOIN `char` ON `pvpladder`.`char_id` = `char`.`char_id`
ORDER BY `pvpladder`.`points` DESC LIMIT 10;

However, there is an uncommon method, the same thing can be done using Aliases 

SELECT `char_id` AS `CID`, (SELECT `name` FROM `char` WHERE `char_id` = `CID`), `points`
FROM `pvpladder` ORDER BY `points` DESC LIMIT 10;

In this example, both tables `char` and `pvpladder` have the `char_id` field index as PRIMARY KEY
and thus both examples return the result in same amount of time

 

However, there is a key difference on the optimization speed if the one of the table is not index properly
The below example, `item_id`.`id` field is index as PRIMARY KEY, but `mob_db`.`DropCardid` is not index 

SELECT `mob_db`.`ID`, `mob_db`.`kname`, `item_db`.`id`, `item_db`.`name_japanese`
FROM `mob_db` LEFT JOIN `item_db` ON `mob_db`.`DropCardid` = `item_db`.`id`
WHERE `mob_db`.`DropCardid` > 0;
SELECT `ID`, `kname`, `DropCardid` AS `ITEM_ID`, (SELECT `name_japanese` FROM `item_db` WHERE `id` = `ITEM_ID`)
FROM `mob_db` WHERE `mob_db`.`DropCardid` > 0;

The 2nd query that uses Aliases will return result faster than table JOIN in this case

Conclusion : If use table JOIN, you have to keep in mind that the joined column has to be index
but if use AS, there is no need to consider this issue

Share this post

Link to post
Share on other sites
  • 0

AnnieRuru    958

AnnieRuru
Posted (edited)

9. What is the maximum string limit for *query_sql

Since query_sql sending the query as a string, we can actually use string manipulation script commands such as *sprintf and *implode

Example : *sprintf

  Reveal hidden contents

Example : *implode

  Reveal hidden contents

So, someone might ask, what is the string limit for query_sql until the map-server show error ?

  Reveal hidden contents

The answer is very surprising ... I just tested with above script, and it still works perfectly fine !
loop -> 64.210 seconds
query -> 3.229 seconds

it means, ahh .... or maybe ...

The answer might be ... 

[SQL]: DB error - MySQL server has gone away
[Debug]: showmsg: dynamic buffer used, increase the static buffer size to 4306185 or more.

until MySQL stop responding XD

 

  Reveal hidden contents

 

9a. UNION

Just now the *implode example, it just shows the `mvp_id`, but if we want to display the information like this, 

+--------+------------------+------------+-----------------------+
| MVP_ID |     MVP_NAME     | DropCardid |     MVP_CARD_NAME     |
+--------+------------------+------------+-----------------------+
|  1086  | Golden Thief Bug |    4128    | Golden Thiefbug Card  |
|  1115  |      Eddga       |    4123    |      Eddga Card       |
|  1150  | Moonlight Flower |    4131    | Moonlight Flower Card |

 

  Reveal hidden contents 
	for ( .@i = 0; .@i < .@size; ++.@i )
		.@values$[.@i] = "( "+ .@mvpid[.@i] +", (SELECT `kname` FROM `mob_db` WHERE ID = "+ .@mvpid[.@i] +"), (SELECT `DropCardid` FROM `mob_db` WHERE ID = "+ .@mvpid[.@i] +"), (SELECT `item_db`.`name_japanese` FROM `item_db` LEFT JOIN `mob_db` ON `item_db`.`id` = `mob_db`.`DropCardid` WHERE `mob_db`.`ID` = "+ .@mvpid[.@i] +") )";
	query_sql "INSERT IGNORE INTO `mvp_table` VALUES "+ implode( .@values$, ", " );

This part ... can be optimize with UNION 

	for ( .@i = 0; .@i < .@size; ++.@i )
		.@values$[.@i] = "SELECT "+ .@mvpid[.@i] +", `kname`, `DropCardid` AS `MVP_CARD`, (SELECT `name_japanese` FROM `item_db` WHERE `id` = `MVP_CARD`) FROM `mob_db` WHERE ID = "+ .@mvpid[.@i];
	query_sql "INSERT IGNORE INTO `mvp_table` "+ implode( .@values$, " UNION " );

DONE ---- FINALLY !! 2 weeks to get this guide done XD

Yes, I knew the chapter 5 and beyond is very tough to understand ... it also took me longer time to write and test all these advance SQL techniques too

Anyway, this topic is now open to Suggestions, Ideas, Improvements, and Questions ~
I'm sure many of you have some questions since the creation of this topic
You may also post up your tricks if you want to share with us

Edited by AnnieRuru

Share this post

Link to post
Share on other sites
  • 0

AnnieRuru    958

AnnieRuru
Posted

Question: How to generate a random number in SQL
Answer: sometimes I think member just doesn't want to use google ...

  

rand(a,b)
 ->
SELECT FLOOR(RAND()*(b-a+1))+a;

Where a is the smallest number and b is the largest number that you want to generate a random number for. 

rand(10,25)
 ->
SELECT FLOOR(RAND()*(25-10+1))+10;

 

Question: Sometimes I get this error

  Reveal hidden contents 
[SQL]: DB error - BIGINT UNSIGNED value is out of range in '(`hercules`.`pvpladder`.`points` - 10)'
[Debug]: at d:\ragnarok\hercules\src\map\script.c:17495 - INSERT INTO `pvpladder` VALUE ( 150001, 'AnnieRuru', 0 ) ON DUPLICATE KEY UPDATE `points` =  IF( `points` - 10 < 0, 0, `points` - 10)
[Debug]: Source (NPC): PvP Ladder at prontera (155,185)

with the attached script, `pvpladder` table does not has any column with BIGINT field ... why ?

 

Answer: drop the UNSIGNED from the `points` field

even if you don't want to save the data in negative value, but the engine still needs to do calculation for it

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
Go To Question Listing
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.

  I accept