SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help

Reply
 
Thread Tools Display Modes
Old 01/08/2020, 03:49 PM   #1
Leaky
Big Clucker
 
Join Date: Jul 2018
Posts: 82
Reputation: 12
Default Fixing MySQL log Error

I have been teaching myself mysql for a couple weeks now and it's going fine but I check my logs and say some errors:
PHP Code:
[10:48:19] [ERRORcache_get_value_index_intinvalid row ('0') or field ('0'index
[10:48:19] [ERRORcache_get_value_index_intinvalid row ('0') or field ('1'index
[10:48:19] [ERRORcache_get_value_nameinvalid row index '0' (number of rows'0')
[
10:48:19] [ERRORcache_get_value_nameinvalid row index '0' (number of rows'0')
[
10:48:19] [ERRORcache_get_value_nameinvalid row index '0' (number of rows'0'
But I don't know what seems to be the issue. I figured out when it occurs though, it seems to send me those errors when a ban account rejoins, when it retrieve the saved info from the DB and send it out.

My could for getting the saved info
PHP Code:

    mysql_format
(handlequerysizeof(query), "SELECT Ban_Name, Ban_IP, Ban_By, Ban_Reason, Ban_Date, Ban_Type FROM `ban_acc` WHERE (`Ban_Name` = '%e' OR `Ban_IP` = '%e') LIMIT 0, 1"GetName(playerid), RPIP(playerid));
    
mysql_tquery(handlequery);

    new 
Cache:result mysql_query(handlequery);
    
cache_get_value_name(0"Ban_Name"Username);
    
cache_get_value_name(0"Ban_IP"IP);
    
cache_get_value_name(0"Ban_By"BannedBy);
    
cache_get_value_name(0"Ban_Reason"BanReason);
    
cache_get_value_name(0"Ban_Date"Date);
       
cache_get_value_name_int(0"Ban_Type"PlayerInfo[playerid][BanType]); 
__________________
Leaky is offline   Reply With Quote
Old 01/08/2020, 05:55 PM   #2
Kwarde
High-roller
 
Kwarde's Avatar
 
Join Date: Nov 2009
Location: The Netherlands
Posts: 2,740
Reputation: 1686
Default Re: Fixing MySQL log Error

When you see "invalid row index '0'" you can assume there is no row 0 (and thus no results).
Start debugging by printing the query to the server log/console.
Since there are no errors of a fault in your query, the query simply returns no results.
Also:
pawn Code:
mysql_tquery(handle, query);

    new Cache:result = mysql_query(handle, query);
What's up with the mysql_tquery(); ? Using a SELECT query in a threaded query without specifying what callback to call (or eventually using an inline function, which I prefer) it's very useless.

EDIT:
If this is a query that is always used (eg. under OnPlayerConnect), you should check if there are rows before proceeding to attempt to fetch data. --If someone is not banned there are no rows, and thus no values to fetch.

EDIT 2:
Example:
pawn Code:
hook OnPlayerConnect(playerid)
{
    //(...)
    mysql_format(handle, query, sizeof(query), "SELECT Ban_Name, Ban_IP, Ban_By, Ban_Reason, Ban_Date, Ban_Type FROM `ban_acc` WHERE (`Ban_Name` = '%e' OR `Ban_IP` = '%e') LIMIT 0, 1", GetName(playerid), RPIP(playerid));

    new Cache:result = mysql_query(handle, query);

    new num_rows;
    cache_get_row_count(num_rows);
    if (num_rows) //OR, if you don't need to re-use the num_rows like here, just use: if (cache_num_rows())
    {
        //Fetch all data and do whatever you want to do
        cache_delete(result);
    }
    return Y_HOOKS_CONTINUE_RETURN_1;
}
This will prevent the error logs
__________________
When the opportunity presents itself to flip-a da table, uh, you flip-a da table.

Discord: Kwarde#8009
Kwarde is offline   Reply With Quote
Old 03/08/2020, 03:52 AM   #3
Leaky
Big Clucker
 
Join Date: Jul 2018
Posts: 82
Reputation: 12
Default Re: Fixing MySQL log Error

Quote:
Originally Posted by Kwarde View Post
When you see "invalid row index '0'" you can assume there is no row 0 (and thus no results).
Start debugging by printing the query to the server log/console.
Since there are no errors of a fault in your query, the query simply returns no results.
Also:
pawn Code:
mysql_tquery(handle, query);

    new Cache:result = mysql_query(handle, query);
What's up with the mysql_tquery(); ? Using a SELECT query in a threaded query without specifying what callback to call (or eventually using an inline function, which I prefer) it's very useless.

EDIT:
If this is a query that is always used (eg. under OnPlayerConnect), you should check if there are rows before proceeding to attempt to fetch data. --If someone is not banned there are no rows, and thus no values to fetch.

EDIT 2:
Example:
pawn Code:
hook OnPlayerConnect(playerid)
{
    //(...)
    mysql_format(handle, query, sizeof(query), "SELECT Ban_Name, Ban_IP, Ban_By, Ban_Reason, Ban_Date, Ban_Type FROM `ban_acc` WHERE (`Ban_Name` = '%e' OR `Ban_IP` = '%e') LIMIT 0, 1", GetName(playerid), RPIP(playerid));

    new Cache:result = mysql_query(handle, query);

    new num_rows;
    cache_get_row_count(num_rows);
    if (num_rows) //OR, if you don't need to re-use the num_rows like here, just use: if (cache_num_rows())
    {
        //Fetch all data and do whatever you want to do
        cache_delete(result);
    }
    return Y_HOOKS_CONTINUE_RETURN_1;
}
This will prevent the error logs
Sorry for the late reply but I fixed all the query and also I had checked for the rows before fetching the data but I still get the errors in the MySQL logs.

PHP Code:
    mysql_format(handlequerysizeof(query), "SELECT Ban_Name, Ban_IP, Ban_By, Ban_Reason, Ban_Date, Ban_Type, Ban_Time, Acc_Hours FROM `ban_acc` WHERE (`Ban_Name` = '%e' OR `Ban_IP` = '%e') LIMIT 0, 1"GetName(playerid), RPIP(playerid));
    
mysql_query(handlequery);

    new 
Cache:result mysql_query(handlequery);
    
cache_get_row_count(rows);

    if(
rows)
    {
    
cache_get_value_name(0"Ban_Name"Username);
    
cache_get_value_name(0"Ban_IP"IP);
    
cache_get_value_name(0"Ban_By"BannedBy);
    
cache_get_value_name(0"Ban_Reason"BanReason);
    
cache_get_value_name(0"Ban_Date"Date);
    
cache_get_value_name_int(0"Ban_Type"PlayerInfo[playerid][BanType]);
    
cache_get_value_name_int(0"Ban_Time"PlayerInfo[playerid][BanTime]);
    
cache_get_value_name_int(0"Acc_Hours"PlayerInfo[playerid][AccHrs]);
//Rest of code which checks and sends the player information regarding their ban.
cache_delete(result);     

__________________
Leaky is offline   Reply With Quote
Old 03/08/2020, 06:21 AM   #4
Kursed
Huge Clucker
 
Kursed's Avatar
 
Join Date: Jan 2015
Posts: 236
Reputation: 35
Default Re: Fixing MySQL log Error

Code:
if(cache_num_rows() > 0)
{
    cache_get_value_index(0, 0, Username);
    cache_get_value_index(0, 1, IP);
    cache_get_value_index(0, 2, BannedBy);
    cache_get_value_index(0, 3, BanReason);
    cache_get_value_index(0, 4, Date);
    cache_get_value_index_int(0, 5, PlayerInfo[playerid][BanType]);
    cache_get_value_index_int(0, 6, PlayerInfo[playerid][BanTime]);
    cache_get_value_index_int(0, 7, PlayerInfo[playerid][AccHrs]);   
} 
cache_delete(result);
__________________

Founder of Darkside: Darkside - 144.217.19.104:7777
Kursed is offline   Reply With Quote
Old 03/08/2020, 11:38 AM   #5
Kwarde
High-roller
 
Kwarde's Avatar
 
Join Date: Nov 2009
Location: The Netherlands
Posts: 2,740
Reputation: 1686
Default Re: Fixing MySQL log Error

Then are you sure that the error is there?
When using an "if (variable)" statement, it checks if the value is not 0 (so using "if (cache_num_rows() > 0)" does exactly the same as "if (cache_num_rows())" @Kursed). If there are no rows it would never run the cache_get_value*() functions.
__________________
When the opportunity presents itself to flip-a da table, uh, you flip-a da table.

Discord: Kwarde#8009
Kwarde is offline   Reply With Quote
Old 03/08/2020, 03:06 PM   #6
Kursed
Huge Clucker
 
Kursed's Avatar
 
Join Date: Jan 2015
Posts: 236
Reputation: 35
Default Re: Fixing MySQL log Error

Quote:
Originally Posted by Kwarde View Post
Then are you sure that the error is there?
When using an "if (variable)" statement, it checks if the value is not 0 (so using "if (cache_num_rows() > 0)" does exactly the same as "if (cache_num_rows())" @Kursed). If there are no rows it would never run the cache_get_value*() functions.
Yeah, I know that but adding '> 0' is not a big mistake. About the code, i hope that works.
__________________

Founder of Darkside: Darkside - 144.217.19.104:7777
Kursed is offline   Reply With Quote
Old 03/08/2020, 03:12 PM   #7
Leaky
Big Clucker
 
Join Date: Jul 2018
Posts: 82
Reputation: 12
Default Re: Fixing MySQL log Error

Quote:
Originally Posted by Kwarde View Post
Then are you sure that the error is there?
When using an "if (variable)" statement, it checks if the value is not 0 (so using "if (cache_num_rows() > 0)" does exactly the same as "if (cache_num_rows())" @Kursed). If there are no rows it would never run the cache_get_value*() functions.
Yes, the error is really there, I cleared the MySQL logs and then retry and the same errors showed up.
__________________
Leaky is offline   Reply With Quote
Old 03/08/2020, 03:38 PM   #8
Kwarde
High-roller
 
Kwarde's Avatar
 
Join Date: Nov 2009
Location: The Netherlands
Posts: 2,740
Reputation: 1686
Default Re: Fixing MySQL log Error

Sorry, when I meant "that the error is there", I meant are you absolutely sure they refer to the ban system? If there are no rows the cache_get functions aren't used so they are definitely somewhere else in the code. Either that or the errors are different than* before.
__________________
When the opportunity presents itself to flip-a da table, uh, you flip-a da table.

Discord: Kwarde#8009

Last edited by Kwarde; 04/08/2020 at 12:49 AM. Reason: then ==> than
Kwarde is offline   Reply With Quote
Old 04/08/2020, 12:24 AM   #9
Leaky
Big Clucker
 
Join Date: Jul 2018
Posts: 82
Reputation: 12
Default Re: Fixing MySQL log Error

Quote:
Originally Posted by Kwarde View Post
Sorry, when I meant "that the error is there", I meant are you absolutely sure they refer to the ban system? If there are no rows the cache_get functions aren't used so they are definitely somewhere else in the code. Either that or the errors are different then before.
So I fixed it and your last suggestion was indeed the problem. But I need help with 1 last thing, I am trying to save IP address, could you tell me where did I go wrong in my code:

PHP Code:
    format(PlayerInfo[playerid][Last_IP_Address], 16"%s"RPIP(playerid));
    
mysql_format(handlequerysizeof(query), "INSERT INTO `users` (`Last_IP_Address`) VALUES ('%e') WHERE Name = '%s'",PlayerInfo[playerid][Last_IP_Address], GetName(playerid));
    
mysql_query(handlequery); 
The issue is, it's collecting the IP but it's now saving it in the table.
__________________
Leaky is offline   Reply With Quote
Old 04/08/2020, 12:42 AM   #10
Kwarde
High-roller
 
Kwarde's Avatar
 
Join Date: Nov 2009
Location: The Netherlands
Posts: 2,740
Reputation: 1686
Default Re: Fixing MySQL log Error

You are trying to insert (or inserting -if all columns may be NULL (thus NOT NULL not used)) a new row instead of updating the data of a row.

Also may I suggest you to store the IP as an integer?
Code:
CREATE TABLE isers(
    (columns...)
    Last_IP_Address INT UNSIGNED DEFAULT NULL, -- DEFAULT NULL so it doesn't need to be specified in the CREATE query
    (columns...)
) ENGINE = InnoDB;
Storing an IP:
pawn Code:
format(..., "UPDATE users SET Last_IP_Address = INET_ATON('%s') WHERE Name = '%s'", PlayerInfo[playerid][Last_IP_Address], GetName(playerid));
//or in insert ofc
format(..., "INSERT INTO users (..., IP_Address) VALUES (..., INET_ATON('%s') WHERE Name = '%s'", PlayerInfo[playerid][Last_IP_Address], GetName(playerid));
Fetching the IP:
pawn Code:
format(..., "SELECT INET_NTOA(IP_Address) AS IP_Address FROM users WHERE Name = '%s'", GetName(playerid));
new Cache:result = mysql_query(handle, query), ip[16];
cache_get_value_name(0, "IP_Address", ip);
INET_ATON changes an IP to an integer (ATON => Address To Number)
INET_NTOA changes an integer back to an IP (NTOA => Number To Address)
Column needs to be an INT UNSIGNED (some IP addressed otherwise would fall out of range).

I used "AS IP_Address" to make the result (cache) return it as IP_Address. Otherwise you would literally have to use "INET_NTOA(IP_Address)" in cache_get_value_name().
If you would want to select all data from a table you'd best approach it as:
Code:
SELECT *, INET_NTOA(Last_IP_Address) AS Last_IP_Address_Readable FROM users WHERE Name = 'someName';
Otherwise you'd have to write down all columns manually. This would return the IP (the readable ony) as Last_IP_Address_Readable. This method is faster and more efficient (and using less space) than storing whole strings.


EDIT:
Quote:
I know that but adding '> 0' is not a big mistake
Overlooked this. I never said it was a mistake (it is not at all). Just merely stating the fact it is not needed persay (some people don't seem to know this). Perhaps it's a personal thing but I don't like typing things if they're not needed.
To me, that's like doing:
pawn Code:
new bool:someBool = !!true;
new someInteger = ~~5;
It does exactly the same as (below) but it just has some extra unneeded characters.
pawn Code:
new bool:someBool = true;
new someInteger = 5;
__________________
When the opportunity presents itself to flip-a da table, uh, you flip-a da table.

Discord: Kwarde#8009
Kwarde is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Please Help Me Fixing This Error WatchDog Server Support 23 31/03/2016 10:01 AM
I need help fixing this error Hakan Scripting Help 1 09/08/2014 05:20 AM
Help Fixing this error Zin Scripting Help 4 07/03/2014 11:03 AM
Need some help fixing this Error Blessed Server Support 0 22/05/2012 12:18 PM
[Help]Fixing A little Error Infinity90 Scripting Help 2 23/04/2012 05:52 PM


All times are GMT. The time now is 05:51 PM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.