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

[John Pilkington]

On 25/01/2024 21:27, Roland Ernst wrote:
> 
> 
> On Thu, Jan 25, 2024 at 9:40 PM John Pilkington <johnpilk222 at gmail.com 
> <mailto: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 <mailto: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
{{{
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)

}}}