Cannot Use MySQL as Storage Method

Question

Cannot Use MySQL as Storage Method

Chihsiao opened this issue 5 years ago · 7 comments

Chihsiao commented 5 years ago

I am not a native English speaker. If there is something wrong with my expressions, I hope it wouldn't trouble you. 😅

I have tried both MySQL and MariaDB as Storage Method, only to find errors, while other plugins using the same database work greatly. When I issued perms info, finding it seems that LuckPerms has connected with the database. But it was said "Table 'minecraft.perms_players' doesn't exist. " when I attempted to join the server.

BTW, I have googled and found it that many people using Hikari run into the same problem, and it works for them to remove autoReconnect and maxReconnects. I hope it could be useful. 😃

Pool Settings

pool-settings:

    # Sets the maximum size of the MySQL connection pool.
    # - Basically this value will determine the maximum number of actual
    #   connections to the database backend.
    # - More information about determining the size of connection pools can be found here:
    #   https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing
    maximum-pool-size: 10

    # Sets the minimum number of idle connections that the pool will try to maintain.
    # - For maximum performance and responsiveness to spike demands, it is recommended to not set
    #   this value and instead allow the pool to act as a fixed size connection pool.
    #   (set this value to the same as 'maximum-pool-size')
    minimum-idle: 10

    # This setting controls the maximum lifetime of a connection in the pool in milliseconds.
    # - The value should be at least 30 seconds less than any database or infrastructure imposed
    #   connection time limit.
    maximum-lifetime: 1800000 # 30 minutes

    # This setting controls the maximum number of milliseconds that the plugin will wait for a
    # connection from the pool, before timing out.
    connection-timeout: 5000 # 10 seconds

    # This setting allows you to define extra properties for connections.
    properties:
        useUnicode: true
        characterEncoding: utf8

`Perms Info`

> perms info
[18:04:05 INFO]: [LP] Running LuckPerms v5.0.72 by Luck.
[18:04:05 INFO]: [LP] -  Platform: Bukkit
[18:04:05 INFO]: [LP] -  Server Brand: Paper
[18:04:05 INFO]: [LP] -  Server Version:
[18:04:05 INFO]: [LP] -  git-Paper-1618 (MC: 1.12.2) - 1.12.2-R0.1-SNAPSHOT
[18:04:05 INFO]: [LP] -  Storage:
[18:04:05 INFO]: [LP] -     Type: MySQL
[18:04:05 INFO]: [LP] -     Ping: 25ms
[18:04:05 INFO]: [LP] -     Connected: true
[18:04:05 INFO]: [LP] -  Messaging: None
[18:04:05 INFO]: [LP] -  Instance:
[18:04:05 INFO]: [LP] -     Static contexts: server=survival
[18:04:05 INFO]: [LP] -     Online Players: 0 (0 unique)
[18:04:05 INFO]: [LP] -     Uptime: 7m 48s
[18:04:05 INFO]: [LP] -     Local Data: 0 users, 0 groups, 0 tracks

When Enabling

[17:56:17 INFO]: [LuckPerms] Enabling LuckPerms v5.0.72
[17:56:17 INFO]:         __    
[17:56:17 INFO]:   |    |__)   LuckPerms v5.0.72
[17:56:17 INFO]:   |___ |      Running on Bukkit - Paper
[17:56:17 INFO]: 
[17:56:17 INFO]: [LuckPerms] Loading configuration...
[17:56:17 INFO]: [LuckPerms] Loading storage provider... [MYSQL]
[17:56:17 INFO]: [me.lucko.luckperms.lib.hikari.HikariDataSource] luckperms-hikari - Starting...
[17:56:17 INFO]: [me.lucko.luckperms.lib.hikari.HikariDataSource] luckperms-hikari - Start completed.
[17:56:22 ERROR]: [LuckPerms] Failed to init storage implementation
[17:56:22 WARN]: java.sql.SQLTransientConnectionException: luckperms-hikari - Connection is not available, request timed out after 5001ms.
[17:56:22 WARN]:        at me.lucko.luckperms.lib.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:697)
[17:56:22 WARN]:        at me.lucko.luckperms.lib.hikari.pool.HikariPool.getConnection(HikariPool.java:196)
[17:56:22 WARN]:        at me.lucko.luckperms.lib.hikari.pool.HikariPool.getConnection(HikariPool.java:161)
[17:56:22 WARN]:        at me.lucko.luckperms.lib.hikari.HikariDataSource.getConnection(HikariDataSource.java:100)
[17:56:22 WARN]:        at me.lucko.luckperms.common.storage.implementation.sql.connection.hikari.HikariConnectionFactory.getConnection(HikariConnectionFactory.java:131)
[17:56:22 WARN]:        at me.lucko.luckperms.common.storage.implementation.sql.SqlStorage.init(SqlStorage.java:154)
[17:56:22 WARN]:        at me.lucko.luckperms.common.storage.Storage.init(Storage.java:114)
[17:56:22 WARN]:        at me.lucko.luckperms.common.storage.StorageFactory.getInstance(StorageFactory.java:88)
[17:56:22 WARN]:        at me.lucko.luckperms.common.plugin.AbstractLuckPermsPlugin.enable(AbstractLuckPermsPlugin.java:147)
[17:56:22 WARN]:        at me.lucko.luckperms.bukkit.LPBukkitBootstrap.onEnable(LPBukkitBootstrap.java:155)
[17:56:22 WARN]:        at org.bukkit.plugin.java.JavaPlugin.setEnabled(JavaPlugin.java:264)
[17:56:22 WARN]:        at org.bukkit.plugin.java.JavaPluginLoader.enablePlugin(JavaPluginLoader.java:316)
[17:56:22 WARN]:        at org.bukkit.plugin.SimplePluginManager.enablePlugin(SimplePluginManager.java:405)
[17:56:22 WARN]:        at org.bukkit.craftbukkit.v1_12_R1.CraftServer.enablePlugin(CraftServer.java:395)
[17:56:22 WARN]:        at org.bukkit.craftbukkit.v1_12_R1.CraftServer.enablePlugins(CraftServer.java:344)
[17:56:22 WARN]:        at net.minecraft.server.v1_12_R1.DedicatedServer.init(DedicatedServer.java:223)
[17:56:22 WARN]:        at net.minecraft.server.v1_12_R1.MinecraftServer.run(MinecraftServer.java:616)
[17:56:22 WARN]:        at java.lang.Thread.run(Thread.java:748)
[17:56:22 INFO]: [LuckPerms] Loading messaging service... [SQL]
[17:56:27 WARN]: java.lang.RuntimeException: java.sql.SQLTransientConnectionException: luckperms-hikari - Connection is not available, request timed out after 5000ms.
[17:56:27 WARN]:        at me.lucko.luckperms.common.messaging.sql.SqlMessenger.init(SqlMessenger.java:57)
[17:56:27 WARN]:        at me.lucko.luckperms.common.messaging.MessagingFactory$SqlMessengerProvider.obtain(MessagingFactory.java:144)
[17:56:27 WARN]:        at me.lucko.luckperms.common.messaging.LuckPermsMessagingService.<init>(LuckPermsMessagingService.java:73)
[17:56:27 WARN]:        at me.lucko.luckperms.common.messaging.MessagingFactory.getServiceFor(MessagingFactory.java:105)
[17:56:27 WARN]:        at me.lucko.luckperms.bukkit.messaging.BukkitMessagingFactory.getServiceFor(BukkitMessagingFactory.java:64)
[17:56:27 WARN]:        at me.lucko.luckperms.common.messaging.MessagingFactory.getInstance(MessagingFactory.java:83)
[17:56:27 WARN]:        at me.lucko.luckperms.common.plugin.AbstractLuckPermsPlugin.enable(AbstractLuckPermsPlugin.java:148)
[17:56:27 WARN]:        at me.lucko.luckperms.bukkit.LPBukkitBootstrap.onEnable(LPBukkitBootstrap.java:155)
[17:56:27 WARN]:        at org.bukkit.plugin.java.JavaPlugin.setEnabled(JavaPlugin.java:264)
[17:56:27 WARN]:        at org.bukkit.plugin.java.JavaPluginLoader.enablePlugin(JavaPluginLoader.java:316)
[17:56:27 WARN]:        at org.bukkit.plugin.SimplePluginManager.enablePlugin(SimplePluginManager.java:405)
[17:56:27 WARN]:        at org.bukkit.craftbukkit.v1_12_R1.CraftServer.enablePlugin(CraftServer.java:395)
[17:56:27 WARN]:        at org.bukkit.craftbukkit.v1_12_R1.CraftServer.enablePlugins(CraftServer.java:344)
[17:56:27 WARN]:        at net.minecraft.server.v1_12_R1.DedicatedServer.init(DedicatedServer.java:223)
[17:56:27 WARN]:        at net.minecraft.server.v1_12_R1.MinecraftServer.run(MinecraftServer.java:616)
[17:56:27 WARN]:        at java.lang.Thread.run(Thread.java:748)
[17:56:27 WARN]: Caused by: java.sql.SQLTransientConnectionException: luckperms-hikari - Connection is not available, request timed out after 5000ms.
[17:56:27 WARN]:        at me.lucko.luckperms.lib.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:697)
[17:56:27 WARN]:        at me.lucko.luckperms.lib.hikari.pool.HikariPool.getConnection(HikariPool.java:196)
[17:56:27 WARN]:        at me.lucko.luckperms.lib.hikari.pool.HikariPool.getConnection(HikariPool.java:161)
[17:56:27 WARN]:        at me.lucko.luckperms.lib.hikari.HikariDataSource.getConnection(HikariDataSource.java:100)
[17:56:27 WARN]:        at me.lucko.luckperms.common.storage.implementation.sql.connection.hikari.HikariConnectionFactory.getConnection(HikariConnectionFactory.java:131)
[17:56:27 WARN]:        at me.lucko.luckperms.common.messaging.sql.SqlMessenger.getConnection(SqlMessenger.java:85)
[17:56:27 WARN]:        at me.lucko.luckperms.common.messaging.sql.AbstractSqlMessenger.init(AbstractSqlMessenger.java:56)
[17:56:27 WARN]:        at me.lucko.luckperms.common.messaging.sql.SqlMessenger.init(SqlMessenger.java:55)
[17:56:27 WARN]:        ... 15 more
[17:56:27 WARN]: [LuckPerms] Messaging service 'sql' not recognised.
[17:56:28 INFO]: [LuckPerms] Loading internal permission managers...
[17:56:28 INFO]: [LuckPerms] Performing initial data load...
[17:56:33 WARN]: java.util.concurrent.CompletionException: java.sql.SQLTransientConnectionException: luckperms-hikari - Connection is not available, request timed out after 5000ms.
[17:56:33 WARN]:        at me.lucko.luckperms.common.storage.Storage.lambda$makeFuture$1(Storage.java:103)
[17:56:33 WARN]:        at java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1640)
[17:56:33 WARN]:        at java.util.concurrent.CompletableFuture$AsyncRun.exec(CompletableFuture.java:1632)
[17:56:33 WARN]:        at java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:289)
[17:56:33 WARN]:        at java.util.concurrent.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1056)
[17:56:33 WARN]:        at java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1692)
[17:56:33 WARN]:        at java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:157)
[17:56:33 WARN]: Caused by: java.sql.SQLTransientConnectionException: luckperms-hikari - Connection is not available, request timed out after 5000ms.
[17:56:33 WARN]:        at me.lucko.luckperms.lib.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:697)
[17:56:33 WARN]:        at me.lucko.luckperms.lib.hikari.pool.HikariPool.getConnection(HikariPool.java:196)
[17:56:33 WARN]:        at me.lucko.luckperms.lib.hikari.pool.HikariPool.getConnection(HikariPool.java:161)
[17:56:33 WARN]:        at me.lucko.luckperms.lib.hikari.HikariDataSource.getConnection(HikariDataSource.java:100)
[17:56:33 WARN]:        at me.lucko.luckperms.common.storage.implementation.sql.connection.hikari.HikariConnectionFactory.getConnection(HikariConnectionFactory.java:131)
[17:56:33 WARN]:        at me.lucko.luckperms.common.storage.implementation.sql.SqlStorage.loadAllGroups(SqlStorage.java:432)
[17:56:33 WARN]:        at me.lucko.luckperms.common.storage.Storage.lambda$loadAllGroups$10(Storage.java:194)
[17:56:33 WARN]:        at me.lucko.luckperms.common.storage.Storage.lambda$makeFuture$1(Storage.java:98)
[17:56:33 WARN]:        ... 6 more
[17:56:33 INFO]: [LuckPerms] Successfully enabled. (took 16075ms)

When Joining The Server.

[17:51:44 ERROR]: [LuckPerms] Exception occurred whilst loading data for a848f776-ae45-43a7-9020-470771e9bd01 - Yamazaki
[17:51:44 WARN]: java.util.concurrent.CompletionException: me.lucko.luckperms.lib.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'minecraft.perms_players' doesn't exist
[17:51:44 WARN]:        at me.lucko.luckperms.common.storage.Storage.lambda$makeFuture$0(Storage.java:90)
[17:51:44 WARN]:        at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1604)
[17:51:44 WARN]:        at java.util.concurrent.CompletableFuture$AsyncSupply.exec(CompletableFuture.java:1596)
[17:51:44 WARN]:        at java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:289)
[17:51:44 WARN]:        at java.util.concurrent.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1056)
[17:51:44 WARN]:        at java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1692)
[17:51:44 WARN]:        at java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:157)
[17:51:44 WARN]: Caused by: me.lucko.luckperms.lib.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'minecraft.perms_players' doesn't exist
[17:51:44 WARN]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[17:51:44 WARN]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
[17:51:44 WARN]:        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
[17:51:44 WARN]:        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
[17:51:44 WARN]:        at me.lucko.luckperms.lib.mysql.jdbc.Util.handleNewInstance(Util.java:425)
[17:51:44 WARN]:        at me.lucko.luckperms.lib.mysql.jdbc.Util.getInstance(Util.java:408)
[17:51:44 WARN]:        at me.lucko.luckperms.lib.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
[17:51:44 WARN]:        at me.lucko.luckperms.lib.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)
[17:51:44 WARN]:        at me.lucko.luckperms.lib.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
[17:51:44 WARN]:        at me.lucko.luckperms.lib.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
[17:51:44 WARN]:        at me.lucko.luckperms.lib.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675)
[17:51:44 WARN]:        at me.lucko.luckperms.lib.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
[17:51:44 WARN]:        at me.lucko.luckperms.lib.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1912)
[17:51:44 WARN]:        at me.lucko.luckperms.lib.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2020)
[17:51:44 WARN]:        at me.lucko.luckperms.lib.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
[17:51:44 WARN]:        at me.lucko.luckperms.lib.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
[17:51:44 WARN]:        at me.lucko.luckperms.common.storage.implementation.sql.SqlStorage.getPlayerName(SqlStorage.java:682)
[17:51:44 WARN]:        at me.lucko.luckperms.common.storage.implementation.sql.SqlStorage.savePlayerData(SqlStorage.java:609)
[17:51:44 WARN]:        at me.lucko.luckperms.common.storage.Storage.lambda$savePlayerData$20(Storage.java:258)
[17:51:44 WARN]:        at me.lucko.luckperms.common.storage.Storage.lambda$makeFuture$0(Storage.java:85)
[17:51:44 WARN]:        ... 6 more
[17:51:44 INFO]: Disconnecting com.mojang.authlib.GameProfile@2d465255[id=a848f776-ae45-43a7-9020-470771e9bd01,name=Yamazaki,properties={textures=[com.mojang.authlib.properties.Property@1a011e94]},legacy=false] (/127.0.0.1:63838): [LP] A database error occurred whilst loading permissions data. Please try again later. If you are a server admin, please check the console for any errors.

BrainStone · Answer 1 · 2020-03-09T19:02:39.000Z

Your error is covered here: https://github.com/lucko/LuckPerms/wiki/Storage-system-errors

Chihsiao · Answer 2 · 2020-03-09T19:17:50.000Z

@BrainStone I have tried before and it doesn't work yet.

BrainStone · Answer 3 · 2020-03-09T19:28:03.000Z

Well the issue is that LP can't connect to your MySQL server. It can't even reach it. So it is 100% your configuration. Not the plugin or Hikari.

electronicboy · Answer 4 · 2020-03-10T04:24:43.000Z

The fact that you have connections dying during startup is sketchy af, would suggest that either the mysql server is slow as heck or you're having connection issues to it in general

as for the other warning, looks like the connection failure likely caused the tables not to be setup properly, I would look into diagnosing the first set of issues and then just drop the lp tables and try again, schemas are here if needed, https://github.com/lucko/LuckPerms/tree/master/common/src/main/resources/me/lucko/luckperms/schema

Chihsiao · Answer 5 · 2020-03-10T00:16:26.000Z

I have confirmed it more than 3 times, to make sure the configuration is correct.
And I have said that after the server said Done (8.272s)! For help, type "help" or "?", it was said by perms info that LuckPerms had connected with MySQL.

> [18:04:05 INFO]: [LP] -  Storage:
> [18:04:05 INFO]: [LP] -     Type: MySQL
> [18:04:05 INFO]: [LP] -     Ping: 25ms
> [18:04:05 INFO]: [LP] -     Connected: true         <- HERE
> [18:04:05 INFO]: [LP] -  Messaging: None

Besides, if I put a wrong configuration, although the exceptions when starting are the same, the outputs of perms info are different, showing that the configuration is correct. Please note the differences between the outputs above and below.

> [23:52:51 INFO]: [LP] -  Storage:
> [23:52:51 INFO]: [LP] -     Type: MySQL
> [23:52:51 INFO]: [LP] -     Connected: false        <- HERE
> [23:52:51 INFO]: [LP] -  Messaging: None

There is no doubt that LuckPerms is indeed connected with MySQL after the server initializing.

Chihsiao · Answer 6 · 2020-03-10T08:28:05.000Z

Thanks for your tips. Importing schemas manually helps.

The fact that you have connections dying during startup is sketchy of, would suggest that either the MySQL server is slow as heck or you're having connection issues to it in general.

As for connections, I think that there shouldn't have been a problem.
There are some reasons:

The server was newly created, as well as the database.
The server enables few plugins using the database, all of which are not heavy-weight plugins, so there shouldn't be many connections even to make LuckPerms unable to connect the database when the server initializing.

Although importing schemas manually helps, messaging service is still unavailable and there are many annoying warnings. There might be some better solutions.

I could offer the connection properties for testing if needed.

lucko · Answer 7 · 2020-03-19T11:12:42.000Z

Sorry but it really does just come down to what's said here: https://github.com/lucko/LuckPerms/wiki/Storage-system-errors

The full extent of my suggestions regarding these sort of issues is detailed in that wiki article. Sorry there's nothing else I've got for ya.

Share to

Pool Settings

Perms Info

When Enabling

When Joining The Server.

`Perms Info`