我自己的mysql server sqlnontransientconnectionexception出现java sql错误

jvidinwx  于 2021-06-18  发布在  Mysql
关注(0)|答案(0)|浏览(167)

我已经编写了一个discord bot,它在我的pc上使用mysql非常好,但是在上传到我的服务器后出现了以下错误:

Thu Oct 25 02:04:23 CEST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

在“public”服务器的功能正常(db4free.net)上,仅显示以下消息:

Thu Oct 25 02:01:04 CEST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

我的代码:
(它在“publicstaticvoidconnect(jdajda)”上失败)

package org.spielearena.core;

import net.dv8tion.jda.core.JDA;
import net.dv8tion.jda.core.entities.Guild;
import net.dv8tion.jda.core.entities.TextChannel;
import org.spielearena.util.RandomString;
import org.spielearena.util.config;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Random;

public class mysql {

    public static Connection con = null;
    public static ArrayList<TextChannel> asyncChannel = new ArrayList<>();

    public static void connect(JDA jda) {
        try {
            if (con == null) {
                con = DriverManager.getConnection("jdbc:mysql://" + config.config.getString("config.mysql.server") + ":" + config.config.getString("config.mysql.port") + "/" + config.config.getString("config.mysql.database") + "?autoReconnect=true", config.config.getString("config.mysql.user"), config.config.getString("config.mysql.pass"));
                PreparedStatement ps = con.prepareStatement("CREATE TABLE IF NOT EXISTS `" + config.config.getString("config.mysql.prefix") + "keys`  ( `serverkey` VARCHAR(500), `serverid` VARCHAR(500), `channelid` VARCHAR(500), `async` VARCHAR(500)) ENGINE = InnoDB;");
                ps.executeUpdate();
                ps = con.prepareStatement("CREATE TABLE IF NOT EXISTS `" + config.config.getString("config.mysql.prefix") + "toDiscord`  ( `serverkey` VARCHAR(500), `message` VARCHAR(5000)) ENGINE = InnoDB;");
                ps.executeUpdate();
                ps = con.prepareStatement("CREATE TABLE IF NOT EXISTS `" + config.config.getString("config.mysql.prefix") + "fromDiscord`  ( `serverkey` VARCHAR(500), `message` VARCHAR(50000), `unixTime` VARCHAR(500)) ENGINE = InnoDB;");
                ps.executeUpdate();
                ResultSet rs = con.createStatement().executeQuery("SELECT * FROM " + config.config.getString("config.mysql.prefix") + "keys");
                while (rs.next()) {
                    if (!existsGuildAndChannel(jda, rs.getString("serverid"), rs.getString("channelid"))) {
                        ps = con.prepareStatement("DELETE FROM " + config.config.getString("config.mysql.prefix") + "keys WHERE serverkey='" + rs.getString("serverkey") + "'");
                        ps.executeUpdate();
                    } else {
                        if (rs.getString("async").equalsIgnoreCase("yes")) {
                            asyncChannel.add(jda.getGuildById(rs.getString("serverid")).getTextChannelById(rs.getString("channelid")));
                        }
                    }
                }
            } else {
               throw new Exception("Internal error #001");
            }
        } catch (Exception e) {
            System.out.println("MySQL error: " + e);
        }
    }

    public static boolean existsGuildAndChannel(JDA jda, String guild, String channel) {
        try {
            return jda.getGuildById(guild).getTextChannelById(channel).canTalk();
        } catch (Exception e) {}
        return false;
    }

    public static void disconnect() {
        try {
            if (con != null) {
                con.close();
                con = null;
            } else {
                throw new Exception("Internal error #001");
            }
        } catch (Exception e) {
            System.out.println("MySQL error: " + e);
        }
    }

    public static void sync(String message, TextChannel channel) {
        try {
            if (con != null) {
                if (asyncChannel.contains(channel)) {
                    ResultSet rs = con.createStatement().executeQuery("SELECT * FROM " + config.config.getString("config.mysql.prefix") + "keys WHERE channelid='" + channel.getId() + "' AND serverid='" + channel.getGuild().getId() + "';");
                    while (rs.next()) {
                        PreparedStatement ps = con.prepareStatement("INSERT INTO " + config.config.getString("config.mysql.prefix") + "fromDiscord (serverkey, message, unixTime) VALUES ('" + rs.getString("serverkey") + "', '" + message + "', '" + Integer.toString(((int)(System.currentTimeMillis() / 1000L))) + "')");
                        ps.execute();
                    }
                }
            }
        } catch (Exception e) {
            System.out.println("MySQL error: " + e);
        }
    }

    public static void sync(JDA jda) {
        try {
            if (con != null) {
                ArrayList<TextChannel> asyncChannel1 = new ArrayList<>();
                ResultSet rs2 = con.createStatement().executeQuery("SELECT * FROM " + config.config.getString("config.mysql.prefix") + "keys");
                while (rs2.next()) {
                    if (!existsGuildAndChannel(jda, rs2.getString("serverid"), rs2.getString("channelid"))) {
                        PreparedStatement ps = con.prepareStatement("DELETE FROM " + config.config.getString("config.mysql.prefix") + "keys WHERE serverkey='" + rs2.getString("serverkey") + "'");
                        ps.executeUpdate();
                    } else {
                        if (rs2.getString("async").equalsIgnoreCase("yes")) {
                            asyncChannel1.add(jda.getGuildById(rs2.getString("serverid")).getTextChannelById(rs2.getString("channelid")));
                        }
                    }
                }
                asyncChannel = asyncChannel1;
                ResultSet rs = con.createStatement().executeQuery("SELECT * FROM " + config.config.getString("config.mysql.prefix") + "toDiscord");
                while (rs.next()) {
                    PreparedStatement ps = con.prepareStatement("DELETE FROM " + config.config.getString("config.mysql.prefix") + "toDiscord WHERE serverkey='" + rs.getString("serverkey") + "' AND message='" + rs.getString("message") + "'");
                    ps.execute();
                    ResultSet rs1 = con.createStatement().executeQuery("SELECT * FROM " + config.config.getString("config.mysql.prefix") + "keys WHERE serverkey='" + rs.getString("serverkey") + "'");
                    while (rs1.next()) {
                        if (!existsGuildAndChannel(jda, rs1.getString("serverid"), rs1.getString("channelid"))) {
                            ps = con.prepareStatement("DELETE FROM " + config.config.getString("config.mysql.prefix") + "keys WHERE serverkey='" + rs.getString("serverkey") + "'");
                            ps.executeUpdate();
                        } else {
                            jda.getGuildById(rs1.getString("serverid")).getTextChannelById(rs1.getString("channelid")).sendMessage(rs.getString("message")).queue();
                        }
                    }
                }
            } else {
                throw new Exception("Internal error #001");
            }
        } catch (Exception e) {
            System.out.println("MySQL error: " + e);
        }
    }

    public static String generateKey(Guild guild, TextChannel channel, Boolean async) {
        try {
            if (con != null) {
                String asy = "no";
                if (async) {
                    asy = "yes";
                    asyncChannel.add(channel);
                }
                RandomString rnd = new RandomString(32, new Random());
                String key = "";
                boolean finish = false;
                try {
                    ResultSet rs = con.createStatement().executeQuery("SELECT * FROM " + config.config.getString("config.mysql.prefix") + "keys WHERE serverid='" + guild.getId() + "'");
                    while (rs.next()) {
                        if (rs.getString("channelid").equalsIgnoreCase(channel.getId())) {
                            return rs.getString("serverkey");
                        }
                    }
                } catch (Exception e) {
                    System.out.println("MySQL error: " + e);
                }
                while (!finish) {
                    key = rnd.nextString();
                    try {
                        boolean alredyExists = false;
                        ResultSet rs = con.createStatement().executeQuery("SELECT * FROM " + config.config.getString("config.mysql.prefix") + "keys WHERE serverkey='" + key + "'");
                        while (rs.next()) {
                            alredyExists = true;
                        }
                        if (!alredyExists) {
                            PreparedStatement ps = con.prepareStatement("INSERT INTO " + config.config.getString("config.mysql.prefix") + "keys (serverkey, serverid, channelid, async) VALUES ('" + key + "', '" + guild.getId() + "', '" + channel.getId() + "', '" + asy + "')");
                            ps.execute();
                            finish = true;
                        }
                    } catch (Exception e) {
                        System.out.println("MySQL error: " + e);
                    }
                }
                return key;
            } else {
                throw new Exception("Internal error #001");
            }
        } catch (Exception e) {
            System.out.println("MySQL error: " + e);
        }
        return null;
    }

}

我的服务器:
5.7.24-mysql社区服务器(gpl)
linux版本4.9.0-8-amd64(debian-kernel@lists.debian.org)(gcc版本6.3.0 20170516(debian 6.3.0-18+deb9u1))#1 smp debian 4.9.110-3+deb9u6(2018-10-08)
openjdk版本“1.8.0\u 181”openjdk运行时环境(build 1.8.0\u 181-8u181-b13-1~deb9u1-b13)openjdk 64位服务器虚拟机(build 25.181-b13,混合模式)

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题