Bug 20144 - Test suite is failing with new default SQL modes
Summary: Test suite is failing with new default SQL modes
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Test Suite (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low normal (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
: 17860 18836 19492 (view as bug list)
Depends on:
Blocks: 17258 30805 21355 21597
  Show dependency treegraph
 
Reported: 2018-02-06 19:12 UTC by Jonathan Druart
Modified: 2022-05-19 07:55 UTC (History)
7 users (show)

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


Attachments
Bug 20144: [sql_modes] Fix borrowers table structure (4.86 KB, patch)
2018-02-12 09:18 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set a default value for lowestPriority (3.35 KB, patch)
2018-02-12 09:23 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Fix default value for statistics.amount (1.43 KB, patch)
2018-02-12 09:30 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Fix generated value for account_offsets.amount (1.29 KB, patch)
2018-02-12 09:38 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set default value for Aqbookseller in TestBuilder (920 bytes, patch)
2018-02-12 09:39 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Fix datatype for aqbasket.authorisedby (3.63 KB, patch)
2018-02-12 09:43 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set default value for aqbasket.is_standing (1.10 KB, patch)
2018-02-12 09:46 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Fix date format in tests (10.43 KB, patch)
2018-02-12 09:49 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set missinglist and recievedlist in subscriptionhistory (1.22 KB, patch)
2018-02-12 09:55 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set auth_header.marcxml to an empty string in tests (1.95 KB, patch)
2018-02-12 09:57 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Shorten cities.city_zipcode (1.32 KB, patch)
2018-02-12 10:03 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Add default values for export_format columns (3.17 KB, patch)
2018-02-12 10:08 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Correct quotes.timestamp values in tests (3.83 KB, patch)
2018-02-12 10:10 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Shorten items.barcode (1.07 KB, patch)
2018-02-12 10:12 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Add a value for borrower_attribute_types.description in tests (1.92 KB, patch)
2018-02-12 10:14 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Fix default value for statistics.itemnumber (1.34 KB, patch)
2018-02-12 10:18 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set default value for Acccountline in TestBuilder (919 bytes, patch)
2018-02-12 10:22 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Shorten aqbudgets.budget_code (4.35 KB, patch)
2018-02-12 10:25 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Remove HAVING clause in GetLateOrders (2.34 KB, patch)
2018-02-12 10:30 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Add default value for subscription_numberpatterns.description in tests (1.52 KB, patch)
2018-02-12 10:32 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set issuingrules.no_auto_renewal_after to NULL in tests (2.63 KB, patch)
2018-02-12 10:37 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Remove HAVING clause in GetUpcomingDueIssues (1.56 KB, patch)
2018-02-12 10:40 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set import_records.marcxml_old to '' on INSERT (1.71 KB, patch)
2018-02-12 10:49 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set default value for biblio.datecreated in tests (1.27 KB, patch)
2018-02-12 10:52 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Correct quotes.timestamp values in tests (2.16 KB, patch)
2018-02-12 10:54 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Fix GROUP BY clause in GetLetters (7.23 KB, patch)
2018-02-12 11:02 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set default value for marc_modification_template_actions (1.56 KB, patch)
2018-02-12 11:06 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Remove HAVING clause in GetBorrowersToExpunge (2.88 KB, patch)
2018-02-12 11:15 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Add default value for subscription_numberpatterns.description in tests (1.04 KB, patch)
2018-02-12 11:16 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set default value for biblio.datecreated in tests (1.12 KB, patch)
2018-02-12 11:23 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set auth_header.marcxml to an empty string in tests (1.08 KB, patch)
2018-02-12 11:33 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Shorten auth_header.authtypecode values in tests (1.18 KB, patch)
2018-02-12 11:35 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set correct value for old_issues.issue_id in tests (1.20 KB, patch)
2018-02-12 11:37 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Shorten values for virtualshelfshares.invitekey in tests (2.39 KB, patch)
2018-02-12 11:39 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Add default value for export_basket.description in tests (1.31 KB, patch)
2018-02-12 11:54 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Fix GROUP BY clause in GetBasketsInfosByBookseller (2.52 KB, patch)
2018-02-12 11:57 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Fix GROUP BY clause in GetInvoices (1.74 KB, patch)
2018-02-12 11:59 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set default value for Itemtype fees in TestBuilder (1.51 KB, patch)
2018-02-12 12:02 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Remove 'Null' string values from GetHardDueDate.t (5.26 KB, patch)
2018-02-12 12:05 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Remove HAVING clause from GetTopIssues (1.46 KB, patch)
2018-02-12 12:29 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set value for pending_offline_operations.timestamp (1.85 KB, patch)
2018-02-12 12:32 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set default value for biblio.datecreated in tests (1.00 KB, patch)
2018-02-12 12:35 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set value for creator_batches.timestamp (1.64 KB, patch)
2018-02-12 12:37 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Remove useless ORDER BY clauses in tests (1.27 KB, patch)
2018-02-12 12:40 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Remove ORDER BY clause for COUNT(*) in GetItemsForInventory (1.94 KB, patch)
2018-02-12 12:44 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set default value for opac_news.content in tests (1.13 KB, patch)
2018-02-12 12:46 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Fix values when creating a sql report in tests (1.64 KB, patch)
2018-02-12 12:48 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Fix search history tests (1.09 KB, patch)
2018-02-12 12:51 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Add default value for subscription_numberpatterns.description in tests (1.87 KB, patch)
2018-02-12 12:53 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Set subscription.numberlength and weeklength to NULL if not passed (984 bytes, patch)
2018-02-12 12:56 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Fix GROUP BY clause in Virtualshelves search (DT) (1.16 KB, patch)
2018-02-12 12:58 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: [sql_modes] Do not store 0000-00-00 (2.46 KB, patch)
2018-02-12 13:42 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 20144: (follow-up) Adjust /patrons endpoint (1.49 KB, patch)
2018-02-13 18:56 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 Jonathan Druart 2018-02-06 19:12:24 UTC

    
Comment 1 Jonathan Druart 2018-02-06 19:13:38 UTC
Test plan:
Edit /etc/mysql/my.cnf (or equivalent), and add the following line to the mysqld block:
sql-mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Then restart mysql and execute the test suite.
Comment 2 Jonathan Druart 2018-02-06 20:52:09 UTC
See https://github.com/joubu/Koha/tree/bug_20144 for the patches
Comment 3 Jonathan Druart 2018-02-06 20:52:33 UTC
*** Bug 19492 has been marked as a duplicate of this bug. ***
Comment 4 Jonathan Druart 2018-02-06 20:52:56 UTC
*** Bug 17860 has been marked as a duplicate of this bug. ***
Comment 5 Jonathan Druart 2018-02-06 20:53:07 UTC
*** Bug 18836 has been marked as a duplicate of this bug. ***
Comment 6 Josef Moravec 2018-02-07 08:36:33 UTC
Hi Jonathan, I just started to testing, found two issues till now:

Can't recreate database, when creating table structure, I got: 
there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

I am on mysql 5.5.59, which is still default in jessie, more timestamp columns are possible from mysql 5.6.5:
https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html

Second one:
Shouldn't be there atomic update for this change?
https://github.com/joubu/Koha/commit/de14e25c7b03ca9a4812df627dec397eae4261ad
Comment 7 Josef Moravec 2018-02-07 10:32:05 UTC
Few another ones:

The tests t/db_dependent/www/batch.t and t/db_dependent/www/search_utf8.t are failing, probably because of this: 
When you try to open batch in manage marc import tool, you get:
DBIx::Class::Storage::DBI::_dbh_execute(): 'koha_kohadev.me.frameworktext' isn't in GROUP BY


t/db_dependent/Circulation/dateexpiry.t is failing with: Incorrect date value: '0000-00-00' for column 'dateexpiry'
Comment 8 Jonathan Druart 2018-02-07 15:31:41 UTC
(In reply to Josef Moravec from comment #6)
> Hi Jonathan, I just started to testing, found two issues till now:
> 
> Can't recreate database, when creating table structure, I got: 
> there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or
> ON UPDATE clause
> 
> I am on mysql 5.5.59, which is still default in jessie, more timestamp
> columns are possible from mysql 5.6.5:
> https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html

Erk, yes we should continue and support both. I have added a follow-up to default to NULL instead.

> Second one:
> Shouldn't be there atomic update for this change?
> https://github.com/joubu/Koha/commit/de14e25c7b03ca9a4812df627dec397eae4261ad

Yep, commit amended.

(In reply to Josef Moravec from comment #7)
> Few another ones:
> 
> The tests t/db_dependent/www/batch.t and t/db_dependent/www/search_utf8.t
> are failing, probably because of this: 
> When you try to open batch in manage marc import tool, you get:
> DBIx::Class::Storage::DBI::_dbh_execute(): 'koha_kohadev.me.frameworktext'
> isn't in GROUP BY

I forgot the www tests! Fixed now.

> t/db_dependent/Circulation/dateexpiry.t is failing with: Incorrect date
> value: '0000-00-00' for column 'dateexpiry'

Yes, see this commit:
"""
    Bug 20144: [sql_modes] NOT FIXED - Incorrect date value: '0000-00-00' for column 'dateexpiry'

    See bug 20145.

    t/db_dependent/Circulation/dateexpiry.t
"""
Comment 9 Josef Moravec 2018-02-07 15:57:57 UTC
Thanks Jonathan, looks good for me now

For signed-off patches see https://github.com/xmorave2/Koha/commits/bug_20144
Comment 10 Julian Maurice 2018-02-09 19:02:01 UTC
The following commit:

Bug 20144: [sql_modes] Fix output_pref with dateformat=iso

makes another test to fail in t/DateUtils.t:

# Failed test 'iso output 12hr'
# at t/DateUtils.t line 47. 
# got: '2011-06-16 12:00' 
# expected: '2011-06-16 12:00 PM' 
# Looks like you failed 1 test of 67.
Comment 11 Katrin Fischer 2018-02-11 15:49:14 UTC
(In reply to Julian Maurice from comment #10)
> The following commit:
> 
> Bug 20144: [sql_modes] Fix output_pref with dateformat=iso
> 
> makes another test to fail in t/DateUtils.t:
> 
> # Failed test 'iso output 12hr'
> # at t/DateUtils.t line 47. 
> # got: '2011-06-16 12:00' 
> # expected: '2011-06-16 12:00 PM' 
> # Looks like you failed 1 test of 67.

Confirmed. Setting to FQA
Comment 12 Julian Maurice 2018-02-12 09:17:25 UTC
The git branch contains more than 60 commits (mostly independents from each other). Some commits are OK while others require more work. In order to not block the whole thing, I'm going to attach the patches that pass QA, and maybe open new reports for problematic patches.
Comment 13 Julian Maurice 2018-02-12 09:18:15 UTC
Created attachment 71410 [details] [review]
Bug 20144: [sql_modes] Fix borrowers table structure

surname, address and city can be NULL

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 14 Julian Maurice 2018-02-12 09:23:50 UTC
Created attachment 71411 [details] [review]
Bug 20144: [sql_modes] Set a default value for lowestPriority

Fix for:
Field 'lowestPriority' doesn't have a default value

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 15 Julian Maurice 2018-02-12 09:30:22 UTC
Created attachment 71412 [details] [review]
Bug 20144: [sql_modes] Fix default value for statistics.amount

Must be an integer, not empty string
Fix for:
Data truncated for column 'value'

Found with
t/db_dependent/Holds/DisallowHoldIfItemsAvailable.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 16 Julian Maurice 2018-02-12 09:38:17 UTC
Created attachment 71413 [details] [review]
Bug 20144: [sql_modes] Fix generated value for account_offsets.amount

Fix error:
Out of range value for column 'amount'

DBD::mysql::st execute failed: Out of range value for column 'amount' at
row 1 [for Statement "INSERT INTO `account_offsets` ( `amount`,
`debit_id`, `type`) VALUES ( ?, ?, ? )" with ParamValues:
0=7925469795795194609664.000000, 1='10', 2='Manual Debit'] at
/usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1832.

7925469795795194609664.000000
=> Should be lower anyway

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 17 Julian Maurice 2018-02-12 09:39:52 UTC
Created attachment 71414 [details] [review]
Bug 20144: [sql_modes] Set default value for Aqbookseller in TestBuilder

Fix for:
Out of range value for column 'tax_rate'

t/db_dependent/Koha/Acquisition/Basket.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 18 Julian Maurice 2018-02-12 09:43:58 UTC
Created attachment 71415 [details] [review]
Bug 20144: [sql_modes] Fix datatype for aqbasket.authorisedby

It's defined as a VARCHAR(10) in DB, but it is expecting a
borrowernumber instead

Fix for
Data too long for column 'authorisedby'

Must be a FK but first a borrowernumber
Need to update kohastructure for the comment?

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 19 Julian Maurice 2018-02-12 09:46:23 UTC
Created attachment 71416 [details] [review]
Bug 20144: [sql_modes] Set default value for aqbasket.is_standing

Fix for:
Column 'is_standing' cannot be null

t/db_dependent/Koha/Acquisition/Booksellers.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 20 Julian Maurice 2018-02-12 09:49:42 UTC
Created attachment 71417 [details] [review]
Bug 20144: [sql_modes] Fix date format in tests

Fix for:
Incorrect date value: '01-01-2013' for column 'startdate'
Incorrect date value: '01-01-2016' for column 'budget_period_enddate'
Incorrect date value: '30-11-2013' for column 'enddate'
Incorrect date value: '31-12-2015' for column 'budget_period_enddate'
Incorrect date value: '12-31-2015' for column 'budget_period_enddate'
Incorrect date value: '01-01-2014' for column 'entrydate'

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 21 Julian Maurice 2018-02-12 09:55:41 UTC
Created attachment 71418 [details] [review]
Bug 20144: [sql_modes] Set missinglist and recievedlist in subscriptionhistory

Fix for:
Field 'missinglist' doesn't have a default value

t/db_dependent/Koha/Acquisition/Booksellers.t

from mysql doc:
BLOB, TEXT, GEOMETRY, and JSON columns cannot be assigned a default
 value.

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 22 Julian Maurice 2018-02-12 09:57:47 UTC
Created attachment 71419 [details] [review]
Bug 20144: [sql_modes] Set auth_header.marcxml to an empty string in tests

Fix for:
Field 'marcxml' doesn't have a default value

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 23 Julian Maurice 2018-02-12 10:03:51 UTC
Created attachment 71420 [details] [review]
Bug 20144: [sql_modes] Shorten cities.city_zipcode

It's a varchar(20)

Fix for:
Data too long for column 'city_zipcode'

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 24 Julian Maurice 2018-02-12 10:08:56 UTC
Created attachment 71421 [details] [review]
Bug 20144: [sql_modes] Add default values for export_format columns

Fix for:
Field 'field_separator' doesn't have a default value
Field 'encoding' doesn't have a default value

field_separator and subfield_separator are only used for MARC csv
profiles, no need to specify a default value

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 25 Julian Maurice 2018-02-12 10:10:52 UTC
Created attachment 71422 [details] [review]
Bug 20144: [sql_modes] Correct quotes.timestamp values in tests

Fix for:
Incorrect datetime value: '0000-00-00 00:00:00' for column 'timestamp'

t/db_dependent/Koha/GetDailyQuote.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 26 Julian Maurice 2018-02-12 10:12:40 UTC
Created attachment 71423 [details] [review]
Bug 20144: [sql_modes] Shorten items.barcode

It's a varchar(20)

Fix for:
Data too long for column 'barcode'

t/db_dependent/Koha/Items.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 27 Julian Maurice 2018-02-12 10:14:05 UTC
Created attachment 71424 [details] [review]
Bug 20144: [sql_modes] Add a value for borrower_attribute_types.description in tests

Fix for:
Field 'description' doesn't have a default value

t/db_dependent/Koha/Patron/Attribute/Types.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 28 Julian Maurice 2018-02-12 10:18:48 UTC
Created attachment 71425 [details] [review]
Bug 20144: [sql_modes] Fix default value for statistics.itemnumber

Must be an integer, not empty string

Fix for:
Incorrect integer value: '' for column 'itemnumber'

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 29 Julian Maurice 2018-02-12 10:22:03 UTC
Created attachment 71427 [details] [review]
Bug 20144: [sql_modes] Set default value for Acccountline in TestBuilder

Fix for:
Data too long for column 'proccode'

t/db_dependent/Accounts.t

FIXME LATER - It's a varchar(4), must be integer!

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 30 Julian Maurice 2018-02-12 10:25:48 UTC
Created attachment 71428 [details] [review]
Bug 20144: [sql_modes] Shorten aqbudgets.budget_code

Fix for:
Data too long for column 'budget_code'

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 31 Julian Maurice 2018-02-12 10:30:16 UTC
Created attachment 71429 [details] [review]
Bug 20144: [sql_modes] Remove HAVING clause in GetLateOrders

More attention needed here!

Fix for:
Non-grouping field 'quantity' is used in HAVING clause

Having is useless, and certainly slower

t/db_dependent/Acquisition.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 32 Julian Maurice 2018-02-12 10:32:36 UTC
Created attachment 71430 [details] [review]
Bug 20144: [sql_modes] Add default value for subscription_numberpatterns.description in tests

Fix for:
Field 'description' doesn't have a default value

t/db_dependent/AdditionalField.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 33 Julian Maurice 2018-02-12 10:37:15 UTC
Created attachment 71431 [details] [review]
Bug 20144: [sql_modes] Set issuingrules.no_auto_renewal_after to NULL in tests

Fix for:
Incorrect integer value: '' for column 'no_auto_renewal_after'

FIXME - must we update values for existing installs?
TODO - check if can be set to an empty string from the interface

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 34 Julian Maurice 2018-02-12 10:40:43 UTC
Created attachment 71432 [details] [review]
Bug 20144: [sql_modes] Remove HAVING clause in GetUpcomingDueIssues

More attention needed here!

Fix for:
Non-grouping field 'days_until_due' is used in HAVING clause

t/db_dependent/Circulation.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 35 Julian Maurice 2018-02-12 10:49:38 UTC
Created attachment 71434 [details] [review]
Bug 20144: [sql_modes] Set import_records.marcxml_old to '' on INSERT

Fix for:
Field 'marcxml_old' doesn't have a default value

t/db_dependent/ImportBatch.t

Fixme - need to update existing DB?

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 36 Julian Maurice 2018-02-12 10:52:21 UTC
Created attachment 71435 [details] [review]
Bug 20144: [sql_modes] Set default value for biblio.datecreated in tests

When created without Koha::Biblio.

Fix for:
Field 'datecreated' doesn't have a default value

t/db_dependent/Items.t

TODO LATER - Use Koha::Biblio instead

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 37 Julian Maurice 2018-02-12 10:54:29 UTC
Created attachment 71436 [details] [review]
Bug 20144: [sql_modes] Correct quotes.timestamp values in tests

Fix for:
Incorrect datetime value: '0000-00-00 00:00:00' for column 'timestamp'

t/db_dependent/Koha.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 38 Julian Maurice 2018-02-12 11:02:45 UTC
Created attachment 71437 [details] [review]
Bug 20144: [sql_modes] Fix GROUP BY clause in GetLetters

This subroutine is wrong and must be rewritten using
Koha::Notice::Templates.
Mainly because the DB structure is bad.
Meanwhile we remove the branchcode from the SELECT to get correct
results, it was not used by callers anyway.

Fix for:
'koha_kohadev.letter.module' isn't in GROUP BY

t/db_dependent/Letters.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 39 Julian Maurice 2018-02-12 11:06:42 UTC
Created attachment 71438 [details] [review]
Bug 20144: [sql_modes] Set default value for marc_modification_template_actions

Cannot be empty strings.

Fix for:
Data truncated for column 'conditional'
Data truncated for column 'conditional_comparison'
Incorrect integer value: '' for column 'conditional_regex'

t/db_dependent/MarcModificationTemplates.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 40 Julian Maurice 2018-02-12 11:15:07 UTC
Created attachment 71440 [details] [review]
Bug 20144: [sql_modes] Remove HAVING clause in GetBorrowersToExpunge

Special attention needed here!

Fix for:
Non-grouping field 'currentissue' is used in HAVING clause

t/db_dependent/Members.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 41 Julian Maurice 2018-02-12 11:16:33 UTC
Created attachment 71441 [details] [review]
Bug 20144: [sql_modes] Add default value for subscription_numberpatterns.description in tests

Fix for:
Field 'description' doesn't have a default value

t/db_dependent/Serials.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 42 Julian Maurice 2018-02-12 11:23:16 UTC
Created attachment 71442 [details] [review]
Bug 20144: [sql_modes] Set default value for biblio.datecreated in tests

Should call Koha::Biblio->new instead

Fix for:
Field 'datecreated' doesn't have a default value

t/db_dependent/UsageStats.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 43 Julian Maurice 2018-02-12 11:33:07 UTC
Created attachment 71443 [details] [review]
Bug 20144: [sql_modes] Set auth_header.marcxml to an empty string in tests

Fix for:
Field 'marcxml' doesn't have a default value

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 44 Julian Maurice 2018-02-12 11:35:46 UTC
Created attachment 71444 [details] [review]
Bug 20144: [sql_modes] Shorten auth_header.authtypecode values in tests

Fix for:
Data too long for column 'authtypecode'

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 45 Julian Maurice 2018-02-12 11:37:19 UTC
Created attachment 71445 [details] [review]
Bug 20144: [sql_modes] Set correct value for old_issues.issue_id in tests

Fix for:
Column 'issue_id' cannot be null

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 46 Julian Maurice 2018-02-12 11:39:50 UTC
Created attachment 71446 [details] [review]
Bug 20144: [sql_modes] Shorten values for virtualshelfshares.invitekey in tests

It's a varchar(10)

Fix for:
Data too long for column 'invitekey'

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 47 Julian Maurice 2018-02-12 11:54:39 UTC
Created attachment 71447 [details] [review]
Bug 20144: [sql_modes] Add default value for export_basket.description in tests

Fix for:
Field 'description' doesn't have a default value

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 48 Julian Maurice 2018-02-12 11:57:40 UTC
Created attachment 71448 [details] [review]
Bug 20144: [sql_modes] Fix GROUP BY clause in GetBasketsInfosByBookseller

This need to be tested from the interface!

Fix for:
'koha_kohadev.aqbasket.basketname' isn't in GROUP BY

t/db_dependent/Acquisition/GetBasketsInfosByBookseller.t

We need this group by.
We should not need to list all fields, from mysql 5.7 doc:
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
"The query is valid if name is a primary key of t or is a unique NOT NULL column. In such cases, MySQL recognizes that the selected column is functionally dependent on a grouping column. "

However, MariaDB did not implemented yet:
https://jira.mariadb.org/browse/MDEV-11588

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 49 Julian Maurice 2018-02-12 11:59:35 UTC
Created attachment 71449 [details] [review]
Bug 20144: [sql_modes] Fix GROUP BY clause in GetInvoices

Need to be tested from the interface!

Fix for:
'koha_kohadev.aqinvoices.invoicenumber' isn't in GROUP BY

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 50 Julian Maurice 2018-02-12 12:02:29 UTC
Created attachment 71450 [details] [review]
Bug 20144: [sql_modes] Set default value for Itemtype fees in TestBuilder

Fix for:
Out of range value for column 'amount'

t/db_dependent/Circulation/Chargelostitem.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 51 Julian Maurice 2018-02-12 12:05:35 UTC
Created attachment 71451 [details] [review]
Bug 20144: [sql_modes] Remove 'Null' string values from GetHardDueDate.t

Must be undef or correct integer value

Fix for:
Incorrect integer value: 'Null' for column 'enrolmentperiod'

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 52 Julian Maurice 2018-02-12 12:29:38 UTC
Created attachment 71452 [details] [review]
Bug 20144: [sql_modes] Remove HAVING clause from GetTopIssues

Fix for:
'koha_kohadev.b.title' isn't in GROUP BY

t/db_dependent/Circulation/GetTopIssues.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 53 Julian Maurice 2018-02-12 12:32:45 UTC
Created attachment 71453 [details] [review]
Bug 20144: [sql_modes] Set value for pending_offline_operations.timestamp

Not null

Fix for:
Incorrect datetime value: 'null' for column 'timestamp'

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 54 Julian Maurice 2018-02-12 12:35:31 UTC
Created attachment 71454 [details] [review]
Bug 20144: [sql_modes] Set default value for biblio.datecreated in tests

Fix for:
Field 'datecreated' doesn't have a default value

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 55 Julian Maurice 2018-02-12 12:37:47 UTC
Created attachment 71455 [details] [review]
Bug 20144: [sql_modes] Set value for creator_batches.timestamp

Fix for:
Incorrect datetime value: 'now()' for column 'timestamp'

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 56 Julian Maurice 2018-02-12 12:40:26 UTC
Created attachment 71456 [details] [review]
Bug 20144: [sql_modes] Remove useless ORDER BY clauses in tests

Fix for:
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 57 Julian Maurice 2018-02-12 12:44:22 UTC
Created attachment 71457 [details] [review]
Bug 20144: [sql_modes] Remove ORDER BY clause for COUNT(*) in GetItemsForInventory

Fix for:
DBD::mysql::st execute failed: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause [for Statement 'SELECT COUNT(*)

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 58 Julian Maurice 2018-02-12 12:46:36 UTC
Created attachment 71458 [details] [review]
Bug 20144: [sql_modes] Set default value for opac_news.content in tests

Fix for:
Field 'content' doesn't have a default value

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 59 Julian Maurice 2018-02-12 12:48:41 UTC
Created attachment 71459 [details] [review]
Bug 20144: [sql_modes] Fix values when creating a sql report in tests

For fix:
Data too long for column 'report_area'
Incorrect integer value: 'null' for column 'cache_expiry'

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 60 Julian Maurice 2018-02-12 12:51:24 UTC
Created attachment 71460 [details] [review]
Bug 20144: [sql_modes] Fix search history tests

Incorrect datetime value: '1374978877' for column 'time'

Need to investigate this: Why did I put that value in the test?
Does the feature correctly works?

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 61 Julian Maurice 2018-02-12 12:53:31 UTC
Created attachment 71461 [details] [review]
Bug 20144: [sql_modes] Add default value for subscription_numberpatterns.description in tests

Fix for:
Field 'description' doesn't have a default value

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 62 Julian Maurice 2018-02-12 12:56:28 UTC
Created attachment 71462 [details] [review]
Bug 20144: [sql_modes] Set subscription.numberlength and weeklength to NULL if not passed

Fix for:
Incorrect integer value: '' for column 'numberlength'
Incorrect integer value: '' for column 'weeklength'

t/db_dependent/Serials/ReNewSubscription.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 63 Julian Maurice 2018-02-12 12:58:23 UTC
Created attachment 71463 [details] [review]
Bug 20144: [sql_modes] Fix GROUP BY clause in Virtualshelves search (DT)

Fix for:
'koha_kohadev.vs.shelfname' isn't in GROUP BY

t/db_dependent/Utils/Datatables_Virtualshelves.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 64 Julian Maurice 2018-02-12 13:07:56 UTC
All attached patches are ready to be pushed.

I will create other bug reports for the other patches.
Comment 65 Julian Maurice 2018-02-12 13:42:25 UTC
Created attachment 71467 [details] [review]
Bug 20144: [sql_modes] Do not store 0000-00-00

This patch needs to be linked with bug 20145.

Fix for:
Incorrect date value: '0000-00-00' for column 'dateexpiry'

We cannot longer store 0000-00-00, but no need to.
The tests must be kept for now as long as bug 20145 has a solution

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 66 Jonathan Druart 2018-02-13 17:18:01 UTC
Pushed to master for 18.05, thanks to everybody involved!
Comment 67 Tomás Cohen Arazi 2018-02-13 18:56:43 UTC
Created attachment 71564 [details] [review]
Bug 20144: (follow-up) Adjust /patrons endpoint

Fields 'address' and 'city' have been maybe nullable by this bug. This
patch makes them nullable on the API as well.

To test:
- Run:
  $ kshell
 k$ prove t/db_dependent/api/v1/patrons.t
=> FAIL: Tests fail (randomly) when address or city are set to null by
         TestBuilder.
- Apply this patch
- Run:
 k$ prove t/db_dependent/api/v1/patrons.t
=> SUCCESS: Tests pass!
- Sign off :-D

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 68 Jonathan Druart 2018-02-13 19:26:08 UTC
Last patch pushed to master.