MySQL Integration

You are here:
← All Topics
Version: 2019.200 Requirements:uMMORPG3d, ToolsScript Define:_MYSQL
Core Changes:YesTutorial Video:No

Description

The UCE Tools AddOn now provides both AddOn users and AddOn developers with a way to integrate mySQL into their projects. This integration is optional, more or less seamless and you can revert back to SQLite anytime.

If you never ever plan to use mySQL, you can ignore all modifications in this file and continue to use the default SQLite database. The script files in UCE Tools won’t harm or affect your project in any way.

If you want to switch to mySQL, read the instructions below carefully and apply all changes. As this is a major core modification there are several steps required. When you are done, further integration of my AddOns will be seamless and they will also auto-adjust to mySQL. Finally, you can revert back to SQLite anytime.

Attention: When switching to mySQL, ALL AddOns and all of your script files will be forced to use it – I cannot provide you support or fixes for the community AddOns out there. I doubt the AddOns out there support mySQL, so they will break. You will have to update them yourself or contact their authors.

Note: If you are already using Paul’s mySQL AddOn – remove it and all mySQL.dlls with it. It is not required as it is part of my Tools now. Also make sure to remove all mySQL modifications applied already – they are now obsolete. UCE Tools takes care of this all!


Core Changes

  1. [Required] Open the following script NetworkManagerMMO.cs. See Changes...
    Locate this line:
    public override void OnValidate()
        {
            base.OnValidate();
    
            // ip has to be changed in the server list. make it obvious to users.
            if (!Application.isPlaying && networkAddress != "")
                networkAddress = "Use the Server List below!";
    
            // need enough character selection locations for character limit
            if (selectionLocations.Length != characterLimit)
            {
                // create new array with proper size
                Transform[] newArray = new Transform[characterLimit];
    
                // copy old values
                for (int i = 0; i < Mathf.Min(characterLimit, selectionLocations.Length); ++i)
                    newArray[i] = selectionLocations[i];
    
                // use new array
                selectionLocations = newArray;
            }
        }

    Modify to match:

    public override void OnValidate()
        {
            base.OnValidate();
    
            // ip has to be changed in the server list. make it obvious to users.
            if (!Application.isPlaying && networkAddress != "")
                networkAddress = "Use the Server List below!";
    
            // need enough character selection locations for character limit
            if (selectionLocations.Length != characterLimit)
            {
                // create new array with proper size
                Transform[] newArray = new Transform[characterLimit];
    
                // copy old values
                for (int i = 0; i < Mathf.Min(characterLimit, selectionLocations.Length); ++i)
                    newArray[i] = selectionLocations[i];
    
                // use new array
                selectionLocations = newArray;
            }
            
            Utils.InvokeMany(typeof(NetworkManagerMMO), this, "OnValidate_");		// Fhiz Core Change
        }
  2. [Required] Open the following script Database.cs. See Changes...
    Add this line to the Beginning:
    #if _SQLITE

    Add this lin to the End:

    #endif

Installation

  1. [Required] Apply core changes as stated above first.
  2. [Required] Make sure that you updated all UCE AddOns (by Fhiz) to the latest version.
  3. [Required] Double check if you are using third party AddOns, if they support mySQL or better remove them (they will break).
  4. [Required] Install latest mySQL on your server https://dev.mysql.com/downloads/
  5. [Required] You can find some basic information about mySQL installation and setup here: https://www.ionos.com/community/hosting/mysql/install-and-use-mysqlmariadb/
  6. [Optioanl] Set character encoding (if MySQL version 7 or earlier) If using MySQL 7 or earlier, the default character set is `latin1`, which causes problems for the mysql driver. You need to change it to utf8mb4 or you will get exceptions edit my.cnf or my.ini and add these settings:
    
    [mysqld]
    init_connect='SET collation_connection = utf8mb4_unicode_ci' 
    init_connect='SET NAMES utf8mb4'
    character-set-server=utf8mb4
    collation-server=utf8mb4_unicode_ci
  7. [Required] Create a database. For example:
    
    create database ummorpg;
    
  8. [Required] Create a user for your database. For example:
    
    create user 'ummorpg'@'%'  identified by 'db_password';
    
  9. [Required] Grant your user the required privileges. For example:
  10. [Required] Restart mySQL. e.g. via linux command line:
  11. [Required] Go to your NetworkManager, in the inspector locate the new “Database Type” Setting and switch from SQLite to mySQL.
  12. [Required] Wait a few seconds and allow the UnityEditor to update everything.
  13. [Required] Edit the new property slots that are now showing by entering your mySQL database credentials.
  14. [Optional] Now build and/or run your game.

Extras

When switching databases back and forth, only the currently active database code is included in your build.

You can easily switch back and forth on NetworkManager, this allows for quick testing using SQLite and mySQL in your game production environment.

When running from the editor while using a remotely hosted database, expect lag. Especially when saving characters. Imagine your server is your local Editor while the database is hosted somewhere else.

When you cannot connect from editor to a remote database, make sure it accepts remote connections.

The two scripting defines that are used are _MYSQL and _SQLITE – should you ever have to add/remove them manually.

While there are red console errors in your project, the AddOn won’t be able to add the _MYSQL / _SQLITE defines. This can result in the fact that the whole database is not working as expected. You should therefore fix all errors first and then make sure the defines have been set. Its a good idea to switch back/forth database once to trigger the defines being set. Or add manually when in doubt.

Do not play around with the database setting on Network Manager before you finished all the integration steps first!


Security Considerations

Before compiling a client, delete your database credentials from the NetworkManager (or overwrite them with junk). This way they won’t be available at the client at all and cannot be exposed by hacking.

Just note your credentials somewhere and dont forget to re-enter them when compiling a server.

Support the IndieMMO Team Today!

Leave a reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.