Bug 17323 - MySQL 5.7 - Column search_history.time cannot be null
Summary: MySQL 5.7 - Column search_history.time cannot be null
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Searching (show other bugs)
Version: master
Hardware: All All
: P5 - low major (vote)
Assignee: Jonathan Druart
QA Contact: Marcel de Rooy
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: 17258
  Show dependency treegraph
 
Reported: 2016-09-20 10:24 UTC by Jonathan Druart
Modified: 2017-06-14 14:58 UTC (History)
5 users (show)

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


Attachments
Bug 17323: Column search_history.time cannot be null (3.37 KB, patch)
2016-09-20 10:43 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 17323: Column search_history.time cannot be null (3.35 KB, patch)
2016-09-20 12:05 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 17323: Column search_history.time cannot be null (3.41 KB, patch)
2016-10-04 22:14 UTC, Chris Cormack
Details | Diff | Splinter Review
Bug 17323: Column search_history.time cannot be null (3.58 KB, patch)
2016-11-18 13:19 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 2016-09-20 10:24:55 UTC
When doing a search, with SearchHistory pref switched on:

DBD::mysql::st execute failed: Column 'time' cannot be null [for Statement "
        INSERT INTO search_history(
            userid, sessionid, query_desc, query_cgi, type, total, time
        ) VALUES(
            ?, ?, ?, ?, ?, ?, ?
        )
    " with ParamValues: 0="7874", 1='12338a0bd8da63e32c79ee84f8493a07', 2="kw,wrdl: d", 3='q=d', 4='biblio', 5=6104, 6=undef] at C4/Search/History.pm line 34.
127.0.0.1 - - [20/Sep/2016:11:22:05 +0100] "GET /cgi-bin/koha/opac-search.pl?q=d HTTP/1.1" 200 272867 "http://catalogue.koha-qa.vm/" "Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Firefox/45.0"
Comment 1 Jonathan Druart 2016-09-20 10:43:13 UTC
Created attachment 55715 [details] [review]
Bug 17323: Column search_history.time cannot be null

When doing a search, with SearchHistory pref switched on:

DBD::mysql::st execute failed: Column 'time' cannot be null [for Statement "
        INSERT INTO search_history(
            userid, sessionid, query_desc, query_cgi, type, total, time
        ) VALUES(
            ?, ?, ?, ?, ?, ?, ?
        )
    " with ParamValues: 0="7874", 1='12338a0bd8da63e32c79ee84f8493a07', 2="kw,wrdl: d", 3='q=d', 4='biblio', 5=6104, 6=undef] at C4/Search/History.pm line 34.

Since MySLQ 5.7, undef does not mean "the default value".
To let the DBMS set the default value (CURRENT_TIMESTAMP), the parameter should not be passed

Test plan:
OPAC+Intranet, swith the 2 SearchHistory syspref on
Do a search
=> Without this patch, you will get an error in the log and the row will not be inserted in the table.
=> With this patch, everything must go fine
Comment 2 Jonathan Druart 2016-09-20 12:05:36 UTC
Created attachment 55717 [details] [review]
Bug 17323: Column search_history.time cannot be null

When doing a search, with SearchHistory pref switched on:

DBD::mysql::st execute failed: Column 'time' cannot be null [for Statement "
        INSERT INTO search_history(
            userid, sessionid, query_desc, query_cgi, type, total, time
        ) VALUES(
            ?, ?, ?, ?, ?, ?, ?
        )
    " with ParamValues: 0="7874", 1='12338a0bd8da63e32c79ee84f8493a07', 2="kw,wrdl: d", 3='q=d', 4='biblio', 5=6104, 6=undef] at C4/Search/History.pm line 34.

Since MySLQ 5.7, undef does not mean "the default value".
To let the DBMS set the default value (CURRENT_TIMESTAMP), the parameter should not be passed

Test plan:
OPAC+Intranet, swith the 2 SearchHistory syspref on
Do a search
=> Without this patch, you will get an error in the log and the row will not be inserted in the table.
=> With this patch, everything must go fine
Comment 3 Jonathan Druart 2016-09-29 13:16:20 UTC
This is a major one!
Comment 4 Chris Cormack 2016-10-04 22:14:21 UTC
Created attachment 56042 [details] [review]
Bug 17323: Column search_history.time cannot be null

When doing a search, with SearchHistory pref switched on:

DBD::mysql::st execute failed: Column 'time' cannot be null [for Statement "
        INSERT INTO search_history(
            userid, sessionid, query_desc, query_cgi, type, total, time
        ) VALUES(
            ?, ?, ?, ?, ?, ?, ?
        )
    " with ParamValues: 0="7874", 1='12338a0bd8da63e32c79ee84f8493a07', 2="kw,wrdl: d", 3='q=d', 4='biblio', 5=6104, 6=undef] at C4/Search/History.pm line 34.

Since MySLQ 5.7, undef does not mean "the default value".
To let the DBMS set the default value (CURRENT_TIMESTAMP), the parameter should not be passed

Test plan:
OPAC+Intranet, swith the 2 SearchHistory syspref on
Do a search
=> Without this patch, you will get an error in the log and the row will not be inserted in the table.
=> With this patch, everything must go fine

Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>
Comment 5 Marcel de Rooy 2016-11-18 13:19:11 UTC
Created attachment 57647 [details] [review]
Bug 17323: Column search_history.time cannot be null

When doing a search, with SearchHistory pref switched on:

DBD::mysql::st execute failed: Column 'time' cannot be null [for Statement "
        INSERT INTO search_history(
            userid, sessionid, query_desc, query_cgi, type, total, time
        ) VALUES(
            ?, ?, ?, ?, ?, ?, ?
        )
    " with ParamValues: 0="7874", 1='12338a0bd8da63e32c79ee84f8493a07', 2="kw,wrdl: d", 3='q=d', 4='biblio', 5=6104, 6=undef] at C4/Search/History.pm line 34.

Since MySLQ 5.7, undef does not mean "the default value".
To let the DBMS set the default value (CURRENT_TIMESTAMP), the parameter should not be passed

Test plan:
OPAC+Intranet, swith the 2 SearchHistory syspref on
Do a search
=> Without this patch, you will get an error in the log and the row will not be inserted in the table.
=> With this patch, everything must go fine

Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Not verified with MySQL 5.7. But this looks good to me.
Works under Jessie.
Comment 6 Kyle M Hall 2016-11-18 13:52:00 UTC
Pushed to master for 16.11, thanks Jonathan!
Comment 7 Julian Maurice 2016-11-30 13:08:30 UTC
Pushed to 3.22.x, will be in 3.22.13
Comment 8 Mason James 2016-12-14 12:13:42 UTC
Pushed to 16.05.x, for 16.05.06 release
Comment 9 Marcel de Rooy 2017-06-14 14:55:01 UTC Comment hidden (obsolete)
Comment 10 Marcel de Rooy 2017-06-14 14:56:34 UTC Comment hidden (obsolete)
Comment 11 Marcel de Rooy 2017-06-14 14:58:01 UTC Comment hidden (obsolete)