New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Installation fails on mysqld because Indices are too large #1082

Closed
jakicoll opened this Issue Jan 1, 2018 · 5 comments

Comments

Projects
None yet
2 participants
@jakicoll

jakicoll commented Jan 1, 2018

Steps to reproduce

Download and install on mysqld (mysqld 10.1.26-MariaDB)

Kimai version [for example: dev 1.1.0.1389|github master|github develop]

dev v1.2.1.1393 (github master)

Relevant log entries from log file (debug tab or temporary/logfile.txt)

[01.01.2018 16:02:11] -- begin install ----------------------------------
[01.01.2018 16:02:12] [ERROR] in [CREATE TABLE kimai18i_preferences (userID int(10) NOT NULL,option varchar(255) NOT NULL,value varchar(255) NOT NULL,PRIMARY KEY (userID,option));] => s:63:"Specified
key was too long; max key length is 767 bytes (#1071)";
[01.01.2018 16:02:17] [ERROR] in [CREATE TABLE kimai18i_configuration (option varchar(255) NOT NULL,value varchar(255) NOT NULL,PRIMARY KEY(option));] => s:63:"Specified key was too long; max key length
is 767 bytes (#1071)";
[01.01.2018 16:02:26] [ERROR] in [INSERT INTO kimai18i_preferences (userID,option,value) VALUES('006128753', 'ui.rowlimit', '100'),('006128753', 'ui.skin', 'standard'),('006128753', 'ui.showCommentsByDef
ault', '0'),('006128753', 'ui.hideOverlapLines', '1'),('006128753', 'ui.showTrackingNumber', '1'),('006128753', 'timezone', 'Europe/Berlin');] => s:56:"Table 'kimai.kimai18i_preferences' doesn't exist (#1146)";
[01.01.2018 16:02:26] [ERROR] in [INSERT INTO kimai18i_configuration (option, value) VALUES('version', '1.2.1'),('login', '1'),('adminmail', 'admin@example.com'),('loginTries', '3'),('loginBanTime', '900')
,('revision', '1393'),('currency_name', 'Euro'),('currency_sign', '€'),('currency_first', '0'),('show_update_warn', '1'),('check_at_startup', '0'),('show_daySeperatorLines', '1'),('show_gabBreaks', '0'),('show_R
ecordAgain', '1'),('show_TrackingNr', '1'),('date_format_0', 'dd.mm.yy'),('date_format_1', '%d.%m.'),('date_format_2', '%d.%m.%Y'),('date_format_3', 'd.m.Y'),('table_time_format', '%H:%M'),('language', 'de'),('r
oundPrecision', '0'),('decimalSeparator', ','),('durationWithSeconds', '0'),('exactSums', '0'),('defaultVat', '0'),('editLimit', '0'),('roundTimesheetEntries', '0'),('roundMinutes', '0'),('roundSeconds', '0'),('
allowRoundDown', '0'),('defaultStatusID', '1')] => s:58:"Table 'kimai.kimai18i_configuration' doesn't exist (#1146)";
[01.01.2018 16:02:26] -- showing install error --------------------------

@jakicoll

This comment has been minimized.

Show comment
Hide comment
@jakicoll

jakicoll Jan 1, 2018

Lowering the maximum lenght of the option field in the tables "configuration" and "preferences" to 190 works well for me.
I don't know, if Kimai ever uses values larger than 190 characters for this; if not, this would be an easy fix.

@@ -93,7 +98,7 @@ exec_query($query);
 
 $query = "CREATE TABLE `${p}preferences` (
   `userID` int(10) NOT NULL,
-  `option` varchar(255) NOT NULL,
+  `option` varchar(190) NOT NULL,
   `value` varchar(255) NOT NULL,
   PRIMARY KEY (`userID`,`option`)
 );";
@@ -224,7 +229,7 @@ $query = "CREATE TABLE `${p}timeSheet` (
 exec_query($query);
 
 $query = "CREATE TABLE `${p}configuration` (
-  `option` varchar(255) NOT NULL,
+  `option` varchar(190) NOT NULL,
   `value` varchar(255) NOT NULL,
   PRIMARY KEY  (`option`)
 );";```

jakicoll commented Jan 1, 2018

Lowering the maximum lenght of the option field in the tables "configuration" and "preferences" to 190 works well for me.
I don't know, if Kimai ever uses values larger than 190 characters for this; if not, this would be an easy fix.

@@ -93,7 +98,7 @@ exec_query($query);
 
 $query = "CREATE TABLE `${p}preferences` (
   `userID` int(10) NOT NULL,
-  `option` varchar(255) NOT NULL,
+  `option` varchar(190) NOT NULL,
   `value` varchar(255) NOT NULL,
   PRIMARY KEY (`userID`,`option`)
 );";
@@ -224,7 +229,7 @@ $query = "CREATE TABLE `${p}timeSheet` (
 exec_query($query);
 
 $query = "CREATE TABLE `${p}configuration` (
-  `option` varchar(255) NOT NULL,
+  `option` varchar(190) NOT NULL,
   `value` varchar(255) NOT NULL,
   PRIMARY KEY  (`option`)
 );";```
@kevinpapst

This comment has been minimized.

Show comment
Hide comment
@kevinpapst

kevinpapst Jan 1, 2018

Member

Which collation has your database? We had that issue with databases using collations with larger characters sets than utf8 (like utf8mb4)

Member

kevinpapst commented Jan 1, 2018

Which collation has your database? We had that issue with databases using collations with larger characters sets than utf8 (like utf8mb4)

@jakicoll

This comment has been minimized.

Show comment
Hide comment
@jakicoll

jakicoll Jan 1, 2018

Yep, it's utf8mb4. Turns out that this is the default setting for my recently deployed MariaDB install.
Since it's widespread, it might be a good idea to adopt your scheme, if lenght of 190 does not cause side effects.

jakicoll commented Jan 1, 2018

Yep, it's utf8mb4. Turns out that this is the default setting for my recently deployed MariaDB install.
Since it's widespread, it might be a good idea to adopt your scheme, if lenght of 190 does not cause side effects.

@kevinpapst

This comment has been minimized.

Show comment
Hide comment
@kevinpapst

kevinpapst Jan 20, 2018

Member

@jakicoll could you please check if my changes (which you proposed) in #1093 fix your problems?

Member

kevinpapst commented Jan 20, 2018

@jakicoll could you please check if my changes (which you proposed) in #1093 fix your problems?

simonschaufi added a commit that referenced this issue Jan 20, 2018

@jakicoll

This comment has been minimized.

Show comment
Hide comment
@jakicoll

jakicoll Mar 5, 2018

I did a fresh install of revision 1394 today and the issue is fixed. Great! Thank you very much!

jakicoll commented Mar 5, 2018

I did a fresh install of revision 1394 today and the issue is fixed. Great! Thank you very much!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment