Bug 21600 - t/db_dependent/api/v1/patrons.t is failing with new SQL modes
Summary: t/db_dependent/api/v1/patrons.t is failing with new SQL modes
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Test Suite (show other bugs)
Version: master
Hardware: All All
: P5 - low major (vote)
Target Milestone: ---
Assignee: Tomás Cohen Arazi
QA Contact: Marcel de Rooy
URL:
Whiteboard:
Keywords:
Depends on: 21597
Blocks:
  Show dependency treegraph
 
Reported: 2018-10-18 13:23 UTC by Jonathan Druart
Modified: 2018-11-28 08:24 UTC (History)
5 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Bot Control: ---
When did the bot last check this:
Who signed the patch off:
Text to go in the release notes:


Attachments
Bug 21600: /patrons should have timestamp/datetime values converted from rfc3339 into valid MySQL values (2.17 KB, patch)
2018-10-18 19:00 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 21600: /patrons should have timestamp/datetime values converted from rfc3339 into valid MySQL values (2.24 KB, patch)
2018-10-18 19:37 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 21600: Use str param instead of dt for output_pref (1.15 KB, patch)
2018-10-18 19:37 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 21600: /patrons should have timestamp/datetime values converted from rfc3339 into valid MySQL values (2.33 KB, patch)
2018-10-19 07:44 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 21600: Use str param instead of dt for output_pref (1.24 KB, patch)
2018-10-19 07:44 UTC, Marcel de Rooy
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-10-18 13:23:29 UTC
I have failed fixing this one, Tomas can you have a look please?

It seems that we try and insert a rfc3339 formatted date into a datetime or timestamp column.

DBD::mysql::st execute failed: Incorrect datetime value: '2018-10-18T12:50:41-03:00' for column 'lastseen'
Comment 1 Tomás Cohen Arazi 2018-10-18 19:00:29 UTC
Created attachment 80875 [details] [review]
Bug 21600: /patrons should have timestamp/datetime values converted from rfc3339 into valid MySQL values

Bug 21597 enabled the option so set strict SQL modes in koha-conf.xml to
be able to improve Koha's comformance to current standards on DB
engines.

This causes lots of parts of Koha to fail because they rely on MySQL
fallbacks that are no longer default behaviours.

While we can workaround this by setting SQL modes on runtime, the
decision has been that this needs fixing.

This patch deals with the /patrons API failing to correctly convert
datetimes into valid SQL timestamps.

To test:
- Run:
  $ kshell
 k$ prove t/db_dependent/api/v1/patrons.t
=> SUCCESS: Tests pass
- Add <strict_sql_modes>1</strict_sql_modes> on your koha-conf.xml
  $ sudo vim /etc/koha/sites/kohadev/koha-conf.xml
- Restart memcached and Plack:
  $ restart_all
- Run:
 k$ prove t/db_dependent/api/v1/patrons.t
=> FAIL: Tests fail due to updated_on and/or lastseen fields wrong
format (MySQL error)
- Apply this patch
- Run:
 k$ prove t/db_dependent/api/v1/patrons.t
=> SUCCESS: Tests pass! It all makes sense!
- Sign off :-D

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 2 Jonathan Druart 2018-10-18 19:37:32 UTC
Created attachment 80876 [details] [review]
Bug 21600: /patrons should have timestamp/datetime values converted from rfc3339 into valid MySQL values

Bug 21597 enabled the option so set strict SQL modes in koha-conf.xml to
be able to improve Koha's comformance to current standards on DB
engines.

This causes lots of parts of Koha to fail because they rely on MySQL
fallbacks that are no longer default behaviours.

While we can workaround this by setting SQL modes on runtime, the
decision has been that this needs fixing.

This patch deals with the /patrons API failing to correctly convert
datetimes into valid SQL timestamps.

To test:
- Run:
  $ kshell
 k$ prove t/db_dependent/api/v1/patrons.t
=> SUCCESS: Tests pass
- Add <strict_sql_modes>1</strict_sql_modes> on your koha-conf.xml
  $ sudo vim /etc/koha/sites/kohadev/koha-conf.xml
- Restart memcached and Plack:
  $ restart_all
- Run:
 k$ prove t/db_dependent/api/v1/patrons.t
=> FAIL: Tests fail due to updated_on and/or lastseen fields wrong
format (MySQL error)
- Apply this patch
- Run:
 k$ prove t/db_dependent/api/v1/patrons.t
=> SUCCESS: Tests pass! It all makes sense!
- Sign off :-D

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 3 Jonathan Druart 2018-10-18 19:37:37 UTC
Created attachment 80877 [details] [review]
Bug 21600: Use str param instead of dt for output_pref

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 4 Marcel de Rooy 2018-10-19 07:44:00 UTC
Created attachment 80898 [details] [review]
Bug 21600: /patrons should have timestamp/datetime values converted from rfc3339 into valid MySQL values

Bug 21597 enabled the option so set strict SQL modes in koha-conf.xml to
be able to improve Koha's comformance to current standards on DB
engines.

This causes lots of parts of Koha to fail because they rely on MySQL
fallbacks that are no longer default behaviours.

While we can workaround this by setting SQL modes on runtime, the
decision has been that this needs fixing.

This patch deals with the /patrons API failing to correctly convert
datetimes into valid SQL timestamps.

To test:
- Run:
  $ kshell
 k$ prove t/db_dependent/api/v1/patrons.t
=> SUCCESS: Tests pass
- Add <strict_sql_modes>1</strict_sql_modes> on your koha-conf.xml
  $ sudo vim /etc/koha/sites/kohadev/koha-conf.xml
- Restart memcached and Plack:
  $ restart_all
- Run:
 k$ prove t/db_dependent/api/v1/patrons.t
=> FAIL: Tests fail due to updated_on and/or lastseen fields wrong
format (MySQL error)
- Apply this patch
- Run:
 k$ prove t/db_dependent/api/v1/patrons.t
=> SUCCESS: Tests pass! It all makes sense!
- Sign off :-D

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 5 Marcel de Rooy 2018-10-19 07:44:06 UTC
Created attachment 80899 [details] [review]
Bug 21600: Use str param instead of dt for output_pref

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 6 Nick Clemens 2018-10-19 17:41:15 UTC
Awesome work all!

Pushed to master for 18.11
Comment 7 Martin Renvoize 2018-11-05 14:47:07 UTC
Pushed to 18.05.x for 18.05.06
Comment 8 Fridolin SOMERS 2018-11-28 08:24:04 UTC
Depends on Bug 21597 not in 17.11.x