Bugzilla Bug 110100

I recently updated from mysql-4.1.14 to mysql-5.0.13_rc.  I ran
revdep-rebuild, and rebuild qt.  The rest of mythtv seems to work ok, but
mythfilldatabase gives lots of various errors like:
DB Error (Inserting into dd_schedule):
Query was:
INSERT INTO dd_schedule
(programid,stationid,scheduletime,duration,repeat,stereo,subtitled,hdtv,closecaptioned,tvrating,partnumber,parttotal,endtime)
VALUES('SH0000010000','15673','2005-10-23T06:00:00','00:30:00',0,0,0,0,0,NULL,0,0,'2005-10-23T06:30:00');
Driver error was [2/1064]:
QMYSQL3: Unable to execute query
Database error was:
You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
'repeat,stereo,subtitled,hdtv,closecaptioned,tvrating,partnumber,parttotal,endtim'
at line 1


Reproducible: Always
Steps to Reproduce:
1. Upgrade to mysql-5.x
2. run mythfilldatabase

Actual Results:  
      SQL errors.  The TV guide in mythtv gradually gets more and more "Unknown"
entries as the data already in the database becomes out of date, and after a
while mythtv no longer records shows, since there's no more guide info.

Expected Results:  
     mythfilldatabase fills the database, and mythtv is all shiny and happy.


     A solution is below.. but first, some emerge info..

Builds fly on this setup.. distcc is running between this XP 2200+, a Sempron
2500+, and an XP 2100+.:
Portage 2.0.53_rc6 (default-linux/x86/2005.0, gcc-3.4.4, glibc-2.3.5-r2,
2.6.13-gentoo-r1 i686)
=================================================================
System uname: 2.6.13-gentoo-r1 i686 AMD Athlon(tm) XP 2200+
Gentoo Base System version 1.12.0_pre9
distcc 2.18.3 i686-pc-linux-gnu (protocols 1 and 2) (default port 3632) [enabled]
dev-lang/python:     2.4.2
sys-apps/sandbox:    1.2.13
sys-devel/autoconf:  2.13, 2.59-r7
sys-devel/automake:  1.4_p6, 1.5, 1.6.3, 1.7.9-r1, 1.8.5-r3, 1.9.6-r1
sys-devel/binutils:  2.16.1
sys-devel/libtool:   1.5.20
virtual/os-headers:  2.6.11-r2
ACCEPT_KEYWORDS="x86 ~x86"
AUTOCLEAN="yes"
CBUILD="i686-pc-linux-gnu"
CFLAGS="-O2 -march=athlon-xp -fomit-frame-pointer"
CHOST="i686-pc-linux-gnu"
CONFIG_PROTECT="/etc /usr/kde/2/share/config /usr/kde/3.4/env
/usr/kde/3.4/share/config /usr/kde/3.4/shutdown /usr/kde/3/share/config
/usr/lib/X11/xkb /usr/lib/mozilla/defaults/pref /usr/share/config
/var/qmail/control"
CONFIG_PROTECT_MASK="/etc/gconf /etc/terminfo /etc/env.d"
CXXFLAGS="-O2 -march=athlon-xp -fomit-frame-pointer"
DISTDIR="/usr/portage/distfiles"
FEATURES="autoconfig distcc distlocks fixpackages sandbox sfperms strict"
GENTOO_MIRRORS="http://gentoo.mirrors.tds.net/gentoo"
MAKEOPTS="-j5"
PKGDIR="/usr/portage/packages"
PORTAGE_TMPDIR="/var/tmp"
PORTDIR="/usr/portage"
PORTDIR_OVERLAY="/usr/local/portage"
SYNC="rsync://rsync.us.gentoo.org/gentoo-portage"
USE="x86 3dnow 3dnowext X a52 aac alsa apm arts avi berkdb bitmap-fonts
browserplugin cdr cjk crypt cups curl dv dvd dvdr dvdread eds emboss encode esd
fam fame ffmpeg flac foomaticdb fortran gdbm gif gimpprint gnome gpm gstreamer
gtk gtk2 guile imagemagick imlib insecure-savers ipv6 java jpeg junit kde libg++
libwww mad matroska mikmod mjpeg mmx motif mozilla mp3 mpeg mppe-mppc mysql
mythtv nas ncurses nls nptl nsplugin nvidia offensive ogg oggvorbis opengl oss
pam pdflib perl pic plugin png ppds python qt quicktime readline samba scanner
sdl slang spell sse ssl subtitles tcltk tcpd threads tiff truetype
truetype-fonts type1-fonts udev usb v4l v4l2 vorbis win32codecs xanim xine xml2
xmms xv xvid zlib userland_GNU kernel_linux elibc_glibc"
Unset:  ASFLAGS, CTARGET, LANG, LC_ALL, LDFLAGS, LINGUAS

     Please see this URL:
http://www.gossamer-threads.com/lists/mythtv/users/131121?do=post_view_flat#131121
     In short, "repeat" is a reserved word in mysql-5, but the mythtv Datadirect
source uses repeat as a table entry.  This post, courtesy of mickc at
whizardries , suggests quoting instances of repeat in SQL queries with ``
characters.  I suspect although mysql-4 doesn't need this change, that it will
be compatible with mysql-4 as well.  I just built a copy of mythtv with this
change, and unlike mickc I'm getting no warnings/errors at all.. it works smoothly.

     I haven't checked out mythtv-cvs to see if this change is already in there.

Created an attachment (id=71157) [details]
     This implements the patch I described, quoting repeat in queries.

no knowledge of this package but:
"repeat" is a reserved word,

the query should be written as

INSERT INTO dd_schedule
(`programid`,`stationid`,`scheduletime`,`duration`,`repeat` ...

or the field `repeat` renamed

cheers

*** Bug 110584 has been marked as a duplicate of this bug. ***

myth svn has renamed the field to isrepeat last night. Should likly modify the 
patch to be the same.

So here's the issue. If we fix this and use the patch, then when the next
version is released the updating of the database schema will fail and everyone
will have to rebuild their database from scratch. And it's not like I can just
comment out that update because then it'll break new installs and people who
didn't emerge the fix.

So there's a big catch 22 here.

I guess I could just do the fix Francesco mentioned for 0.18.1

*** Bug 111772 has been marked as a duplicate of this bug. ***

Created an attachment (id=74246) [details]
The patch as implemented by the upstream mythtv.

Here is the patch as implemented by the upstream group.
I've tested it, and mythfilldatabase works for me.

This should have 'mythtv' added to the summary to ease searches.  Thanks :)

changing summary so I can find this darn thing

This will be fixed by the up coming mythtv-0.18.2_pre7882 version.

This will include the 0-18-fixes branch which 0.18.1 came from and has since
included further fixes. While this fix wasn't backported into that branch, I
have applied the patch. 7882 is the rev number of the last commit to that
branch.

Use MythTV 0.19