Mailing List Archive

backup script failing
i've just upgraded from 30 to 31 and i noticed that while my cron job for the mythconverg_backup.pl has been running, only a small file is being created (2kb, rather than around 150mb) and has been failing since January.

manually connecting with either root or mythtv db user via 'mysql -h sql -r USERNAME -p mythconverg' successfully connects and mythtv otherwise works correctly.
i used phpmyadmin to backup my db before the v31 upgrade.

unfortunately the problem remains after the upgrade which i hoped might recreate/fix what appears to be a DB issue, as part of the db schema update.

----
$ ./mythconverg_backup.pl --verbose
....
'/usr/bin/mysqldump' --defaults-extra-file='/tmp/lL5M1sscfa' --host='sql' --port='3306' --user='mythtv' --add-drop-table --add-locks --allow-keywords --complete-insert --extended-insert --lock-tables --no-create-db --quick --add-drop-table 'mythconverg' 2>&1 1>'/mnt/mythtv/dbbackups/mythconverg-1361-20200425130837.sql'

mysqldump exited with status: 2
mysqldump output:
mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'mythconverg' AND TABLE_NAME = 'archiveitems';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)
----

What could be the problem here, and more importantly whats the fix?
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org
Re: backup script failing [ In reply to ]
On Sat, 25 Apr 2020 at 13:25, Tim Draper <veehexx@zoho.com> wrote:
>
> i've just upgraded from 30 to 31 and i noticed that while my cron job for the mythconverg_backup.pl has been running, only a small file is being created (2kb, rather than around 150mb) and has been failing since January.
>
> manually connecting with either root or mythtv db user via 'mysql -h sql -r USERNAME -p mythconverg' successfully connects and mythtv otherwise works correctly.
> i used phpmyadmin to backup my db before the v31 upgrade.
>
> unfortunately the problem remains after the upgrade which i hoped might recreate/fix what appears to be a DB issue, as part of the db schema update.
>
> ----
> $ ./mythconverg_backup.pl --verbose
> ....
> '/usr/bin/mysqldump' --defaults-extra-file='/tmp/lL5M1sscfa' --host='sql' --port='3306' --user='mythtv' --add-drop-table --add-locks --allow-keywords --complete-insert --extended-insert --lock-tables --no-create-db --quick --add-drop-table 'mythconverg' 2>&1 1>'/mnt/mythtv/dbbackups/mythconverg-1361-20200425130837.sql'
>
> mysqldump exited with status: 2
> mysqldump output:
> mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'mythconverg' AND TABLE_NAME = 'archiveitems';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)
> ----
>
> What could be the problem here, and more importantly whats the fix?

See https://serverfault.com/questions/912162/mysqldump-throws-unknown-table-column-statistics-in-information-schema-1109

From that link:

This is due to a new flag that is enabled by default in mysqldump 8.
You can disable it by adding --column-statistics=0. The command will
be something like:

mysqldump --column-statistics=0 --host=<server> --user=<user>
--password=<password>

To disable column statistics by default, you can add

[mysqldump]
column-statistics=0

to a MySQL config file, such as /etc/my.cnf or ~/.my.cnf.

Cheers,
Nick
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org
Re: backup script failing [ In reply to ]
On 4/25/20 8:32 AM, Nick Morrott wrote:
> On Sat, 25 Apr 2020 at 13:25, Tim Draper <veehexx@zoho.com> wrote:
>>
>> i've just upgraded from 30 to 31 and i noticed that while my cron job for the mythconverg_backup.pl has been running, only a small file is being created (2kb, rather than around 150mb) and has been failing since January.
>>
>> manually connecting with either root or mythtv db user via 'mysql -h sql -r USERNAME -p mythconverg' successfully connects and mythtv otherwise works correctly.
>> i used phpmyadmin to backup my db before the v31 upgrade.
>>
>> unfortunately the problem remains after the upgrade which i hoped might recreate/fix what appears to be a DB issue, as part of the db schema update.
>>
>> ----
>> $ ./mythconverg_backup.pl --verbose
>> ....
>> '/usr/bin/mysqldump' --defaults-extra-file='/tmp/lL5M1sscfa' --host='sql' --port='3306' --user='mythtv' --add-drop-table --add-locks --allow-keywords --complete-insert --extended-insert --lock-tables --no-create-db --quick --add-drop-table 'mythconverg' 2>&1 1>'/mnt/mythtv/dbbackups/mythconverg-1361-20200425130837.sql'
>>
>> mysqldump exited with status: 2
>> mysqldump output:
>> mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'mythconverg' AND TABLE_NAME = 'archiveitems';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)
>> ----
>>
>> What could be the problem here, and more importantly whats the fix?
>
> See https://serverfault.com/questions/912162/mysqldump-throws-unknown-table-column-statistics-in-information-schema-1109
>
> From that link:
>
> This is due to a new flag that is enabled by default in mysqldump 8.
> You can disable it by adding --column-statistics=0. The command will
> be something like:
>
> mysqldump --column-statistics=0 --host=<server> --user=<user>
> --password=<password>
>
> To disable column statistics by default, you can add
>
> [mysqldump]
> column-statistics=0
>
> to a MySQL config file, such as /etc/my.cnf or ~/.my.cnf.
>
> Cheers,
> Nick

Interesting. On my fresh 20.04 (beta from last year) install, the database 'information_schema', has
the 'COLUMN_STATISTICS' table. I'm running MySQL 8.0.19.


--
Bill
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org
Re: backup script failing [ In reply to ]
---- On Sat, 25 Apr 2020 14:32:31 +0100 Nick Morrott <knowledgejunkie@gmail.com> wrote ----
> This is due to a new flag that is enabled by default in mysqldump 8.
> You can disable it by adding --column-statistics=0. The command will
> be something like:
>
> mysqldump --column-statistics=0 --host=<server> --user=<user>
> --password=<password>
>
> To disable column statistics by default, you can add
>
> [mysqldump]
> column-statistics=0
>
> to a MySQL config file, such as /etc/my.cnf or ~/.my.cnf.
>
> Cheers,
> Nick

any other solutions? either mythtv devs need to tweak the backup scripts (mythconverg_backup.pl) to exclude column stats in the mysqldump cmd (I've taken a quick look and cant see a way to add it myself), or theres something else i'm missing as the [mysqldump] option doesnt appear to work - even after restarting mariadb the same error occurs. It appears the official docker mariadb container uses /etc/mysql/my.cnf.

fwiw i did C&P the mysqldump string that mythconverg_backup.pl uses and added the --column-statistics=0 parameter which works.
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org
Re: backup script failing [ In reply to ]
On Tue, Apr 28, 2020 at 12:42 PM Tim Draper <veehexx@zoho.com> wrote:

> any other solutions?

Have you run mysql_upgrade every time you have
upgraded the mysql-server package? That is a
required step until 8.0.16 (when Oracle has
decided that most are not actually tall enough to
ride the ride and automatically upgrade the
information_schema and other system tables).
I am not sure if some distros automatically run
mysql_upgrade, but there are lots of good reasons
they should not (and is why Oracle's change is
also bad).
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org
Re: backup script failing [ In reply to ]
---- On Tue, 28 Apr 2020 19:23:26 +0100 Gary Buhrmaster <gary.buhrmaster@gmail.com> wrote ----
> On Tue, Apr 28, 2020 at 12:42 PM Tim Draper <veehexx@zoho.com> wrote:
>
> > any other solutions?
>
> Have you run mysql_upgrade every time you have
> upgraded the mysql-server package? That is a
> required step until 8.0.16 (when Oracle has
> decided that most are not actually tall enough to
> ride the ride and automatically upgrade the
> information_schema and other system tables).
> I am not sure if some distros automatically run
> mysql_upgrade, but there are lots of good reasons
> they should not (and is why Oracle's change is
> also bad).
>

well this was a fresh deployment of the official mariadb container via docker, with a mythtv db restore over the top (db possibly created from around mythtv28 era) so i'd expect not to have to worry about the mysql_upgrade in this case. But no, i dont run this when i've upgraded in the past. figured docker/distro would handle that or atleast prompt it needs doing.

_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org