Bug 16402 - DB structure cannot be loaded in MySQL 5.7
Summary: DB structure cannot be loaded in MySQL 5.7
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Installation and upgrade (web-based installer) (show other bugs)
Version: Main
Hardware: PC Linux
: P5 - low critical (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
: 16393 (view as bug list)
Depends on:
Blocks: 17258
  Show dependency treegraph
 
Reported: 2016-04-30 06:08 UTC by Gopalakrishna
Modified: 2019-06-27 09:24 UTC (History)
11 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Web Installation Error (88.69 KB, image/png)
2016-04-30 06:08 UTC, Gopalakrishna
Details
Bug 16402: Make kohastructure.sql valid for MySQL v5.7 (2.09 KB, patch)
2016-05-05 14:17 UTC, Jonathan Druart
Details | Diff | Splinter Review
[SIGNED-OFF] Bug 16402: Make kohastructure.sql valid for MySQL v5.7 (2.26 KB, patch)
2016-05-06 20:46 UTC, Bernardo Gonzalez Kriegel
Details | Diff | Splinter Review
[PASSED QA] Bug 16402: Make kohastructure.sql valid for MySQL v5.7 (2.32 KB, patch)
2016-05-08 09:25 UTC, Katrin Fischer
Details | Diff | Splinter Review
Bug 16402: Fix fox existing installations (797 bytes, patch)
2016-05-09 14:09 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 16402: Make kohastructure.sql valid for MySQL v5.7 (2.36 KB, patch)
2016-05-16 15:38 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 16402: Fix fox existing installations (852 bytes, patch)
2016-05-16 15:38 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 16402: (QA followup) (1.38 KB, patch)
2016-05-16 15:38 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Gopalakrishna 2016-04-30 06:08:07 UTC
Created attachment 51046 [details]
Web Installation Error

OS: Ubuntu 16.04
Type: Ubuntu package installation
Koha: Current Stable Version on 29th April 2016

Blank Database creation was successful.
Very first login prompted for password.
At Step 3 persistent error:

The following error occurred while importing the database structure:

mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1171 (42000) at line 1385: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

Please contact your system administrator

On second instance creation - did not appear Login Page at all.
Comment 1 Jonathan Druart 2016-05-02 14:01:24 UTC
Hello Gopalakrishna,
Please try to comment the sql_mode=STRICT_TRANS_TABLES line in your MySQL/MariaDB config file.
Comment 2 Charles Gruener 2016-05-05 13:39:47 UTC
I attempted to change the sql_mode as suggested and the error persists upon trying to import the database structure.  The file /usr/share/koha/intranet/cgi-bin/installer/data/mysql/kohastructure.sql needs to be updated to comply with the changes introduced in MySQL release 5.7.3.

See: http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html

Specifically: Columns in a PRIMARY KEY must be NOT NULL, but if declared explicitly as NULL produced no error. Now an error occurs. For example, a statement such as CREATE TABLE t (i INT NULL PRIMARY KEY) is rejected. The same occurs for similar ALTER TABLE statements. (Bug #13995622, Bug #66987, Bug #15967545, Bug #16545198)

Since I don't know much about how the database needs to be structured to properly function, I'll just call out the fields that MySQL is erroring out on in kohastructure.sql rather than attempting to write my own patch.

On line 1385: CREATE TABLE 'letter' uses 'branchcode' as part of its PRIMARY_KEY, but is "default NULL"

On line 2476: CREATE TABLE 'permissions' uses 'code' as part of its PRIMARY_KEY but is "DEFAULT NULL"

If I change those two (branchcode and code) to be "NOT NULL", I am able to get the installer to continue, but as I stated above, I don't know if this is the correct fix as I don't know how this impacts the database structure for the usability of koha.
Comment 3 Jonathan Druart 2016-05-05 14:17:46 UTC Comment hidden (obsolete)
Comment 4 Richard Gardner 2016-05-05 20:51:03 UTC
Hey there,

Was having this same problem. Just wanted to give you all a shout-out for the fix. Was having trouble locating the file but eventually got it all worked out. 

Still ended up getting::

mysql: [Warning] Using a password on the command line interface can be insecure.

but I found a workaround for that and now i've created and access the staff interface via command line. I am still having some trouble connecting via web-browser, but I suspect it's because I just set my nameservers up earlier today and they haven't had time to propagate. I hope that is the "problem" at least.

Otherwise I may need to figure out my host files, .conf files, or the proper settings for my server's DNS manager.

Anyway, thanks again.
Comment 5 Katrin Fischer 2016-05-06 06:01:14 UTC
If one of you could tell that Jonathan's patch works (sign off on it), this would help to get the fix into Koha :)
Comment 6 Jonathan Druart 2016-05-06 13:29:36 UTC
*** Bug 16393 has been marked as a duplicate of this bug. ***
Comment 7 Bernardo Gonzalez Kriegel 2016-05-06 20:46:19 UTC Comment hidden (obsolete)
Comment 8 g.meyer 2016-05-07 08:44:15 UTC
(In reply to Richard Gardner from comment #4)
> Hey there,
> 
> Was having this same problem. Just wanted to give you all a shout-out for
> the fix. Was having trouble locating the file but eventually got it all
> worked out. 
> 
> Still ended up getting::
> 
> mysql: [Warning] Using a password on the command line interface can be
> insecure.
> 
> but I found a workaround for that and now i've created and access the staff
> interface via command line. I am still having some trouble connecting via
> web-browser, but I suspect it's because I just set my nameservers up earlier
> today and they haven't had time to propagate. I hope that is the "problem"
> at least.
> 
> Otherwise I may need to figure out my host files, .conf files, or the proper
> settings for my server's DNS manager.
> 
> Anyway, thanks again.

Hello Richard,
I did not find the file. Could you please give me a hint?
Thanks
Comment 9 Katrin Fischer 2016-05-08 09:08:46 UTC
Upping severity as this blocks people from being able to install Koha.
Comment 10 Katrin Fischer 2016-05-08 09:13:53 UTC
Hm, I think this will work for new installations, but shouldn't we change existing ones to match?
Comment 11 Katrin Fischer 2016-05-08 09:25:26 UTC Comment hidden (obsolete)
Comment 12 Jonathan Druart 2016-05-09 09:14:10 UTC
(In reply to Katrin Fischer from comment #10)
> Hm, I think this will work for new installations, but shouldn't we change
> existing ones to match?

Yes indeed we must, I have thought about that during the week-end :)
Patches coming soon...
Comment 13 Jonathan Druart 2016-05-09 14:09:18 UTC Comment hidden (obsolete)
Comment 14 Tomás Cohen Arazi 2016-05-16 15:38:00 UTC
Created attachment 51540 [details] [review]
Bug 16402: Make kohastructure.sql valid for MySQL v5.7

A primary key should not contain null value, and MySQL v5.7 is now more
strict and won't accept our errors in the DB structure.

Test plan:
  mysql < installer/data/mysql/kohastructure.sql
should work using MySQL v5.7

QA:
- letter.branchcode should not be NULL, the default (all) is an empty
string
- permissions.code should not be NULL, it's always a string

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>
Clean install of Ubuntu 16.05, MySQL 5.7.11
kohastructure.sql loads ok, just one warning
No errors

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 15 Tomás Cohen Arazi 2016-05-16 15:38:10 UTC
Created attachment 51541 [details] [review]
Bug 16402: Fix fox existing installations

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 16 Tomás Cohen Arazi 2016-05-16 15:38:19 UTC
Created attachment 51542 [details] [review]
Bug 16402: (QA followup)

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 17 Tomás Cohen Arazi 2016-05-16 15:39:04 UTC
Works as expected. I added a followup to ease the task for the RM.
Comment 18 Kyle M Hall 2016-05-16 17:10:56 UTC
Pushed to master for Koha 16.05. Thanks Jonathan!
Comment 19 Julian Maurice 2016-05-16 17:23:11 UTC
Patches pushed to 3.22.x, will be in 3.22.7
Comment 20 Katrin Fischer 2016-05-18 00:19:13 UTC
I am trying to install Koha on Ubuntu 16.05 and I can't finish the web installer. After the tables are created (and they are), the web installer is stuck with this error:

[Warning] Using a password on the command line interface can be insecure.

You can't continue to choose the sample data, install the mandatory data etc. 

Leaving this here for now - if needed I can file a new bug report tomorrow.
Comment 21 Tomás Cohen Arazi 2016-05-18 00:21:05 UTC
(In reply to Katrin Fischer from comment #20)
> I am trying to install Koha on Ubuntu 16.05 and I can't finish the web
> installer. After the tables are created (and they are), the web installer is
> stuck with this error:
> 
> [Warning] Using a password on the command line interface can be insecure.
> 
> You can't continue to choose the sample data, install the mandatory data
> etc. 
> 
> Leaving this here for now - if needed I can file a new bug report tomorrow.

There's a bug filled for that already, it mentions mysql 5.6
Comment 22 Katrin Fischer 2016-05-18 00:27:20 UTC
bug 13669 doesn't mention the error - was this the one you were thinking about?
Comment 23 Tomás Cohen Arazi 2016-05-18 00:28:47 UTC
(In reply to Katrin Fischer from comment #22)
> bug 13669 doesn't mention the error - was this the one you were thinking
> about?

The first comment explains the failure.