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

[John Pilkington]

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

}}}