MU Server with MySQL Database

[XyXeL]

I'm wondering why everyone says that MU server has to use MSSQL as its DB. I want to show you, it is possible to use MySQL (and other DBs should work the same way). In fact, MU server is created to work with MSSQL, so server files will need some changes, but comparing to assembler coding main.exe, I really don't find this difficult :) So let's see how does it work:

What do you need

Brain

MySQL: http://dev.mysql.com/downloads/mysql/5.1.html
MySQL Connector/ODBC: http://dev.mysql.com/downloads/connector/odbc/5.1.html
DreamCoder for MySQL (for simplicity): http://www.download.com/DreamCoder-f...-10617276.html
MSSQL: http://www.microsoft.com/downloads/d...displaylang=en
MSSQL2MySQL: http://mssql2mysql.en.softonic.com/
Hex editor (I am using PSPad): http://www.pspad.com/en/download.php

Server files: (used 0.97d + 0.99): http://***************/index.php?dir=...00.97d%2B0.99/
Client files: I think, you can find on your own :)

How to do

0) Download and install the required soft.

1) Restore MSSQL DBs as you would do normally.

2) Create 2 MySQL DBs - `muonline` and `ranking`. Also create some passworded! login (do not use root, in fact it won't work with root anyway). You may use DreamCoder for ease.

3) Run MSSQL2MySQL and migrate DBs:
http://is.muni.cz/www/324521/7112953/mumysql1.jpg
http://is.muni.cz/www/324521/7112953/mumysql2.jpg
One of the differences between MSSQL and MySQL is, that in MySQL, there is a keyword CHARACTER. This makes problems with the table Character, so you have to rename the table (I renamed it to `chars`).
http://is.muni.cz/www/324521/7112953/mumysql3.jpg

4) Create 2 MySQL ODBCs: MU and Ranking and link them into right DBs.
http://is.muni.cz/www/324521/7112953/mumysql4.jpg

5) Now we have to modify server files to make them work correctly with MySQL. We have to solve a few problems:
> MySQL doesn't use NT authority, so you have to fill all logins and passwords correctly.
> MSSQL function GETDATE() is named NOW() in MySQL.
> Stored procedures aren't called with keyword EXEC, but with CALL and must use parenthesis.
> We have renamed the table Character.

5a) Open DataServer in hex editor and find this:

Replace DB connection info with your valid login. For ODBC DSN, you created "MU" in the step 4, didn't you? :)

Next thing, search for queries using Character table. We have renamed Character, so we must modify all queries (I mean ALL, not only these on the picture ;)). Also search for GETDATE() function and replace it with NOW(). Don't worry about blank spaces, SQL ignores them.

Save and do the same with the second DataServer (or just copy the exe).

5b) Open JoinServer with hex editor and find:

Again, replace DSN, user and password with your valid login. Replace it everywhere in the JoinServer file, you can find it about 10x. If you didn't yet, find the login that uses USELOG DSN instead of MuOnlineJoinDB and modifi it the same way.

Search for GETDATE() function and replace it with NOW().

Now search for queries with keyword EXEC and replace EXEC with CALL. Rename procedures WZ_CONNECT_MEMB() and WZ_DISCONNECT_MEMB() to connect() and disconnect(). Don't forget the parenthesis ;)



Save JoinServer

5c) Open ExDB with hex editor and find:

Again replace the login info.
Now search for the procedure WZ_GuildCreate and add parenthesis to the parameters:

Save ExDB.

6) Finish setting IPs, ports etc. as you would do normally. Don't forget to fill svconfig.ini (EventServer and RankingServer) with right DSN, user and password.

7) Create stored procedures (again, you may use DreamCoder for ease):

CREATE PROCEDURE connect(acc varchar(10), server varchar(20), userip varchar(15))
BEGIN
  IF EXISTS (SELECT * FROM memb_stat WHERE memb___id=acc) THEN UPDATE memb_stat SET ConnectStat=1, ServerName=server, IP=userip, ConnectTM=now() WHERE memb___id=acc;
  ELSE INSERT INTO memb_stat(memb___id, ConnectStat, ServerName, IP, ConnectTM) VALUES(acc, 1, server, userip, NOW());
  END IF;
END

CREATE PROCEDURE disconnect(acc varchar(10))
BEGIN
  UPDATE memb_stat SET ConnectStat=0, DisConnectTM=now() WHERE memb___id=acc;
END

CREATE PROCEDURE WZ_getitemserial()
BEGIN
    UPDATE GameServerInfo SET ItemCount = ItemCount + 1;
    SELECT ItemCount FROM GameServerInfo;
END

CREATE PROCEDURE WZ_GuildCreate(gname varchar(20), gmaster varchar(20))
BEGIN
    IF EXISTS (SELECT * FROM Guild WHERE G_Name=gname) THEN SELECT 1;
    ELSEIF EXISTS (SELECT * FROM GuildMember WHERE Name=gmaster) THEN SELECT 2;
    ELSE BEGIN
        INSERT INTO Guild(G_Name, G_Master, G_Score) VALUES(gname, gmaster, 0);
        INSERT INTO GuildMember(Name, G_Name, G_Level) VALUES(gmaster, gname, 1); 
        SELECT 0; 
    END;
    END IF;
END

You may copy other procedures too, but these are the most important I think...

8) If you want, disable MSSQL. Run servers and enjoy your MySQL MU Server :)
http://is.muni.cz/www/324521/7112953/mumysql.jpg

Notes

> It is not really necessary to instal MSSQL. You may create whole DBs manually. I used it just because migration is much more simple.
> You don't have to use exactly the same soft as I named, but be sure yours does the same thing :)
> You don't even have to follow my guide if you find how does it work :)
> I'm not SQL master, there may be more errors in queries, in fact it's on you to test ;)
> Any questions and comments will be posted in this topic - no PM, no ICQ etc...

Credits

Created by XyXeL (C)2009

[MentaL]

[[RCZ]ShadowKing]

some incompatibility at guildmember table but with a few mssql/mysql experience can be fixed.10/10

[BluStudio]

Great Job....i've done this thing in the past, but i couldn't migrate the stored procedures

[Sam3000]

Need help with season 1-3 dataserver...
Cant find dns, login and password...

[Hidden]

really good job!!
in MYSQL there's a function called dump,which backs up all of your db's into a file and can email them/upload to ftp. Which is really useful to do every week or so :)

[BluStudio]

But why to hex the data server when you can choose that options from DataServer.ini.dat...i mean why to change user/pass with hex?

[Bosco Wong]

what is the port for the mu i am from singapore

i having portforward problem

[BluStudio]

Now...if we want a faster mu online server we need to make it run under linux, I mean without Wine....but this is very hard work....even if there is eDataServer(which Error has the sources)

[skade]

I have a problem :/..

ALL WORKS..

BUT, WHEN I TRY CONNECT TO THE SERVER, THE SERVER SAYS "ACCOUNT INVALID"

I DONT KNOW WHAT IS THE REASON :/

PLEASE HELP ME

THANKS

PD : SORRY FOR MY BAD ENGLISH

[marat569]

I get this error:

02:02:29p INSERT INTO Chars
SQLSTATE:42000, diagnosis:[MySQL][ODBC 5.1 Driver][mysqld-5.0.51b-community-nt-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

[Stifi]

works only on old versions :(

[[RCZ]ShadowKing]

works only on old versions :(

in new ds versions this info are stored in a file named dataserver.ini.dat.You need to decrypt it and change the info..

[KREATOR]

how do u decrypt it?

[rockly]

:D ITS WORK IN ALL MU VERSION??????? IHAV SERVER SEASON2 ???? ..... tnx

[luizfc]

Nice man but I think there is only one problem.
How can I use a Web Site for Mu Online. In MySql
What do I need to change ?