WORLDGUARD-3355 - Reported by neoteknic

Hello,
I have a bug with WG on server start, maybe a problem in my DB ?
DB not modified, no pb with craftbukkit 1.7 and 5.6 WG.
Happen on b5 and snapshot 01-27
craftbukkit build : 01-27
SQL user have full access on the DB

[22:38:52] [Server thread/INFO]: [WorldGuard] Loaded configuration for world 'mainhub_nether'
[22:38:52] [Server thread/INFO]: [WorldGuard] Loading region data...
[22:38:52] [Server thread/INFO]: [WorldGuard] Unloading and saving region data that is currently loaded...
[22:38:52] [Server thread/INFO]: [WorldGuard] Migrating regions in 'creative' to convert names -> UUIDs...
[22:38:52] [Server thread/INFO]: [WorldGuard] Database: jdbc:mysql://localhost/minecraft (MySQL 5.6)
[22:38:52] [Server thread/INFO]: [WorldGuard] Current version of schema `minecraft`: 2
[22:38:52] [Server thread/INFO]: [WorldGuard] Schema `minecraft` is up to date. No migration necessary.
[22:38:52] [Server thread/INFO]: [WorldGuard] Loading region data for loaded worlds...
[22:38:52] [Server thread/ERROR]: Error occurred while enabling WorldGuard v6.0.0-SNAPSHOT.1573- (Is it up to date?)
java.lang.RuntimeException: An unexpected error occurred (loaded.get() returned null)
at com.sk89q.worldguard.protection.managers.storage.sql.DataLoader.loadFlags(DataLoader.java:243) ~[?:?]
at com.sk89q.worldguard.protection.managers.storage.sql.DataLoader.load(DataLoader.java:80) ~[?:?]
at com.sk89q.worldguard.protection.managers.storage.sql.SQLRegionDatabase.loadAll(SQLRegionDatabase.java:215) ~[?:?]
at com.sk89q.worldguard.protection.managers.migration.UUIDMigration.migrate(UUIDMigration.java:79) ~[?:?]
at com.sk89q.worldguard.protection.managers.migration.AbstractMigration.migrate(AbstractMigration.java:57) ~[?:?]
at com.sk89q.worldguard.bukkit.RegionContainer.migrate(RegionContainer.java:280) ~[?:?]
at com.sk89q.worldguard.bukkit.RegionContainer.autoMigrate(RegionContainer.java:299) ~[?:?]
at com.sk89q.worldguard.bukkit.RegionContainer.initialize(RegionContainer.java:91) ~[?:?]
at com.sk89q.worldguard.bukkit.WorldGuardPlugin.onEnable(WorldGuardPlugin.java:178) ~[?:?]
at org.bukkit.plugin.java.JavaPlugin.setEnabled(JavaPlugin.java:321) ~[craftbukkit.jar:git-Bukkit-15e81cf]
at org.bukkit.plugin.java.JavaPluginLoader.enablePlugin(JavaPluginLoader.java:327) [craftbukkit.jar:git-Bukkit-15e81cf]
at org.bukkit.plugin.SimplePluginManager.enablePlugin(SimplePluginManager.java:404) [craftbukkit.jar:git-Bukkit-15e81cf]
at org.bukkit.craftbukkit.v1_8_R1.CraftServer.loadPlugin(CraftServer.java:341) [craftbukkit.jar:git-Bukkit-15e81cf]
at org.bukkit.craftbukkit.v1_8_R1.CraftServer.enablePlugins(CraftServer.java:313) [craftbukkit.jar:git-Bukkit-15e81cf]
at net.minecraft.server.v1_8_R1.MinecraftServer.q(MinecraftServer.java:394) [craftbukkit.jar:git-Bukkit-15e81cf]
at net.minecraft.server.v1_8_R1.MinecraftServer.k(MinecraftServer.java:362) [craftbukkit.jar:git-Bukkit-15e81cf]
at net.minecraft.server.v1_8_R1.MinecraftServer.a(MinecraftServer.java:317) [craftbukkit.jar:git-Bukkit-15e81cf]
at net.minecraft.server.v1_8_R1.DedicatedServer.init(DedicatedServer.java:190) [craftbukkit.jar:git-Bukkit-15e81cf]
at net.minecraft.server.v1_8_R1.MinecraftServer.run(MinecraftServer.java:473) [craftbukkit.jar:git-Bukkit-15e81cf]
at java.lang.Thread.run(Thread.java:745) [?:1.8.0_25 

Comment by neoteknic

I have this in my DB (migrate table) :
ersion_rank installed_rank version description type script checksum installed_by installed_on execution_time success
1 1 1 << Flyway Init >> INIT << Flyway Init >> NULL minecraft 2015-01-27 21:21:29 0 1
2 2 2 Bug fix and UUID SQL V2__Bug_fix_and_UUID.sql -512531648 minecraft 2015-01-27 21:21:30 346 1

Comment by neoteknic

All my users record have a NULL uuid value

Comment by cbarber

Sorry this is a year late, but I was able to reproduce the issue on WorldGuard 6.1. The problem is caused by deleting a region that has flags defined. The flags remained orphaned in the region_flags table causing the load issue on next restart.

Comment by bloodrayne1995

@cbarber We have this error, too. But if I understand the source code on GitHub right it occurs if the world has no entries in _regions-Table.
According to the source code on Github (com/sk89q/worldguard/protection/managers/storage/sql/DataLoader.java:75) it tries to load following data:

• Cuboids
• Polygons
• Globals
• Flags
• Users
• Groups

The first three points will be executed with no errors. But flags loading (line 220, void loadFlags) generates the error.
In detail it creates a query of the _region_flags and execute it.
Next it read all data from the coming ResultSet and import it into the HashMap data. But then (i dont understand why) it tries to map all flags to the regions. But the fact that the world got no regions the next selection go into the else-branch and throws this error.
Maybe I missunderstanding this, but for my opinion it needs a selction before the mapping, if the world has got regions.

If the developer need some logfiles or a database export, he can write me an email.

Greetings,
EONABloodrayne

Comment by cbarber

The correct fix is to not leave orphaned records in the database. The schema should make use of foreign keys, not null constrains, and either delete rows properly in the code, or use cascade.

Comment by wizjany

if this issue is a big concern to you i'd suggest fixing it and making a PR.

Comment by cbarber

I worked around the issue by switching to the YAML backend instead of MYSQL so I have very little vested interest. Just stating what should be done for the MYSQL backend to be considered usable.

Comment by wizjany

that is exactly the reason this won't really get fixed.

Comment by cbarber

Submit a bug bounty to encourage it to be fixed.

Comment by wizjany

with what motivation?

Comment by bloodrayne1995

I try to make a workaround as a push request this evening.

Comment by bloodrayne1995

ok, the reason of this failure is as mentioned @Crcbarber.
To find these regions which causes the error I have created a SQL Query

SELECT r1.region_id
FROM <prefix>region_flag r1
WHERE r1.world_id=<worldId> AND r1.region_id NOT IN (SELECT r2.id FROM <prefix>region r2 WHERE r2.world_id=<worldId>);

U have to replace with the configured prefix within the worlguard config and u have to replace with the world-id of the world which generates this error. the world-id can be find in the world Table.

As Correction in the plugin, it has to select all region-flags of regions, they are in this world AND in the region-table.

The code have to be like this:

PreparedStatement stmt = closer.register(conn.prepareStatement(
                    "SELECT region_id, flag, value " +
                    "FROM " + config.getTablePrefix() + "region_flag " +
                    "WHERE world_id = " + worldId + " AND region_id IN (SELECT id FROM " +    config.getTablePrefix() + "region WHERE world_id=" + worldId + ")"));

Comment by cbarber

This issue still exists. Current migrations do no correctly add foreign keys to an existing schema because Flyway is being used incorrectly. Correct fix is to add a new migration that adds the foreign keys if they do not exist. Links to relevant lines of code causing the issue were deleted.