Mailing List Archive

Hoi John,

Wednesday, January 24, 2024, 11:45:07 PM, you wrote:

> While updating to Master and 22.04 I installed MariaDB 10.6.12. It
> seems to work but my cutting scripts don't. They read 'offset' values
> from the recordedseek table, and I understand that 'offset' is now an
> SQL reserved word.

> Most of what I've read about MythTV and 10.6 has been about TimeZone
> stuff, and the Recordedseek wiki still talks about offset.

> Perhaps fortunately the box hadn't made many recordings.

> Is there a simple fix?

> TIA

> John


Doesn't enclosing fieldnames in `` work? It's what phpMyAdmin does.




Tot mails,
Hika mailto:hikavdh@gmail.com

"Zonder hoop kun je niet leven
Zonder leven is er geen hoop
Het eeuwige dilemma
Zeker als je hoop moet vernietigen om te kunnen overleven!"

De lerende Mens

_______________________________________________
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

On Wed, Jan 24, 2024 at 10:46?PM John Pilkington <johnpilk222@gmail.com> wrote:

> Is there a simple fix?

Escape quote (backtick) the (now reserved) word `offset`.

That was what the MythTV code itself did
to address the update.
_______________________________________________
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

On 24/01/2024 23:22, Gary Buhrmaster wrote:
> On Wed, Jan 24, 2024 at 10:46?PM John Pilkington <johnpilk222@gmail.com> wrote:
>
>> Is there a simple fix?
>
> Escape quote (backtick) the (now reserved) word `offset`.
>
> That was what the MythTV code itself did
> to address the update.

Thanks, Gary and Hika.

I haven't yet got this working with MariaDB 10.6.12, with various random
changes in the field sequence and capitalisation. Your suggested
changes in Fedora 38 (10.5) made it fail, but it still worked in el7
(!). As Gary mentioned when this first came up, it's a precomputed
query. Issue 373

Here's the current el7 trial version, with the suggested backticks.

mariadb-server-5.5.68-1.el7.x86_64

It works, at least with DVBradio.

John


{{{
lag=4 # In frames. Best value might depend on recording source,
MythTV version and seektable history.
scope=2000 # Sometimes h264 keyframes in the wild are much more
widely spaced than expected.
# This might only have been true for 'rebuilt'
seektables, but the large value should do no harm.

for frame in $(cat revedlist$$)
do
i=$((${frame} - ${lag}))
j=$((${i}))
k=$((${i} + ${scope}))
echo "select `offset`, mark from recordedseek
where chanid=$chanid and starttime='$starttime' and type=9
and mark >= ${j} and mark < ${k} order by `offset` limit 3 ;" |
mysql -N -u${DBUserName} -p${DBPassword} -h${DBLocalHostName}
${DBName}
done > tmp0$$

echo "Full results of DB read:"
cat tmp0$$
cat tmp0$$ | sed -n '1,${p;n;n;}' > tmp1$$ # select lines 1,4,7...
cat tmp0$$ | sed -n '2,${p;n;n;}' > tmp2$$ # 2,5,8...
cat tmp0$$ | sed -n '3,${p;n;n;}' > tmp3$$
rm tmp0$$

echo

}}}
_______________________________________________
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

On 25/01/2024 16:14, John Pilkington wrote:
> On 24/01/2024 23:22, Gary Buhrmaster wrote:
>> On Wed, Jan 24, 2024 at 10:46?PM John Pilkington
>> <johnpilk222@gmail.com> wrote:
>>
>>> Is there a simple fix?
>>
>> Escape quote (backtick) the (now reserved) word `offset`.
>>
>> That was what the MythTV code itself did
>> to address the update.
>
> Thanks, Gary and Hika.
>
> I haven't yet got this working with MariaDB 10.6.12, with various random
> changes in the field sequence and capitalisation.  Your suggested
> changes in Fedora 38 (10.5) made it fail, but it still worked in el7
> (!).  As Gary mentioned when this first came up, it's a precomputed
> query.  Issue 373
>
> Here's the current el7 trial version, with the suggested backticks.
>
> mariadb-server-5.5.68-1.el7.x86_64
>
> It works, at least with DVBradio.
>
> John
>
>
> {{{
> lag=4        #  In frames.  Best value might depend on recording source,
> MythTV version and seektable history.
> scope=2000   #  Sometimes h264 keyframes in the wild are much more
> widely spaced than expected.
>              #  This might only have been true for 'rebuilt'
> seektables, but the large value should do no harm.
>
> for frame in $(cat revedlist$$)
> do
>     i=$((${frame} - ${lag}))
>     j=$((${i}))
>     k=$((${i} + ${scope}))
>     echo  "select `offset`, mark from recordedseek
>     where chanid=$chanid and starttime='$starttime' and type=9
>     and mark >= ${j} and mark < ${k}  order by `offset` limit 3 ;" |
>     mysql -N -u${DBUserName} -p${DBPassword} -h${DBLocalHostName}
> ${DBName}
> done > tmp0$$
>
> echo "Full results of DB read:"
> cat tmp0$$
> cat tmp0$$  | sed -n '1,${p;n;n;}' > tmp1$$  # select lines 1,4,7...
> cat tmp0$$  | sed -n '2,${p;n;n;}' > tmp2$$  # 2,5,8...
> cat tmp0$$  | sed -n '3,${p;n;n;}' > tmp3$$
> rm tmp0$$
>
> echo
>
> }}}
{{{
Looking mor hopeful here, running the mysql commands in sequence (no
pipe and with a smaller effective value of the 'scope')


john@Matts-HP-Compaq-dc5800-Microtower:~/Bashtests$ mysql -N -umythtv
-pmythtv -hlocalhost mythconverg
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 408
Server version: 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.

MariaDB [mythconverg]> select `offset` , mark from recordedseek where
chanid=10002 and starttime=20190204190500 and type=9 and mark >= 8244
and mark < 8444 order by `offset` limit 3 ;
+-----------+------+
| 193649588 | 8265 |
| 194136320 | 8289 |
| 194649184 | 8313 |
+-----------+------+
3 rows in set (0.001 sec)

MariaDB [mythconverg]> \q
Bye

}}}
_______________________________________________
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

On Thu, Jan 25, 2024 at 9:40?PM John Pilkington <johnpilk222@gmail.com>
wrote:

> On 25/01/2024 16:14, John Pilkington wrote:
> > On 24/01/2024 23:22, Gary Buhrmaster wrote:
> >> On Wed, Jan 24, 2024 at 10:46?PM John Pilkington
> >> <johnpilk222@gmail.com> wrote:
> >>
> >>> Is there a simple fix?
> >>
> >> Escape quote (backtick) the (now reserved) word `offset`.
> >>
> >> That was what the MythTV code itself did
> >> to address the update.
> >
> > Thanks, Gary and Hika.
> >
> > I haven't yet got this working with MariaDB 10.6.12, with various random
> > changes in the field sequence and capitalisation. Your suggested
> > changes in Fedora 38 (10.5) made it fail, but it still worked in el7
> > (!). As Gary mentioned when this first came up, it's a precomputed
> > query. Issue 373
> >
> > Here's the current el7 trial version, with the suggested backticks.
> >
> > mariadb-server-5.5.68-1.el7.x86_64
> >
> > It works, at least with DVBradio.
> >
> > John
> >
> >
> > {{{
> > lag=4 # In frames. Best value might depend on recording source,
> > MythTV version and seektable history.
> > scope=2000 # Sometimes h264 keyframes in the wild are much more
> > widely spaced than expected.
> > # This might only have been true for 'rebuilt'
> > seektables, but the large value should do no harm.
> >
> > for frame in $(cat revedlist$$)
> > do
> > i=$((${frame} - ${lag}))
> > j=$((${i}))
> > k=$((${i} + ${scope}))
> > echo "select `offset`, mark from recordedseek
> > where chanid=$chanid and starttime='$starttime' and type=9
> > and mark >= ${j} and mark < ${k} order by `offset` limit 3 ;" |
> > mysql -N -u${DBUserName} -p${DBPassword} -h${DBLocalHostName}
> > ${DBName}
> > done > tmp0$$
> >
> > echo "Full results of DB read:"
> > cat tmp0$$
> > cat tmp0$$ | sed -n '1,${p;n;n;}' > tmp1$$ # select lines 1,4,7...
> > cat tmp0$$ | sed -n '2,${p;n;n;}' > tmp2$$ # 2,5,8...
> > cat tmp0$$ | sed -n '3,${p;n;n;}' > tmp3$$
> > rm tmp0$$
> >
> > echo
> >
> > }}}
> {{{
> Looking mor hopeful here, running the mysql commands in sequence (no
> pipe and with a smaller effective value of the 'scope')
>
>
> john@Matts-HP-Compaq-dc5800-Microtower:~/Bashtests$ mysql -N -umythtv
> -pmythtv -hlocalhost mythconverg
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
>
> Welcome to the MariaDB monitor. Commands end with ; or \g.
> Your MariaDB connection id is 408
> Server version: 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04
>
> Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
>
> Type 'help;' or '\h' for help. Type '\c' to clear the current input
> statement.
>
> MariaDB [mythconverg]> select `offset` , mark from recordedseek where
> chanid=10002 and starttime=20190204190500 and type=9 and mark >= 8244
> and mark < 8444 order by `offset` limit 3 ;
> +-----------+------+
> | 193649588 | 8265 |
> | 194136320 | 8289 |
> | 194649184 | 8313 |
> +-----------+------+
> 3 rows in set (0.001 sec)
>
> MariaDB [mythconverg]> \q
> Bye
>
> }}}
>
>
And when you put single quotes around the *starttime* value, does above
query work as well?

Roland

On 25/01/2024 21:27, Roland Ernst wrote:
>
>
> On Thu, Jan 25, 2024 at 9:40?PM John Pilkington <johnpilk222@gmail.com
> <mailto:johnpilk222@gmail.com>> wrote:
>
> On 25/01/2024 16:14, John Pilkington wrote:
> > On 24/01/2024 23:22, Gary Buhrmaster wrote:
> >> On Wed, Jan 24, 2024 at 10:46?PM John Pilkington
> >> <johnpilk222@gmail.com <mailto:johnpilk222@gmail.com>> wrote:
> >>
> >>> Is there a simple fix?
> >>
> >> Escape quote (backtick) the (now reserved) word `offset`.
> >>
> >> That was what the MythTV code itself did
> >> to address the update.
> >
> > Thanks, Gary and Hika.
> >
> > I haven't yet got this working with MariaDB 10.6.12, with various
> random
> > changes in the field sequence and capitalisation.  Your suggested
> > changes in Fedora 38 (10.5) made it fail, but it still worked in el7
> > (!).  As Gary mentioned when this first came up, it's a precomputed
> > query.  Issue 373
> >
> > Here's the current el7 trial version, with the suggested backticks.
> >
> > mariadb-server-5.5.68-1.el7.x86_64
> >
> > It works, at least with DVBradio.
> >
> > John
> >
> >
> > {{{
> > lag=4        #  In frames.  Best value might depend on recording
> source,
> > MythTV version and seektable history.
> > scope=2000   #  Sometimes h264 keyframes in the wild are much more
> > widely spaced than expected.
> >               #  This might only have been true for 'rebuilt'
> > seektables, but the large value should do no harm.
> >
> > for frame in $(cat revedlist$$)
> > do
> >      i=$((${frame} - ${lag}))
> >      j=$((${i}))
> >      k=$((${i} + ${scope}))
> >      echo  "select `offset`, mark from recordedseek
> >      where chanid=$chanid and starttime='$starttime' and type=9
> >      and mark >= ${j} and mark < ${k}  order by `offset` limit 3 ;" |
> >      mysql -N -u${DBUserName} -p${DBPassword} -h${DBLocalHostName}
> > ${DBName}
> > done > tmp0$$
> >
> > echo "Full results of DB read:"
> > cat tmp0$$
> > cat tmp0$$  | sed -n '1,${p;n;n;}' > tmp1$$  # select lines 1,4,7...
> > cat tmp0$$  | sed -n '2,${p;n;n;}' > tmp2$$  # 2,5,8...
> > cat tmp0$$  | sed -n '3,${p;n;n;}' > tmp3$$
> > rm tmp0$$
> >
> > echo
> >
> > }}}
> {{{
> Looking mor hopeful here, running the mysql commands in sequence (no
> pipe and with a smaller  effective value of the 'scope')
>
>
> john@Matts-HP-Compaq-dc5800-Microtower:~/Bashtests$ mysql -N -umythtv
> -pmythtv -hlocalhost mythconverg
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
>
> Welcome to the MariaDB monitor.  Commands end with ; or \g.
> Your MariaDB connection id is 408
> Server version: 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04
>
> Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
>
> Type 'help;' or '\h' for help. Type '\c' to clear the current input
> statement.
>
> MariaDB [mythconverg]> select `offset` , mark  from recordedseek where
> chanid=10002 and starttime=20190204190500 and type=9 and mark >= 8244
> and mark < 8444 order by `offset` limit 3 ;
> +-----------+------+
> | 193649588 | 8265 |
> | 194136320 | 8289 |
> | 194649184 | 8313 |
> +-----------+------+
> 3 rows in set (0.001 sec)
>
> MariaDB [mythconverg]> \q
> Bye
>
> }}}
>
>
> And when you put single quotes around the /starttime/ value, does above
> query work as well?
>
> Roland
{{{
Hi Roland: Yes, if this is exactly what you meant. The byte offsets
are different because the recording has just been processed to my
'leanfront compatible' 3-stream format. Doing that doesn't involve
cutting and doesn't require byte-offset values.

ionice -c3 mythffmpeg -hide_banner -ignore_unknown -fflags +genpts -i
infile.ts -vcodec copy -acodec copy -scodec copy -avoid_negative_ts
make_zero -f mpegts outfile.ts

John

MariaDB [mythconverg]> select `offset` , mark from recordedseek where
chanid=10002 and starttime=20190204190500 and type=9 and mark >= 8244
and mark < 8444 order by mark limit 3 ;
+-----------+------+
| 152335460 | 8265 |
| 152722176 | 8289 |
| 153106824 | 8313 |
+-----------+------+
3 rows in set (0.044 sec)

MariaDB [mythconverg]> select `offset` , mark from recordedseek where
chanid=10002 and starttime='20190204190500' and type=9 and mark >= 8244
and mark < 8444 order by mark limit 3 ;
+-----------+------+
| 152335460 | 8265 |
| 152722176 | 8289 |
| 153106824 | 8313 |
+-----------+------+
3 rows in set (0.000 sec)

}}}
_______________________________________________
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

On 26/01/2024 00:01, John Pilkington wrote:

{{{
lag=4 # In frames. Best value might depend on recording source,
MythTV version and seektable history.
scope=2000 # Sometimes h264 keyframes in the wild are much more
widely spaced than expected.
# This might only have been true for 'rebuilt'
seektables, but the large value should do no harm.

for frame in $(cat revedlist$$)
do
i=$((${frame} - ${lag}))
j=$((${i}))
k=$((${i} + ${scope}))
echo "select `offset`, mark from recordedseek
where chanid=$chanid and starttime='$starttime' and type=9
and mark >= ${j} and mark < ${k} order by `offset` limit 3 ;" |
mysql -N -u${DBUserName} -p${DBPassword} -h${DBLocalHostName}
${DBName}
done > tmp0$$

echo "Full results of DB read:"
cat tmp0$$
cat tmp0$$ | sed -n '1,${p;n;n;}' > tmp1$$ # select lines 1,4,7...
cat tmp0$$ | sed -n '2,${p;n;n;}' > tmp2$$ # 2,5,8...
cat tmp0$$ | sed -n '3,${p;n;n;}' > tmp3$$
rm tmp0$$

echo


That script snippet aims to find the byte-offsets of keyframes near to
frame numbers read from the cutlist.

MariaDB 10.6.12 in ubuntu 22.04 failed with it, although I have been
using similar code for years with earlier versions of mysql.

It's working now after some changes: essentially using " select * "
and "order by mark" in the sql query, so avoiding mentioning the newly
reserved sql word "offset" altogether.

echo "select * from recordedseek
where chanid=$chanid and starttime='$starttime' and type=9
and mark >= ${j} and mark < ${k} order by mark limit 3 ;"
| ${mysqlconnect}

That changes the content of tmp0$$, the queries output file, and the
details of its unpacking. I have related scripts that have been
prettified and intend to do the same here.

}}}

John P

_______________________________________________
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

On Mon, Jan 29, 2024 at 3:09?PM John Pilkington <johnpilk222@gmail.com>
wrote:

> On 26/01/2024 00:01, John Pilkington wrote:
>
> {{{
> lag=4 # In frames. Best value might depend on recording source,
> MythTV version and seektable history.
> scope=2000 # Sometimes h264 keyframes in the wild are much more
> widely spaced than expected.
> # This might only have been true for 'rebuilt'
> seektables, but the large value should do no harm.
>
> for frame in $(cat revedlist$$)
> do
> i=$((${frame} - ${lag}))
> j=$((${i}))
> k=$((${i} + ${scope}))
> echo "select `offset`, mark from recordedseek
> where chanid=$chanid and starttime='$starttime' and type=9
> and mark >= ${j} and mark < ${k} order by `offset` limit 3 ;" |
> mysql -N -u${DBUserName} -p${DBPassword} -h${DBLocalHostName}
> ${DBName}
> done > tmp0$$
>
> echo "Full results of DB read:"
> cat tmp0$$
> cat tmp0$$ | sed -n '1,${p;n;n;}' > tmp1$$ # select lines 1,4,7...
> cat tmp0$$ | sed -n '2,${p;n;n;}' > tmp2$$ # 2,5,8...
> cat tmp0$$ | sed -n '3,${p;n;n;}' > tmp3$$
> rm tmp0$$
>
> echo
>
>
> That script snippet aims to find the byte-offsets of keyframes near to
> frame numbers read from the cutlist.
>
> MariaDB 10.6.12 in ubuntu 22.04 failed with it, although I have been
> using similar code for years with earlier versions of mysql.
>
> It's working now after some changes: essentially using " select * "
> and "order by mark" in the sql query, so avoiding mentioning the newly
> reserved sql word "offset" altogether.
>
> echo "select * from recordedseek
> where chanid=$chanid and starttime='$starttime' and type=9
> and mark >= ${j} and mark < ${k} order by mark limit 3 ;"
> | ${mysqlconnect}
>
> That changes the content of tmp0$$, the queries output file, and the
> details of its unpacking. I have related scripts that have been
> prettified and intend to do the same here.
>
> }}}
>
> John P
>
>
You need to escape the backticks in the mysql command:
*echo "select \`offset\`, mark from recordedseek where.... "*
otherwise bash thinks it is a command. Try on the command line
*$ echo "`pwd`" *
and
*$ echo "\`pwd\`"*


Roland

Hoi Roland,

Monday, January 29, 2024, 4:29:14 PM, you wrote:

> You need to escape the backticks in the mysql command:

> echo "select \`offset\`, mark from recordedseek where.... "
> otherwise bash thinks it is a command. Try on the command line
> $ echo "`pwd`" 
> and
> $ echo "\`pwd\`"


> Roland

Ah yes, interesting. Like you have to double escape when you do not
want bash to act on an escape!


Tot mails,
Hika mailto:hikavdh@gmail.com

"Zonder hoop kun je niet leven
Zonder leven is er geen hoop
Het eeuwige dilemma
Zeker als je hoop moet vernietigen om te kunnen overleven!"

De lerende Mens

_______________________________________________
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