[Misiur]

Which version are you using?
What does your code look like? Come over to scripting help

[CKA3KuH]

Quote:

Originally Posted by CKA3KuH Код: [04:03:26] [DEBUG] mysql_set_charset - charset: "cp1251", connection: 1 [04:03:26] [DEBUG] mysql_query - connection: 1, query: "SET NAMES cp1251;", use_cache: false [04:03:26] [DEBUG] CMySQLQuery::Execute - starting query execution [04:03:26] [DEBUG] CMySQLQuery::Execute - query was successful [04:03:26] [DEBUG] CMySQLResult::CMySQLResult() - constructor called [04:03:26] [DEBUG] CMySQLResult::~CMySQLResult() - deconstructor called [04:03:26] [DEBUG] mysql_query - connection: 1, query: "SET SESSION character_set_server='cp1251';", use_cache: false [04:03:26] [DEBUG] CMySQLQuery::Execute - starting query execution [04:03:26] [DEBUG] CMySQLQuery::Execute - query was successful [04:03:26] [DEBUG] CMySQLResult::CMySQLResult() - constructor called [04:03:26] [DEBUG] CMySQLResult::~CMySQLResult() - deconstructor called [04:03:26] [DEBUG] CMySQLResult::GetRowDataByName - row: '0', field: "description", data: "??????? ??????? ? 2-? ???????? ????????"

For fixing use mysql_tquery or mysql_pquery (not mysql_query) for setting charset.

[maddinat0r]

Quote:

Originally Posted by CKA3KuH Bug here! mysql_set_charset("cp1251") doesn't work in R35 with mysql_tquery or mysql_pquery. Fix it pls. [...]

Thanks for reporting. Seems like mysql_set_charset only works for mysql_query. I'll fix that.

Quote:

Originally Posted by CuervO I had very strange results with R35 on a simple SELECT query: [...]

Quote:

Originally Posted by Kar Already reported CuervO, thanks for the information though it will help pain

Thank you very much CuervO for your detailed report, that's some useful information I can really work with.
Also sorry for any inconvenience caused by R35, I'll try to fix that bug ASAP.

Quote:

Originally Posted by CKA3KuH For fixing use mysql_tquery or mysql_pquery (not mysql_query) for setting charset.

That would be a temporary solution for mysql_tquery. However, since mysql_pquery uses multiple connections and that CHARSET query only sets the charset for the used connection, I do not recommend using mysql_pquery with custom charsets yet.

[Kar]

Код:

 [debug] Server crashed while executing Kcnr.amx
[20:21:11] [debug] AMX backtrace:
[20:21:11] [debug] #0 native mysql_reconnect () [b7281b90] from mysql_static_.so
[20:21:11] [debug] #1 001c6c48 in public OnQueryError (0x000007d6, 0x0053bdec, 0x0053bd94, 0x0053bc30, 0x00000001) from Kcnr.amx
[20:21:11] [debug] Native backtrace:
[20:21:11] [debug] #0 b665d31b in _ZN10StackTraceC1EPv () from plugins/crashdetect.so
[20:21:11] [debug] #1 b66577c2 in _ZN11CrashDetect20PrintNativeBacktraceERSoPv () from plugins/crashdetect.so
[20:21:11] [debug] #2 b66583ac in _ZN11CrashDetect20PrintNativeBacktraceEPv () from plugins/crashdetect.so
[20:21:11] [debug] #3 b6658856 in _ZN11CrashDetect11OnExceptionEPv () from plugins/crashdetect.so
[20:21:11] [debug] #4 b665cf6c in ?? () from plugins/crashdetect.so
[20:21:11] [debug] #5 b7799600 in ?? ()
[20:21:11] [debug] #6 b3f00098 in ?? () from `v·–v·t%яіt%яіu%яіt%яі%'яіt'яі4w·
[20:21:11] [debug] #7 b72bc7f0 in mysql_real_connect () from plugins/mysql_static_.so
[20:21:11] [debug] #8 b726a45d in _ZN16CMySQLConnection7ConnectEv () from plugins/mysql_static_.so
[20:21:11] [debug] #9 b7270cbc in _ZN11CMySQLQuery7ExecuteEv () from plugins/mysql_static_.so
[20:21:11] [debug] #10 b726a7ec in _ZN12CMySQLHandle14ProcessQueriesEv () from plugins/mysql_static_.so
[20:21:11] [debug] #11 b726c1c7 in _ZN5boost6detail11thread_dataINS_3_bi6bind_tIvNS_4_mfi3mf0Iv12CMySQLHandleEENS2_5list1INS2_5valueIPS6_EEEEEEE3runEv () from plugins/mysql_static_.so
[20:21:11] [debug] #12 b72905c1 in ?? () from plugins/mysql_static_.so
[20:21:11] [debug] #13 b7779954 in ?? () from /lib/i386-linux-gnu/libpthread.so.0
[20:21:11] [debug] #14 b75ca95e in clone () from /lib/i386-linux-gnu/libc.so.6

Код:

 
[20:21:35] EID: 2006 | Error: MySQL server has gone away | Query: SELECT * FROM `Player_Bans` WHERE `Name` = 'Zatona' OR IP = '217.55.121.31' LIMIT 0,1
[20:21:35] [debug] Server crashed due to an unknown error
[20:21:35] [debug] Native backtrace:
[20:21:35] [debug] #0 b660931b in _ZN10StackTraceC1EPv () from plugins/crashdetect.so
[20:21:35] [debug] #1 b66037c2 in _ZN11CrashDetect20PrintNativeBacktraceERSoPv () from plugins/crashdetect.so
[20:21:35] [debug] #2 b66043ac in _ZN11CrashDetect20PrintNativeBacktraceEPv () from plugins/crashdetect.so
[20:21:35] [debug] #3 b6604856 in _ZN11CrashDetect11OnExceptionEPv () from plugins/crashdetect.so
[20:21:35] [debug] #4 b6608f6c in ?? () from plugins/crashdetect.so
[20:21:35] [debug] #5 b7745600 in ?? ()
[20:21:35] [debug] #6 b3e00098 in ?? () from `Xq·Vq·Д,ВіД,ВіЕ,ВіД,Віu.ВіД.Ві4Уq·
[20:21:35] [debug] #7 b72687f0 in mysql_real_connect () from plugins/mysql_static_.so
[20:21:35] [debug] #8 b721645d in _ZN16CMySQLConnection7ConnectEv () from plugins/mysql_static_.so
[20:21:35] [debug] #9 b721ccbc in _ZN11CMySQLQuery7ExecuteEv () from plugins/mysql_static_.so
[20:21:35] [debug] #10 b72167ec in _ZN12CMySQLHandle14ProcessQueriesEv () from plugins/mysql_static_.so
[20:21:35] [debug] #11 b72181c7 in _ZN5boost6detail11thread_dataINS_3_bi6bind_tIvNS_4_mfi3mf0Iv12CMySQLHandleEENS2_5list1INS2_5valueIPS6_EEEEEEE3runEv () from plugins/mysql_static_.so
[20:21:35] [debug] #12 b723c5c1 in ?? () from plugins/mysql_static_.so
[20:21:35] [debug] #13 b7725954 in ?? () from /lib/i386-linux-gnu/libpthread.so.0
[20:21:35] [debug] #14 b757695e in clone () from /lib/i386-linux-gnu/libc.so.6

Код:

[debug] Server crashed due to an unknown error
[03:39:15] [debug] Native backtrace:
[03:39:15] [debug] #0 b665431b in _ZN10StackTraceC1EPv () from plugins/crashdetect.so
[03:39:15] [debug] #1 b664e7c2 in _ZN11CrashDetect20PrintNativeBacktraceERSoPv () from plugins/crashdetect.so
[03:39:15] [debug] #2 b664f3ac in _ZN11CrashDetect20PrintNativeBacktraceEPv () from plugins/crashdetect.so
[03:39:15] [debug] #3 b664f856 in _ZN11CrashDetect11OnExceptionEPv () from plugins/crashdetect.so
[03:39:15] [debug] #4 b6653f6c in ?? () from plugins/crashdetect.so
[03:39:15] [debug] #5 b7790600 in ?? ()
[03:39:15] [debug] #6 b7517667 in gsignal () from /lib/i386-linux-gnu/libc.so.6
[03:39:15] [debug] #7 b751aa52 in abort () from /lib/i386-linux-gnu/libc.so.6
[03:39:15] [debug] #8 b755398d in ?? () from /lib/i386-linux-gnu/libc.so.6
[03:39:15] [debug] #9 b755da8a in ?? () from /lib/i386-linux-gnu/libc.so.6
[03:39:15] [debug] #10 b7560c02 in ?? () from /lib/i386-linux-gnu/libc.so.6
[03:39:15] [debug] #11 b75624bc in __libc_malloc () from /lib/i386-linux-gnu/libc.so.6
[03:39:15] [debug] #12 b76cd7b5 in _Znwj () from /usr/lib/i386-linux-gnu/libstdc++.so.6
[03:39:15] [debug] #13 b6717d14 in ?? () from plugins/irc.so
[03:39:15] [debug] #14 b6702d49 in ?? () from plugins/irc.so
[03:39:15] [debug] #15 b6703fcb in ?? () from plugins/irc.so
[03:39:15] [debug] #16 b670e977 in ?? () from plugins/irc.so
[03:39:15] [debug] #17 b67205be in ?? () from plugins/irc.so
[03:39:15] [debug] #18 b671bcab in ?? () from plugins/irc.so
[03:39:15] [debug] #19 b6726c44 in ?? () from plugins/irc.so
[03:39:15] [debug] #20 b66ed840 in ?? () from plugins/irc.so
[03:39:15] [debug] #21 b7770954 in ?? () from /lib/i386-linux-gnu/libpthread.so.0
[03:39:15] [debug] #22 b75c195e in clone () from /lib/i386-linux-gnu/libc.so.6

Just throwing them here.

R34, so hopefully they are fixed in r36, something to do with connections I guess

[maddinat0r]

Quote:

Originally Posted by Kar [...] Just throwing them here. R34, so hopefully they are fixed in r36, something to do with connections I guess

Thanks for reporting, but R34 is (at least internally) worlds apart R36, it really wouldn't make sense to find the source of these crashes (waste of time). I'll give you another beta version when these weird cache bugs are fixed, maybe you can reproduce it there.

[Kar]

Yea, please test the cache bugs fully, the last time I put that version up 100 accounts got created with wrong values on my server lol. Thank god it was in the night, so there was low activity. I can do some brute tests on my home host to test if needed.

The last 2 crashes have been going on for ages.. for alot of people, theres never any amx backtrace so it's pretty hard to recreate.

[kurta999]

If I change pool_size in mysql_connect() to 20 and I'll use mysql_pquery, then this will be very fast?

If server will use lot of resources, it doesn't matter.

[iZN]

Quote:

Originally Posted by kurta999 If I change pool_size in mysql_connect() to 20 and I'll use mysql_pquery, then this will be very fast? If server will use lot of resources, it doesn't matter.

Yes. I believe so.

[PowerPC603]

I'm using R34 of your plugin and I have a question.

Why does "mysql_connect" return 1, even when MySQL isn't running?

pawn Код:

#define mysql_host "127.0.0.1"
#define mysql_user "PowerPC603"
#define mysql_password "MyPass"
#define mysql_database "ppctrucking"

public OnFilterScriptInit()
{
    // Connect to the MySQL database and store the connection-handle
    mysql_debug(1);
    SQL_db = mysql_connect(mysql_host, mysql_user, mysql_database, mysql_password);

    if (SQL_db)
        printf("MySQL connection successful");
    else
        printf("MySQL failed to connect");

When I shutdown MySQL, or even change the name of the database (change the define to "ppctruck" for example, mysql_connect still returns "1".

When I add this code to check if the database really exists, it works fine even though mysql_connect still returns 1.

pawn Код:

format(Query, sizeof(Query), "SHOW DATABASES LIKE '%s'", mysql_database);
    mysql_query(SQL_db, Query);
    mysql_store_result(SQL_db);
    if (mysql_num_rows(SQL_db) == 1)
        printf("Connecting to MySQL was successful, database \"%s\" exists", mysql_database);
    else
    {
        printf("ERROR: Connecting to MySQL failed, database \"%s\" doesn't exist", mysql_database);
        printf("Shutting down server...");
        mysql_free_result(SQL_db);
        mysql_close(SQL_db);
        SetTimer("ShutdownServer", 5000, false);

        return 1;
    }

[Konstantinos]

mysql_connect returns the connection handle, if you want to check whether the connection was successful or not, use mysql_errno:

pawn Код:

SQL_db = mysql_connect(mysql_host, mysql_user, mysql_database, mysql_password);

if (!mysql_errno(SQL_db)) printf("MySQL connection successful");
else printf("MySQL failed to connect");

It returns 0 when no errors; therebefore the connection did not fail.

[TopFuel]

Quote:

Originally Posted by PowerPC603 I'm using R34 of your plugin and I have a question. Why does "mysql_connect" return 1, even when MySQL isn't running? ...

EDIT: oops Konstantinos was faster

https://sampwiki.blast.hk/wiki/MySQL/R33#mysql_connect

Quote:

Returns Connection handle.

use this to see if the connection was made:
https://sampwiki.blast.hk/wiki/MySQL/R33#mysql_errno

pawn Код:

mysql_connect("127.0.0.1", "root", "mydatabase", "mypass");
if(mysql_errno() != 0) 
    print("Could not connect to database!");

[maddinat0r]

Quote:

Originally Posted by kurta999 If I change pool_size in mysql_connect() to 20 and I'll use mysql_pquery, then this will be very fast? If server will use lot of resources, it doesn't matter.

If your server has a CPU with 20 cores, sure.
I don't think that a value greater than '8' would really make sense. Your queries would have to take several seconds to execute (and I'm talking about EVERY query you send) to make a use of a connection pool size greater than 8 really reasonable.

Quote:

Originally Posted by PowerPC603 I'm using R34 of your plugin and I have a question. Why does "mysql_connect" return 1, even when MySQL isn't running?[...]

"1" is the ID of the connection handle and does not represent the state (true/false) of a successfully established connection to a database.
If you call "mysql_connect", the plugin creates a connection handler, not a direct connection to a database. Internally the plugin directly connects to a database and this connection might fail. But the creation of a connection handler is guaranteed to be always successful.

EDIT: lol, third reply to the same question, I really need to speed up on answering

[PowerPC603]

Ok, I got it.

But I want my script to automatically create the database if it doesn't exist.

pawn Код:

SQL_db = mysql_connect(mysql_host, mysql_user, "ppctrucking", mysql_password);
    mysql_query(SQL_db, "CREATE DATABASE IF NOT EXISTS `ppctrucking`");

Something like this.

In this case, the connectionhandle is 1, as you guys explained, but the connection to the database isn't established as the database doesn't exist.
Therefore, the query fails to execute.

But I noticed using Navicat that there are some default databases in my mysql-server package:
- information_schema
- mysql
- performance_schema

If I connect to database "mysql" first, the connection is successful and the query to create the database works fine.

My question:
Can I assume EVERY mysql-server holds these default databases?

If so, I could first connect to database "mysql", create my database if it doesn't exist, close the connection, then open a new connection to my newly created database and populate it with the required tables.

Or is there another way to connect to mysql when there are no databases at all?
I tried using "" as database name but that failed as well.


EDIT:
After investigating a little further (clearing the logs from my mysql package and starting up Navicat, also using ****** search), it seems database "information_schema" should always exist.
Navicat seems to use that database to be able to show all other databases, as there are SELECT queries in the log which refer to "information_schema".

Then perhaps I could use that database to:
- setup my first connection to "information_schema" database
- check if there are no errors, and if there are, shutdown the server (user may be wrong or MySQL isn't installed or whatever)
- create my database "ppctrucking"
- close the connection
- setup a new connection to the new database "ppctrucking"
- populate the new database with the required tables
- insert default data into those new tables

[Misiur]

If you intend to put it in your gamemode, don't. Most of people using it are using some sort of shared hosting where they don't have power over their db name, and of course aren't allowed to create a new one.

Quote:

MySQL creates a mysql database, which it uses to store users and privileges.

Connecting to that database combined even with a single SQLi is not wise - usually that db is available only to root user, and using root account for your game connection is not wise as well.

[PowerPC603]

Quote:

Originally Posted by Misiur If you intend to put it in your gamemode, don't. Most of people using it are using some sort of shared hosting where they don't have power over their db name, and of course aren't allowed to create a new one. Connecting to that database combined even with a single SQLi is not wise - usually that db is available only to root user, and using root account for your game connection is not wise as well.

Ah, I didn't know hosting companies create a database for their users, with a name they decide in their place.
So the script should just assume the database exists and check if there were errors after connecting to it.
In case of errors, shutdown the server might be the best option then.

But there would be no harm in creating the tables inside the script if they don't exist?

pawn Код:

#define mysql_host "127.0.0.1"
#define mysql_user "PowerPC603"
#define mysql_password "MyPass"
#define mysql_database "ppctrucking"
new SQL_db; // Will hold the connectionhandle to the database

public OnFilterScriptInit()
{
    SQL_db = mysql_connect(mysql_host, mysql_user, mysql_database, mysql_password);
    // Check if there were errors after trying to connect
    if (mysql_errno() == 0)
    {
        printf("Connecting to MySQL was successful");
    }
    else // The database doesn't exist, so close the connection and shutdown the server after 5 seconds
    {
        printf("ERROR: Connecting to MySQL failed, shutting down server...");
        printf(" ");
        printf(" ");
        printf(" ");
        mysql_close(SQL_db);
        SetTimer("ShutdownServer", 5000, false);

        return 1;
    }

    // Rest of code
}

// This timer is used to shutdown the server if connecting to the database failed
forward ShutdownServer();
public ShutdownServer()
{
    SendRconCommand("exit");

    return 1;
}

[Misiur]

Yup, that's the best option. You might want to add option for tablename prefixes (few small servers using same database).

[PowerPC603]

Quote:

Originally Posted by Misiur Yup, that's the best option. You might want to add option for tablename prefixes (few small servers using same database).

Servers sharing databases could pose problems.

I'm not only storing player-data in MySQL, but also alot of other things:
- speedcameras
- police stations
- gas-stations
- housing data
- locations
- cargo-types
- route-data for performing trucker routes for example

All that data is only loaded when the server starts and the data will be stored in arrays for fast access.
Most of that data is pretty static, unless the admin edits stuff in-game.

Let's say an admin creates a new speedcamera on server 1.
That script will hold the data for the new speedcamera in it's arrays and also the new speedcamera is stored in MySQL.
But server 2 (which already runs and has loaded the data on startup) doesn't know a new speedcamera is created in MySQL.
If an admin on server 2 would create a new speedcamera as well, the query fails to insert the data because that ID is already created.
But the speedcamera is created in the script, and next time server 1 will have the new speedcamera loaded properly, but server 2 has the same new speedcamera that was created on server 1, and the new camera created on server 2 is gone as it wasn't saved.

And I don't want to use MySQL queries all the time, as there are timers that run for multiple players every second to check the distance to ALL speedcams and see if they're speeding.

And the same problems will appear for all other data as well: houses, gas-stations, locations, route-data, ...



But I think you mean every server should have their own tables in the same database?
Server 1 could have "ppc_speedcameras" while server 2 uses "test_speedcameras" as table names, under the same database?
And the script needs to put "ppc_" in front of the tablenames for every query, for server 1, and server 2 puts "test_" in front of the table names?

Nice idea, I didn't know hosts did that as well.
I've never used MySQL before, and I also haven't hosted a server yet.
I only home-hosted my test-server.

EDIT:
Table-prefixes already implemented as it wasn't much to edit.

[Misiur]

I don't recommend using single database for multiple servers (maximum number of connections, unrestricted access to other server tables with same credentials, etc.) - but if you have tiny servers (or testing server which shouldn't work on production server data), that's not a bad option. And yes, I mean prefixes for tables, so you can have
server1_speedcameras and separate server2_speedcameras - while running exactly the same gamemode.

[anou1]

Last friday I downloaded R35 version on github but now I can't find it again.

Anyone got a direct link for linux please ?

[Konstantinos]

Quote:

Originally Posted by anou1 Last friday I downloaded R35 version on github but now I can't find it again. Anyone got a direct link for linux please ?

It's on the first post at the top and it directs you to all the releases (versions).

https://github.com/pBlueG/SA-MP-MySQL/releases/