SQL errors in Mythweb

For discussion related to MythTV which doesn't belong in another forum.

Moderator: Forum Moderators

Post Reply
Tonyr63
Junior
Posts: 60
Joined: Thu Sep 03, 2015 2:14 pm
Location: South Australia
Ireland

SQL errors in Mythweb

Post by Tonyr63 »

MythWeb Errors

Monday, 4 February 2019
4:34 PM

Selecting Recording in MythWeb returns:

datetime: 2019-02-04 15:47:22 (ACDT)
errornum: 256
error type: User Error
error string: !!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]!!
filename: /usr/share/mythtv/mythweb/classes/Database/Query/mysqlicompat.php
error line: 98

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

Backtrace:

file: /usr/share/mythtv/mythweb/classes/Database/Query/mysqlicompat.php
line: 98
class:
function: trigger_error
type:
args: Array
(
[0] => 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]
[1] => 256
)

file: /usr/share/mythtv/mythweb/classes/Database.php
line: 261
class: Database_Query_mysqlicompat
function: execute
type: ->
args: Array
(
[0] => Array
(
[0] => 3002
[1] => 1545818640
)

)

file: /usr/share/mythtv/mythweb/modules/tv/classes/Program.php
line: 789
class: Database
function: query
type: ->
args: Array
(
[0] => SELECT recordedmarkup.type,
recordedmarkup.data
FROM recordedmarkup
WHERE recordedmarkup.chanid = ?
AND recordedmarkup.starttime = FROM_UNIXTIME(?)
AND recordedmarkup.type IN (10, 11, 12, 13, 14)
GROUP BY recordedmarkup.type
ORDER BY SUM((SELECT IFNULL(rm.mark, recordedmarkup.mark)
FROM recordedmarkup AS rm
WHERE rm.chanid = recordedmarkup.chanid
AND rm.starttime = recordedmarkup.starttime
AND rm.type IN (10, 11, 12, 13, 14)
AND rm.mark > recordedmarkup.mark
ORDER BY rm.mark ASC LIMIT 1)- recordedmarkup.mark) DESC
LIMIT 1
[1] => 3002
[2] => 1545818640
)

file: /usr/share/mythtv/mythweb/modules/tv/tmpl/default/recorded.php
line: 150
class: Program
function: getAspect
type: ->
args: Array ( )
file: /usr/share/mythtv/mythweb/modules/tv/recorded.php
line: 193
class:
function: require_once
type:
args: Array
(
[0] => /usr/share/mythtv/mythweb/modules/tv/tmpl/default/recorded.php
)

file: /usr/share/mythtv/mythweb/modules/tv/handler.php
line: 82
class:
function: require_once
type:
args: Array
(
[0] => /usr/share/mythtv/mythweb/modules/tv/recorded.php
)

file: /usr/share/mythtv/mythweb/mythweb.php
line: 35
class:
function: require_once
type:
args: Array
(
[0] => /usr/share/mythtv/mythweb/modules/tv/handler.php
)


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

$_GET: Array
(
[reverse] =>
[sortby] =>
)

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

$_SESSION: Array
(
[cache_engine] => Cache_Null
[stream] => Array
(
[include_user_and_password] =>
)

[prefer_channum] => 1
[recorded_pixmaps] => 1
[guide_favonly] =>
[timeslot_size] => 300
[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] =>
[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] => tv
[1] => session
)

)

[backend] => Array
(
[timezone] => Array
(
[value] => Australia/Adelaide
[last_check_time] => 1549256530
)

[10.1.1.32] => Array
(
[proto_version] => Array
(
[last_check_version] => 88
[last_check_time] => 1549257109
)

)

)

[language] => English
[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] => recorded
)

)

[list_time] => 1549256400
[scheduled_recordings] => Array
(
[disp_scheduled] => 1
[disp_duplicates] =>
[disp_deactivated] =>
[disp_conflicts] => 1
)

[] => Array ( )
[schedules_sortby] => Array
(
[0] => Array
(
[field] => airdate
[reverse] =>
)

[1] => Array
(
[field] => title
[reverse] =>
)

)

[recorded_sortby] => Array
(
[0] => Array
(
[field] => airdate
[reverse] => 1
)

[1] => Array
(
[field] => title
[reverse] =>
)

)

)

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

$_SERVER: Array
(
[REDIRECT_STATUS] => 200
[HTTP_HOST] => mediacentre
[HTTP_CONNECTION] => keep-alive
[HTTP_UPGRADE_INSECURE_REQUESTS] => 1
[HTTP_DNT] => 1
[HTTP_USER_AGENT] => Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36
[HTTP_ACCEPT] => text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8
[HTTP_REFERER] => http://mediacentre/mythweb/tv/schedules
[HTTP_ACCEPT_ENCODING] => gzip, deflate
[HTTP_ACCEPT_LANGUAGE] => en-US,en;q=0.9
[HTTP_COOKIE] => mythweb_id=224sf9h91v5uhuqkehd587e1o3
[PATH] => /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
[SERVER_SIGNATURE] => <address>Apache/2.4.18 (Ubuntu) Server at mediacentre Port 80</address>

[SERVER_SOFTWARE] => Apache/2.4.18 (Ubuntu)
[SERVER_NAME] => mediacentre
[SERVER_ADDR] => 10.1.1.32
[SERVER_PORT] => 80
[REMOTE_ADDR] => 10.1.1.25
[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] => 53514
[REDIRECT_URL] => /mythweb/tv/recorded
[GATEWAY_INTERFACE] => CGI/1.1
[SERVER_PROTOCOL] => HTTP/1.1
[REQUEST_METHOD] => GET
[QUERY_STRING] =>
[REQUEST_URI] => /tv/recorded
[SCRIPT_NAME] => /mythweb/mythweb.php
[PATH_INFO] => /tv/recorded
[PATH_TRANSLATED] => /var/www/html/tv/recorded
[PHP_SELF] => /mythweb/mythweb.php/tv/recorded
[REQUEST_TIME_FLOAT] => 1549257442.399
[REQUEST_TIME] => 1549257442
[STATUS] => 200
[URL] => /mythweb/tv/recorded
[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
[disk_size] => 488205459456
[disk_used] => 110705270784
[dupsin_all] => 15
[dupsin_newepisodes] => 16
[dupsin_oldrecorded] => 2
[dupsin_recorded] => 1
[error_email] =>
[gb] => 1073741824
[hostname] => MediaCentre
[http_host] => mediacentre
[kb] => 1024
[max_stars] => 4
[mb] => 1048576
[module] => tv
[modules_path] => /usr/share/mythtv/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://mediacentre/mythweb/
[root_url] => http://mediacentre/mythweb/
[searchtype_keyword] => 3
[searchtype_manual] => 5
[searchtype_people] => 4
[searchtype_power] => 1
[searchtype_title] => 2
[skin] => default
[skin_img_url] => http://mediacentre/mythweb/skins/default/img/
[skin_url] => http://mediacentre/mythweb/skins/default/
[star_character] => &#9733;
[stream_url] => http://mediacentre:80//mythweb/
[tb] => 1099511627776
[timeslot_blocks] => 3
[timeslot_size] => 300
[timeslotbar_skip] => 20
[tmpl] => default
[tmpl_dir] => modules/tv/tmpl/default/
)

Selecting Listing produces a similar text dump.

How do I troubleshoot this?
Gribnif
Junior
Posts: 88
Joined: Wed May 14, 2014 12:46 pm
United States of America

Re: SQL errors in Mythweb

Post by Gribnif »

Hello,

This is due to a recent(ish) change in the MySQL defaults, which cause it to be more strict than it used to be. Short of fixing the SQL code in Mythweb, the easier thing to do is to add this line to /etc/my.cnf:

Code: Select all

sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
This effectively removes the ONLY_FULL_GROUP_BY mode, which is what causes the error to be flagged rather than ignored.

The other thing you could do is to use MariaDB, which is a drop-in replacement for MySQL, and does not include this mode by default.
User avatar
bill6502
Developer
Posts: 2307
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: SQL errors in Mythweb

Post by bill6502 »

@Tonyr63

See if the fix here: https://github.com/MythTV/mythweb/commit/f02e5e2
in your classes/Database.php file.

Mention your MythTV and mythweb versions from your repo please. For *buntu,
I believe this will get them: dpkg -l | egrep "mythtv|mythweb"
Tonyr63
Junior
Posts: 60
Joined: Thu Sep 03, 2015 2:14 pm
Location: South Australia
Ireland

Re: SQL errors in Mythweb

Post by Tonyr63 »

I tried using sudo gedit /etc/my.cnf and was not allowed to save getting the following:

** (gedit:27002): WARNING **: Set document metadata failed: Setting attribute metadata::gedit-encoding not supported

What tool should I use to write thisw line. I note that the my.cnf was empty so was this file created?
Tonyr63
Junior
Posts: 60
Joined: Thu Sep 03, 2015 2:14 pm
Location: South Australia
Ireland

Re: SQL errors in Mythweb

Post by Tonyr63 »

Hi Gribnif

I checked my etc folder and despite the gedit errors it did complete the file save and the file contains the text you provided. On restarting the Myth server and firefox I still have the same problem.

Noticing that many files in the etc folder have extension of .conf rather than cnf so I renamed the file in case you made an error in your advice and recycled the machine and retested. Unfortunately the problem remains.

Do you know why the script you provided may not work in my environment which is:

Ubuntu 16.04 LTS
MythTv 0.28-2
Mythweb is the most recent i could find installed 4 days ago.

The dpkg command i was advise to run in an earlier response returned nothing.
Gribnif
Junior
Posts: 88
Joined: Wed May 14, 2014 12:46 pm
United States of America

Re: SQL errors in Mythweb

Post by Gribnif »

Hi Tony,

I don't normally use Ubuntu, but I looked in a list of files in the package you are likely using, and I think you'd want to add the line to:

Code: Select all

/etc/mysql/mysql.conf.d/mysqld.cnf
in the

Code: Select all

[mysqld]
section. Remove the edits you made previously.
Tonyr63
Junior
Posts: 60
Joined: Thu Sep 03, 2015 2:14 pm
Location: South Australia
Ireland

Re: SQL errors in Mythweb

Post by Tonyr63 »

Hi

I applied the changes as suggested which restored the showing of Listing and recorded programs in Mythweb however when I tried to schedule a recording and went to commit I got the following:

datetime: 2019-02-07 18:08:36 (ACDT)
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: /usr/share/mythtv/mythweb/classes/Database/Query/mysqlicompat.php
error line: 98

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

Backtrace:

file: /usr/share/mythtv/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: /usr/share/mythtv/mythweb/classes/Database.php
line: 261
class: Database_Query_mysqlicompat
\
Perhaps there are further changes to be made,
User avatar
paulh
Developer
Posts: 909
Joined: Thu Feb 06, 2014 6:09 pm
Great Britain

Re: SQL errors in Mythweb

Post by paulh »

I think your problems would magically go away if you used a more up to date version of MythTV and MythWeb :roll:
Tonyr63
Junior
Posts: 60
Joined: Thu Sep 03, 2015 2:14 pm
Location: South Australia
Ireland

Re: SQL errors in Mythweb

Post by Tonyr63 »

Hi Paul

As far as I know I have the latest version of Mythweb. What is the best way of checking? Can you provide specific version numbers you are recommending?
User avatar
Steve Goodey
Moderator
Posts: 219
Joined: Fri Feb 07, 2014 6:30 pm
Location: Colchester, England
Great Britain

Re: SQL errors in Mythweb

Post by Steve Goodey »

Might be different versions in Ireland. :twisted:
Don't forget the Wiki.
User avatar
paulh
Developer
Posts: 909
Joined: Thu Feb 06, 2014 6:09 pm
Great Britain

Re: SQL errors in Mythweb

Post by paulh »

The fix for the group by problem was committed Oct 2016 :-
https://github.com/MythTV/mythweb/commit/f02e5e2

The fix for the 00:00:00 date was committed Apr 2016 here :-
https://github.com/MythTV/mythweb/commit/55ade6f31a

So you need to upgrade to MythTV 29.0 or 30.0 and same for MythWeb. 0.28 is just too old to have the fixes unfortunately.
Tonyr63
Junior
Posts: 60
Joined: Thu Sep 03, 2015 2:14 pm
Location: South Australia
Ireland

Re: SQL errors in Mythweb

Post by Tonyr63 »

Hello

Can upgrade from 0.28 to 30.0 or do I need to do it in two steps?
If I upgrade will mythweb then work or do I need to apply a post upgrade remediation?
Where do I check the version of Mythweb i have installed and how do I avoid accidentally install an earlier version Mythweb?
Mythweb was installed just 2 weeks ago so why is it not the current version. Did APT Get Install know to install the version that matched my current version of MythTv as I never specified any such parameter?

Reading the wiki referenced above I was confused by:
For a general installation guide see Build from Source and navigate to the Install MythWeb section, otherwise see the distribution specific pages.
What are "distribution specific pages"

Is there not a up to date package file that avoids the need to check out of GIT and building using Linux commands?
This can be tricky for users not very familiar with Linux commands and variances in distributions can make using such commands for users without these skills onerous?
User avatar
paulh
Developer
Posts: 909
Joined: Thu Feb 06, 2014 6:09 pm
Great Britain

Re: SQL errors in Mythweb

Post by paulh »

You can go straight from 0.28 to 30.0 that shouldn't be a problem.

I don't use MythWeb but I believe it's working fine in 30.0 (just tried it and everything appears to be working).

You appear to be using Ubuntu 16.04 LTS which is fine but out of the box Ubuntu only apply bug fixes to apps like MythTV so you don't get major version upgrades which is why you are stuck on 0.28. The nice people at MythBuntu do have some PPA's that allow newer versions of MythTV to be installed but I believe they only support the last LTS version which is 18.04 and the last regular Ubuntu version currently 18.10.

I would update to 18.04 LTS which will get you to 29.0 I think and if you are feeling brave enable the 30.0 PPA which will allow you to move to MythTV 30.0.

Search this forum for MythBuntu PPA should provide more information on the PPA's.
Tonyr63
Junior
Posts: 60
Joined: Thu Sep 03, 2015 2:14 pm
Location: South Australia
Ireland

Re: SQL errors in Mythweb

Post by Tonyr63 »

Hi
I will capture an image of my current environment 1st and upgrade to 18.04 when I get some free time.
Thanks for your assistance which has been very helpful and most appreciated.
Post Reply