com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: ',3'

Question

I'm trying to write a shop plugin for my Minecraft server, but I keep getting an error whenever anyone does the /purchase command.

Here's the error:

2012-07-03 04:27:28 [SEVERE]    com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: ',3'
2012-07-03 04:27:28 [SEVERE]    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3591)
2012-07-03 04:27:28 [SEVERE]    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
2012-07-03 04:27:28 [SEVERE]    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
2012-07-03 04:27:28 [SEVERE]    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
2012-07-03 04:27:28 [SEVERE]    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620)
2012-07-03 04:27:28 [SEVERE]    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1662)
2012-07-03 04:27:28 [SEVERE]    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1581)
2012-07-03 04:27:28 [SEVERE]    at com.Chipmunk9998.Cod.CodCommandExecutor.onCommand(CodCommandExecutor.java:1421)
2012-07-03 04:27:28 [SEVERE]    at org.bukkit.command.PluginCommand.execute(PluginCommand.java:40)
2012-07-03 04:27:28 [SEVERE]    at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:166)
2012-07-03 04:27:28 [SEVERE]    at org.bukkit.craftbukkit.CraftServer.dispatchCommand(CraftServer.java:479)
2012-07-03 04:27:28 [SEVERE]    at com.Chipmunk9998.Cod.CodCommandExecutor.onCommand(CodCommandExecutor.java:1443)
2012-07-03 04:27:28 [SEVERE]    at org.bukkit.command.PluginCommand.execute(PluginCommand.java:40)
2012-07-03 04:27:28 [SEVERE]    at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:166)
2012-07-03 04:27:28 [SEVERE]    at org.bukkit.craftbukkit.CraftServer.dispatchCommand(CraftServer.java:479)
2012-07-03 04:27:28 [SEVERE]    at net.minecraft.server.NetServerHandler.handleCommand(NetServerHandler.java:821)
2012-07-03 04:27:28 [SEVERE]    at net.minecraft.server.NetServerHandler.chat(NetServerHandler.java:781)
2012-07-03 04:27:28 [SEVERE]    at net.minecraft.server.NetServerHandler.a(NetServerHandler.java:764)
2012-07-03 04:27:28 [SEVERE]    at net.minecraft.server.Packet3Chat.handle(Packet3Chat.java:34)
2012-07-03 04:27:28 [SEVERE]    at net.minecraft.server.NetworkManager.b(NetworkManager.java:229)
2012-07-03 04:27:28 [SEVERE]    at net.minecraft.server.NetServerHandler.a(NetServerHandler.java:113)
2012-07-03 04:27:28 [SEVERE]    at net.minecraft.server.NetworkListenThread.a(NetworkListenThread.java:78)
2012-07-03 04:27:28 [SEVERE]    at net.minecraft.server.MinecraftServer.w(MinecraftServer.java:567)
2012-07-03 04:27:28 [SEVERE]    at net.minecraft.server.MinecraftServer.run(MinecraftServer.java:459)
2012-07-03 04:27:28 [SEVERE]    at net.minecraft.server.ThreadServerApplication.run(SourceFile:492)

And here's my code:

if (cmdsender == null) {
  File weaponFile = new File(plugin.getDataFolder(), "weapons.yml");
  FileConfiguration weaponData = YamlConfiguration.loadConfiguration(weaponFile);

  String sql = "UPDATE account_info SET Money = Money - "
    + weaponData.getString(args[0] + "." + args[1] + ".Price")
    + " WHERE Username = '" + args[2] + "'";

  try {
    plugin.st.executeUpdate(sql);
  } catch (SQLException e) {
    e.printStackTrace();
  }

  sql = "UPDATE account_info SET Bought_" + args[0] + " = Bought_" + args[0]
    + " + ," + args[1] + " WHERE Username = '" + args[2] + "'";

  try {
    plugin.st.executeUpdate(sql);
  } catch (SQLException e) {
    e.printStackTrace();
  }

  plugin.getServer().getPlayer(args[2]).sendMessage(
    "You have successfully bought the "
    + weaponData.getString(args[0] + "." + args[1] + ".Name") + "."
  );

  return true;
}

I tried googling it, but couldn't find anything that helped.

Solution

It looks like you have an error in this line:

  sql = "UPDATE account_info SET Bought_" + args[0] + " = Bought_" + args[0]
    + " + ," + args[1] + " WHERE Username = '" + args[2] + "'";

There is an erroneous comma in this string before args[1]. If args[0], args[1] and args[2] contain 1, 3 and foo respectively, then sql will evaluate to:

UPDATE account_info SET Bought_1 = Bought_1 + ,3 WHERE Username = 'foo'

Which is clearly a syntax error and explains your error message Data truncation: Truncated incorrect DOUBLE value: ',3'.

Comments

[Sujay]

I'm guessing this has to do with your args[]. Can you do a simple System.out.println() of your args[] to check their values?

[Rahul Agrawal]

just write and check sql like System.out.println(sql); I think value which is expected as double is passed as string which has , comma. So which causes the query to fail.

[David Carpenter]

I was trying to add the comma as a way to split the string up so I could list all the items they have and let them select which one they wanted to use. Is there any way around that, or a different character I can put there?

[eggyal]

@DavidCarpenter: To do that, you would need to use MySQL's CONCAT() function and quote your string literals e.g. ... = CONCAT(Bought_" + args[0] + ", '," + args[1] + "') WHERE .... However, you should probably normalise your data structure by storing e.g. (Username, Bought) pairs in a separate table, with which you could then join your queries as appropriate.