Solved MySQL mass insert acting up

Hey guys

So in my plugin I need to convert neary 10000 lines of config entries into a MySQL database. This is what that file looks like(not full thing):
http://hastebin.com/jaxoyidiva.sm

I know, not a nice config setup but it wasn't my plugin so I cannot do anything about it. Anyways, I needed to get all those values and put it into a mysql database. I have three columns: Location as a string, Generator type as a string, and an int as an int.

Code:

"CREATE TABLE IF NOT EXISTS chests (Location tinytext, Generator tinytext, ToAdd int)"

That is what I am calling to create my table. This works perfect. The next thing I do is loop through all the values in the config file and put them in a hashmap.

Code:

    public void convertOldChests() {
        FileConfiguration config = oldConfig.getOldConfig();
        HashMap<Location, ChestGeneratorType> chests = new HashMap<Location, ChestGeneratorType>();
        int count = 0;
        for (String s : config.getKeys(false)) {
            String generator = config.getString(s).toLowerCase();
            String[] split = s.split("_");

            if (Bukkit.getWorld(split[3]) == null) {
                continue;
            }

            Location l = new Location(Bukkit.getWorld(split[3]), Double.parseDouble(split[0]), Double.parseDouble(split[1]), Double.parseDouble(split[2]));
            if (generators.containsKey(generator)) {
                count++;
                chests.put(l, generators.get(generator));
                //generators.get(generator).addNewChest(new Chests(l, generators.get(generator), 0));
            }
        }
        methods.addChests(chests);
        getConfig().set("convertedOldChests", true);
        saveConfig();
        Bukkit.getConsoleSender().sendMessage(ChatColor.GREEN + "[ParallaxGens] Converted Old Chests! " + count);
    }

This code runs really fast, not having any issues with it. Anyways after it loops through all the config values and saves them into the hashmap "chests", it runs the addChests method. This is a method which does the mass insert into MySQL:

Code:

    public void addChests(HashMap<Location, ChestGeneratorType> map) {
        StringBuilder mysqlS = new StringBuilder("INSERT INTO chests (Location, Generator, ToAdd) VALUES ");
        StringBuilder dataDump = new StringBuilder("");

        int runs = 0;
        for (Location l : map.keySet()) {
            map.get(l).chests.add(new Chests(l, map.get(l), 0));
            dataDump.append(l.toString() + "=" + map.get(l).configName);
            dataDump.append("\n");

            if (runs == map.keySet().size() - 1) {
                mysqlS.append("('" + Utils.locationToString(l) + "','" + map.get(l).configName + "','" + 0 + "')");
                break;
            }
            mysqlS.append("('" + Utils.locationToString(l) + "','" + map.get(l).configName + "','" + 0 + "'), ");
            runs++;
        }

        System.out.print(mysqlS);
        PrintWriter writer = null;
        PrintWriter writer2 = null;
        try {
            writer = new PrintWriter("plugins/ParallaxGens/mysqlDump.txt", "UTF-8");
            writer2 = new PrintWriter("plugins/ParallaxGens/dataDump.txt", "UTF-8");
            writer.println(mysqlS);
            writer2.println(dataDump);
            writer.close();
            writer2.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        java.sql.PreparedStatement statement;
        try {
            statement = plugin.sql.openConnection().prepareStatement(mysqlS.toString());
            statement.executeUpdate();
        } catch (SQLException sqlE) {
            sqlE.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

Now whats weird is that, this only inserts 1513 values into MySQL, even though it should be inputting 9800+. To prove I was doing it right, I printed two text files which show the data in the hashmap and what the full query looks like.

HashMap dump: https://dl.dropboxusercontent.com/u/104089738/dumps/dataDump.txt
MySQL Query Dump: https://dl.dropboxusercontent.com/u/104089738/dumps/mysqlDump.txt

If you search for the ","s in the Query dump, and devide by 3 that represents the number of values, which is around 9800+. This is correct. Searching for the word "Location" in the hashmap dump also brings back a count of 9800+. This is right.

So why when I run this mass insert, it only inputs 1513 values into MySQL? And to make it more weird, it only inputs values where the generator is "irongenerator"... https://gyazo.com/6a163d5619635d1372028b5a1c42cdcf

Even more weird is that in the dump files, there is only 1513 "irongenerator" values. So what, is MySQL somehow favoring this generator type, or am I doing something wrong?

Please help. This is just so confusing for me. MySQL should be able to handle hundreds of thousands of inserts at a time, right?

Thanks,
HeyAwesomePeople

NOTE: I also tried to just upload to MySQL as it read the config. So it would read the value in the config then immeditaly upload to the database. Same result. Only 1513 entries with then all being irongenerator...

 

Looks like 23 lines, not 10000. Big difference between 23 and 10000 you know.

 

@henderry2019 Opps I just selected a small portion of the file. In reality there is 9800 lines:

https://gyazo.com/dd99176543738ff98ae9de1af96430ce

 

@HeyAwesomePeople

Please tell me you're not trying to run this on the server, this should be a separate application.

Have you ensured that the HashMap contains what you're expecting?

 

Yeah I have ensured that. I did a dump of the HashMap and it's exactly what I want.

https://dl.dropboxusercontent.com/u/104089738/dumps/dataDump.txt

And yes I'm running this on a server but it only executes once in the plugins lifetime on startup. Then all other MySQl calls are done ASync. This is not the issue though.

@mythbusterma

Using MySQL workbench, running this query: https://dl.dropboxusercontent.com/u/104089738/dumps/mysqlDump.txt
I am able to input all the values I needed to. This makes it an issue in my code somehow. Now I just need to figure out how this could be...

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

 

@HeyAwesomePeople

Oh. I just realised what your issue was. Your query is way too long. MySQL is perfectly capable of many inserts, but it expects them in smaller batches. Also, you should start using prepared statements properly.

Do this instead:

Code:

for (Location location: map.keySet()) {
     PreparedStatement ps = connection.prepareStatement("INSERT INTO chests (Location, Generator, ToAdd) VALUES (?,?,?);");
      ps.setString(1, ...);
      ps.setString(2, ...);
      ps.setString(3, ...);
      ps.executeUpdate();
}

Finally, remove your public variable "sql," that's just sloppy.

 

Another guy told me to use batchUpdates.

Code:

        try {
            statement = plugin.sql.openConnection().prepareStatement("INSERT INTO chests (Location, Generator, ToAdd) VALUES (?,?,?)");
            for (Map.Entry<Location, ChestGeneratorType> entry : map.entrySet()) {
                Location           loc  = entry.getKey();
                ChestGeneratorType type = entry.getValue();
                type.chests.add(new Chests(loc, type, 0));

                statement.setString(1, Utils.locationToString(loc));
                statement.setString(2, type.configName);
                statement.setInt(3, 0);

                statement.addBatch();
            }

            statement.executeBatch();
        } catch (BatchUpdateException e) {
            int[] i = e.getUpdateCounts();
            Bukkit.getConsoleSender().sendMessage("Values: " + i);
            e.printStackTrace();
        } catch (SQLException sqlE) {
            sqlE.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

Sounds promising, but still didn't work. It only inputted 1513 entries, all with the column "Generator" being "irongenerator". And yeah I will remove that public sql.

Edit: The value of stateent.executeBatch() is an array list with 9857 values. Doesn't this mean all of it should've been executed?

 

@HeyAwesomePeople

You were saying something about the rows not being the correct values, are you sure that you're not getting duplicates?

 

@mythbusterma

Yes I am sure. The Location value is the only thing that has the potential to be Unique, but duplicates do not occur.

My gosh...

Code:

    public void addChests(HashMap<Location, ChestGeneratorType> map) {
        StringBuilder dataDump = new StringBuilder("");


        java.sql.PreparedStatement statement;
        try {
            statement = plugin.sql.openConnection().prepareStatement("INSERT INTO chests (Location, Generator, ToAdd) VALUES (?,?,?);");
            for (Map.Entry<Location, ChestGeneratorType> entry : map.entrySet()) {
                Location           loc  = entry.getKey();
                ChestGeneratorType type = entry.getValue();
                type.chests.add(new Chests(loc, type, 0));

                statement.setString(1, Utils.locationToString(loc));
                statement.setString(2, type.configName);
                statement.setInt(3, 0);

                statement.addBatch();
            }

            statement.executeBatch();

            ResultSet rs = null;
            rs = statement.executeQuery("SELECT * FROM chests");

            while (rs.next()) {
                dataDump.append("Location: " + rs.getString(1) + " Generator: " + rs.getString(2) + " ToAdd: " + rs.getString(3) + "\n");
            }

        } catch (BatchUpdateException e) {
            int[] i = e.getUpdateCounts();
            Bukkit.getConsoleSender().sendMessage("Values: " + i);
            e.printStackTrace();
        } catch (SQLException sqlE) {
            sqlE.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

        PrintWriter writer = null;
        try {
            writer = new PrintWriter("plugins/ParallaxGens/callDump.txt", "UTF-8");
            writer.println(dataDump);
            writer.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

    }

Okay this is weird. I execute the Batch then immediately select everything from the table and put it into a txt file. That text file contains EVERYTHING, all 9800+ lines like it should... How can I be getting a resultset from the database larger than what the database even contains?
https://dl.dropboxusercontent.com/u/104089738/dumps/callDump.txt

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

 

@HeyAwesomePeople

Try checking the information you're looking for using the MySQL CLI instead of the Workbench.

 

@mythbusterma

What do you mean? To view database I have used phpmyadmin.

EDIT: Returning the data in a separate call still results in 1513 entries being returned(on the server).

@mythbusterma
Ugh dammit I found the issue.

Essentially I was running TRUNCATE on the table right after I put in all the irongenerators, which makes sense on why it just stuck to irongenerators. Thanks guys

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