MySQL: MySQLIntegrityConstraintViolationException
tim-schneider opened this issue ยท 9 comments
Bug report
Players horses can't be deleted due to a foreign key. Some players with dead horses can't view their horse list because it only deleted a part of the databases entries about the horse.
How to reproduce the bug ?
- A players horse died some way
- execute /zh list or /zh free or /zh admin burial or /zh admin clear
Console output
[11:16:30] [Server thread/INFO]: Schneidertm issued server command: /zh list -t playername
[11:16:30] [Server thread/WARN]: java.lang.reflect.InvocationTargetException
[11:16:30] [Server thread/WARN]: at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[11:16:30] [Server thread/WARN]: at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
[11:16:30] [Server thread/WARN]: at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
[11:16:30] [Server thread/WARN]: at java.base/java.lang.reflect.Constructor.newInstance(Unknown Source)
[11:16:30] [Server thread/WARN]: at com.github.xibalba.zhorse.managers.CommandManager.onCommand(CommandManager.java:57)
[11:16:30] [Server thread/WARN]: at org.bukkit.command.PluginCommand.execute(PluginCommand.java:44)
[11:16:30] [Server thread/WARN]: at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:151)
[11:16:30] [Server thread/WARN]: at org.bukkit.craftbukkit.v1_12_R1.CraftServer.dispatchCommand(CraftServer.java:677)
[11:16:30] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.PlayerConnection.handleCommand(PlayerConnection.java:1472)
[11:16:30] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.PlayerConnection.a(PlayerConnection.java:1277)
[11:16:30] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.PacketPlayInChat.a(PacketPlayInChat.java:45)
[11:16:30] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.PacketPlayInChat.a(PacketPlayInChat.java:5)
[11:16:30] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.PlayerConnectionUtils.lambda$ensureMainThread$0(PlayerConnectionUtils.java:14)
[11:16:30] [Server thread/WARN]: at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
[11:16:30] [Server thread/WARN]: at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
[11:16:30] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.SystemUtils.a(SourceFile:46)
[11:16:30] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.MinecraftServer.D(MinecraftServer.java:843)
[11:16:30] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.DedicatedServer.D(DedicatedServer.java:426)
[11:16:30] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.MinecraftServer.C(MinecraftServer.java:767)
[11:16:30] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.MinecraftServer.run(MinecraftServer.java:665)
[11:16:30] [Server thread/WARN]: at java.base/java.lang.Thread.run(Unknown Source)
[11:16:30] [Server thread/WARN]: Caused by: java.lang.NullPointerException: Name is null
[11:16:30] [Server thread/WARN]: at java.base/java.lang.Enum.valueOf(Unknown Source)
[11:16:30] [Server thread/WARN]: at org.bukkit.entity.EntityType.valueOf(EntityType.java:18)
[11:16:30] [Server thread/WARN]: at com.github.xibalba.zhorse.commands.CommandList.buildVariantMessage(CommandList.java:116)
[11:16:30] [Server thread/WARN]: at com.github.xibalba.zhorse.commands.CommandList.buildAliveHorseList(CommandList.java:74)
[11:16:30] [Server thread/WARN]: at com.github.xibalba.zhorse.commands.CommandList.execute(CommandList.java:45)
[11:16:30] [Server thread/WARN]: at com.github.xibalba.zhorse.commands.CommandList.<init>(CommandList.java:31)
[11:16:30] [Server thread/WARN]: ... 21 more
[11:18:45] [Server thread/INFO]: Schneidertm issued server command: /zh free -t playername -i 1
[11:18:46] [Server thread/WARN]: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`minecraftdb`.`zhorse_inventory_item`, CONSTRAINT `fk_inventory_item_horse` FOREIGN KEY (`uuid`) REFERENCES `zhorse_horse` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION)
[11:18:46] [Server thread/WARN]: at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[11:18:46] [Server thread/WARN]: at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
[11:18:46] [Server thread/WARN]: at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
[11:18:46] [Server thread/WARN]: at java.base/java.lang.reflect.Constructor.newInstance(Unknown Source)
[11:18:46] [Server thread/WARN]: at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
[11:18:46] [Server thread/WARN]: at com.mysql.jdbc.Util.getInstance(Util.java:408)
[11:18:46] [Server thread/WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
[11:18:46] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
[11:18:46] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
[11:18:46] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
[11:18:46] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
[11:18:46] [Server thread/WARN]: at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
[11:18:46] [Server thread/WARN]: at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
[11:18:46] [Server thread/WARN]: at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
[11:18:46] [Server thread/WARN]: at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
[11:18:46] [Server thread/WARN]: at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
[11:18:46] [Server thread/WARN]: at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
[11:18:46] [Server thread/WARN]: at com.github.xibalba.zhorse.database.SQLDatabaseConnector.executeUpdate(SQLDatabaseConnector.java:73)
[11:18:46] [Server thread/WARN]: at com.github.xibalba.zhorse.database.SQLDatabaseConnector.executeUpdate(SQLDatabaseConnector.java:67)
[11:18:46] [Server thread/WARN]: at com.github.xibalba.zhorse.managers.DataManager.removeHorse(DataManager.java:575)
[11:18:46] [Server thread/WARN]: at com.github.xibalba.zhorse.commands.CommandFree.removeLostHorse(CommandFree.java:70)
[11:18:46] [Server thread/WARN]: at com.github.xibalba.zhorse.commands.CommandFree.execute(CommandFree.java:49)
[11:18:46] [Server thread/WARN]: at com.github.xibalba.zhorse.commands.CommandFree.<init>(CommandFree.java:37)
[11:18:46] [Server thread/WARN]: at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[11:18:46] [Server thread/WARN]: at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
[11:18:46] [Server thread/WARN]: at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
[11:18:46] [Server thread/WARN]: at java.base/java.lang.reflect.Constructor.newInstance(Unknown Source)
[11:18:46] [Server thread/WARN]: at com.github.xibalba.zhorse.managers.CommandManager.onCommand(CommandManager.java:57)
[11:18:46] [Server thread/WARN]: at org.bukkit.command.PluginCommand.execute(PluginCommand.java:44)
[11:18:46] [Server thread/WARN]: at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:151)
[11:18:46] [Server thread/WARN]: at org.bukkit.craftbukkit.v1_12_R1.CraftServer.dispatchCommand(CraftServer.java:677)
[11:18:46] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.PlayerConnection.handleCommand(PlayerConnection.java:1472)
[11:18:46] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.PlayerConnection.a(PlayerConnection.java:1277)
[11:18:46] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.PacketPlayInChat.a(PacketPlayInChat.java:45)
[11:18:46] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.PacketPlayInChat.a(PacketPlayInChat.java:5)
[11:18:46] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.PlayerConnectionUtils.lambda$ensureMainThread$0(PlayerConnectionUtils.java:14)
[11:18:46] [Server thread/WARN]: at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
[11:18:46] [Server thread/WARN]: at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
[11:18:46] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.SystemUtils.a(SourceFile:46)
[11:18:46] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.MinecraftServer.D(MinecraftServer.java:843)
[11:18:46] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.DedicatedServer.D(DedicatedServer.java:426)
[11:18:46] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.MinecraftServer.C(MinecraftServer.java:767)
[11:18:46] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.MinecraftServer.run(MinecraftServer.java:665)
[11:18:46] [Server thread/WARN]: at java.base/java.lang.Thread.run(Unknown Source)
Environment specifications
- Operating system : Linux (Ubuntu 16.04.4)
- Java version : 9
- Server version : 1.12.2 (git-Paper-1337)
- ZHorse version : 1.6.9
- Database : MySQL
- MySQL version: 5.7.21
Thanks. I'll try this next time. I already removed every entry of the dead horses which were associated with the player in question which fixed the problem for now. Is the table inventory_item abandoned?
Yep, it has been replaced by horse_inventory table in 1.6.9. The thing is that I completely forgot about the foreign key in inventory_item, thus preventing removal of horses from the database..
Could you confirm that simply deleting this table completely fixes this issue ?
If I try to execute DELETE FROM minicraft.zhorse_horse where owner = "1ce**d1a-d**a-4c**-****-c6ce8a702218"; this is the output:
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`minicraft`.`zhorse_horse_inventory`, CONSTRAINT `fk_horse_inventory_horse` FOREIGN KEY (`uuid`) REFERENCES `zhorse_horse` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION)
Hey Zedd7,
sorry, my response did take that long but it now occurred again. As you suggested I dropped the inventory_item but sadly the error is the same as before:
[19:23:50] [Server thread/INFO]: Schneidertm issued server command: /zh list -t buggedPlayer32
[19:23:50] [Server thread/WARN]: java.lang.reflect.InvocationTargetException
[19:23:50] [Server thread/WARN]: at jdk.internal.reflect.GeneratedConstructorAccessor328.newInstance(Unknown Source)
[19:23:50] [Server thread/WARN]: at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
[19:23:50] [Server thread/WARN]: at java.base/java.lang.reflect.Constructor.newInstance(Unknown Source)
[19:23:50] [Server thread/WARN]: at com.github.xibalba.zhorse.managers.CommandManager.onCommand(CommandManager.java:57)
[19:23:50] [Server thread/WARN]: at org.bukkit.command.PluginCommand.execute(PluginCommand.java:44)
[19:23:50] [Server thread/WARN]: at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:151)
[19:23:50] [Server thread/WARN]: at org.bukkit.craftbukkit.v1_12_R1.CraftServer.dispatchCommand(CraftServer.java:677)
[19:23:50] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.PlayerConnection.handleCommand(PlayerConnection.java:1472)
[19:23:50] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.PlayerConnection.a(PlayerConnection.java:1277)
[19:23:50] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.PacketPlayInChat.a(PacketPlayInChat.java:45)
[19:23:50] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.PacketPlayInChat.a(PacketPlayInChat.java:5)
[19:23:50] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.PlayerConnectionUtils.lambda$ensureMainThread$0(PlayerConnectionUtils.java:14)
[19:23:50] [Server thread/WARN]: at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
[19:23:50] [Server thread/WARN]: at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
[19:23:50] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.SystemUtils.a(SourceFile:46)
[19:23:50] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.MinecraftServer.D(MinecraftServer.java:843)
[19:23:50] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.DedicatedServer.D(DedicatedServer.java:426)
[19:23:50] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.MinecraftServer.C(MinecraftServer.java:767)
[19:23:50] [Server thread/WARN]: at net.minecraft.server.v1_12_R1.MinecraftServer.run(MinecraftServer.java:665)
[19:23:50] [Server thread/WARN]: at java.base/java.lang.Thread.run(Unknown Source)
[19:23:50] [Server thread/WARN]: Caused by: java.lang.NullPointerException: Name is null
[19:23:50] [Server thread/WARN]: at java.base/java.lang.Enum.valueOf(Unknown Source)
[19:23:50] [Server thread/WARN]: at org.bukkit.entity.EntityType.valueOf(EntityType.java:18)
[19:23:50] [Server thread/WARN]: at com.github.xibalba.zhorse.commands.CommandList.buildVariantMessage(CommandList.java:116)
[19:23:50] [Server thread/WARN]: at com.github.xibalba.zhorse.commands.CommandList.buildAliveHorseList(CommandList.java:74)
[19:23:50] [Server thread/WARN]: at com.github.xibalba.zhorse.commands.CommandList.execute(CommandList.java:45)
[19:23:50] [Server thread/WARN]: at com.github.xibalba.zhorse.commands.CommandList.<init>(CommandList.java:31)
[19:23:50] [Server thread/WARN]: ... 20 more
In this case two horses have even the same id - I don't think thats beneficial:
