[Owncloud] MySQL index issue

[Jörn Friedrich Dreyer]

Hi Andreas,

thx for taking the time to figure out sane lengths!

On 24.09.2012 18:14, Andreas Schneider wrote:
> On Monday 24 September 2012 18:02:06 Andreas Schneider wrote:
>> On Monday 24 September 2012 18:00:16 Andreas Schneider wrote:
>>> Hello,
>>>
>>> I get the following error:
>>>
>>> {"app":"core","message":"Failed to update database structure (MDB2 Error:
>>> unknown error, _doQuery: [Error message: Could not execute
>>> statement]\n[Last executed query: CREATE INDEX
>>> `appconfig_appid_key_index` ON `cld_appconfig` (`appid`,
>>> `configkey`)]\n[Native code: 1071]\n[Native message: Specified key was
>>> too long; max key length is 1000
>>> bytes]\n)","level":4,"time":1348502048}
>>>
>>> The reason for this is cause appid and configkey are varchar(255) and you
>>> can't index them cause they are two big.
>>>
>>> See http://bugs.mysql.com/bug.php?id=4541
>>>
>>> Why needs the appid to be such a big key? Same for the configkey ...
>>
>> Same for the preferences table with userid, appid and configkey and for sure
>> others.
> 
> In one table the userid is 200 then in the next 100 and 255. This will 
> probably not work out.
> 
> 
> Please choose sane values. Suggestions:
> 
> userid 32 chars (who really chooses a username longer than that?)
I think somewhere the userid was discussed to be an email address, which
sure gets longer than 32 chars. Wikipedia says the maximum there is 254
chars: http://en.wikipedia.org/wiki/Email_address#Syntax

> appid 32 chars (my_super_duper_mega_cool_app_which_does_nothing is for
>                 sure not a good choice.)
+1

> configkey 64 chars (maybe more)
+1

so long

Jörn

-- 
Jörn Friedrich Dreyer (jfd at owncloud.com)
Software Developer
ownCloud GmbH

Your Data, Your Cloud, Your Way!

ownCloud GmbH, GF: Markus Rex, Holger Dyroff
Schloßäckerstrasse 26a, 90443 Nürnberg, HRB 28050 (AG Nürnberg)