17323 – MySQL 5.7 - Column search_history.time cannot be null

Bug 17323 - MySQL 5.7 - Column search_history.time cannot be null

Summary: MySQL 5.7 - Column search_history.time cannot be null

Status:	CLOSED FIXED

Alias:	None

Product:	Koha
Classification:	Unclassified
Component:	Searching (show other bugs)
Version:	Main
Hardware:	All All

Importance:	P5 - low major
Assignee:	Jonathan Druart
QA Contact:	Marcel de Rooy

URL:
Keywords:

Depends on:
Blocks:	17258
	Show dependency tree / graph

Reported:	2016-09-20 10:24 UTC by Jonathan Druart
Modified:	2019-06-27 09:24 UTC (History)
CC List:	5 users (show)

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

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
Show Obsolete (3) View All Add an attachment (proposed patch, testcase, etc.)

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 (khall) 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)

ERROR:
Bareword "dt_from_string" not allowed while "strict subs" in use at C4/Search/History.pm line 47.

Look here:
userid, sessionid, query_desc, query_cgi, type, total} . ( 

Look better :) Different brackets

Comment 10 Marcel de Rooy 2017-06-14 14:56:34 UTC Comment hidden (obsolete)

(In reply to Marcel de Rooy from comment #9)
> ERROR:
> Bareword "dt_from_string" not allowed while "strict subs" in use at
> C4/Search/History.pm line 47.
> 
> Look here:
> userid, sessionid, query_desc, query_cgi, type, total} . ( 
> 
> Look better :) Different brackets

Nope Look even better :)

Comment 11 Marcel de Rooy 2017-06-14 14:58:01 UTC Comment hidden (obsolete)

(In reply to Marcel de Rooy from comment #10)
> (In reply to Marcel de Rooy from comment #9)
> > ERROR:
> > Bareword "dt_from_string" not allowed while "strict subs" in use at
> > C4/Search/History.pm line 47.
> > 
> > Look here:
> > userid, sessionid, query_desc, query_cgi, type, total} . ( 
> > 
> > Look better :) Different brackets
> 
> Nope Look even better :)

Forget this. Remains of 17600 ;)