MySQL Duplicate Error (Nation Edition?)

[LlmDl]

Originally reported on Google Code with ID 2167

What steps will reproduce the problem?
1. Install latest Towny.
2. Setup Towny to work with a MySQL database.
3. Create a Town/Nation
4. Change the Mayor/King of that Town or Nation, or change the Name of it, or basically
anything else.
5. Console will be spammed with one or two MySQL errors about how there are duplicate
entries.

What is the expected output? :
The town or nation or whatever should automatically be modified in the database with
no errors.

What do you see instead? :
The console gets spammed with the following error...

[00:00:00] [Craft Scheduler Thread - 1242/WARN]: [Towny] Error: SQL: Duplicate entry
'NATION-NAME-HERE' for key 'PRIMARY' --> com.mysql.jdbc.JDBC4PreparedStatement@2269bdda:
INSERT INTO TOWNY_NATIONS (`enemies`, `assistants`, `allies`, `tag`, `towns`, `name`,
`neutral`, `capital`, `taxes`) VALUES ('ENEMIES-HERE','ASSISTANTS-HERE','ALLIANCES-HERE','','TOWNS-IN-THE-NATION','NATION-NAME-HERE','0','CAPITAL-TOWN-HERE','0.0')

What is your Towny version number? :
Towny v0.88.0.1

What is your TownyChat version number? :
Towny Chat v0.4

What is your Bukkit version number? :
CraftBukkit version git-Spigot-1.7.9-R0.2-204-g534549b (MC: 1.7.10) (Implementing API
version 1.7.10-R0.1-SNAPSHOT)

What operating system? :
Linux

Please use pastebin.com to link the following files:
- Your full server startup from server.log,
I cannot at this time.

- Your towny config.yml (if using MySQL please remove pasword,)
I cannot at this time.

- Your townyperms.yml,
I cannot at this time.

- Your permissions file.
I cannot at this time.

In the case of chat-issues please link your:
- Chatconfig.yml
I cannot at this time.

- Channels.yml
I cannot at this time.

Skipping these files is not an option. It is the easiest way to diagnose an
issue ticket properly. If you do skip this step then your ticket will most
likely be deleted and you will be asked to resubmit.

Before you go ahead and delete this ticket, know that this is the simplest solution
to the problem. I think you guys are smart enough to know that this isn't specific
to my server and that logs and configuration files will not help.

You said on the previous issue of the same kind that you can't fix it until someone
suggests a fix or something along those lines, here are ways you can fix it:

Method 1. Check to see if the entry is already in the database, if it is, then use
the UPDATE MySQL command.

Method 2. Use the INSERT ... ON DUPLICATE KEY UPDATE MySQL command which inserts an
entry and then if the entry already exists, just updates it. This wouldn't even require
you to add a check to see if the entry already exists and you wouldn't need to have
more than one MySQL statement. You just need to add: "ON DUPLICATE KEY UPDATE" to the
end of the statements.

Method 3. This is probably the EASIEST method. Simply change your INSERT MySQL commands
to REPLACE commands. "REPLACE works exactly like INSERT, except that if an old row
in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the
old row is deleted before the new row is inserted."

Reported by ds9starfighter on 2014-10-19 17:36:41

[LlmDl]

I created a PULL request on Github for ElgarL's Towny git. It's up to one of the people
with write access to the git to accept it now. If they do, the error should be resolved
the next time a Towny jar file is released. My fix may create errors in the future,
but right now I can't see any that it could create and I doubt there are any.

Reported by Aoredon on 2014-10-19 18:10:46

[LlmDl]

We'd rather figure out why its breaking on only a few servers, since it functions fine
on everyone else's. 

It's up to elgarl whether he uses your PR. 

Reported by LlmDlio on 2014-10-19 20:46:44

[LlmDl]

Please download latest dev, 0.88.0.2

Reported by LlmDlio on 2014-10-19 21:03:58

• Status changed: Need_Feedback

[LlmDl]

Reported by LlmDlio on 2014-10-22 16:06:08

• Status changed: Fixed