Opened 9 years ago
Closed 9 years ago
Last modified 8 years ago
#12713 closed Bug Report - General (Fixed)
Mythweb fails with mysql Server version: 5.7.11-0ubuntu5
| Reported by: | Owned by: | Karl Egly | |
|---|---|---|---|
| Priority: | minor | Milestone: | 0.28.1 |
| Component: | Plugin - MythWeb | Version: | 0.28.0 |
| Severity: | medium | Keywords: | |
| Cc: | Ticket locked: | no |
Description
After today's update of XenialXerus? 08/04/16
4.4.0-17-generic #33-Ubuntu SMP Tue Mar 29 17:17:28 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux.
Mythweb (latest) fails. MythTV Version : v0.28-rc1-3-g2520617 MythTV Branch : fixes/0.28 Network Protocol : 88 Library API : 0.28.20160309-1 QT Version : 5.5.1
mythweb 94ec03ada41da6ab7883b0eb037ea58239f05913 Sat Mar 5
I think this is due to Xenial forcing an upgrade of mysql server from 5.6 to 5.7. The Listings and Recordings pages fail but all others are ok. Listings fail with this error:- Error Fatal Error
!NoTrans: SQL Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mythconverg.channel.chanid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by #1055!!
Recorded fails with this error Fatal Error
!NoTrans: SQL Error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mythconverg.recordedmarkup.data' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by #1055!!
Attachments (2)
Change History (30)
comment:1 Changed 9 years ago by
| Milestone: | 0.28 → 0.29 |
|---|
comment:2 follow-up: 5 Changed 9 years ago by
A surprising move to 0.29 if mysql 5.7 is the default for Xenial. However an easy fix is to unset the ONLY_FULL_GROUP_BY option which is set in 5.7. This can be done by setting sql_mode to only the other default modes in mythtv.cnf. so edit /etc/mysql/conf.d/mythtv.cnf and add the last line to the exisiting contents so the new file reads :-
[mysqld] #bind-address=0.0.0.0 max_connections=100 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
comment:3 Changed 9 years ago by
I'm happy to shepherd a patch that fixes the SQL statement into 0.28.1 if you (or someone else) have one.
There is a log in #12719 (a duplicate of this issue)
comment:4 Changed 9 years ago by
Sorry by knowledge of sql is not up to changing the queries but the change to mythtv.cnf does work. Presumably the patch can only be applied to a mysql 5.7 installation. It would have to be done by the installation code.
comment:5 follow-ups: 9 13 27 Changed 9 years ago by
Replying to jksjdevelop@…:
A surprising move to 0.29 if mysql 5.7 is the default for Xenial. However an easy fix is to unset the ONLY_FULL_GROUP_BY option which is set in 5.7. This can be done by setting sql_mode to only the other default modes in mythtv.cnf. so edit /etc/mysql/conf.d/mythtv.cnf and add the last line to the exisiting contents so the new file reads :-
[mysqld] #bind-address=0.0.0.0 max_connections=100 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Correction - this still fails with a date-time error when creating a recording - reduce the options to only those present in 5.5.
[mysqld] #bind-address=0.0.0.0 max_connections=100 sql_mode=NO_ENGINE_SUBSTITUTION
comment:8 Changed 9 years ago by
| Milestone: | 29.0 → 0.28.1 |
|---|---|
| Owner: | changed from Rob Smith to Karl Egly |
| Status: | new → accepted |
comment:9 Changed 9 years ago by
| Status: | accepted → infoneeded |
|---|
Replying to jksjdevelop@…:
Correction - this still fails with a date-time error when creating a recording - reduce the options to only those present in 5.5.
Can you post the actual error? The log in #12719 allowed me to fix one issue. But I could not reproduce an error when creating a recording rule from the guide.
comment:11 Changed 9 years ago by
Trace back when trying to create a recording.http://192.168.1.108/mythweb/tv/detail/1101/1460546100
datetime: 2016-04-13 09:38:54 (BST)
errornum: 256
error type: User Error
error string: !!NoTrans: SQL Error: Incorrect datetime value: '00:00:00' for column 'next_record' at row 1 [#1292]!!
filename: /home/tv/MythSource/mythweb/classes/Database/Query/mysqlicompat.php
error line: 98
==========================================================================
Backtrace:
file: /home/tv/MythSource/mythweb/classes/Database/Query/mysqlicompat.php
line: 98
class:
function: trigger_error
type:
args: Array
(
[0] => SQL Error: Incorrect datetime value: '00:00:00' for column 'next_record' at row 1 [#1292]
[1] => 256
)
file: /home/tv/MythSource/mythweb/classes/Database.php
line: 261
class: Database_Query_mysqlicompat
function: execute
type: ->
args: Array
(
[0] => Array
(
[0] => 0
[1] => 1
[2] => 1101
[3] => 1460546100
[4] => 1460546100
[5] => 1460548800
[6] => 1460548800
[7] => 0
[8] => Bargain Hunt
[9] => Newark
[10] => Antiques challenge. Newark Antiques and Collectors Fair plays host to Tim Wonnacott and experts Philip Serrell and Anita Manning as the red and blue teams scour the grounds.
[11] => Default
[12] => 0
[13] => Leisure/Hobbies
[14] => 0
[15] => 0
[16] => 0
[17] => 0
[18] => 0
[19] => 5
[20] => Default
[21] => 8
[22] => 15
[23] => BBC ONE HD
[24] => fp.bbc.co.uk/kr1tn8
[25] => fp.bbc.co.uk/23v6jb
[26] => 0
[27] => 4
[28] => 12:15:00
[29] => 719528
[30] => 0
[31] => 0
[32] => 0
[33] => 0
[34] => 0
[35] => 0
[36] => 0
[37] => 1
[38] => Default
[39] => Default
[40] => 0
[41] => 00:00:00
[42] => 00:00:00
[43] => 00:00:00
[44] =>
[45] => 0
[46] => 0
[47] => 0
[48] => 1
)
)
file: /home/tv/MythSource/mythweb/modules/tv/classes/Schedule.php
line: 385
class: Database
function: query
type: ->
args: Array
(
[0] => REPLACE INTO record (recordid,type,chanid,starttime,startdate,endtime,enddate,search,
title,subtitle,description,profile,recpriority,category,
maxnewest,inactive,maxepisodes,autoexpire,startoffset,endoffset,
recgroup,dupmethod,dupin,station,seriesid,programid,autocommflag,
findday,findtime,findid,autotranscode,parentid,transcoder,
autouserjob1,autouserjob2,autouserjob3,autouserjob4,autometadata,
playgroup,storagegroup,prefinput,
next_record,last_record,last_delete,inetref,season,episode,filter,recgroupid)
VALUES (?,?,?,
FROM_UNIXTIME(?),FROM_UNIXTIME(?),FROM_UNIXTIME(?),FROM_UNIXTIME(?),
?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
[1] => 0
[2] => 1
[3] => 1101
[4] => 1460546100
[5] => 1460546100
[6] => 1460548800
[7] => 1460548800
[8] => 0
[9] => Bargain Hunt
[10] => Newark
[11] => Antiques challenge. Newark Antiques and Collectors Fair plays host to Tim Wonnacott and experts Philip Serrell and Anita Manning as the red and blue teams scour the grounds.
[12] => Default
[13] => 0
[14] => Leisure/Hobbies
[15] => 0
[16] => 0
[17] => 0
[18] => 0
[19] => 0
[20] => 5
[21] => Default
[22] => 8
[23] => 15
[24] => BBC ONE HD
[25] => fp.bbc.co.uk/kr1tn8
[26] => fp.bbc.co.uk/23v6jb
[27] => 0
[28] => 4
[29] => 12:15:00
[30] => 719528
[31] => 0
[32] => 0
[33] => 0
[34] => 0
[35] => 0
[36] => 0
[37] => 0
[38] => 1
[39] => Default
[40] => Default
[41] => 0
[42] => 00:00:00
[43] => 00:00:00
[44] => 00:00:00
[45] =>
[46] => 0
[47] => 0
[48] => 0
[49] => 1
)
file: /home/tv/MythSource/mythweb/modules/tv/detail.php
line: 250
class: Schedule
function: save
type: ->
args: Array
(
[0] => 1
)
file: /home/tv/MythSource/mythweb/modules/tv/handler.php
line: 82
class:
function: require_once
type:
args: Array
(
[0] => /home/tv/MythSource/mythweb/modules/tv/detail.php
)
file: /home/tv/MythSource/mythweb/mythweb.php
line: 35
class:
function: require_once
type:
args: Array
(
[0] => /home/tv/MythSource/mythweb/modules/tv/handler.php
)
==========================================================================
$_GET: Array
(
[chanid] => 1101
[starttime] => 1460546100
[manualid] => 0
)
==========================================================================
$_POST: Array
(
[record] => 1
[profile] => Default
[transcoder] => 0
[recgroup] => Default
[storagegroup] => Default
[playgroup] => Default
[recpriority] => 0
[dupmethod] => 8
[dupin] => 15
[prefinput] => 0
[inetref] =>
[season] =>
[episode] =>
[autometadata] => 1
[maxepisodes] => 0
[startoffset] => 0
[endoffset] => 5
[save] => Update Recording Settings
)
==========================================================================
$_SESSION: Array
(
[cache_engine] => Cache_Null
[stream] => Array
(
[include_user_and_password] =>
)
[prefer_channum] => 1
[recorded_pixmaps] => 1
[guide_favonly] =>
[timeslot_size] => 600
[num_time_slots] => 36
[timeslot_blocks] => 3
[timeslotbar_skip] => 20
[max_stars] => 4
[star_character] => ★
[show_popup_info] => 1
[show_channel_icons] => 1
[sortby_channum] => 1
[recorded_paging] => null
[genre_colors] => 1
[show_video_covers] => 1
[settings] => Array
(
[screens] => Array
(
[tv] => Array
(
[upcoming recordings] => Array
(
[title] => on
[channel] => on
[record date] => on
[length] => on
)
)
)
comment:12 Changed 9 years ago by
Additional info:- the above is detected by the NO_ZERO_DATE option, clearing this and the page is ok.
comment:13 Changed 9 years ago by
Correction - this still fails with a date-time error when creating a recording - reduce the options to only those present in 5.5.
[mysqld] #bind-address=0.0.0.0 max_connections=100 sql_mode=NO_ENGINE_SUBSTITUTION
This fix worked for me. Thanks!!!!!
comment:14 follow-up: 15 Changed 9 years ago by
On the upcoming recordings page, 'Never Record' generates the following error, Don't Record works.
datetime: 2016-04-18 21:53:27 (BST)
errornum: 256
error type: User Error
error string: !!NoTrans: SQL Error: Field 'season' doesn't have a default value [#1364]!!
filename: /home/tv/MythSource/mythweb/classes/Database/Query/mysqlicompat.php
error line: 98
==========================================================================
Backtrace:
file: /home/tv/MythSource/mythweb/classes/Database/Query/mysqlicompat.php
line: 98
class:
function: trigger_error
type:
args: Array
(
[0] => SQL Error: Field 'season' doesn't have a default value [#1364]
[1] => 256
)
file: /home/tv/MythSource/mythweb/classes/Database.php
line: 261
class: Database_Query_mysqlicompat
function: execute
type: ->
args: Array
(
[0] => Array ( )
)
file: /home/tv/MythSource/mythweb/modules/tv/classes/Program.php
line: 646
class: Database
function: query
type: ->
args: Array
(
[0] => REPLACE INTO oldrecorded (chanid,starttime,endtime,title,subtitle,description,category,seriesid,programid,recordid,station,rectype,recstatus,duplicate) VALUES ('11940',NOW(),NOW(),'The Hairy Bikers\' Pubs That Built Britain','Cornwall','Si and Dave discover pubs that were at the centre of an illegal industry in the late 1700s.','News','fp.bbc.co.uk/ve3zps','fp.bbc.co.uk/247obc','4229','BBC TWO HD','4',11,1)
)
file: /home/tv/MythSource/mythweb/modules/tv/upcoming.php
line: 36
class: Program
function: rec_never_record
type: ->
args: Array ( )
file: /home/tv/MythSource/mythweb/modules/tv/handler.php
line: 82
class:
function: require_once
type:
args: Array
(
[0] => /home/tv/MythSource/mythweb/modules/tv/upcoming.php
)
file: /home/tv/MythSource/mythweb/mythweb.php
line: 35
class:
function: require_once
type:
args: Array
(
[0] => /home/tv/MythSource/mythweb/modules/tv/handler.php
)
==========================================================================
$_GET: Array
(
[never_record] => yes
[chanid] => 11940
[starttime] => 1461259800
[manualid] => 0
)
==========================================================================
$_SESSION: Array
(
[cache_engine] => Cache_Null
[stream] => Array
(
[include_user_and_password] =>
)
[prefer_channum] => 1
[recorded_pixmaps] => 1
[guide_favonly] =>
[timeslot_size] => 600
[num_time_slots] => 36
[timeslot_blocks] => 3
[timeslotbar_skip] => 20
[max_stars] => 4
[star_character] => ★
[show_popup_info] => 1
[show_channel_icons] => 1
[sortby_channum] => 1
[recorded_paging] => null
[genre_colors] => 1
[show_video_covers] => 1
[settings] => Array
(
[screens] => Array
(
[tv] => Array
(
[upcoming recordings] => Array
(
[title] => on
[channel] => on
[record date] => on
[length] => on
)
)
)
[last] => Array
(
[0] => mythweb
[1] => defaults
)
)
[backend] => Array
(
[localhost] => Array
(
[proto_version] => Array
(
[last_check_version] => 77
[last_check_time] => 1390078890
)
)
[timezone] => Array
(
[value] => Europe/London
[last_check_time] => 1460996269
)
[192.168.1.108] => Array
(
[proto_version] => Array
(
[last_check_version] => 88
[last_check_time] => 1461006319
)
)
)
[language] => English_GB
[date_statusbar] => %a %b %e, %Y, %I:%M %p
[date_scheduled] => %a %b %e, %Y (%I:%M %p)
[date_scheduled_popup] => %a %b %e, %Y
[date_recorded] => %a %b %e, %Y (%I:%M %p)
[date_search] => %a %b %e, %Y, %I:%M %p
[date_listing_key] => %a %b %e, %Y, %I:%M %p
[date_listing_jump] => %a %b %e, %Y
[date_channel_jump] => %a %b %e, %Y
[date_job_status] => %a %b %e, %Y, %I:%M %p
[time_format] => %I:%M %p
[tv] => Array
(
[last] => Array
(
[0] => upcoming
[1] => 11940
[2] => 1461259800
)
)
[list_time] => 1461344400
[recording_details] => Array
(
[show_Conflict] => 1
[show_PreviousRecording] => 1
[show_EarlierShowing] => 1
[show_CurrentRecording] => 1
[show_WillRecord] => 1
)
[file_url_override] =>
[scheduled_recordings] => Array
(
[disp_scheduled] => 1
[disp_duplicates] =>
[disp_deactivated] =>
[disp_conflicts] => 1
)
[scheduled_sortby] => Array
(
[0] => Array
(
[field] => title
[reverse] => 1
)
[1] => Array
(
[field] => airdate
[reverse] =>
)
)
[recorded_sortby] => Array
(
[0] => Array
(
[field] => title
[reverse] =>
)
[1] => Array
(
[field] => airdate
[reverse] => 1
)
)
)
==========================================================================
$_SERVER: Array
(
[REDIRECT_STATUS] => 200
[HTTP_HOST] => 192.168.1.108
[HTTP_USER_AGENT] => Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:45.0) Gecko/20100101 Firefox/45.0
[HTTP_ACCEPT] => text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
[HTTP_ACCEPT_LANGUAGE] => en-GB,en;q=0.5
[HTTP_ACCEPT_ENCODING] => gzip, deflate
[HTTP_REFERER] => http://192.168.1.108/mythweb/tv/upcoming?sortby=title
[HTTP_COOKIE] => mythweb_id=p15957ti4tnhhd9t7a2p0o74b5
[HTTP_CONNECTION] => keep-alive
[PATH] => /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
[SERVER_SIGNATURE] => <address>Apache/2.4.18 (Ubuntu) Server at 192.168.1.108 Port 80</address>
[SERVER_SOFTWARE] => Apache/2.4.18 (Ubuntu)
[SERVER_NAME] => 192.168.1.108
[SERVER_ADDR] => 192.168.1.108
[SERVER_PORT] => 80
[REMOTE_ADDR] => 192.168.1.116
[DOCUMENT_ROOT] => /var/www/html
[REQUEST_SCHEME] => http
[CONTEXT_PREFIX] =>
[CONTEXT_DOCUMENT_ROOT] => /var/www/html
[SERVER_ADMIN] => webmaster@localhost
[SCRIPT_FILENAME] => /var/www/html/mythweb/mythweb.php
[REMOTE_PORT] => 55554
[REDIRECT_URL] => /mythweb/tv/upcoming/11940/1461259800
[REDIRECT_QUERY_STRING] => never_record=yes
[GATEWAY_INTERFACE] => CGI/1.1
[SERVER_PROTOCOL] => HTTP/1.1
[REQUEST_METHOD] => GET
[QUERY_STRING] => never_record=yes
[REQUEST_URI] => /tv/upcoming/11940/1461259800?never_record=yes
[SCRIPT_NAME] => /mythweb/mythweb.php
[PATH_INFO] => /tv/upcoming/11940/1461259800
[PATH_TRANSLATED] => /var/www/html/tv/upcoming/11940/1461259800
[PHP_SELF] => /mythweb/mythweb.php/tv/upcoming/11940/1461259800
[REQUEST_TIME_FLOAT] => 1461012807.327
[REQUEST_TIME] => 1461012807
[STATUS] => 200
[URL] => /mythweb/tv/upcoming/11940/1461259800
[HTTP_X_FORWARDED_PROTO] =>
[HTTPS] =>
[HTTP_PORT] => 80
)
==========================================================================
$constant_list["user"]: Array
(
[ERROR] => 512
[E_ASSERT_ERROR] => 4096
[FATAL] => 256
[PHP_MIN_VERSION] => 5.3
[WARNING] => 1024
[WebDBSchemaVer] => 4
[dupsin_all] => 15
[dupsin_newepisodes] => 16
[dupsin_oldrecorded] => 2
[dupsin_recorded] => 1
[error_email] =>
[gb] => 1073741824
[hostname] => tv
[http_host] => 192.168.1.108
[kb] => 1024
[max_stars] => 4
[mb] => 1048576
[module] => tv
[modules_path] => /home/tv/MythSource/mythweb/modules
[num_time_slots] => 36
[prefer_channum] => 1
[rectype_always] => 4
[rectype_daily] => 2
[rectype_dontrec] => 8
[rectype_findone] => 6
[rectype_once] => 1
[rectype_override] => 7
[rectype_template] => 11
[rectype_weekly] => 5
[root] => /mythweb/
[root_auth_url] => http://192.168.1.108/mythweb/
[root_url] => http://192.168.1.108/mythweb/
[searchtype_keyword] => 3
[searchtype_manual] => 5
[searchtype_people] => 4
[searchtype_power] => 1
[searchtype_title] => 2
[skin] => default
[skin_img_url] => http://192.168.1.108/mythweb/skins/default/img/
[skin_url] => http://192.168.1.108/mythweb/skins/default/
[star_character] => ★
[stream_url] => http://192.168.1.108:80//mythweb/
[tb] => 1099511627776
[timeslot_blocks] => 3
[timeslot_size] => 600
[timeslotbar_skip] => 20
[tmpl] => default
[tmpl_dir] => modules/tv/tmpl/default/
)
Changed 9 years ago by
| Attachment: | 0001-unbreak-don-t-record.patch added |
|---|
comment:15 Changed 9 years ago by
Replying to jksjdevelop@…:
On the upcoming recordings page, 'Never Record' generates the following error, Don't Record works.
datetime: 2016-04-18 21:53:27 (BST) errornum: 256 error type: User Error error string: !!NoTrans: SQL Error: Field 'season' doesn't have a default value [#1364]!! filename: /home/tv/MythSource/mythweb/classes/Database/Query/mysqlicompat.php error line: 98
I could not reproduce that here. Can you verify this patch fixes it for you? attachment:0001-unbreak-don-t-record.patch
comment:16 Changed 9 years ago by
Responding to the above patch. I cannot reproduce the 'Never Record' issue today even using the same programmes. The patch seems to kill the following pages Listings,Upcomings,Recorded without generating any error messages. Of course I may have applied it wrongly but it looks ok to me. Best wait for some more evidence and somebody else to try the patch.
comment:17 Changed 9 years ago by
Correct, I can confirm the above comment:16
I've return to default 5.7 config and have done some testing/patching. See patch attached. This more or less solves things, but needs to be reviewed/tested.
Changed 9 years ago by
| Attachment: | mythweb-1.path added |
|---|
comment:18 Changed 9 years ago by
A quick patch using the above 'comment 17' seems to work ok. Will test more thoroughly tonight. Many thanks.
comment:21 Changed 9 years ago by
Thanks for the commit dekarl!
Still 2 issues left, which were included in patch. Let me explain in more details.
- Listings
SQL Error: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mythconverg.program.endtime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by [#1055]
There is an 'incomplete' GROUP BY in modules/tv/includes/programs.php. The question what is the added value of the GROUP BY. I've tried the queries with and without GROUP BY and get similar results. Therefor I suggested to remove the GROUP BY in modules/tv/includes/programs.php in function load_all_program_data.
- The second issue reveals if after fixing above, select a show and record it.
SQL Error: Incorrect datetime value: '00:00:00' for column 'next_record' at row 1
Changing 00:00:00 to NULL will solve this. Same applies for last_record and last_delete.
comment:26 Changed 9 years ago by
| Resolution: | → Fixed |
|---|---|
| Status: | infoneeded → closed |
The reported issues have been fixed. Please open a fresh ticket for new issues.
comment:27 Changed 9 years ago by
This correction is indeed the correct answer, at least that was the case in my installation. Thank you!
Replying to jksjdevelop@…:
Replying to jksjdevelop@…: Correction - this still fails with a date-time error when creating a recording - reduce the options to only those present in 5.5.
[mysqld] #bind-address=0.0.0.0 max_connections=100 sql_mode=NO_ENGINE_SUBSTITUTION
comment:28 Changed 8 years ago by
Gave up on Mythbuntu 0.28 for this error. Installed Xubuntu with Myth backend, database, web packages. Upon setup completion, got same error.
Tried this fix, didn't work for me.
[mysqld] #bind-address=0.0.0.0 max_connections=100 sql_mode=NO_ENGINE_SUBSTITUTION
Below however did work for me.
[mysqld] #bind-address=:: bind-address=0.0.0.0 max_connections=100 sql_mode=NO_ENGINE_SUBSTITUTION
Moving to 0.29