[Tutorial] Setting up MYSQL.

[Rocky]

How to set up mySQL database for your vc-mp 0.4 server.

After eight - nine months away from vc-mp, I am back here on my first day trying out vc-mp 0.4. I see lot of people haven't been using MYSQL because they don't know how to set it up. So i thought i'd start my first day on 0.4 doing a tutorial on it. Before i start let me clear some questions regarding it.

1. Why use mySQL? Why not stick to sqlite?

mySQL databases can be written by more than one connection at the same time. ie, information stored in the database can be accessed or overwritten by another website/server at the same time whereas sqlite databases can only be written/edited one at a time. Another reason is web statistics even though it is possible on sqlite, If you have your server and website in different hosts you will need to update the database every time on the website's directory (yup that sucks, That's what PureDM server used to do).

2. Is mySQL faster than sqlite?


MySQL is way faster inserting records, but loses that advantage when selecting a lot of rows one after another.
SQLite does it completely the other way around. Slow with inserting (needs a lot of IO operations), but faster with selecting.
I think this is to do with the memory loading SQLite does. It keeps parts of it database in memory, so selecting rows from there is pretty quick. Below is the benchmark results done by Thijin.

[SCRIPT]  MySQL Benchmark
[SCRIPT]  Start: 0.091
[SCRIPT]  Connection made 0.098
[SCRIPT]  Table made 0.121
[SCRIPT]  10.000 records inserted: 3.569
[SCRIPT]  10.000 records selected: 55.287
[SCRIPT]  MySQL finished.

[SCRIPT]  SQLite Benchmark
[SCRIPT]  Start: 0.093
[SCRIPT]  Connection made: 0.094
[SCRIPT]  Table made: 0.1
[SCRIPT]  10.000 records inserted: 44.053
[SCRIPT]  10.000 records selected: 48.065
[SCRIPT]  SQLite finished.

Code Select Expand

function sqlite()
{
print( "SQLite Benchmark" );
print( "Start: " + clock() );
local DB = ConnectSQL("test.sqlite");
print( "Connection made: " + clock() );
QuerySQL(DB, "CREATE TABLE IF NOT EXISTS `benchmark` (`testInt` INTEGER, `testChar` TEXT, `testFloat` REAL)");
print( "Table made: " + clock() );
for( local i = 0; i < 10000; i++ )
{
QuerySQL(DB, "INSERT INTO `benchmark` (testInt, testChar, testFloat) VALUES(" + i + ", 'test string omfg', 123456.789);");
}
print( "10.000 records inserted: " + clock() );
for( local i = 0; i < 10000; i++ )
{
local res = QuerySQL(DB, "SELECT * FROM `benchmark` WHERE `testInt` = " + i);
}
print( "10.000 records selected: " + clock() );
print( "SQLite finished." );
}

function mysql()
{
print( "MySQL Benchmark" );
print( "Start: " + clock() );
local my = mysql_connect("localhost", "test", "uwotm8", "test");
print( "Connection made " + clock() );
mysql_query(my, "CREATE TABLE IF NOT EXISTS `benchmark` (`testInt` int(10) NOT NULL, `testChar` varchar(255) NOT NULL, `testFloat` float NOT NULL);");
print( "Table made " + clock() );
for( local i = 0; i < 10000; i++ )
{
mysql_query(my, "INSERT INTO `benchmark` (testInt, testChar, testFloat) VALUES(" + i + ", 'test string omfg', 123456.789);");
}
print( "10.000 records inserted: " + clock() );
for( local i = 0; i < 10000; i++ )
{
local res = mysql_query(my, "SELECT * FROM `benchmark` WHERE `testInt` = " + i);
}
print( "10.000 records selected: " + clock() );
print( "MySQL finished." );
}
//mysql();
//sqlite();




That's all that came up on my mind but feel free to ask questions here. So lets get started.

1. First, download and install mysql. Best thing to do is download a package such as wamp or EasyPHP. The package contains mysql, php, phpmyadmin, apache server and all stuffs you will need in future and go ahead and install it.

2. Now, Start up the package (easyphp or wamp or any you are using) and open up phpmyadmin (type localhost/phpmyadmin in browser; dir may vary according to package installed) and type in a name and create a new database then click on that database go to privileges -> add user and type in username and password and check all grant access. then click ok.

3. load the mysql and squirrel plugin to your vc-mp server. open up your gamemode .nut file and add these lines.

Do not store mysql details in plain global variable like i did in here. This is made for tutorial purposes only.

Code Select Expand


mysql_hostname <- "localhost";
mysql_database <- " ";
mysql_username <- " ";
mysql_password <- " ";

function onScriptLoad()
{
   mysqlDB <- mysql_connect( mysql_hostname, mysql_username, mysql_password, mysql_database);
   if( mysqlDB ) print( "[SERVER] Connection to mySQL database successful." );
   else print( "[SERVER] Connection to mySQL failed." );
}

Type in the database name, user name and password that you used to create database in phpmyadmin in variable fields respectively.

4. That's it. Here are the functions and their syntax now start coding your scripts.

Code Select Expand

mysql_connect( szHost, szUsername, szPassword, szDatabase, iPort )
mysql_close( pConnection )
mysql_query( pConnection, szQuery )
mysql_num_rows( pResult )
mysql_num_fields( pResult )
mysql_fetch_row( pResult )
mysql_fetch_assoc( pResult )
mysql_fetch_lengths( pResult )
mysql_free_result( pResult )
mysql_errno( pConnection )
mysql_error( pConnection )
mysql_ping( pConnection )
mysql_escape_string( pConnection, szString )
mysql_select_db( pConnection, szDatabase )
mysql_change_user( pConnection, szUser, szPassword )
mysql_warning_count( pConnection )
mysql_affected_rows( pConnection )
mysql_insert_id( pConnection )
mysql_info( pConnection )

Please do reply here if you have questions regarding mysql. I will try my best to figure them out. I will be writing each and function and their uses soon here.

[Thijn]

Putting the username, password etc. in "global" variables isn't a good idea. If you happen to have an exploit in your server the login details may leak.

Aside from VCMP / Squirrel, SQLite is slower then MySQL because of threading. Like you said, SQLite can only read/write once at a time. This basically means every query needs to finish before it processes the next one. MySQL can do multiple things at once.

The only "but" here is the way the squirrel plugin is made. The MySQL plugin doesn't really support multi-threading. So the advantage is basically gone.
While I haven't done any benchmarking, I think MySQL is still faster then SQLite but I will do some testing later.

[Rocky]

Putting the username, password etc. in "global" variables isn't a good idea. If you happen to have an exploit in your server the login details may leak.

Aside from VCMP / Squirrel, SQLite is slower then MySQL because of threading. Like you said, SQLite can only read/write once at a time. This basically means every query needs to finish before it processes the next one. MySQL can do multiple things at once.

The only "but" here is the way the squirrel plugin is made. The MySQL plugin doesn't really support multi-threading. So the advantage is basically gone.
While I haven't done any benchmarking, I think MySQL is still faster then SQLite but I will do some testing later.

Thanks for the information. Those variables were only made for tutorial purposes. When you have time feel free to edit the post and add benchmark details here. Thanks

[DizzasTeR]

This should also be moved to Tutorial section.

[Thijn]

Just did a bit of benchmarking. Turns out I was wrong
MySQL is way faster inserting records, but loses that advantage when selecting a lot of rows one after another.
SQLite does it completely the other way around. Slow with inserting (needs a lot of IO operations), but faster with selecting.
I think this is to do with the memory loading SQLite does. It keeps parts of it database in memory, so selecting rows from there is pretty quick.

[SCRIPT]  MySQL Benchmark
[SCRIPT]  Start: 0.091
[SCRIPT]  Connection made 0.098
[SCRIPT]  Table made 0.121
[SCRIPT]  10.000 records inserted: 3.569
[SCRIPT]  10.000 records selected: 55.287
[SCRIPT]  MySQL finished.

[SCRIPT]  SQLite Benchmark
[SCRIPT]  Start: 0.093
[SCRIPT]  Connection made: 0.094
[SCRIPT]  Table made: 0.1
[SCRIPT]  10.000 records inserted: 44.053
[SCRIPT]  10.000 records selected: 48.065
[SCRIPT]  SQLite finished.

Code Select Expand

function sqlite()
{
print( "SQLite Benchmark" );
print( "Start: " + clock() );
local DB = ConnectSQL("test.sqlite");
print( "Connection made: " + clock() );
QuerySQL(DB, "CREATE TABLE IF NOT EXISTS `benchmark` (`testInt` INTEGER, `testChar` TEXT, `testFloat` REAL)");
print( "Table made: " + clock() );
for( local i = 0; i < 10000; i++ )
{
QuerySQL(DB, "INSERT INTO `benchmark` (testInt, testChar, testFloat) VALUES(" + i + ", 'test string omfg', 123456.789);");
}
print( "10.000 records inserted: " + clock() );
for( local i = 0; i < 10000; i++ )
{
local res = QuerySQL(DB, "SELECT * FROM `benchmark` WHERE `testInt` = " + i);
}
print( "10.000 records selected: " + clock() );
print( "SQLite finished." );
}

function mysql()
{
print( "MySQL Benchmark" );
print( "Start: " + clock() );
local my = mysql_connect("localhost", "test", "uwotm8", "test");
print( "Connection made " + clock() );
mysql_query(my, "CREATE TABLE IF NOT EXISTS `benchmark` (`testInt` int(10) NOT NULL, `testChar` varchar(255) NOT NULL, `testFloat` float NOT NULL);");
print( "Table made " + clock() );
for( local i = 0; i < 10000; i++ )
{
mysql_query(my, "INSERT INTO `benchmark` (testInt, testChar, testFloat) VALUES(" + i + ", 'test string omfg', 123456.789);");
}
print( "10.000 records inserted: " + clock() );
for( local i = 0; i < 10000; i++ )
{
local res = mysql_query(my, "SELECT * FROM `benchmark` WHERE `testInt` = " + i);
}
print( "10.000 records selected: " + clock() );
print( "MySQL finished." );
}
//mysql();
//sqlite();


[.]

As an addition to Thijn's benchmark which prove that SQLite is a bit slower with inserts I'd like to recommend reading this discussion as well. And also the fact that I could move an SQLite database from disk to memory to work with it and then save it back when I'm done with my program. Of course that brings the issue of data not being saved in case of a crash but I just mentioned that it is possible to do that.

Therefore, I'm pretty sure that if I optimize my query as specified in that discussion and I move the database to memory I could easily exceed the performance of MySQL. Now, you might wonder: But wouldn't moving the database to memory cause high memory usage? Well, that depends on your database. But doesn't MySQL use a huge amount of memory as well? 256MB to MySQL is nothing. Especially when you have InnoDB enabled.

But again, MySQL can also be optimized to work better by selecting an appropriate storage engine and probably I could use it's feature to create tables in memory as well (if the storage engine supports it).

It's all about the needs. But to be honest, SQLite can also be pretty fast if used properly.

[Rocky]

...

Added to main post. Thanks for the benchmarks, Now that's one less thing to worry about.

...

Is it just me or is that link to discussion broken for everyone?

[Honey]

That link works for me,

 MySQL and SQLite are one of the best databases but when it comes to vcmp, most of the people use SQLite because they think it's easier to use, If they read this tut They'll learn a lot so in my opinion, This topic is a great effort, Well done

[EK.IceFlake]

Why dont you use MySQLite (MySQL without server)? ☻☺☻

[Rocky]

Why dont you use MySQLite (MySQL without server)? ☻☺☻

Well the main reason to use mysql is to php access database to be used in web applications. The package includes phpmyadmin, apache server, php and all tools necessary to run or create or test php codes etc. Like I mentioned you may use any you want but I will be using easyphp for this tutorial.

[.]

Well the main reason to use mysql is to php access database to be used in web applications.

You can do the same with SQLite in PHP.

apache server,...

NGINX FTW

[Gudio]

NGINX FTW

I wouldn't recommend nginx to people who have no clue how to configure it with PHP or who want to use random tutorial.
It can be very dangerous because many tutorials like 'nginx + php + mysql + phpmyadmin' let attackers hack the whole server due to executing images by PHP.

[.]

I wouldn't recommend nginx to people who have no clue how to configure it with PHP or who want to use random tutorial.

To be honest, I find NGINX to be more easy to configure than Apache. I'm guessing it depends on the preferences.

[Rocky]

I wouldn't recommend nginx to people who have no clue how to configure it with PHP or who want to use random tutorial.

To be honest, I find NGINX to be more easy to configure than Apache. I'm guessing it depends on the preferences.

I found apache easy myself. I could easily set it up in a go when I first found it. Like I have written in the tutorial people can user any they want.

I meant to say if the server and website  using it are in different hosts then mysql is useful. I am really bad when typing in mobile so by the time I type a sentence I forget what to type next.