MySQL 5.7 is more strict, structure and sample data fails to load.
Created attachment 51727 [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
Created attachment 51728 [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
Created attachment 51741 [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.
The change in the kohastructure should be done for existing install (i.e. atomic update).
Created attachment 51751 [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
Created attachment 51760 [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>
Created attachment 51761 [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>
Created attachment 51762 [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>
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. :(
Created attachment 53639 [details] [review] Bug 16573: Define virtualshelves.created_on as datetime for existing installs
(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.
Now the Elastic search upgrade stuff was bombing horribly. for 3.23.0.50. commented it out... everything else fine.
Created attachment 53664 [details] [review] Bug 16573: Define virtualshelves.created_on as datetime for existing installs Signed-off-by: Mark Tompsett <mtompset@hotmail.com>
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>
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>
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>
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>
(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?
Pushed to master for Koha 16.11, thanks Bernardo!
Pushed in 16.05. Will be in 16.05.03.
(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).
(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. :(
Patches pushed to 3.22.x, will be in 3.22.10
Created attachment 54658 [details] [review] Bug 16573: (RM followup) Update DBIx schema and prevent upgrade issues This patch updates the DBIx::Class schema files, and introduces the IGNORE option to the updatedatabase.pl's ALTER TABLE to prevent issues for users upgrading from 3.22 onwards. Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Back to the RM queue.
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.
Mark, you are right! I missed that in the docs. Kyle, just upgrade the schema files!
(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!
(In reply to Jonathan Druart from comment #28) > ping! bug 17234?
(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.
*** Bug 16664 has been marked as a duplicate of this bug. ***