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

[John Pilkington]

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