SA-MP Forums Archive
MySQL no active cache issue - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: MySQL no active cache issue (/showthread.php?tid=675311)


MySQL no active cache issue - Adamoneoone - 09.05.2020

Hello everybody, and before any of you go keyboard warrior mode, i did actually research it on ****** and found at least a dozen of posts, none of which actually helped my solve my issue, that's why I'm here.

Using the following code, i get the no active cache issue (cache_get_row_count: no active cache):
pawn Code:
LoadAvailableBounties(playerid)
{
    new DB_Query[256];
    mysql_format(Database, DB_Query, sizeof(DB_Query), "SELECT * FROM `player_bounties`");
    mysql_tquery(Database, DB_Query, "LoadBounties", "i", playerid);
    printf("%s", DB_Query); // the query is printed correctly
}

forward LoadBounties(playerid);
public LoadBounties(playerid)
{
    if(cache_num_rows()>0) // the error line is here and fairly enough, only the first entry is shown in the dialog
    {
        new target, hitprice;
        for(new i; i<cache_num_rows(); i++)
        {
            cache_get_value_int(i, "hitid", target);
            cache_get_value_int(i, "amount", hitprice);
            // some more code not mysql related
        }
    }
    return 1;
}

CMD:bounties(playerid, params[])
{
    LoadAvailableBounties(playerid);
    return 1;
}
Thanks a lot!

Re: MySQL no active cache issue - BaFFyJunior - 09.05.2020

Code:
LoadAvailableBounties(playerid)
{
    new DB_Query[256];
    mysql_format(Database, DB_Query, sizeof(DB_Query), "SELECT * FROM `player_bounties`");
    mysql_tquery(Database, DB_Query, "LoadBounties", "i", playerid);
    printf("%s", DB_Query); // the query is printed correctly
}

forward LoadBounties(playerid);
public LoadBounties(playerid)
{
    if(cache_num_rows()>0) // the error line is here and fairly enough, only the first entry is shown in the dialog
    {
        new target, hitprice;
        for(new i; i<cache_num_rows(); i++)
        {
            cache_get_value_int(i, "hitid", target);
            cache_get_value_int(i, "amount", hitprice);
            // some more code not mysql related
        }
    }
    return 1;
}

CMD:bounties(playerid, params[])
{
    LoadAvailableBounties(playerid);
    return 1;
}
PHP Code:
LoadAvailableBounties(playerid)
{
    new DB_Query[450];
    mysql_format(Database, DB_Query, sizeof(DB_Query), "SELECT * FROM `player_bounties`");
    mysql_tquery(Database, DB_Query, "LoadBounties", "i", playerid);
    printf("%s", DB_Query);
}
function LoadBounties(playerid)
{
    new Rows;
    cache_get_row_count(Rows);
   
    if(!Rows) 
         return true;
 
    new target, hitprice;
    for(new i; i<Rows; i++)
    {
        cache_get_value_int(i, "hitid", target);
        cache_get_value_int(i, "amount", hitprice);
        // some more code not mysql related
    }
    return 1;
} 
Try that maybe. Also, what is player_bounties and what you want to get from that table? If you wanna get information related to the player typing command bounties use:
PHP Code:
LoadAvailableBounties(playerid)
{
    new DB_Query[256];
    mysql_format(Database, DB_Query, sizeof(DB_Query), "SELECT * FROM `player_bounties` WHERE `ID` = %d", PlayerInfo[playerid][SQLID]); // replace playerinfo with your player variables and `ID` to the id column in your table. Hope you understood.
    mysql_tquery(Database, DB_Query, "LoadBounties", "i", playerid);
    printf("%s", DB_Query); // the query is printed correctly
} 


Re: MySQL no active cache issue - Adamoneoone - 09.05.2020

The function has to be forwarded afaik, idk if function does that. You've simply rewritten cache_num_rows into cache_get_row_count which is where the error comes from...
Quote:

WHERE `ID` = %d
as you've written is useless as i'm trying to get everything from the table.
Thanks for the effort though.

Re: MySQL no active cache issue - Calisthenics - 09.05.2020

If the cache was not active, `LoadBounties` would not be called. But I have seen it again I think, for an unknown reason `cache_num_rows` returned different results according to that author.

For now enable log ALL and use this:
pawn Code:
new rows = cache_num_rows();

if(rows>0)
{ 
    new target, hitprice;

    for(new i; i<rows; i++)


Re: MySQL no active cache issue - Adamoneoone - 09.05.2020

Thanks for the reply, i did that and it shows the correct amount of lines, however now the data shown isn't correct (the first line is duped) and i get the following in my logs:
cache_get_value_name_int: no active cache

pawn Code:
new rows;
    cache_get_row_count(rows);
    if(rows)
    {
        new target, hitprice;
        for(new i; i<rows; i++)
        {
            cache_get_value_int(i, "hitid", target);
            cache_get_value_int(i, "amount", hitprice);


Re: MySQL no active cache issue - Calisthenics - 09.05.2020

Might be an issue with how you display the data? Post full mysql logs and code.

Re: MySQL no active cache issue - Adamoneoone - 09.05.2020

This is the logs:
Quote:

[15:25:13] [DEBUG] mysql_format(1, 0x2289C748, 256, "SELECT * FROM `player_bounties`") (core/assassinations/bounties.pwn:45 -> core/assassinations/bounties.pwn:73)
[15:25:13] [DEBUG] mysql_format: return value: '31' (core/assassinations/bounties.pwn:45 -> core/assassinations/bounties.pwn:73)
[15:25:13] [DEBUG] mysql_tquery(1, "SELECT * FROM `player_bounties`", "LoadBounties", "i") (core/assassinations/bounties.pwn:46 -> core/assassinations/bounties.pwn:73)
[15:25:13] [DEBUG] CCallback::Create(amx=0xf4f8320, name='LoadBounties', format='i', params=0x2289c730, param_offset=5)
[15:25:13] [DEBUG] CCallback::Create - callback index for 'LoadBounties': 48
[15:25:13] [DEBUG] processing specifier 'i' with parameter index 0
[15:25:13] [DEBUG] retrieved and pushed value '0'
[15:25:13] [INFO] Callback 'LoadBounties' set up for delayed execution.
[15:25:13] [DEBUG] created delayed callback with 1 parameter
[15:25:13] [DEBUG] CHandle::Execute(this=0x992a770, type=1, query=0x8948e30)
[15:25:13] [DEBUG] CHandle::Execute - return value: true
[15:25:13] [DEBUG] mysql_tquery: return value: '1' (core/assassinations/bounties.pwn:46 -> core/assassinations/bounties.pwn:73)
[15:25:13] [DEBUG] CConnection::Execute(query=0x8948e30, this=0xfda3020, connection=0x1854a42
[15:25:13] [DEBUG] CQuery::Execute(this=0x8948e30, connection=0x1854a42
[15:25:13] [INFO] query "SELECT * FROM `player_bounties`" successfully executed within 24.439 milliseconds
[15:25:13] [DEBUG] CResultSet::Create(connection=0x1854a428, query_str='SELECT * FROM `player_bounties`')
[15:25:13] [DEBUG] created new resultset '0x25d60788'
[15:25:13] [DEBUG] fetched MySQL result '0x87fe4e8'
[15:25:13] [DEBUG] allocated 72 bytes for PAWN result
[15:25:13] [DEBUG] CCallback::Execute(amx=0xf4f8320, index=48, num_params=1)
[15:25:13] [INFO] Executing callback 'LoadBounties' with 1 parameter...
[15:25:13] [DEBUG] processing internal specifier 'c'
[15:25:13] [DEBUG] pushed value '0' onto AMX stack
[15:25:13] [DEBUG] executing AMX callback with index '48'
[15:25:13] [DEBUG] cache_get_row_count(0x2289CC04) (core/assassinations/bounties.pwn:54)
[15:25:13] [DEBUG] cache_get_row_count: return value: '1' (core/assassinations/bounties.pwn:54)
[15:25:13] [DEBUG] cache_get_value_name_int(0, "hitid", 0x2289CC00) (core/assassinations/bounties.pwn:60)
[15:25:13] [DEBUG] cache_get_value_name_int: assigned value: '2' (core/assassinations/bounties.pwn:60)
[15:25:13] [DEBUG] cache_get_value_name_int: return value: '1' (core/assassinations/bounties.pwn:60)
[15:25:13] [DEBUG] cache_get_value_name_int(0, "amount", 0x2289CBFC) (core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] cache_get_value_name_int: assigned value: '500000' (core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] cache_get_value_name_int: return value: '1' (core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] mysql_format(1, 0x2289B850, 128, "SELECT Username FROM `players` WHERE ID = %d LIMIT 1") (core/data/saveload.pwn:388 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] mysql_format: return value: '51' (core/data/saveload.pwn:388 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] mysql_query(1, "SELECT Username FROM `players` WHERE ID = 2 LIMIT 1", 1) (core/data/saveload.pwn:390 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] CHandle::Execute(this=0x992a770, type=3, query=0x89492f0)
[15:25:13] [DEBUG] CConnection::Execute(query=0x89492f0, this=0x1ef55558, connection=0x1e8fae9
[15:25:13] [DEBUG] CQuery::Execute(this=0x89492f0, connection=0x1e8fae9
[15:25:13] [INFO] query "SELECT Username FROM `players` WHERE ID = 2 LIMIT 1" successfully executed within 25.214 milliseconds
[15:25:13] [DEBUG] CResultSet::Create(connection=0x1e8fae98, query_str='SELECT Username FROM `players` WHERE ID = 2 LIMIT 1')
[15:25:13] [DEBUG] created new resultset '0x25d605c8'
[15:25:13] [DEBUG] fetched MySQL result '0x25d4a548'
[15:25:13] [DEBUG] allocated 28 bytes for PAWN result
[15:25:13] [DEBUG] CHandle::Execute - return value: true
[15:25:13] [DEBUG] mysql_query: return value: '2' (core/data/saveload.pwn:390 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] cache_get_value_name(0, "Username", 0x2289B7EC, 25) (core/data/saveload.pwn:391 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] cache_get_value_name: assigned value: 'player' (core/data/saveload.pwn:391 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] cache_get_value_name: return value: '1' (core/data/saveload.pwn:391 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] cache_delete(2) (core/data/saveload.pwn:392 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] cache_delete: return value: '1' (core/data/saveload.pwn:392 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] cache_get_value_name_int(1, "hitid", 0x2289CC00) (core/assassinations/bounties.pwn:60)
[15:25:13] [ERROR] cache_get_value_name_int: no active cache (core/assassinations/bounties.pwn:60)
[15:25:13] [DEBUG] cache_get_value_name_int(1, "amount", 0x2289CBFC) (core/assassinations/bounties.pwn:61)
[15:25:13] [ERROR] cache_get_value_name_int: no active cache (core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] mysql_format(1, 0x2289B850, 128, "SELECT Username FROM `players` WHERE ID = %d LIMIT 1") (core/data/saveload.pwn:388 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] mysql_format: return value: '51' (core/data/saveload.pwn:388 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] mysql_query(1, "SELECT Username FROM `players` WHERE ID = 2 LIMIT 1", 1) (core/data/saveload.pwn:390 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] CHandle::Execute(this=0x992a770, type=3, query=0x894938
[15:25:13] [DEBUG] CConnection::Execute(query=0x8949388, this=0x1ef55558, connection=0x1e8fae9
[15:25:13] [DEBUG] CQuery::Execute(this=0x8949388, connection=0x1e8fae9
[15:25:13] [INFO] query "SELECT Username FROM `players` WHERE ID = 2 LIMIT 1" successfully executed within 26.23 milliseconds
[15:25:13] [DEBUG] CResultSet::Create(connection=0x1e8fae98, query_str='SELECT Username FROM `players` WHERE ID = 2 LIMIT 1')
[15:25:13] [DEBUG] created new resultset '0x25d604e8'
[15:25:13] [DEBUG] fetched MySQL result '0x25d4acc8'
[15:25:13] [DEBUG] allocated 28 bytes for PAWN result
[15:25:13] [DEBUG] CHandle::Execute - return value: true
[15:25:13] [DEBUG] mysql_query: return value: '2' (core/data/saveload.pwn:390 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] cache_get_value_name(0, "Username", 0x2289B7EC, 25) (core/data/saveload.pwn:391 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] cache_get_value_name: assigned value: 'player' (core/data/saveload.pwn:391 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] cache_get_value_name: return value: '1' (core/data/saveload.pwn:391 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] cache_delete(2) (core/data/saveload.pwn:392 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] cache_delete: return value: '1' (core/data/saveload.pwn:392 -> core/assassinations/bounties.pwn:61)
[15:25:13] [DEBUG] AMX callback executed with error '0'
pawn Code:
new rows;
    cache_get_row_count(rows);
    if(rows)
    {
        new target, hitprice, bountystring[1024], hitname[25];
        for(new i; i<rows; i++)
        {
            cache_get_value_int(i, "hitid", target);
            cache_get_value_int(i, "amount", hitprice);
            format(hitname, sizeof(hitname), "%s is wanted for $%i\n", GetNameFromSQL_ID(target), hitprice);
            strcat(bountystring, hitname);
        }


Re: MySQL no active cache issue - Calisthenics - 09.05.2020

When you execute another query (non-threaded) inside the callback, the cache is reset. You will have to re-apply it.
pawn Code:
new rows = cache_num_rows();

if(rows)
{
    new Cache: cache_id = cache_save(),
        target, hitprice, 
        bountystring[1024], hitname[25];

    for(new i; i<rows; i++)
    {
        cache_set_active(cache_id); // re-apply cache

        cache_get_value_int(i, "hitid", target);
        cache_get_value_int(i, "amount", hitprice);
            
        format(hitname, sizeof(hitname), "%s is wanted for $%i\n", GetNameFromSQL_ID(target), hitprice);
        strcat(bountystring, hitname);
}
but why not joining the tables and retrieve the name of player instead of executing more queries? This is how it should be done.

Re: MySQL no active cache issue - Adamoneoone - 09.05.2020

Oh ok thanks a lot, so i went the inner join route and it seems to work correctly now.