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

[John Pilkington]

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

}}}