BungeeSuite Bans db conversion errors

[TheLecturer]

Hi - just starting to attempt the conversion from BungeeSuite to geSuit. Hit the below errors with the conversion of the bans db. Hope you can help.

19:51:35 [INFO] Enabled BungeeCord version git:BungeeCord-Bootstrap:1.7-SNAPSHOT:"a4dd0db":877
19:51:36 [INFO] Enabled plugin geSuit version 0.7.5b-58 by geNAZt
19:51:36 [INFO] Enabled plugin Yamler version 2.2.2-SNAPSHOTb-59 by geNAZt
...
20:04:39 [SEVERE] java.sql.SQLException: Parameter index out of range (8 > number of parameters, which is 7).
20:04:39 [SEVERE]       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
20:04:39 [SEVERE]       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
20:04:39 [SEVERE]       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
20:04:39 [SEVERE]       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
20:04:39 [SEVERE]       at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3796)
20:04:39 [SEVERE]       at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3778)
20:04:39 [SEVERE]       at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3823)
20:04:39 [SEVERE]       at com.mysql.jdbc.PreparedStatement.setNull(PreparedStatement.java:3863)
20:04:39 [SEVERE]       at com.mysql.jdbc.PreparedStatement.setDate(PreparedStatement.java:3687)
20:04:39 [SEVERE]       at com.mysql.jdbc.PreparedStatement.setDate(PreparedStatement.java:3667)
20:04:39 [SEVERE]       at net.cubespace.geSuit.database.Bans.insertBanConvert(Bans.java:207)
20:04:39 [SEVERE]       at net.cubespace.geSuit.database.Bans.insertBanConvert(Bans.java:191)
20:04:39 [SEVERE]       at net.cubespace.geSuit.database.convert.Converter$Bans.convert(Converter.java:160)
20:04:39 [SEVERE]       at net.cubespace.geSuit.database.convert.Converter$1.run(Converter.java:283)

mysql> select * from BungeeBans;
...
482 rows in set (0.01 sec)

mysql> select * from bans;
Empty set (0.00 sec)

mysql> describe BungeeBans;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| player       | varchar(100) | NO   | PRI |         |       |
| banned_by    | varchar(100) | YES  |     | NULL    |       |
| reason       | varchar(255) | YES  |     | NULL    |       |
| type         | varchar(100) | YES  |     | NULL    |       |
| banned_on    | datetime     | NO   |     | NULL    |       |
| banned_until | datetime     | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

All other table transfers (BungeePlayers > players, BungeeHomes > homes, etc.) appear to have worked fine.

[TheLecturer]

Thanks for the update - re-ran today. Is this the expected output into "bans"? Not what I was expecting (but then I'm not the dev!). UUID seems to be in the wrong column, and all player names NULL'd...?

+-----+----------------------------------+-------------+-----------+------------------+---------------------------------------------------------------+---------+---------------------+---------------------+
| id  | banned_playername                | banned_uuid | banned_ip | banned_by        | reason                                                        | type    | banned_on           | banned_until        |
+-----+----------------------------------+-------------+-----------+------------------+---------------------------------------------------------------+---------+---------------------+---------------------+
|   1 | 616aaf72b27c49cebe0a535e93476fec | NULL        | NULL      | TheLecturer      | Inappropriate language                                        | ban     | 2013-11-26 00:00:00 | NULL                |
|   2 | 0d4ad9ba1bb74656b3061e13bede8ced | NULL        | NULL      | TheLecturer      | trolling                                                      | ban     | 2013-12-29 00:00:00 | NULL                |
|   3 | e00f1a320fbd41df866a486fdfc8b116 | NULL        | NULL      | TheLecturer      | Breaking Server Rules                                         | ban     | 2013-03-19 00:00:00 | NULL                |

Below are the same first three records from the BungeeBans table:

+-------------------+------------------+---------------------------------------------------------------+---------+---------------------+---------------------+
| player            | banned_by        | reason                                                        | type    | banned_on           | banned_until        |
+-------------------+------------------+---------------------------------------------------------------+---------+---------------------+---------------------+
| shane_mcl         | TheLecturer      | Inappropriate language                                        | ban     | 2013-11-26 00:00:00 | NULL                |
| VickyAlexis       | TheLecturer      | trolling                                                      | ban     | 2013-12-29 23:20:07 | NULL                |
| 12321pop2         | TheLecturer      | Breaking Server Rules                                         | ban     | 2013-03-19 00:14:06 | NULL                |

Another (potentially minor) point - both the date fields in "bans" seem to default to midnight, the hours and minutes are being ignored. Thanks.

[geNAZt]

No that should not be the output, you are right the uuid should be in the uuid column, the player name should be there and the date should be correct.

[geNAZt]

can you convert again with this build: http://jenkins.cube-space.net/job/geSuit/61/

[TheLecturer]

Getting closer, many thanks for your responsiveness! UUID and player names now all looks good. Dates are still losing the hours and minutes.

I also noticed a couple of users whose UUID's are NULL - does this mean that perhaps Mojang deleted the account?

+-----+-------------------+----------------------------------+-----------+------------------+---------------------------------------------------------------+---------+---------------------+---------------------+
| id  | banned_playername | banned_uuid                      | banned_ip | banned_by        | reason                                                        | type    | banned_on           | banned_until        |
+-----+-------------------+----------------------------------+-----------+------------------+---------------------------------------------------------------+---------+---------------------+---------------------+
|   1 | shane_mcl         | 616aaf72b27c49cebe0a535e93476fec | NULL      | TheLecturer      | Inappropriate language                                        | ban     | 2013-11-26 00:00:00 | NULL                |
|   2 | VickyAlexis       | 0d4ad9ba1bb74656b3061e13bede8ced | NULL      | TheLecturer      | trolling                                                      | ban     | 2013-12-29 00:00:00 | NULL                |
|   3 | 12321pop2         | e00f1a320fbd41df866a486fdfc8b116 | NULL      | TheLecturer      | Breaking Server Rules                                         | ban     | 2013-03-19 00:00:00 | NULL                |
...
| 198 | creepergirl156450 | NULL                             | NULL      | TheLecturer      | Breaking Server Rules                                         | ban     | 2013-12-25 00:00:00 | NULL                |
...
| 256 | CockSodomy        | NULL                             | NULL      | jewelsjc         | Breaking server rules                                         | ban     | 2014-01-25 00:00:00 | NULL                |
...
+-----+-------------------+----------------------------------+-----------+------------------+---------------------------------------------------------------+---------+---------------------+---------------------+
482 rows in set (0.00 sec)

[geNAZt]

CockSodomy: http://api.fishbans.com/stats/CockSodomy (Is not premium [maybe non payed legacy account]) - This account will not be able to login into 1.8+

creepergirl156450: http://api.fishbans.com/stats/creepergirl156450 (Account name is too long and has not converted to UUIDS) - This account will also not be able to login into 1.8+

[geNAZt]

The nulled UUIDs should now not be inserted into the Bans table since they can't be used in 1.8+ (maybe also 1.7.6 must lookup). Also the Times should be correct now.

http://jenkins.cube-space.net/job/geSuit/62/artifact/target/geSuit.jar

[TheLecturer]

Thanks for the explanation on the UUID's. Sorry to report the time stamps still seem incorrect. Dumped a load of info below, let me know if there is anything else you want me to provide. Thanks.

08:35:42 [INFO] Enabled BungeeCord version git:BungeeCord-Bootstrap:1.7-SNAPSHOT:"a4dd0db":877
...
08:35:42 [INFO] Enabled plugin geSuit version 0.7.7b-62 by geNAZt

mysql> select * from BungeeBans;
+-------------------+------------------+---------------------------------------------------------------+---------+---------------------+---------------------+
| player            | banned_by        | reason                                                        | type    | banned_on           | banned_until        |
+-------------------+------------------+---------------------------------------------------------------+---------+---------------------+---------------------+
| shane_mcl         | TheLecturer      | Inappropriate language                                        | ban     | 2013-11-26 00:00:00 | NULL                |
| VickyAlexis       | TheLecturer      | trolling                                                      | ban     | 2013-12-29 23:20:07 | NULL                |
| 12321pop2         | TheLecturer      | Breaking Server Rules                                         | ban     | 2013-03-19 00:14:06 | NULL                |

mysql> select * from bans;
+-----+-------------------+----------------------------------+-----------+------------------+---------------------------------------------------------------+---------+---------------------+---------------------+
| id  | banned_playername | banned_uuid                      | banned_ip | banned_by        | reason                                                        | type    | banned_on           | banned_until        |
+-----+-------------------+----------------------------------+-----------+------------------+---------------------------------------------------------------+---------+---------------------+---------------------+
|   1 | shane_mcl         | 616aaf72b27c49cebe0a535e93476fec | NULL      | TheLecturer      | Inappropriate language                                        | ban     | 2013-11-26 00:00:00 | NULL                |
|   2 | VickyAlexis       | 0d4ad9ba1bb74656b3061e13bede8ced | NULL      | TheLecturer      | trolling                                                      | ban     | 2013-12-29 00:00:00 | NULL                |
|   3 | 12321pop2         | e00f1a320fbd41df866a486fdfc8b116 | NULL      | TheLecturer      | Breaking Server Rules                                         | ban     | 2013-03-19 00:00:00 | NULL                |

mysql> describe BungeeBans;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| player       | varchar(100) | NO   | PRI |         |       |
| banned_by    | varchar(100) | YES  |     | NULL    |       |
| reason       | varchar(255) | YES  |     | NULL    |       |
| type         | varchar(100) | YES  |     | NULL    |       |
| banned_on    | datetime     | NO   |     | NULL    |       |
| banned_until | datetime     | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> describe bans;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | PRI | NULL    | auto_increment |
| banned_playername | varchar(100) | YES  |     | NULL    |                |
| banned_uuid       | varchar(100) | YES  |     | NULL    |                |
| banned_ip         | varchar(15)  | YES  |     | NULL    |                |
| banned_by         | varchar(100) | YES  |     | NULL    |                |
| reason            | varchar(255) | YES  |     | NULL    |                |
| type              | varchar(100) | YES  |     | NULL    |                |
| banned_on         | datetime     | NO   |     | NULL    |                |
| banned_until      | datetime     | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:      103
Current database:   hub
Current user:       minecraft@localhost
SSL:            Not in use
Current pager:      /bin/more
Using outfile:      ''
Using delimiter:    ;
Server version:     5.1.73 Source distribution
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:        /var/lib/mysql/mysql.sock
Uptime:         1 hour 54 min 37 sec

Threads: 2  Questions: 9109  Slow queries: 0  Opens: 40  Flush tables: 1  Open tables: 21  Queries per second avg: 1.324
--------------

[minecraft@localhost geSuit]$ cat config.yml
Database:
  Threads: 5
  Port: '3306'
  Host: localhost
  Database: hub
  Password: XXXXX
  Username: minecraft
ConvertFromBungeeSuite: false   <--- This was true, I assume it switches to false after conversion...
BungeeSuiteDatabase:
  Threads: 5
  Port: '3306'
  Host: localhost
  Database: hub
  Password: XXXXX
  Username: minecraft
MOTD:
  # Turn this to false if you want to use your regular /motd comand (requires restart)
  Enabled: true
Seen:
  # Turn this to false if you want to use your your regular /seen comand (requires restart)
  Enabled: false
# 
NewPlayerBroadcast: true
BroadcastProxyConnectionMessages: true
PlayerDisconnectDelay: 10
# This should be true on offline Mode Server since they can't use UUIDs provided by Mojang
OverwriteUUID: false
# Do not alter this. It will be used automaticly.
Inited: true
Version:
  Database:
    # Stored version informations. If you alter this you can damage your Database
    Ban: 3
    # Stored version informations. If you alter this you can damage your Database
    Homes: 2
    # Stored version informations. If you alter this you can damage your Database
    Players: 2
    # Stored version informations. If you alter this you can damage your Database
    Portals: 1
    # Stored version informations. If you alter this you can damage your Database
    Spawns: 1
    # Stored version informations. If you alter this you can damage your Database
    Warps: 1

[TheLecturer]

Hi geNAZt.

I ran some tests today on the ban and tempban commands, and they appear to be working fine (once I worked out the time parameter is now 5m instead of m:5, for example!)

So - I don't really care about the timestamps on the legacy-bans that are coming over from BungeeBans (they have been banned after all). I'm happy for you to close this ticket if you don't care about it either. However if you want to carry on working on it, I'll be glad to test it for you - up to you.

Many thanks.