[mythtv-users] DB 'offset' problem with MariaDB in ubuntu 22.04

[Roland Ernst]

On Thu, Jan 25, 2024 at 9:40 PM John Pilkington <johnpilk222 at 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 at 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 at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.mythtv.org/pipermail/mythtv-users/attachments/20240125/456c2ea9/attachment.htm>