Author Topic: ULib MySQL Authentication  (Read 6731 times)

0 Members and 1 Guest are viewing this topic.

Offline MrPresident

  • Ulysses Team Member
  • Hero Member
  • *****
  • Posts: 2554
  • Karma: 391
    • View Profile
    • |G4P| Gman4President
ULib MySQL Authentication
« on: April 03, 2015, 08:25:05 PM »
What This Is
This is a replacement for ULib's UCL storage system. The current system saves and reads all stored users and their accesses from a single text file. This works fine, unless you have multiple servers or a LOT of information in that file.

What This Does
This overrides the Save and Load functions in UCL. Instead of loading and saving your users to the users.txt file, it will instead load and save them from a database.


What This Doesn't Do
This only handles users. It doesn't store groups or group permissions. Those are still going to be stored in the groups.txt
This doesn't handle banning. There are plenty of MySQL banning modules out there.

What Is Modified
You will need to *replace* your ucl.lua file in addons\ULib\lua\ulib\server\ucl.lua
We typically NEVER recommend that you replace any of our stock files as this could cause issues or the changes will be reverted the next time you update ULib.. However, due to the nature of this kind of modification simply writing it into an addon wouldn't work because of the way modules are loaded. If you choose to install this, I HIGHLY recommend making a backup of your ULib addon first.
Inside the ucl.lua file I added some MySQL functions. I also modified the SaveFile function as well as completely commented out the LoadFromFile function. I also slightly modified addUser and removeUser functions.

How To Install
1. Install the MySQLoo module. If you don't know where/how to get this I suggest Google.
2. Download the copy of ucl.lua from this post (YOU NEED TO BE LOGGED IN TO SEE IT)
3. Replace Addons\ULib\lua\server\ucl.lua with the one I provided.
4. Create a database in your MySQL called 'ulibsql' and set up a user to have permissions for it. (Don't create any tables inside of it, my code will handle that)
5. Edit the information at the top of the file to suit your MySQL database.
6. To be sure the table generated: run 'lua_run ULib_SQL_CreateTable()' from your server console.
7. From your server console or in game (if you are a superadmin) run ulib_loadlegacy to load your users from your users.txt and load them into your database.

WARNING/CAUTION
This is ONLY for people who know how to use the MySQLOO module. I will not provide support for getting that installed or configured.
This is not for server owners who do not know enough about lua or modifying files to at least edit the SQL configuration at the top of the file.
If you update ULib this modification will be lost and you'll have to reinstall it.
This comes as-is and while I did to testing, there is no way for me to know if it'll work in every situation. Please make a backup of your ULib files before installing this.

Help/Support
I am not going to help you install MySQLOO.
If you have MySQLOO installed and configured properly and are having trouble with this modification, please feel free to post here and I will try and assist you the best I can.



Please let me know how this works for you. :)
« Last Edit: April 27, 2015, 07:02:39 PM by MrPresident »

Offline JamminR

  • Ulysses Team Member
  • Hero Member
  • *****
  • Posts: 7707
  • Karma: 350
  • Sertafide Ulysses Jenius
    • View Profile
    • Team Ulysses [ULib/ULX, other fine releases]
Re: ULib MySQL Authentication
« Reply #1 on: April 03, 2015, 08:59:09 PM »
But But but, couldn't you use our current ULib hooks for pre/post translate commands to hook into ucl change, prevent the original function, perform yours, and not have to overwrite our original ucl file?
*sniffle*

(I say 'our' because you are 'us' now that we've assimilated you) :)
The price one pays for pursuing any profession or calling is an intimate knowledge of its ugly side. - James Baldwin

Offline MrPresident

  • Ulysses Team Member
  • Hero Member
  • *****
  • Posts: 2554
  • Karma: 391
    • View Profile
    • |G4P| Gman4President
Re: ULib MySQL Authentication
« Reply #2 on: April 03, 2015, 09:08:18 PM »
No, because all of this stuff happens so far into the background of UCL that it isn't hooked. I could have done it with the Add and Remove user, but the rest I couldn't. I had to override the function entirely and in my experience, addons have a weird way of loading in different orders each time so there really isn't a good way to write a module to do that.

I've talked to Megiddo about redoing how our modules are loaded to allow for something like this. I believe it's on our list of things for the next big ULX/ULib rewrite.

Offline Zaddion

  • Newbie
  • *
  • Posts: 15
  • Karma: 0
    • View Profile
Re: ULib MySQL Authentication
« Reply #3 on: April 03, 2015, 10:43:28 PM »
I was really hoping I could get this to work without having to ask for help. I have MYSQLOO installed (another addon that I use requires it), I made a database for the addon, I filled out the sql information at the top of the ucl file, and I overwrote the file. I restarted my server and I tried running the command, I waited, waited longer, it didn't say if it was successful or anything, so I waited a couple of minutes and checked my database, nothing happened? Is it an overtime thing? It'd be nice if it told me if the command worked if it did work. I double checked my sql information and made sure it was all correct. Not sure what I did wrong.

Offline MrPresident

  • Ulysses Team Member
  • Hero Member
  • *****
  • Posts: 2554
  • Karma: 391
    • View Profile
    • |G4P| Gman4President
Re: ULib MySQL Authentication
« Reply #4 on: April 03, 2015, 10:52:58 PM »
If you did it correctly, it should generate a table inside of your database when you first start your server.
Make sure you have permissions assigned for the database correctly.

Do you get any messages in console while ULX is loading?

Offline Zaddion

  • Newbie
  • *
  • Posts: 15
  • Karma: 0
    • View Profile
Re: ULib MySQL Authentication
« Reply #5 on: April 03, 2015, 10:56:14 PM »
If you did it correctly, it should generate a table inside of your database when you first start your server.
Make sure you have permissions assigned for the database correctly.

Do you get any messages in console while ULX is loading?

Is there a specific message I am looking for? It'd be easier if you could add me on steam where we can discuss this http://steamcommunity.com/id/zaddion/

Offline MrPresident

  • Ulysses Team Member
  • Hero Member
  • *****
  • Posts: 2554
  • Karma: 391
    • View Profile
    • |G4P| Gman4President
Re: ULib MySQL Authentication
« Reply #6 on: April 03, 2015, 11:07:51 PM »
Working with Zaddion, it would appear that in some cases the Table might not generate itself. If that's the case just run the following at your server's console:

lua_run ULib_SQL_CreateTable()

Offline Bite That Apple

  • Hero Member
  • *****
  • Posts: 848
  • Karma: 388
  • Apple Innovations 2010®
    • View Profile
    • Fun 4 Everyone Gaming
Re: ULib MySQL Authentication
« Reply #7 on: April 04, 2015, 02:53:36 AM »
So I got this to work, but I'm curious, does it just load the users.txt file every time someone joins the server?

So pretty much I made a bot join, and it spams the entire console with:
SUCCESSFULLY ADDED/UPDATED (STEAM_ID) to the database

So can you please describe it's actions?

- - - - - - - -

Also, to fix your little table thing not working, this is what I did to fix the issue.
Stars at line 49:
Code: Lua
  1. function ULib_SQL_ConnectToDatabase()
  2.         ULib.ucl.db = mysqloo.connect( DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, DATABASE_PORT )
  3.         ULib.ucl.db.onConnectionFailed = function(D, E)
  4.                 MsgN( "[ulib.ucl] Failed to Connect to Database: " .. E )
  5.                 ULib_SQL_ConnectToDatabase()
  6.         ULib.ucl.db.onConnected = function(D,E)
  7.                 MsgN( "[ulib.ucl] Connected to Database: " .. E )
  8.                 ULib_SQL_CreateTable()
  9.         end
  10.         ULib.ucl.db:connect()
  11. end
  12. end

Then line 116 to 122, completely remove, as well as line 125:
Line 116 - 122
Code: Lua
  1. function ULib_SQL_TryCreateTable()
  2.         if ULib.ucl.db:status() == 0 then
  3.                 ULib_SQL_CreateTable()
  4.         else
  5.                 timer.Simple( 1, ULib_SQL_TryCreateTable )
  6.         end
  7. end

Line 125
Code: Lua
  1. ULib_SQL_TryCreateTable()
« Last Edit: April 04, 2015, 02:59:01 AM by Bite That Apple »
Quote from: John F. Kennedy 1963
A man may die, nations may rise and fall, but an idea lives on.
In most cases I do not make addons for people. You may ask, and I may or may not accept, paying me will not change the answer if it was no. Feel free to try.
4-8-15-16-23-42


My Created Addons:
> Player Connect/Disconnect Message < ---- > URL Playsound Addon < ---------- > Leaderboards Records < ----------------
> Player Chat Tags < ----------------------------- > Last Joined Checker < ------------- > Workshop Dupes Permissions < ------
> Utime Hour Changer Addon < ---------------- > Autopromote XGUI Version < ---- > UtimeMOO Server/Global Edition < --
> Give Weapon Addon < ------------------------- > Player Force Spawn < ------------- >
> Set Modelany Addon < ------------------------- > Set Spawn Points < ---------------- >
> Apple H.U.D. < ----------------------------------- > Advanced Anti Noclip Killing < -- >

Offline MrPresident

  • Ulysses Team Member
  • Hero Member
  • *****
  • Posts: 2554
  • Karma: 391
    • View Profile
    • |G4P| Gman4President
Re: ULib MySQL Authentication
« Reply #8 on: April 04, 2015, 03:42:19 PM »
It only reads the users.txt when you run the legacy command.
What you saw was it saving users to the database.

Currently it saves all users to the database every time the save command is called (which is quite often). I am going to work on rewriting some of that, so that it only saves users that need to be saved instead of every user which depending on how many users you have can be lots of queries on the database.

Offline MrPresident

  • Ulysses Team Member
  • Hero Member
  • *****
  • Posts: 2554
  • Karma: 391
    • View Profile
    • |G4P| Gman4President
Re: ULib MySQL Authentication
« Reply #9 on: April 04, 2015, 04:07:30 PM »
Also, thanks for pointing out the connection callbacks. I didn't know those existed.

Offline Bite That Apple

  • Hero Member
  • *****
  • Posts: 848
  • Karma: 388
  • Apple Innovations 2010®
    • View Profile
    • Fun 4 Everyone Gaming
Re: ULib MySQL Authentication
« Reply #10 on: April 05, 2015, 01:37:51 AM »
Also, thanks for pointing out the connection callbacks. I didn't know those existed.

Yeah, most of my server runs on MySQL; utime, lastlogin, chats, private addons I've made, ect.
Quote from: John F. Kennedy 1963
A man may die, nations may rise and fall, but an idea lives on.
In most cases I do not make addons for people. You may ask, and I may or may not accept, paying me will not change the answer if it was no. Feel free to try.
4-8-15-16-23-42


My Created Addons:
> Player Connect/Disconnect Message < ---- > URL Playsound Addon < ---------- > Leaderboards Records < ----------------
> Player Chat Tags < ----------------------------- > Last Joined Checker < ------------- > Workshop Dupes Permissions < ------
> Utime Hour Changer Addon < ---------------- > Autopromote XGUI Version < ---- > UtimeMOO Server/Global Edition < --
> Give Weapon Addon < ------------------------- > Player Force Spawn < ------------- >
> Set Modelany Addon < ------------------------- > Set Spawn Points < ---------------- >
> Apple H.U.D. < ----------------------------------- > Advanced Anti Noclip Killing < -- >

Offline MrPresident

  • Ulysses Team Member
  • Hero Member
  • *****
  • Posts: 2554
  • Karma: 391
    • View Profile
    • |G4P| Gman4President
Re: ULib MySQL Authentication
« Reply #11 on: April 27, 2015, 07:04:55 PM »
Heavily modified.
I did a pure conversion before which meant that whenever the SaveUsers function was called, it wanted to do queries for every user in the users table. Which could have been THOUSANDS. I didn't see this as a problem because when I tested it I have 9 and it worked fine.

Since it's not writing a file anymore, there was no need to override users who's info hasn't changed.
The system will now only query the database and update information for players who's information has changed. This should cut back a ton on SQL server load and the system should actually work not for people with larger databases.

Offline Durpy?

  • Newbie
  • *
  • Posts: 6
  • Karma: 0
    • View Profile
Re: ULib MySQL Authentication
« Reply #12 on: February 26, 2016, 06:32:59 PM »
This might be a bit late but I'm currently having issues with this. The issue is ranks resettings due to an auto promotion system I have.

In a nutshell, when a player connects to the server, the db is offline and the server isn't connected to it, resulting in your rank being a regular user, during that time it takes too long for the server to connect to the db before the server starts the auto promotion, and in turn overriding your original rank. Is there any way around this?

Offline MrPresident

  • Ulysses Team Member
  • Hero Member
  • *****
  • Posts: 2554
  • Karma: 391
    • View Profile
    • |G4P| Gman4President
Re: ULib MySQL Authentication
« Reply #13 on: February 26, 2016, 08:08:29 PM »
Not really. Is your database hosted externally? Free Database hosts are typically pretty slow.

This is a hacky implementation of MYSQL for ULib, it's not really something I want to spend a lot of time trying to perfect.
If it works, it works, otherwise, you might want to look for another solution for cross-server ranks.

Offline JamminR

  • Ulysses Team Member
  • Hero Member
  • *****
  • Posts: 7707
  • Karma: 350
  • Sertafide Ulysses Jenius
    • View Profile
    • Team Ulysses [ULib/ULX, other fine releases]
Re: ULib MySQL Authentication
« Reply #14 on: February 27, 2016, 06:40:19 AM »
Additionally, we (Team Ulysses) see this issue repeatedly with non-mySQL authentication and autopromote scripts.
I'm of the belief that, preferably, don't use them, and if your going to use them, find/have the author of the one you use make them more tolerant to delays in authentication.
Our forum has several posts over the years reporting this same issue due to autopromote scripts.
The price one pays for pursuing any profession or calling is an intimate knowledge of its ugly side. - James Baldwin