Bug 16573 - Web installer fails to load structure and sample data on MySQL 5.7
Summary: Web installer fails to load structure and sample data on MySQL 5.7
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Installation and upgrade (web-based installer) (show other bugs)
Version: master
Hardware: All All
: P5 - low critical (vote)
Assignee: Bernardo Gonzalez Kriegel
QA Contact: Testopia
URL:
Keywords:
: 16664 (view as bug list)
Depends on:
Blocks: 17258 16554
  Show dependency treegraph
 
Reported: 2016-05-24 01:34 UTC by Bernardo Gonzalez Kriegel
Modified: 2019-06-27 09:24 UTC (History)
10 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Who signed the patch off:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 16573: fix structure and sample data for MySQL 5.7 (14.89 KB, patch)
2016-05-24 01:50 UTC, Bernardo Gonzalez Kriegel
Details | Diff | Splinter Review
Bug 16573: fix linkid value on marc_subfield_structure (401.87 KB, patch)
2016-05-24 01:51 UTC, Bernardo Gonzalez Kriegel
Details | Diff | Splinter Review
Bug 16573: fix structure and sample data for MySQL 5.7 (15.16 KB, patch)
2016-05-24 17:08 UTC, Bernardo Gonzalez Kriegel
Details | Diff | Splinter Review
Bug 16573: change created_on type - atomic update (1001 bytes, patch)
2016-05-24 21:25 UTC, Bernardo Gonzalez Kriegel
Details | Diff | Splinter Review
Bug 16573: fix linkid value on marc_subfield_structure (401.92 KB, patch)
2016-05-25 05:04 UTC, M. Tompsett
Details | Diff | Splinter Review
Bug 16573: fix structure and sample data for MySQL 5.7 (15.21 KB, patch)
2016-05-25 05:04 UTC, M. Tompsett
Details | Diff | Splinter Review
Bug 16573: change created_on type - atomic update (1.02 KB, patch)
2016-05-25 05:04 UTC, M. Tompsett
Details | Diff | Splinter Review
Bug 16573: Define virtualshelves.created_on as datetime for existing installs (961 bytes, patch)
2016-07-22 08:52 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 16573: Define virtualshelves.created_on as datetime for existing installs (1013 bytes, patch)
2016-07-22 19:34 UTC, M. Tompsett
Details | Diff | Splinter Review
Bug 16573: fix linkid value on marc_subfield_structure (401.99 KB, patch)
2016-07-25 10:48 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 16573: fix structure and sample data for MySQL 5.7 (15.28 KB, patch)
2016-07-25 10:49 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 16573: change created_on type - atomic update (1.10 KB, patch)
2016-07-25 10:49 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 16573: Define virtualshelves.created_on as datetime for existing installs (1.06 KB, patch)
2016-07-25 10:49 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 16573: (RM followup) Update DBIx schema and prevent upgrade issues (2.54 KB, patch)
2016-08-19 19:49 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 Bernardo Gonzalez Kriegel 2016-05-24 01:34:45 UTC
MySQL 5.7 is more strict, structure and sample data fails to load.
Comment 1 Bernardo Gonzalez Kriegel 2016-05-24 01:50:59 UTC Comment hidden (obsolete)
Comment 2 Bernardo Gonzalez Kriegel 2016-05-24 01:51:11 UTC Comment hidden (obsolete)
Comment 3 Bernardo Gonzalez Kriegel 2016-05-24 17:08:55 UTC Comment hidden (obsolete)
Comment 4 Jonathan Druart 2016-05-24 19:23:45 UTC
The change in the kohastructure should be done for existing install (i.e. atomic update).
Comment 5 Bernardo Gonzalez Kriegel 2016-05-24 21:25:40 UTC Comment hidden (obsolete)
Comment 6 M. Tompsett 2016-05-25 05:04:04 UTC Comment hidden (obsolete)
Comment 7 M. Tompsett 2016-05-25 05:04:11 UTC Comment hidden (obsolete)
Comment 8 M. Tompsett 2016-05-25 05:04:18 UTC Comment hidden (obsolete)
Comment 9 M. Tompsett 2016-05-25 05:12:25 UTC
I did a fresh, full git install under Ubuntu 16.04 LTS.
Attempting to install gave a nasty Step 3 problem.
Applying these patches made it go away.
I transplanted an older DB dump and ran an upgrade. It ran.
Though, my upgrade from 3.15.x seems to be missing a created_on. :(
Comment 10 Jonathan Druart 2016-07-22 08:52:07 UTC Comment hidden (obsolete)
Comment 11 Jonathan Druart 2016-07-22 08:53:13 UTC
(In reply to M. Tompsett from comment #9)
> Though, my upgrade from 3.15.x seems to be missing a created_on. :(

Could you test again with this last patch (I do not have MySQL 5.7 to test).
Moreover it seems that items_last_borrower.created_on should be changed as well.
Comment 12 M. Tompsett 2016-07-22 19:28:15 UTC
Now the Elastic search upgrade stuff was bombing horribly. for 3.23.0.50.
commented it out... everything else fine.
Comment 13 M. Tompsett 2016-07-22 19:34:22 UTC Comment hidden (obsolete)
Comment 14 Jonathan Druart 2016-07-25 10:48:57 UTC
Created attachment 53676 [details] [review]
Bug 16573: fix linkid value on marc_subfield_structure

followup to previous patch.

This patch fixes linkid value. It was set to '', but
declared as tinyint.
Mysql < 5.7 replaced this with nearest right value, i.e 0
Mysql 5.7 gives error here.

Also some normalization, all lines with data begins with two tabs

To test:
On top of Bug 16572 and previous patch

1) Run webinstaller
2) Add mandatory values
3) There must be no problems on MySQL 5.7

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 15 Jonathan Druart 2016-07-25 10:49:01 UTC
Created attachment 53677 [details] [review]
Bug 16573: fix structure and sample data for MySQL 5.7

Web install on Ubuntu 16.04/Mysql 5.7 fails.

This patch makes small changes to make installation
possible to kohastructure.sql and some sample files.

Sample values for quotes table can't have '0000-00-00 ...' values
nor NULL values, now() is perhaps an option.

Depends on Bug 16572

To test:
1) System with MySQL 5.7, for example Ubuntu 16.05
2) Apply 16572
3) Try web install, it fails
4) Apply this and next patch
5) Try again, now it succeed

This is only for English install, i18n files needs revision
I can do that if this is accepted.

Perphaps a change is needed to updatedatabase.pl

Ammended patch, 'created_on' field on virtualshelves
can't be timestamp default null, mysql 5.5 complains
that only one timestamp column can be defined as
default not null. Changed to 'datetime' type.

Can provide followup with updatedabase change,
but need an opinion if this type change makes sense.

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 16 Jonathan Druart 2016-07-25 10:49:06 UTC
Created attachment 53678 [details] [review]
Bug 16573: change created_on type - atomic update

This patch change 'created_on' virtualshelves column to
datetime type

To test:
1) Apply the patch
2) Run updatedatabase.pl
3) Verify type of 'created_on' column

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 17 Jonathan Druart 2016-07-25 10:49:10 UTC
Created attachment 53679 [details] [review]
Bug 16573: Define virtualshelves.created_on as datetime for existing installs

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 18 Jonathan Druart 2016-07-25 10:50:10 UTC
(In reply to M. Tompsett from comment #12)
> Now the Elastic search upgrade stuff was bombing horribly. for 3.23.0.50.
> commented it out... everything else fine.

Ok thanks Mark.
I think it's better to fix it on another bug report otherwise this one will never get pushed :)

Could you please open a bug report for this upgrade issue and describe what the error is?
Comment 19 Kyle M Hall 2016-08-01 10:19:34 UTC
Pushed to master for Koha 16.11, thanks Bernardo!
Comment 20 Frédéric Demians 2016-08-05 06:05:33 UTC
Pushed in 16.05. Will be in 16.05.03.
Comment 21 M. Tompsett 2016-08-11 16:01:08 UTC
(In reply to Kyle M Hall from comment #19)
> Pushed to master for Koha 16.11, thanks Bernardo!

Are you sure you pushed all the patches? I just did a git bz apply, and it only said the first one was already applied.

I was trying to test bug 16554 (I think).
Comment 22 M. Tompsett 2016-08-11 16:03:04 UTC
(In reply to M. Tompsett from comment #21)
> (In reply to Kyle M Hall from comment #19)
> > Pushed to master for Koha 16.11, thanks Bernardo!
> 
> Are you sure you pushed all the patches? I just did a git bz apply, and it
> only said the first one was already applied.
> 
> I was trying to test bug 16554 (I think).

Nevermind, I failed to git pull. Sorry. :(
Comment 23 Julian Maurice 2016-08-17 12:09:42 UTC
Patches pushed to 3.22.x, will be in 3.22.10
Comment 24 Tomás Cohen Arazi 2016-08-19 19:49:40 UTC Comment hidden (obsolete)
Comment 25 Tomás Cohen Arazi 2016-08-19 19:51:37 UTC
Back to the RM queue.
Comment 26 M. Tompsett 2016-08-19 21:14:54 UTC
Comment on attachment 54658 [details] [review]
Bug 16573: (RM followup) Update DBIx schema and prevent upgrade issues

Review of attachment 54658 [details] [review]:
-----------------------------------------------------------------

::: installer/data/mysql/updatedatabase.pl
@@ +12789,4 @@
>  $DBversion = '16.06.00.012';
>  if ( CheckVersion($DBversion) ) {
>      $dbh->do(q{
> +        ALTER IGNORE TABLE virtualshelves MODIFY COLUMN created_on DATETIME not NULL;

"As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error."
(https://dev.mysql.com/doc/refman/5.7/en/alter-table.html)
I'd leave out the ignore.
Comment 27 Tomás Cohen Arazi 2016-08-21 21:37:44 UTC
Mark, you are right! I missed that in the docs. Kyle, just upgrade the schema files!
Comment 28 Jonathan Druart 2016-09-05 11:39:00 UTC
(In reply to Tomás Cohen Arazi from comment #27)
> Mark, you are right! I missed that in the docs. Kyle, just upgrade the
> schema files!

ping!
Comment 29 M. Tompsett 2016-09-05 14:08:53 UTC
(In reply to Jonathan Druart from comment #28)
> ping!

bug 17234?
Comment 30 Jonathan Druart 2016-09-05 14:22:40 UTC
(In reply to M. Tompsett from comment #29)
> (In reply to Jonathan Druart from comment #28)
> > ping!
> 
> bug 17234?

No, the schema files are not up-to-date.
Comment 31 Jonathan Druart 2016-09-06 08:18:29 UTC
*** Bug 16664 has been marked as a duplicate of this bug. ***