search

IntellectualSites / PlotSquared

PlotSquared - Reinventing the plotworld
GNU General Public License v3.0
546 stars 748 forks source link

MySQL Exception due to VARCHAR(256) and 512 #2793

Closed MeFisto94 closed 4 years ago

MeFisto94 commented 4 years ago

Bug Report Template:

Required Information section:

ALL FIELDS IN THIS SECTION ARE REQUIRED, and must contain appropriate information

Server config info (/plot debugpaste / file paste links):

[19:19:48 WARN]: java.sql.BatchUpdateException: Specified key was too long; max key length is 767 bytes
.... [19:19:48 WARN]: at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
.... [19:19:48 WARN]: at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
.... [19:19:48 WARN]: at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
.... [19:19:48 WARN]: at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
.... [19:19:48 WARN]: at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
.... [19:19:48 WARN]: at com.mysql.jdbc.Util.getInstance(Util.java:408)
.... [19:19:48 WARN]: at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1154)
.... [19:19:48 WARN]: at com.mysql.jdbc.StatementImpl.executeBatchInternal(StatementImpl.java:1044)
.... [19:19:48 WARN]: at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:954)
.... [19:19:48 WARN]: at com.plotsquared.core.database.SQLManager.createTables(SQLManager.java:1274)
.... [19:19:48 WARN]: at com.plotsquared.core.database.SQLManager.<init>(SQLManager.java:174)
.... [19:19:48 WARN]: at com.plotsquared.core.PlotSquared.setupDatabase(PlotSquared.java:1675)
.... [19:19:48 WARN]: at com.plotsquared.core.PlotSquared.<init>(PlotSquared.java:225)
.... [19:19:48 WARN]: at com.plotsquared.bukkit.BukkitMain.onEnable(BukkitMain.java:177)

Server type:

Select one

  • [X] Spigot / Paper (CraftBukkit should not be used, re-test with Spigot first!)
  • [] Sponge
  • [] NukkitX

PlotSquared version:

built from 591eb60a718b254bfc33d8fac18a3d45a0e2a1ae

Minecraft Version:

Select one

Server build info:

Not Applicable

WorldEdit/FAWE versions:

Description of the problem:

When trying to load the plugin for the first time, some Tables aren't created and the plugin can't work (subsequent errors accord because the tables cannot be found).

How to replicate:

Run PlotSquared on a Debian 9.11 with their MariaDB:

Server-Version: 10.1.44-MariaDB-0+deb9u1 - Debian 9.11
nginx/1.10.3
Datenbank-Client Version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: 3591daad22de08524295e1bd073aceeff11e6579 $
PHP-Erweiterung: mysqliDokumentation curlDokumentation mbstringDokumentation
PHP-Version: 7.2.30-1+0~20200419.40+debian9~1.gbpb1b799

Additional Information:

Research showed that https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes#16820166 is the cause and indeed when copying the statement for plot_flags and manually truncating the size of value and flag, the statement works.

It seems this is no problem for https://stackoverflow.com/a/52749571 (5.7.7 (MariaDB 10.2.2?)) but I haven't found any hint on this on this repo. We're currently trying to update to a more recent MariaDB Version.

Other plugins being used on the server:

Relevant console output, log lines, and/or screenshots:

Additional relevant comments/remarks:

I omitted some information which is not relevant to the Bug (World Edit Versioning etc)

AFFIRMATION OF COMPLETION:

MeFisto94 commented 4 years ago

Sorry for getting back so late, indeed this is solved by updating to MariaDB 10.3+. Now maybe you want to wrap the Exception at table creation and link to some wiki entry telling that 10.3.0 is the minimum required version. Alternatively state it somewhere on the repo, but I guess the first approach, while more hacky, would be more obvious to users running into the problem

Citymonstret commented 4 years ago

Yeah, we're aware of this. Changes have been made to the table creation code so that the key now fits within the (old) MySQL limit. This mostly affects people with old MySQL/MariaDB installs, but we have gotten more reports of this in the Discord.

dordsor21 commented 4 years ago

These should be fixed in the latest release.