Mariadb issues

Discussion in 'Systems Administration' started by Mithrandir, Nov 20, 2018.

  1. Hi everyone.
    So I'm not very good at administrating databases apparently, even though I thought it would be fairly simple.

    I've recently migrated my network to a new host, and with it, it's whole mariadb database.
    What I did in order to move the database was simply create a new one on the new host, export everything from the old one on the old host, and import on the new mariadb. Then, I made sure there was only 3 accounts (like in the old one).
    Two of them are only accessible from @ localhost and have full permissions over the database, one of these two is dedicated for server plugin usage, the 3rd is accessible from my website's IP and is read only for stats.

    I've been having connection errors though, which can sometimes cause partial or complete network crashes. Sometimes even the whole OS can freeze, rendering absolutely uncontrollable through ssh. These sql issues usually happen at least every 24h.

    Here's some errors I keep finding:
    Error on Network crash, from Hub (Same error on all of my other network servers, before Ubuntu freezes), also checked current connection ammount and it's usually around 81/1000:
    https://hastebin.com/evulaburov.sql
    Some connection leak errors popping up (No crash, just warns):
    https://pastebin.com/ny4mD7b8
    Small errors showing up in batches every now and then:
    https://pastebin.com/VUmNrrmS
    https://pastebin.com/RLGdjRBK
    New one I found on bungeecord, I may try checking out what it's suggesting:
    https://pastebin.com/U2cJC6An

    I know LuckPerms is written all over those two first errors, but the database may still be poorly configured on my side, allthough I don't know what else I could do so that it's propperly set up. I also tried setting the database's max_connections to 300 (previously 100), and tampering with LuckPerms config (Which had no issues before the host migration).

    Hoping and appreciating if someone can help!
     
    #1 Mithrandir, Nov 20, 2018
    Last edited: Nov 24, 2018
  2. Did you modify/create the same accounts on the new MariaDB database? Also do they have the same passwords?

    The bungeecord problem is pretty easy solved. your wait_timeout in the my.cnf is lower than the maximum lifetime of your luckperms process

    Example:

    Wait_Timeout: 200
    Maximum-lifetime: 300
    NOT POSSIBLE

    Wait-Timeout: 300
    Maximum-lifetime: 200
    POSSIBLE

    Let me know how this goes.
     
  3. @Killerate953 Thanks for the answer. I'm currently still experiencing the database issues.

    I re-created the same users, with the same passwords, however, some differences may be present permission-wise. The current permissions are as explained 1 account is only read, and only accessible from website, the other two have full permissions and are only accessible from localhost, one of them being root, allthough I think I made that account from scratch, just like the others, by first deleting all accesses.

    As for now, my database's "wait_timeout" variable is set to: 28800 seconds = 480 min
    And as for LuckPerms: maximum-lifetime: 300000 # 5 minutes (originally milliseconds)
    I saw the solution you suggest on luckperms's wiki, and made sure it was way below. A user on github even suggested me to set it way low as a test, which didn't help.

    Let me know if you have any more ideas.
     
  4. Try lowering the wait timeout to 240 minutes and try again.
    Also try lowering your max connection amount to 200? Because 1000 is alot and could potentially be a security issue later on.

    If this doesnt work, let me know :)
     
  5. @Killerate953

    Did both.
    First impression is that these errors still pop up, at least right now, allthough I must say the servers still haven't been restarted:

    https://pastebin.com/skvGh2aE

    I'll let you know if there's any crashes/changes after restarts. Allthough I do suspect the issue is not yet solved.
     
  6. Yeah it's getting pretty spammy... doesn't look like it helped much xD
    upload_2018-12-1_20-42-46.png
     
  7. Can you send the config.yml in a pastebin to this thread?
     
  8. Ok, but it won't include passwords, and I'm assuming you meant LuckPerms:

    https://pastebin.com/0gTsuNvH

    In any case, I didn't even make any changes to it after or before host change.
     
  9. Try setting messaging-service: none to sql
     
  10. @Killerate953
    Odd, I tried to do what you said, but it doesn't seem to be changing:
    upload_2018-12-3_22-16-27.png
    And I think I'm doing it right?
     
  11. Well now we know where it is going wrong.
    Can you check if this setting has been applied to the my.cnf file in /etc/mysql?

    EDIT:

    Can you also pastebin the latest MariaDB logs in this thread?
    You can find these within: /var/log/mysql
     
    #13 Killerate953, Dec 4, 2018
    Last edited: Dec 4, 2018
  12. @Killerate953 Hey, sorry for the large response intervals :)

    show variables like "wait_timeout"; returns 28800 as shown.
    However, even though I set wait_timeout to 240, it's shown as 600 within my.cnf (If I'm looking at this right):

    upload_2018-12-4_19-1-54.png

    This is all I see within /var/log/mysql, no idea what I'm looking at...:

    upload_2018-12-4_19-5-33.png

    Also tried to access through Filezilla but I get a permission denied message:

    Screenshot_6.png
     
  13. Well there is your problem..

    You havent changed the wait_timeout in the mysql config file (my.cnf)
    Change the wait_timeout there as well
    And then in luckperms put your maximum_connection time to a variable under that wait_timeout.
    if you understand what I mean.
     
  14. @Killerate953
    Sorry for delay, was fixing something else at the same time xD
    I just set max_connections from my.cnf to 240 secods (If it actually is seconds, it's kinda confusing that such variable is here but there's a diferent value viewable through commands) and maximum-lifetime from LuckPerms as you can see:

    upload_2018-12-4_22-49-10.png
    + Also Restarted all servers except bungee.

    Same errors or similar:
    https://hastebin.com/pumapimepa.md
     
  15. In theory it's 3 minutes maximum-lifetime and < 4 minutes max_connections on my.cnf... shouldn't that be enough? :unsure:
     
  16. I would make a bigger gap, lets say 20 min wait_timeout.
    300 max_connection as luckperms already uses 10 per server
    maximum-lifetime would be 10 minutes I suppose.
     
  17. It's been around 1h since I applied your changes and I can't see any errors.
    Looks like that may have done it! But if that's not the case, I'll let you know...
    In any case, thank you very much for your help in this matter :D
     

Share This Page