Modify

Opened 17 months ago

Closed 16 months ago

Last modified 8 months ago

#12713 closed Bug Report - General (Fixed)

Mythweb fails with mysql Server version: 5.7.11-0ubuntu5

Reported by: jksjdevelop@… Owned by: dekarl
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)

0001-unbreak-don-t-record.patch (1.9 KB) - added by dekarl 16 months ago.
mythweb-1.path (4.8 KB) - added by charles.bovy@… 16 months ago.

Download all attachments as: .zip

Change History (30)

comment:1 Changed 17 months ago by stuarta

  • Milestone changed from 0.28 to 0.29

Moving to 0.29

comment:2 follow-up: Changed 17 months ago by 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

comment:3 Changed 17 months ago by dekarl

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 17 months ago by jksjdevelop@…

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 in reply to: ↑ 2 ; follow-ups: Changed 17 months ago by jksjdevelop@…

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:6 Changed 17 months ago by stuarta

  • Milestone changed from 0.29 to 29.0

Milestone renamed

comment:7 Changed 17 months ago by Karl Dietz <dekarl@…>

In 659c0f39051ca901a526418d702a23afd175e8aa/mythweb:

use full group by for listing view

MySQL 5.7 enables ONLY_FULL_GROUP_BY by default

Refs #12713

comment:8 Changed 17 months ago by dekarl

  • Milestone changed from 29.0 to 0.28.1
  • Owner changed from kormoc to dekarl
  • Status changed from new to accepted

comment:9 in reply to: ↑ 5 Changed 17 months ago by dekarl

  • Status changed from accepted to 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:10 Changed 17 months ago by Karl Dietz <dekarl@…>

In 74e21c9a3bdb8d695373918f67b681e899ca8427/mythweb:

use full group by for listing view

MySQL 5.7 enables ONLY_FULL_GROUP_BY by default

Refs #12713

(cherry picked from commit 659c0f39051ca901a526418d702a23afd175e8aa)

comment:11 Changed 16 months ago by jksjdevelop@…

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] => &#9733;
    [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 16 months ago by jksjdevelop@…

Additional info:- the above is detected by the NO_ZERO_DATE option, clearing this and the page is ok.

comment:13 in reply to: ↑ 5 Changed 16 months ago by john.hoyt@…

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: Changed 16 months ago by 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

==========================================================================

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] => &#9733;
    [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] => &#9733;
    [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 16 months ago by dekarl

comment:15 in reply to: ↑ 14 Changed 16 months ago by dekarl

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 16 months ago by jksjdevelop@…

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 16 months ago by charles.bovy@…

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 16 months ago by charles.bovy@…

comment:18 Changed 16 months ago by jksjdevelop@…

A quick patch using the above 'comment 17' seems to work ok. Will test more thoroughly tonight. Many thanks.

comment:19 Changed 16 months ago by Karl Dietz <dekarl@…>

In 0ce44f7892249c953e15b45bfbe68387806ad8b3/mythweb:

unbreak "don't record" and getAspect()

Refs #12713

comment:20 Changed 16 months ago by Karl Dietz <dekarl@…>

In 82c458123fd243cef9ce874e955e1a72fcc6e4a4/mythweb:

unbreak "don't record" and getAspect()

Refs #12713

(cherry picked from commit 0ce44f7892249c953e15b45bfbe68387806ad8b3)

comment:21 Changed 16 months ago by charles.bovy@…

Thanks for the commit dekarl!

Still 2 issues left, which were included in patch. Let me explain in more details.

  1. 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.

  1. 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:22 Changed 16 months ago by Karl Dietz <dekarl@…>

In 55ade6f31ae227f8a8344192614eb52e9893b8aa/mythweb:

use NULL instead of ZERO datetime when saving to recording rules

for MySQL 5.7
Refs #12713

comment:23 Changed 16 months ago by Karl Dietz <dekarl@…>

In 0c07a6f3aae60ecc694966c32d0ca4757045edc5/mythweb:

use NULL instead of ZERO datetime when saving to recording rules

for MySQL 5.7
Refs #12713

(cherry picked from commit 55ade6f31ae227f8a8344192614eb52e9893b8aa)

comment:24 Changed 16 months ago by Karl Dietz <dekarl@…>

In 24e5d7616bb28007b0b99f3055385af4921aba81/mythweb:

remove grouping from SQL when loading programs

to work with the new defaults of MySQL 5.7.
Should reenable with e.g. ANY_VALUE or rewrite to use the Service API.

Refs #12713

comment:25 Changed 16 months ago by Karl Dietz <dekarl@…>

In f1cb29b0b20586c959c5c2d33ed2b3853dab3f12/mythweb:

remove grouping from SQL when loading programs

to work with the new defaults of MySQL 5.7.
Should reenable with e.g. ANY_VALUE or rewrite to use the Service API.

Refs #12713

(cherry picked from commit 24e5d7616bb28007b0b99f3055385af4921aba81)

comment:26 Changed 16 months ago by dekarl

  • Resolution set to Fixed
  • Status changed from infoneeded to closed

The reported issues have been fixed. Please open a fresh ticket for new issues.

comment:27 in reply to: ↑ 5 Changed 16 months ago by mythtv@…

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 months ago by dm@…

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

Add Comment

Modify Ticket

Action
as closed The owner will remain dekarl.
The resolution will be deleted. Next status will be 'new'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.