[mythtv-users] MySQL Database Overflow

[Nathan Lanchbury]

Thankz guys for your help.  Problem is now SOLVED!

Have you run the optimize_mythdb.pl script on the database?
I didn't know what the script was called, as I only ever used it through
the mythweb site.  When I did it simply said the database was fine.  I
then tried repairing the database with myisam command line script same
deal no change.  I even when as far as to REPAIR TABLE ........ USE_FRM;
through mysql and still no luck

Do you see any errors in the mythbackend logfile?
The only error I was getting is from the mythbackend running out of RAM
space because it had soaked a whole 3GBs of it.  Can't remember what it
was now, completely unhelpful though.

Mine is heaps shorter?
/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

ANSWER (well sort of)

I've been able to import my database records into a new blank one.  One
table at a time, slowly, gently.  I've not restarted the mythbackend
since importing, I've clicked check, extended on the mythweb page half a
dozen times now.  Hopefully no more troubles, except for the case of
leaving half my database behind but at least I've got my old recordings
Yay!

I didn't think it was a programs settings thing, as a new database
worked, still I used your larger my.cnf is case.  Yes I runs a Fedora
OS.  Thankz for your help.

Nathan Lanchbury
mythtv at lanchbury.id.au

-----Original Message-----
From: mythtv-users-bounces at mythtv.org
[mailto:mythtv-users-bounces at mythtv.org] On Behalf Of Yeechang Lee
Sent: Monday, 13 October 2008 4:16 AM
To: Discussion about mythtv
Subject: Re: [mythtv-users] MySQL Database Overflow

Nathan Lanchbury <mythtv at lanchbury.id.au> says:
> I have seen this problem once before and it turned out my database
> was corrupt (when exported over 500MBs)

It's not your database's size per se that's causing the issue; mine
has been over 500MB for ages.

(If I ever pull the trigger on connecting an Infiniband/eSATA-based
external enclosure with a bunch of the new 1.5TB drives it should hit
2GB+.)

> so I was able to strip out what was needed and import it back.

Vhat iss dis "strip out"?

I take it 'perl optimize_mythdb.pl' doesn't work?

> So everything shows to me the same thing has happened. BUT stripping
> the data doesn't work. The last night backup doesn't help along with
> the week backup, as is or stripped to just the recorded and
> scheduled recordings.

Your last sentence doesn't parse.

If restoring your database
(<URL:http://www.mythtv.org/docs/mythtv-HOWTO-23.html#ss23.5>), even
going back a few days, doesn't help, that'd indicate a non MythDB
database-related problem.

I'm no database guru--I have to look up any SQL queries beyond 'select
* from _____'--but here's the /etc/my.cnf for my 2GB RAM backend;
maybe your database has exceeded some MySQL memory limit? As you
mention ATRPMS I presume you're also using a Fedora- or CentOS-based
system. There are settings for both InnoDB and MyISAM engine but I use
MyISAM.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql
# 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

skip-locking
key_buffer = 432M # Ideally enough for all .MYI files
max_allowed_packet = 16M
table_cache = 256
net_buffer_length = 8M
sort_buffer_size = 6M
read_buffer_size = 6M
join_buffer_size = 6M
read_rnd_buffer_size = 6M
myisam_sort_buffer_size = 512M
thread_cache = 16
query_cache_limit = 4M
query_cache_size  = 128M
query_cache_type  = 1

innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 432M
innodb_additional_mem_pool_size = 32M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 96M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50 
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=8

# skip-innodb
set-variable=thread_stack=256k

expire_logs_days = 365

# utf8
# init-connect='SET NAMES utf8'
# character-set-server=utf8
# collation-server=utf8_general_ci
# skip-character-set-client-handshake

# log-bin = mysqld-binary-log
sync_binlog = 1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

-- 
Frontend:		P4 3.0GHz, 1.5TB software RAID 5 array
Backend:		Quad-core Xeon 1.6GHz, 6.6TB sw RAID 6
Video inputs:		Four high-definition over FireWire/OTA
Accessories:		47" 1080p LCD, 5.1 digital, and MX-600
_______________________________________________
mythtv-users mailing list
mythtv-users at mythtv.org
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users