[mythtv-users] !!NoTrans: SQL Error: Table

[Stephen Worthington]

On Tue, 13 Dec 2016 01:53:26 +0100, you wrote:

>Hoi Dick,
>
>Tuesday, December 13, 2016, 1:00:06 AM, you wrote:
>
>> On 12/12/2016 03:48 PM, Hika van den Hoven wrote:
>>>
>>>> Try running it from the home directory and just to make sure create
>>>> that mythconverg directory there before you run it. Also check what
>>>> temp directory is set in my.conf. It should be set to /tmp or
>>>> /var/tmp. The variable is tmpdir so:
>>>> tmpdir = /tmp/
>
>> I find /etc/mysql/my.cnf, and it has
>
>> tmpdir          = /tmp
>
>
>>>> Oh and if you create that mythconverg directory create rw rights to
>>>> the world in there.
>
>> drwxrwxrwx  2 rsteff rsteff     4096 Dec 12 15:43 mythconverg
>
>
>mysql>> repair table program;
>> +---------------------+--------+----------+--------------------------------------------------------+
>> | Table               | Op     | Msg_type | 
>> Msg_text                                               |
>> +---------------------+--------+----------+--------------------------------------------------------+
>> | mythconverg.program | repair | error    | Can't create new tempfile:
>> './mythconverg/program.TMD' |
>> | mythconverg.program | repair | status   | Operation 
>> failed                                       |
>> +---------------------+--------+----------+--------------------------------------------------------+
>> 2 rows in set (0.00 sec)
>
>Does that file exist in /var/lib/mysql/mythconverg ? If so, can you
>delete. But you first should stop both mysql and any program using it
>like your backend and then restart it.

Yes, that happened to me once, when I ran out of disk space when cron
was running the optimize_db script.  There were a couple of *.TMD
files left behind in the /var/lib/mysql/mythconverg directory and
repairs all failed until I deleted them (and also got back enough free
space to allow the database to work properly).  So shut down
mythbackend and any other programs that use mysql and shut down mysql,
then delete all /var/lib/mysql/mythconverg/*.TMD files.  Then make
sure that you have enough space on the hard drive for a full copy of
the largest table (recordedseek.MYD) and its index (recordedseek.MYI)
to be made at the same time, plus some spare space above that.

The mythconverg_backup.pl script also uses lots of space when making a
backup.  For it to work, your hard drive needs as much free space as a
full copy of the entire database, multiplied by a percentage to allow
for the overhead of conversion to ASCII text, plus the size of the
final compressed backup file.  So as your database grows, if your
backups are going to the default location of
/var/lib/mythtv/db_backups on the same drive as the database, you will
eventually run out of free space during the backups and that can then
cause a database operation to fail by running out of free space and
then you get table crashes like what has happened to you.  If you
suspect that the backup is causing free space problems, it is best to
change your cron job for mythconverg_backup.pl to get it to store the
backups on a different hard disk from the system and database.

I also have a version of mythconverg_backup.pl I modified that adds an
option to put the temporary uncompressed ASCII file that gets created
during the backup process in another location as well.  Normally the
ASCII file is created in the same place as the ultimate compressed
backup file and is then compressed from the ASCII file to the
compressed file.  Since they are both on the same hard disk at the
same time, that can create worse space problems and it also slows down
the backup operation as the compression operation is reading from and
writing to the same hard disk.  That makes backups especially slow if
you are storing your backups on a network drive.  I have put my
mythconverg_backup-jsw.pl file on my web server if anyone would like a
copy:

  http://www.jsw.gen.nz/mythtv/mythconverg_backup-jsw.pl