Help with MySQL implementation.

Discussion in 'Spigot Plugin Development' started by chmod_777, Jan 8, 2020.

  1. Hi,
    I'm trying to implement database storage into my plugin, but I keep running into an error that occurs when the server tries to reach the MySQL server (both running on localhost):

    Code (Text):
    [17:36:04] [Server thread/WARN]: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

    The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    [17:36:04] [Server thread/WARN]:        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    [17:36:04] [Server thread/WARN]:        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    [17:36:04] [Server thread/WARN]:        at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    [17:36:04] [Server thread/WARN]:        at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    [17:36:04] [Server thread/WARN]:        at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    [17:36:04] [Server thread/WARN]:        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    [17:36:04] [Server thread/WARN]:        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
    [17:36:04] [Server thread/WARN]:        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:335)
    [17:36:04] [Server thread/WARN]:        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2187)
    [17:36:04] [Server thread/WARN]:        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2220)
    [17:36:04] [Server thread/WARN]:        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2015)
    [17:36:04] [Server thread/WARN]:        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:768)
    [17:36:04] [Server thread/WARN]:        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    [17:36:04] [Server thread/WARN]:        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    [17:36:04] [Server thread/WARN]:        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    [17:36:04] [Server thread/WARN]:        at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    [17:36:04] [Server thread/WARN]:        at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    [17:36:04] [Server thread/WARN]:        at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    [17:36:04] [Server thread/WARN]:        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    [17:36:04] [Server thread/WARN]:        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:385)
    [17:36:04] [Server thread/WARN]:        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:323)
    [17:36:04] [Server thread/WARN]:        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
    [17:36:04] [Server thread/WARN]:        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
    [17:36:04] [Server thread/WARN]:        at net.il0c4l.seasons.storage.MySQLDataHandler.openConnection(MySQLDataHandler.java:35)
    [17:36:04] [Server thread/WARN]:        at net.il0c4l.seasons.storage.MySQLDataHandler.<init>(MySQLDataHandler.java:22)
    [17:36:04] [Server thread/WARN]:        at net.il0c4l.seasons.Main.setStorageType(Main.java:39)
    [17:36:04] [Server thread/WARN]:        at net.il0c4l.seasons.Main.onEnable(Main.java:21)
    [17:36:04] [Server thread/WARN]:        at org.bukkit.plugin.java.JavaPlugin.setEnabled(JavaPlugin.java:263)
    [17:36:04] [Server thread/WARN]:        at org.bukkit.plugin.java.JavaPluginLoader.enablePlugin(JavaPluginLoader.java:352)
    [17:36:04] [Server thread/WARN]:        at org.bukkit.plugin.SimplePluginManager.enablePlugin(SimplePluginManager.java:417)
    [17:36:04] [Server thread/WARN]:        at org.bukkit.craftbukkit.v1_15_R1.CraftServer.enablePlugin(CraftServer.java:462)
    [17:36:04] [Server thread/WARN]:        at org.bukkit.craftbukkit.v1_15_R1.CraftServer.enablePlugins(CraftServer.java:376)
    [17:36:04] [Server thread/WARN]:        at net.minecraft.server.v1_15_R1.MinecraftServer.a(MinecraftServer.java:456)
    [17:36:04] [Server thread/WARN]:        at net.minecraft.server.v1_15_R1.DedicatedServer.init(DedicatedServer.java:266)
    [17:36:04] [Server thread/WARN]:        at net.minecraft.server.v1_15_R1.MinecraftServer.run(MinecraftServer.java:783)
    [17:36:04] [Server thread/WARN]:        at java.base/java.lang.Thread.run(Thread.java:835)
    [17:36:04] [Server thread/WARN]: Caused by: java.net.ConnectException: Connection refused (Connection refused)
    [17:36:04] [Server thread/WARN]:        at java.base/java.net.PlainSocketImpl.socketConnect(Native Method)
    [17:36:04] [Server thread/WARN]:        at java.base/java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:399)
    [17:36:04] [Server thread/WARN]:        at java.base/java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:242)
    [17:36:04] [Server thread/WARN]:        at java.base/java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:224)
    [17:36:04] [Server thread/WARN]:        at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:403)
    [17:36:04] [Server thread/WARN]:        at java.base/java.net.Socket.connect(Socket.java:591)
    [17:36:04] [Server thread/WARN]:        at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211)
    [17:36:04] [Server thread/WARN]:        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:299)
    [17:36:04] [Server thread/WARN]:        ... 28 more
     

    I read somewhere while trying to troubleshoot that communication link failures are frequently due to an idling connection, but it doesn't seem like the two ever talk in the first place. Im positive that the MySQL server is running and that user credentials and database name are consistent on database server and plugin config.

    When I use 'localhost' or '127.0.0.1' as the host address, I immediately get an error after 'openConnection()' is called when MySQLDataHandler is instantiated. When I use '0.0.0.0' as the host port, the error is thrown about 30 seconds after the function is called.

    Ive also read while learning sql and how to implement it into a plugin that database connections can run synchronously and asynchronously (not sure what the difference is) and I'm wondering if I'm just going about this the wrong way. Im somewhat new to coding and plugin development so that could completely be the case.

    Im not really sure if this is a problem with my code or the sql server and I've exhausted my efforts at troubleshooting online so if anyone can chime in that would be great.

    Code (Java):
    public class Main extends JavaPlugin {

        private DataHandler storage;

        @Override
        public void onEnable(){
            getLogger().log(Level.INFO, "Enabling seasons plugin!");
            saveDefaultConfig();
            setStorageType();
            new EntityDeathListener(this);

        }

        @Override
        public void onDisable(){
            if(storage.getClass().toString().contains("net.il0c4l.seasons.storage.MySQLDataHandler")){
                ((MySQLDataHandler) storage).closeConnection();
            }
        }


        public void setStorageType(){
            String type = DataHandler.getStorageType(getConfig());
            switch(type){
                case "mysql":
                    Map<String, Object> creds = getConfig().getConfigurationSection("storage.mysql").getValues(false);
                    storage = new MySQLDataHandler(this, (String) creds.get("username"), (String) creds.get("password"), (String) creds.get("host"), (String) creds.get("port"), (String) creds.get("dbname"));
                    getLogger().log(Level.INFO, "CONNECTED");
                    break;
                case "sqlite":
                    storage = new SQLiteDataHandler(this);
                    break;
                case "default":
                    getLogger().log(Level.WARNING, "No storage type chosen. Defaulting to flat file storage!");
                    getConfig().set("storage.yaml.use", true);
                case "yaml":
                    storage = new FlatDataHandler(this, "data.yml");
                    break;

            }
            getLogger().log(Level.INFO, "Set storage type to " + type);
        }
       
    }

    Code (Java):
    public abstract class DataHandler {

        private FileConfiguration config;
        private static final String[] STORAGE_LIST = {"mysql", "sqlite", "yaml"};

        public DataHandler(Main plugin){
            config = plugin.getConfig();
        }

        public static String getStorageType(FileConfiguration config){
            ConfigurationSection section = config.getConfigurationSection("storage");
            for(int i=0; i<section.getKeys(false).size(); i++){
                if(config.getBoolean("storage.type." + STORAGE_LIST[i])) {
                    return STORAGE_LIST[i];
                }
            }
            return "default";
        }

        public abstract boolean entryExists(UUID uuid);

        public abstract void removeEntry(UUID uuid);

        public abstract void addEntry(UUID uuid, int current, int progress);

        public abstract void setCurrent(UUID uuid, int current);

        public abstract void setProgress(UUID uuid, int progress);

    }

    Code (Java):
    public class MySQLDataHandler extends DataHandler {

        private final String URL, USERNAME, PASSWORD;
        private static Connection connection;

        public MySQLDataHandler(Main plugin, String username, String password, String host, String port, String dbName){
            super(plugin);
            this.PASSWORD = password;
            this.USERNAME = username;
            URL = String.format("jdbc:mysql://%1$s:%2$s/%3$s", host, port, dbName);
            openConnection();
            createTable("seasons");
        }

        public void openConnection(){
            try{
                Class.forName("com.mysql.jdbc.Driver");
            } catch(ClassNotFoundException e){
                e.printStackTrace();
                Bukkit.getServer().getLogger().log(Level.SEVERE, DRIVER_NOT_FOUND);
                return;
            }
            try{
                connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            } catch(SQLException e){
                e.printStackTrace();
                Bukkit.getServer().getLogger().log(Level.SEVERE, SQL_EXCEPTION_OPEN);
            }
        }

        public void closeConnection(){
            try{
                if(connection != null && !connection.isClosed()){
                    connection.close();
                }
            } catch(SQLException e){
                e.printStackTrace();
                Bukkit.getServer().getLogger().log(Level.SEVERE, SQL_EXCEPTION_CLOSE);
            }
        }

        public void createTable(String tableName){
            String sql = "CREATE TABLE IF NOT EXISTS " + tableName + "(" +
                    "uuid VARCHAR(64) NOT NULL AUTO_INCREMENT, " +
                    "current INT NOT NULL, " +
                    "progress INT NOT NULL, " +
                    "PRIMARY KEY ( uuid ) " +
            ");";

            try{
                PreparedStatement stmt = connection.prepareStatement(sql);
                stmt.executeUpdate();
            } catch(SQLException e){
                e.printStackTrace();
                Bukkit.getServer().getLogger().log(Level.SEVERE, SQL_EXCEPTION);
            }
        }


        @Override
        public boolean entryExists(UUID uuid) {
            return false;
        }

        @Override
        public void removeEntry(UUID uuid) {

        }

        @Override
        public void addEntry(UUID uuid, int current, int progress) {

        }

        @Override
        public void setCurrent(UUID uuid, int current) {

        }

        @Override
        public void setProgress(UUID uuid, int progress) {

        }

    Code (YAML):
    storage:
      type
    :
        mysql
    : true
        sqlite
    : false
        yaml
    : false

      mysql
    :
        host
    : 'localhost'
        port
    : '8080'
        username
    : ''
        password
    : ''
        dbname
    : 'seasons'
     
  2. The correct address is localhost if you’re running it locally. Connect to your database with MySQL client and make sure it is up. Make sure your port is correct. Make sure your credentials are correct.
     
  3. Are you sure that your mysql server running in 8080 port? By standard that is 3306.

    Be sure that your user and pasword are correct. That firewall rules doesn`t block connection and database exists.