Inconsistent Player UUIDs

I am storing player UUIDs in a database when they connect for the first time. However, if they then run a command that finds them via UUID it is unable to find them. Any help?

 

Napkin222 If you are using offline mode they will be inconsistent.
Switch to online mode.

 

When you say 'Database', do you mean MySQL?

MySQL is finicky when it comes to a query which stores values. If you do not surround the UUID with ''s in the query, for example, it will not store correctly. This was an odd issue I found by chance, while working on a SQL heavy plugin.

If you could, paste your code which connects to the database, as well as the query you're using.

 

Jhtzb The server is in online mode.

FerusGrim

Code:java

//To add a player:
statement.execute("REPLACE INTO squibblygoo SET uuid='" + UUIDFetcher.getUUIDOf(e.getPlayer().getName()).toString() + "', username='" + e.getPlayer().getName() + "'");
 
//To find a player:
ResultSet set = statement.executeQuery("SELECT * FROM squibblygoo WHERE uuid='" + UUIDFetcher.getUUIDOf(p.getName()).toString() + "'");

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

Napkin222

I would recommend, for the sake of human-readability, that you stop using statement(), and switch to using preparedStatement().

Code:java

PreparedStatement ps = conn.prepareStatement("REPLACE INTO sqibblygoo SET uuid=?, username=?");
ps.setString(1, UUIDFetcher.getUUIDOf(e.getPlayer().getName()).toString();
ps.setString(2, e.getPlayer().getName());

prepareStatement will automatically prepend and append ''s to your query, so do NOT surround the ?s with them.

 

Napkin222 One thing I recommend doing though is saving the UUID as a string without "-"s, saves a bit of space and all you need to do is add back the "-"s when fetching it from the database.

Code sniplet:

PHP:

public String addBackDashes(String uuid) {
    return uuid.replaceAll("(\w{8})(\w{4})(\w{4})(\w{4})(\w{12})", "$1-$2-$3-$4-$5");
}

 

Thank you, I've replaced them all with PreparedStatements, will test now.

 

The server is in online-mode, but these are the UUIDs from the same player:

Code:

   
 
    5d02033c-3c66-3de2-bf68-5f13fa0f9e95    => UUID From Server
    3e9b7245-0741-4904-a7db-b1fee6115e51    => UUID In Database
 

Anyone? This will break all my plugins

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

Napkin222 Try using this to get the uuids

 

That's what I'm using.

Azubuso

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

also make sure your case is correct as how the case is with the orginal player.

if you are getting the UUID's from a fetcher which connects to the site of mojang uppercase and lowercase has a nasty side effect because it returns a other UUID rather than it should return the same, I don't know if it suposed to work like that since ive seen this close when they launched the Mojang accounts client.

 

xize Actually the request is case insensitive. (Click)

Napkin222 That's really strange...hmm

 

Here is my entire class if that will help:

Code:java

package me.llamositopia.kunal.squibblygoo;
 
import com.evilmidget38.UUIDFetcher;
import com.vexsoftware.votifier.model.Vote;
import com.vexsoftware.votifier.model.VotifierEvent;
import org.bukkit.Bukkit;
import org.bukkit.ChatColor;
import org.bukkit.Material;
import org.bukkit.command.Command;
import org.bukkit.command.CommandSender;
import org.bukkit.entity.Player;
import org.bukkit.event.EventHandler;
import org.bukkit.event.Listener;
import org.bukkit.event.inventory.InventoryInteractEvent;
import org.bukkit.event.player.PlayerJoinEvent;
import org.bukkit.inventory.Inventory;
import org.bukkit.inventory.ItemStack;
import org.bukkit.inventory.meta.ItemMeta;
import org.bukkit.plugin.java.JavaPlugin;
 
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.logging.Logger;
 
public class Main extends JavaPlugin implements Listener, Runnable{
 
    Logger log;
    private Connection connection;
    private Statement statement;
 
    ArrayList<String> thisMonth_l = new ArrayList<>(), lastMonth_l = new ArrayList<>();
 
    public void onEnable() {
        log = this.getLogger();
        saveDefaultConfig();
        {
            log.info("Connecting to MySQL database....");
            String ip = getConfig().getString("connection.ip", "localhost");
            String username = getConfig().getString("connection.username", "squibblygoo");
            String password = getConfig().getString("connection.password", "12345678");
            String database = getConfig().getString("connection.database", "squibblygoo");
            int port = getConfig().getInt("connection.port", 3306);
            String connectionFull = "jdbc:mysql://" + ip + ":" + port + "/" + database;
            log.info("Using connection protocol: " + connectionFull);
            try {
                connection = DriverManager.getConnection(connectionFull, username, password);
                log.info("Connected to database. Creating statement....");
                statement = connection.createStatement();
                log.info("Statement created. Creating friends table if not already existing....");
                statement.execute("CREATE TABLE IF NOT EXISTS squibblygoo (uuid VARCHAR(50) NOT NULL PRIMARY KEY, username VARCHAR(16) NOT NULL, votes INT NOT NULL DEFAULT 0)");
                statement.execute("CREATE TABLE IF NOT EXISTS squibblygoo_old SELECT uuid, username, votes FROM squibblygoo");
            } catch (SQLException e) {
                log.info("Could not set up interactions with database.... Disabling....");
                Bukkit.getPluginManager().disablePlugin(this);
                return;
            }
        }
        log.info("Tables created. Registering listeners and timers....");
        Bukkit.getPluginManager().registerEvents(this, this);
        Bukkit.getScheduler().scheduleSyncRepeatingTask(this, this, 200, 200);
        log.info("Registered. Creating inventory items....");
        log.info("SquibblyGoo enabled.");
    }
 
    public void onDisable() {
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        log.info("SquibblyGoo has been disabled.");
    }
 
    public boolean onCommand(CommandSender sender, Command command, String label, String[] args) {
        System.out.println("Executed command: " + command.getName());
        if (command.getName().equalsIgnoreCase("vote")){
            System.out.println("Executed command vote: sender instanceof Player: " + (sender instanceof Player));
            if (!(sender instanceof Player)){
                sender.sendMessage(getConfig().getString("vote.link"));
                return true;
            }
            System.out.println("Executing showPlayerStats((Player) sender);");
            showPlayerStats((Player) sender);
            return true;
        }
        return false;
    }
 
    public void run() {
        if (getConfig().getInt("automated.oldMonth")!=Calendar.getInstance().get(Calendar.MONTH)){
            try {
                statement.execute("DROP TABLE squibblygoo_old");
                statement.execute("CREATE TABLE IF NOT EXISTS squibblygoo_old SELECT uuid, username, votes FROM squibblygoo");
                statement.execute("DROP TABLE squibblygoo");
                statement.execute("CREATE TABLE IF NOT EXISTS squibblygoo (uuid VARCHAR(50) NOT NULL PRIMARY KEY, username VARCHAR(16) NOT NULL, votes INT NOT NULL DEFAULT 0)");
                for (Player a : Bukkit.getOnlinePlayers()){
                    PreparedStatement ps = connection.prepareStatement("REPLACE INTO squibblygoo SET uuid=?, username=?");
                    ps.setString(1, UUIDFetcher.getUUIDOf(a.getName()).toString());
                    ps.setString(2, a.getName());
                    ps.execute();
                }
                getConfig().set("automated.oldMonth", Calendar.getInstance().get(Calendar.MONTH));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
 
    @EventHandler
    public void onPlayerConnect(PlayerJoinEvent e){
        try {
            PreparedStatement ps = connection.prepareStatement("UPDATE squibblygoo SET username=? WHERE uuid=?");
            ps.setString(1, e.getPlayer().getName());
            ps.setString(2, UUIDFetcher.getUUIDOf(e.getPlayer().getName()).toString());
            ps.execute();
            PreparedStatement ps_old = connection.prepareStatement("UPDATE squibblygoo_old SET username=? WHERE uuid=?");
            ps_old.setString(1, e.getPlayer().getName());
            ps_old.setString(2, UUIDFetcher.getUUIDOf(e.getPlayer().getName()).toString());
            ps_old.execute();
            PreparedStatement ps_new = connection.prepareStatement("REPLACE INTO squibblygoo_old SET username=?, uuid=?");
            ps_new.setString(1, e.getPlayer().getName());
            ps_new.setString(2, UUIDFetcher.getUUIDOf(e.getPlayer().getName()).toString());
            ps_new.execute();
        } catch (Exception e1) {
            e1.printStackTrace();
        }
    }
 
    @EventHandler
    public void onInventoryEvent(InventoryInteractEvent e){
        e.setCancelled(e.getInventory().getName().equals(ChatColor.DARK_AQUA + "Vote Menu"));
    }
 
    @EventHandler
    public void onVote(VotifierEvent e){
        Vote vote = e.getVote();
        Player p = Bukkit.getPlayer(vote.getUsername());
        if (p==null){
            return;
        }
        try {
            PreparedStatement ps = connection.prepareStatement("SELECT * FROM squibblygoo WHERE uuid=?");
            ps.setString(1, UUIDFetcher.getUUIDOf(p.getName()).toString());
            ResultSet set = ps.executeQuery();
            int votes = set.getInt("votes");
            votes++;
            PreparedStatement ps2 = connection.prepareStatement("UPDATE squibblygoo SET votes=? WHERE uuid=?");
            ps2.setInt(1, votes);
            ps2.setString(2, UUIDFetcher.getUUIDOf(p.getName()).toString());
            ps2.execute();
        } catch (Exception e1) {
            e1.printStackTrace();
        }
    }
 
    private void loadVoteStats() {
        try {
            thisMonth_l.clear();
            lastMonth_l.clear();
            ResultSet set = statement.executeQuery("SELECT * FROM squibblygoo ORDER BY votes DESC");
            for (int j = 0; j < 10; j++) {
                set.next();
                thisMonth_l.add(ChatColor.GOLD + set.getString("username") + ": " + ChatColor.YELLOW + set.getInt("votes"));
            }
            set = statement.executeQuery("SELECT * FROM squibblygoo_old ORDER BY votes DESC");
            for (int j = 0; j < 10; j++) {
                set.next();
                lastMonth_l.add(ChatColor.GOLD + set.getString("username") + ": " + ChatColor.YELLOW + set.getInt("votes"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
 
    private void showPlayerStats(Player p){
        try {
            System.out.println("Retrieving player data from database....");
            PreparedStatement ps = connection.prepareStatement("SELECT * FROM squibblygoo WHERE uuid=?");
            ps.setString(1, UUIDFetcher.getUUIDOf(p.getName()).toString());
            ResultSet set = ps.executeQuery();
            set.next();
            int votesThisMonth = set.getInt("votes");
            System.out.println("Retrieving old player data from database....");
            PreparedStatement ps_old = connection.prepareStatement("SELECT * FROM squibblygoo WHERE uuid=?");
            ps_old.setString(1, UUIDFetcher.getUUIDOf(p.getName()).toString());
            set = ps_old.executeQuery();
            set.next();
            int votesLastMonth = set.getInt("votes");
            ArrayList<String> personal_l = new ArrayList<>();
            personal_l.add(ChatColor.GRAY + "Your votes this month: " + ChatColor.DARK_AQUA + votesThisMonth);
            personal_l.add(ChatColor.GRAY + "Your votes last month: " + ChatColor.DARK_AQUA + votesLastMonth);
            System.out.println("Creating inventory....");
            Inventory i = Bukkit.createInventory(null, 9, "Vote Menu");
            System.out.println("Adding items....");
            ItemStack personal = new ItemStack(Material.EYE_OF_ENDER, 1);
            ItemStack thisMonth = new ItemStack(Material.GOLD_HELMET, 1);
            ItemStack lastMonth = new ItemStack(Material.DIAMOND_HELMET, 1);
            ItemStack link = new ItemStack(Material.EMERALD, 1);
            ItemMeta pe = personal.getItemMeta();
            ItemMeta tm = thisMonth.getItemMeta();
            ItemMeta lm = lastMonth.getItemMeta();
            ItemMeta li = link.getItemMeta();
            pe.setDisplayName(ChatColor.DARK_AQUA + "Your Votes");
            tm.setDisplayName(ChatColor.DARK_AQUA + "Top Voters - This Month");
            lm.setDisplayName(ChatColor.DARK_AQUA + "Top Voters - Last Month");
            li.setDisplayName(ChatColor.GREEN + "Click here to vote!");
            pe.setLore(personal_l);
            tm.setLore(thisMonth_l);
            lm.setLore(lastMonth_l);
            personal.setItemMeta(pe);
            thisMonth.setItemMeta(tm);
            lastMonth.setItemMeta(lm);
            link.setItemMeta(li);
            i.setItem(1, personal);
            i.setItem(3, thisMonth);
            i.setItem(5, lastMonth);
            i.setItem(7, link);
            System.out.println("Opening inventory for player....");
            p.openInventory(i);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
}
 

Azubuso
FerusGrim
xize

 

Azubuso
Yea they probably changed that, I'm not up to date anymore since ive made my own system around it I only know it was a cause at the beginning I think when 1.7.2 was released shortly, but I never knew if case sensitivy was actually a bug or intended behaviour where it returned the wrong uuid based on the wrong case.

Napkin222
Do what FerusGrim said, he actually made a topic about it where he explains if you make a table with the name uuid it simply transforms every entry on that table to a UUID which means that you are double converting a uuid..., strange enough I can't believe it myself because I would think the table won't be stored like a type maybe only with the exception of a _ which means a auto increment atleast in sqlite, but else the type would/should be defined on the second collom like:

table|type|can be null or is primary

It goes against my whole theory but it seems its true if you read it here:
http://forums.bukkit.org/threads/no...ingly-random-uuids-mysql.269562/#post-2500594