[StrickenKid]

SA:MP MySQL Plugin
Version: 2.1.1

Now Linux Stable!

Latest Release: 2.1.1 Build-165

ChangeLog:

• 1/25/2011 - v2.1.1
  Fixed bug that caused the server to crash when mysql_close was called with multiple connections.
  Fixed bug with mysql_query_array that caused strings to randomly override each other.
  Fixed bug that caused the server to crash when a null string was passed through a parameter.
  Changed a few error strings.
  
  
• 11/24/2010 - v2.1:
  Thanks to Blacklite properly debugging the plugin, all reported crashes have been fixed.
  The following functions which were unstable have been fixed: mysql_fetch_field, mysql_fetch_field_num, mysql_fetch_int, mysql_fetch_float and mysql_fetch_string.
  I have also found a proper way to fix libmysqlclient_r.so.X missing errors. See below.
  Fixed mysql_query's return value; returns 0 on successful query, returns error id on failed query,
  
  
• 8/7/2010 - v2.0:
  Biggest update since initial release.
  Entire plugin has been re-designed using classes, and all functions have been re-coded.
  Full linux multi-threaded compatibility, compiled with thread-safe mysql build. Tested on linux (debian 5) server, sever executed a total of 3,542 queries and was online for about a half hour, no crashes. I will also be testing it further for longer periods of time.
  Added mysql_init: initializes mysql connection and returns connection handle, (instead of initializing mysql inside mysql_connect).
  Removed mysql_reload function and added mysql_refresh, added mysql_reload define macro.
  Added mysql_get_character_set: gets the current character set in use.
  Added wiki page to the SA:MP Wiki.
  
  
• 3/26/2010 - v1.2.1:
  Fixed various bugs that caused the server to crash with various functions.
  Added more error checking to prevent crashes even if pawn coded badly.
  Cleaned up the code more.
  Optimized the code a lot.
  Changed some small things with the thread (for testing).
  Added list functionality for the AMX variable, it should work fully with filterscripts now. (Thanks to Incognito for the technique)
  
  
• 2/7/2010 - v1.2:
  Added new parameter to mysql_query to push a spare id of any kind to the threaded callback. (suggested by Wicko)
  Added mysql_query_array, which is the same as mysql_query, but lets you push an array of integers, floats, booleans, etc, or a string to OnMysqlQueryArray.
  Added callback OnMysqlQueryArray which works with mysql_query_array.
  Fixed some more small coding errors.
  Here is another small example by Wicko on how the new callback and function is used.
  
  
• 2/3/2010 - v1.1.2:
  Fixed small bug with mysql_fetch_field.
  Added mysql_fetch_row_data to work in conjunction with mysql_fetch_field. Here is an example on how to use this function.
  Added mysql_fetch_string.
  
  
• 2/2/2010 - v1.1.1:
  Added ProcessTick functionality to the plugin to prevent random crashes when calling OnMysqlQuery from query thread.
  Updated mysql_query to prevent allocated memory from not being free'd on error. (Thanks to Wicko)
  Changed max connections to 4. If you need to be connected to more than 4 databases at a time you can change MAX_CONNECTIONS in the source and compile.
  
  
• 1/31/2010 - v1.1:
  Added multi-connection support! You can connect up to a max of 20 databases at a time!
  Added threaded query callback! You've all been asking for it! And here it is!
  Added error handling callback! You can now have full control of error handling!
  Added mysql_result_stored function to check if a result was successfully stored after mysql_store_result.
  Added 1 more param to mysql_log to toggle errors printed to the server window. And changed the first param to take log types (info below).
  Fixed many small script errors and optimized most of the functions!
  
  
• 1/26/10 - v1.0.2:
  Fixed a couple more small problems.
  Added functions: mysql_fetch_field_num, mysql_fetch_int, and mysql_fetch_float.
  Cleaned up the code a bit.
  
  
• 1/24/10 - v1.0.1:
  Fixed small memory leak and bug that crashed the server if mysql_fetch_row was called with a result ~256 cells big and when mysql_log was on.

About:

This is a MySQL Plugin that enables you to connect to any MySQL database to make any query, and retrieve the results. This is pretty much the fastest and most stable MySQL plugin in SA:MP. This plugin is setup so it will send you an error message on what you've done wrong instead of just crashing, making it very stable. For example, if you execute a SELECT query and forget to store the result and try to retrieve it with mysql_fetch_row, the mysql_fetch_row function will send: "MySQL Error: 'mysql_fetch_row' called when no result stored." to the console window. This plugin is also equipped with a whopping 28 native functions, all useful for your SA:MP server.

How To Install:

• Windows:
  1. Download the plugin.
  2. Place the mysql.dll in your plugins folder, and move libmysql.dll to the same directory the server exe is.
  3. Open your server.cfg and add "mysql" to "plugins", if there is no "plugins" line, add one.
  4. Add mysql.inc to your /pawno/includes/ and add "#include <mysql>" to the top of your script.
• Linux:
  1. Download the plugin to your linux server.
  2. Place the mysql.so in your plugins folder.
  3. Open your server.cfg and add "mysql.so" to "plugins", if there is no "plugins" line, add one.
  4. On the PC that you compile your script on, add mysql.inc to your /pawno/includes/ and add "#include <mysql>" to the top of your script.

Functions:

• native MySQL:mysql_init(logtype = LOG_ONLY_ERRORS, printerrors = 1);
• native mysql_connect(const host[], const user[], const pass[], const db[], MySQL:handle = (MySQL:0), auto_reconnect = 0);
• native mysql_close(MySQL:handle = MySQL:0);
• native mysql_refresh(options, MySQL:handle = MySQL:0);
• native mysql_select_db(const db[], MySQL:handle = MySQL:0);
• native mysql_query(const query[], resultid = (-1), spareid = (0), MySQL:handle = MySQL:0);
• native mysql_query_array(const query[], resultid = (-1), {Float,_}:extravars[], MySQL:handle = (MySQL:0));
• native mysql_store_result(MySQL:handle = MySQL:0);
• native mysql_free_result(MySQL:handle = MySQL:0);
• native mysql_result_stored(MySQL:handle = MySQL:0);
• native mysql_fetch_field(const fieldname[], dest[], MySQL:handle = MySQL:0);
• native mysql_fetch_field_num(fieldnum, dest[], MySQL:handle = MySQL:0);
• native mysql_fetch_row(dest[], const splitter[] = "|", MySQL:handle = MySQL:0);
• native mysql_fetch_row_data(MySQL:handle = MySQL:0);
• native mysql_real_escape_string(const string[], dest[], MySQL:handle = MySQL:0);
• native mysql_num_rows(MySQL:handle = MySQL:0);
• native mysql_num_fields(MySQL:handle = MySQL:0);
• native mysql_affected_rows(MySQL:handle = MySQL:0);
• native mysql_insert_id(MySQL:handle = MySQL:0);
• native mysql_ping(MySQL:handle = MySQL:0);
• native mysql_error(dest[], MySQL:handle = MySQL:0);
• native mysql_errno(MySQL:handle = MySQL:0);
• native mysql_warning_count(MySQL:handle = MySQL:0();
• native mysql_info(dest[], MySQL:handle = MySQL:0);
• native mysql_stat(dest[], MySQL:handle = MySQL:0);
• native mysql_get_server_info(dest[], MySQL:handle = MySQL:0);
• native mysql_get_host_info(dest[], MySQL:handle = MySQL:0);
• native mysql_data_seek(rownum, MySQL:handle = MySQL:0);
• native mysql_set_character_set(const csname[], MySQL:handle = MySQL:0);
• native mysql_get_character_set(csname[], MySQL:handle = MySQL:0);
• native mysql_fetch_int(MySQL:handle = MySQL:0);
• native mysql_fetch_float(MySQL:handle = MySQL:0);
• native mysql_fetch_string(dest[], MySQL:handle = MySQL:0);

Wiki Page (Detailed Function List):

Can be found here.

Feel free to contribute to the wiki page.

Callbacks:

• forward OnMysqlQuery(resultid, spareid, MySQL:handle)
  • You use this callback if you want to have your query executed in a separate thread. Avoiding any lagging of the server itself, it basically separates the query from the server so if it takes the query 5 seconds to run, the server wont lag at all. You can now use spareid to pass a playerid or any other id.
  • Example below on use.
• forward OnMysqlQueryArray(resultid, extravars[], MySQL:handle)
  • You use this callback for the same reason as OnMysqlQuery, but if you want to pass more than 1 extra variable, and/or if you want to pass something other then an integer. Because it uses the {Float,_}: tag, you can pass any tag, you can also pass a string to the callback.
  • Example below on use.
• forward OnMysqlError(error[], errorid, MySQL:handle)
  • You use this callback to have 100% control on handling errors from mysql queries and functions. With the raw data being passed through you can do anything you want with it. You can also print the errors from your script and disable errors being printed from the plugin with mysql_log params (explained below). Also, for the advanced users, HERE is a list of error codes.
  • Example below on use.

See the wiki page for callback examples.

Downloads (2.1.1 - Build 165):


IMPORTANT: If you cannot compile your own mysql build (on a hosted server e.g. ServerFFS), use the static build.

• Windows (comes with libmysql.dll, .NET Framework required)
  • XP / Vista / 7
• Linux (compiled on Debian 5)
  • Linux Distributions
  • Static Linux Distributions (Use on Hosted Servers e.g. ServerFFS)
• Source
  • Cross Compatible
• Scripts
  • Example Script
  • Example Callback Script

Running on Linux?

Having problems with libmysqlclient_r.so.X?

If you cannot do this, use the static build.

1. First of all, you must locate where your mysql libraries are located.
   Mine are located at: /usr/local/mysql/lib/mysql
2. Now, open libc.conf located in /etc/ld.so.conf.d
3. Add the mysql libraries' path to the end of this file, on a new line.
4. Save the file, and execute: ldconfig

Want To Compile It Yourself?

• Windows:
  • Visual Studio 2008 Required
  • Open the project file and click Build>Build Solution or press F7
• Linux:
  • make, gcc, g++, mysql-client, and libmysqlclient15-dev required
  • A thread-safe mysql build needs to also be installed. See here, and here for more info on how to do this.
  • cd to the directory the plugin files are in and simply type "make"

Thanks To:

• Kye - Plugin SDK and param count define
• Incognito - AMX list / queue techniques
• Wicko - Helping me with understanding threading / coding support / bug catching
• Blacklite - Linux compiling / Testing / Bug fixing / Split function (in mysql.inc)
• Popz - Linux compiling help

Post Comments And Questions!

[/list][/list]

[Blacklite]

Good to see it finally here. Nice job, StrickenKid!

[[NoV]LaZ]

Looks very interesting, but you forgot to add the source code.
________
Infant Zoloft

[StrickenKid]

Quote:

Originally Posted by [NoVt LaZ ] Looks very interesting, but you forgot to add the source code.

Sorry, fixed, and I didn't forget, I just got the links mixed up.

[MX_Master]

Great! it's exactly what I need (:

[Toney]

Hm nice done i think it will most stable and full mysql version for SA-MP client. Thanks.

[potato]

Nice work!
But can you give in few words why should I (or someone else for that matter) use this plugin instead of those other plugins?
I do see some extra functions, that I actually needed before, but is this all?

[Jay_]

Nice work. Just a question though, what makes this plugin better than this one? It may have some extra natives, but it lacks something performance wise which makes G-sTyLeZzZ's version outrank yours: a threaded callback.

[MenaceX^]

Wow, I've been waiting so long for a new plugin.

I'll talk to you in MSN

[Dreftas]

Cool. I will try this plugin instead of G-Stylezz Really good work.

[Eazy_Efolife]

Good work.

[Dreftas]

Damn.. crashed

[Misiek]

I'd use it as soon as possible if it had multithreading capabilities like G-Stylezz's one does.
Otherwise another MySQL plugin is usable only for a tiny group of people here and I advise you to go this way.

Regards.

[Toney]

Dreft maybe do you want post more info about crashed.. I test it , works fine.

[Dreftas]

It crashes on a long row which is taken with mysql_fetch_field. Row is ~1050 symbols lenght.

[StrickenKid]

Quote:

Originally Posted by Dreft It crashes on a long row which is taken with mysql_fetch_field. Row is ~1050 symbols lenght.

I will check that right now. Thanks.

EDIT:

Ok, I've tested it with 3000 characters and the plugin worked fine, I then tried to print the row and that is what crashed it. So make sure you're not printing a row with ~1500 characters in it.
Also, make sure your buffer string that your saving the row to is ~1500 characters because that would cause a crash.

Quote:

Originally Posted by Jay_ Nice work. Just a question though, what makes this plugin better than this one? It may have some extra natives, but it lacks something performance wise which makes G-sTyLeZzZ's version outrank yours: a threaded callback.

The reason for me creating this plugin is to let SA:MP have the most stable plugin, because the other 2 MySQL plugins that are released indeed have bugs and those bugs haven't been fixed yet. And these bugs were found many months ago....

So no, my plugin doesn't have a threaded callback, but, I wouldn't want to add that because that's unique to G-Stylezzz plugin, and in my opinion that would just cause trouble to be copying features from others.

This was also created for speed, as far as I know, its faster than adrenaline-dj's plugin (haven't tested g-stylezzz plugin yet) but i did some testing on my plugin and it takes arround 1.0 to 1.3 seconds to execute 5,000,000 (5 million) mysql_fetch_row's. I tested the same on adrenaline-dj's plugin, and it crashed.

[MenaceX^]

I wonder if you'll make a debug mode.
If yes talk to me, I have several suggestions.

[StrickenKid]

Quote:

Originally Posted by MenaceX^ I wonder if you'll make a debug mode. If yes talk to me, I have several suggestions.

mysql_log(1); will turn on logging (debugging) The plugin also automatically prints errors you make to the server window.

For example:

Code:

mysql_query("select * from `accounts` where user_id = 5");
new buffer[1024];
mysql_fetch_row(buffer);
mysql_free_result();

In the code above, I never did mysql_store_result, so, you will get the folloing error in the server window:
"MySQL error: 'mysql_fetch_row' called when no result stored."

But yeah, well talk on MSN later.

[Dreftas]

Ok, i turned off mysql_log() which was printing that all row and crash gone I wish that you fix this problem :P

Thanks for plugin again, it will change old G-Stylezz plugin for sure

[Blantas]

Looks good. I'll check it out tomorow.