I'm trying to fix a little plugin that I'm making for MineCraft servers. The plugin uses code that tries to automatically adjusts to the server needs, first converting the tables on old tables, creating the new ones and after using some objects that contains human decisions to parse or update specific information to the new tables, parsing all the data that is not duplicated already to the new tables, then removing the old ones.
The code is kinda messy, I didn't had lot of time this days, but I was trying to get a free week to remake all the code of the plugin. The problem is that everything was working fine, but one day I decided to update the plugin on a server that I use for testing, that is using MySQL. The problem in this server is that I was using the same code all the time and I didn't had problems, but after some time without using it, now it's not working.
This is the part of the code that is failing:
protected boolean tables() {
boolean update = false, result = update;
if (!this.sql.execute(
"CREATE TABLE IF NOT EXISTS information(param VARCHAR(16),value VARCHAR(16),CONSTRAINT PK_information PRIMARY KEY (param));",
new Data[0]))
return false;
List<String> tlist = new ArrayList<>();
try {
this.sql.execute("SET FOREIGN_KEY_CHECKS=0;", new Data[0]);
ResultSet set = this.sql.query("SELECT value FROM information WHERE `param`='version';", new Data[0]);
String version = "";
if (set.next())
version = set.getString(1);
if (!version.equals(MMOHorsesMain.getPlugin().getDescription().getVersion())) {
update = true;
ResultSet tables = this.sql.query("SHOW TABLES;", new Data[0]);
while (tables.next()) {
String name = tables.getString(1);
if (!name.equals("information")) {
if (!this.sql.execute("CREATE TABLE " + name + "_old LIKE " + name + ";", new Data[0]))
throw new Exception();
if (!this.sql.execute("INSERT INTO " + name + "_old SELECT * FROM " + name + ";", new Data[0]))
throw new Exception();
tlist.add(name);
}
}
String remove = "";
for (String table : tlist)
remove = String.valueOf(remove) + (remove.isEmpty() ? "" : ",") + table;
this.sql.reconnect();
this.sql.execute("DROP TABLE IF EXISTS " + remove + ";", new Data[0]);
The database stores an extra data that it's the version of the plugin. I use it to check if the database is from another version and, if that's the case, regenerate the database. It's working fine on SQLite, but the only problem comes here on MySQL.
The first part gets the actual version and checks. The plugin starts disabling the foreign keys. This is not the best part but as I said, I didn't actually had time to remake all this code, also this code comes from a compiled version cause due some GitHub issues I lost part of the last updates. If it requires the update, it starts transforming every table on _old tables. Everything works fine here, data is parsed to the _old tables and is managed correctly, but the problem is when it has to removes the original tables.
DROP TABLE IF EXISTS cosmetics,horses,inventories,items,trust,upgrades;
This is the SQL statement that is used to remove the original ones, but, I don't know if it works like that, but if that's the case, the _old tables got the foreign keys that the original tables too and when I try to remove them, it doesn't allow, even if the FOREIGN_KEY_CHECKS is on 0. I also set a debug before to check if the checking was disabled and it was. To simulate the best environment where people is used to work, I'm using a prebuilder minecraft hosting from a friend, using MariaDB 10.4.12.
I'm asking him if he updated it since the last time I was preparing this server, but I'm still waiting for his answer. Anyway, even if it's a newer or older MariaDB version, what I'm trying is to make it the most elastic possible so it can be adapted to different versions without problems. Everything seems to work fine, but as I can't delete the original databases, I can't replace them with the new format.
I wish this is just an error that happens with certain DB configurations, but I'd like to get an answer of someone with knowledge to make sure I didn't upload a broken version.
