SQL Error

[Nesyth]

Description of the bug

Can't save any doors settings. Making them unbuyable dissapears always when map is changing.

How to make the bug happen

Try to make door unbuyable.

Lua errors

1. Booting up the server;

[ERROR] lua/includes/modules/hook.lua:84: gamemodes/darkrp/gamemode/libraries/mysqlite/mysqlite.lua:280: Specified key was too long; max key length is 767 bytes (CREATE TABLE IF NOT EXISTS FAdmin_CAMIPrivileges(privname VARCHAR(255) NOT NULL PRIMARY KEY);)
  1. v - [C]:-1
   2. unknown - lua/includes/modules/hook.lua:84

1. Not allowing any ownership on doors;

[ERROR] lua/includes/modules/hook.lua:84: gamemodes/darkrp/gamemode/libraries/mysqlite/mysqlite.lua:280: Table 'darkrp.darkrp_doorjobs' doesn't exist (DELETE FROM darkrp_doorjobs WHERE idx = 2370 AND map = "rp_downtown_altered";)
  1. v - [C]:-1
   2. unknown - lua/includes/modules/hook.lua:84

Why the developer of DarkRP is responsible for this issue

Well, you're the one who's coding the gamemode.

[Bo98]

Do you know what encoding your database uses?

[Nesyth]

Changing the encoding actually helped.

latin1_swedish_ci ftw

[Bo98]

I suspect you were using something like utf8mb4/utf16/utf32. These use 4 bytes per character. Your first error was complaining about "Specified key was too long; max key length is 767 bytes". The primary key in the table it was complaining about is 255 characters long. This would take 255 * 4 = 1020 bytes. This is over the 767 byte limit set by the InnoDB engine in MySQL.

Ways around it could've been:

• Update to MySQL 5.7.8 or later. There are new defaults that mean that larger key sizes should now be supported by default.
  • If you are using MySQL 5.7.7 specifically, set the ROW_FORMAT of the table to be DYNAMIC or COMPRESSED.
  • If you are using an older MySQL version, you will have to turn on the innodb_large_prefix setting. This requires setting innodb_file_format to BARRACUDA and also the ROW_FORMAT of the table described above.
• Change the encoding, like you've already done, to something that uses no more than 3 bytes per character like utf8mb3 (255 * 3 = 765). Latin 1 character sets take up 1 byte per character. This can be enforced for just this one table.
• Decrease the key size to 191. This could raise some (unlikely) compatibility issues though.

Whether or not this is something DarkRP should care about is another question. As it is right now, DarkRP is incompatible with 4-byte character sets on older MySQL versions. I would generally encourage people to use up-to-date MySQL versions but that isn't always possible as most people don't manage their MySQL servers. Since I'm only aware of this one table being an issue (I haven't thoroughly checked) it is probably safe to either enforce a character set or decrease the key length on this particular table/column. Nobody needs the supplementary characters that the 4th byte of UTF-8 offers (uncommon CJK characters, emojis, musical notations, etc.) for privilege names, so enforcing a character set should be safe, especially since it was not safe for addons to do so anyway as that would've required the database to be set up specifically to use a 4 byte character set. Otherwise, you might be able to get away with decreasing the the key's size to 191 if enforcing a character set for this particular table/column is not wanted for some reason.