[nabz]

Hi all
I have installed MySQL on Dlink DNS-320 NAS using fun_plug 0.7. All my movies were scanned and added fine, but now when I try to add more movies XBMC seems to lose connection. After logging using SSH to my NAS I get 'mysqld_safe mysqld_restarted' message.


My advancedsettings.xml

Code:

<advancedsettings>
        <pathsubstitution>
            <substitute>
                <from>special://masterprofile/Thumbnails/</from>
                <to>smb://xbmc:xbmcxbmc@Sharecenter/xbmc/userdata/Thumbnails/</to>
            </substitute>
        </pathsubstitution>
    <videodatabase>
        <type>mysql</type>
        <host>192.168.137.120</host>
        <port>3306</port>
        <user>xbmc</user>
        <pass>xbmc</pass>
        <name>XBMCVideo</name>
    </videodatabase>
    <musicdatabase>
        <type>mysql</type>
        <host>192.168.137.120</host>
        <port>3306</port>
        <user>xbmc</user>
        <pass>xbmc</pass>
        <name>XBMCMusic</name>
    </musicdatabase>
     <videolibrary>
          <importwatchedstate>false</importwatchedstate>
     </videolibrary>
</advancedsettings>

Here is MySQL error.log

Code:

23:41:29 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16384
read_buffer_size=262144
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 49437 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

130309 23:41:29 mysqld_safe Number of processes running now: 0
130309 23:41:29 mysqld_safe mysqld restarted
130309 23:41:29 InnoDB: The InnoDB memory heap is disabled
130309 23:41:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130309 23:41:29 InnoDB: Compressed tables use zlib 1.2.6
130309 23:41:29 InnoDB: Initializing buffer pool, size = 16.0M
130309 23:41:29 InnoDB: Completed initialization of buffer pool
130309 23:41:29 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 65604185
130309 23:41:29  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 65604195
130309 23:41:29  InnoDB: Waiting for the background threads to start
130309 23:41:30 InnoDB: 1.1.8 started; log sequence number 65604195
130309 23:41:30 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130309 23:41:30 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
130309 23:41:30 [Note] Server socket created on IP: '0.0.0.0'.
130309 23:41:31 [Warning] 'user' entry 'root@ShareCenter' ignored in --skip-name-resolve mode.
130309 23:41:31 [Warning] 'proxies_priv' entry '@ root@ShareCenter' ignored in --skip-name-resolve mode.
130309 23:41:31 [Note] Event Scheduler: Loaded 0 events
130309 23:41:31 [Note] /ffp/bin/mysqld: ready for connections.
Version: '5.5.28'  socket: '/ffp/var/run/mysql/mysql.sock'  port: 3306  Source distribution
130309 23:41:31  InnoDB: Error: space id and page n:o stored in the page
InnoDB: read in are 0:484, should be 0:1055!
130309 23:41:31  InnoDB: Assertion failure in thread 1187906752 in file btr0pcur.c line 437
InnoDB: Failing assertion: btr_page_get_prev(next_page, mtr) == buf_block_get_page_no(btr_pcur_get_block(cursor))
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

And here is [mysqld] section of my.cnf file

Code:

# The MySQL server
[mysqld]
basedir         = /ffp
port        = 3306
socket        = /ffp/var/run/mysql/mysql.sock
log_error    = /srv/mysql/log/error.log
pid-file        = /ffp/run/mysqld/mysqld.pid
datadir         = /srv/mysql/datadir
tmpdir          = /srv/mysql/tmp
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 256K
skip-name-resolve

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (using the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
server-id    = 1

# Uncomment the following if you want to log updates
#log-bin=/srv/mysql/binlog/mysql-bin.log

# binary logging format - mixed recommended
#binlog_format=mixed
#expire_logs_days        = 10
#max_binlog_size         = 100M

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /srv/mysql/innodb/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /srv/mysql/innodblogdir/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Obviusly, XBMC crashes (hangs) and the next time I try to start it crashes, creating a dump. For repairing it I have to delete the XBMCMusic database (?).

Also I tried increasing memory to my.cnf but when I do it MySQL daemon doesn't start... perhaps established values were wrong...

I've read in wiki exists mysql performance article but I could not find it.

Can anyone help me? I can't add anything to my DB

Thanks in advanced

[Ardalista]

i got mysql running on my dlink dns 320l but it wasn't without headaches...

What version of mysql have you got installed?

how much available space do you have for the tmp dir?

[nabz]

Hi Ardalista,
thanks for your reply.
The installed MySQL version is 5.5.28... and about free space... do you mean df?

root@ShareCenter:/srv/mysql/tmp# df
Filesystem 1K-blocks Used Available Use% Mounted on
%root% 9911 4507 4892 48% /
/dev/ram0 9911 4507 4892 48% /
/usr/local/tmp/image.cfs 24448 24448 0 100% /usr/local/modules
/dev/mtdblock5 5120 504 4616 10% /usr/local/config
/dev/sda4 495884 15339 480545 4% /mnt/HD_a4
/dev/sdb4 495884 7114 488770 2% /mnt/HD_b4
/dev/sda2 1440089276 1024379692 415709584 72% /mnt/HD/HD_a2
/dev/sdb2 1440089276 1345697172 94392104 94% /mnt/HD/HD_b2

Mysql tmp dir is empty

If you mean 'tmp_table_size', it and 'max_heap_table_size' are not defined in my.cnf

Thanks

---

Also, I've modified my.cnf

tmp_table_size = 16M
max_heap_table_size = 16M
innodb_buffer_pool_size = 128M
max_allowed_packet = 1M

But still getting the same error message . Also tried to make a dump of all databases and...

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `actors` at row: 13320

130310 21:40:32 mysqld_safe Number of processes running now: 0

130310 21:40:32 mysqld_safe mysqld restarted


Thanks for your help!

[Ardalista]

Ahh, I had issues with the configuration of that install for MySQL. in the end I reset funplug and then installed optware and used MySQL 5.0.88-1 and that works like a charm.

Might want to give that a try.. it's a bit of messing around but it works.

[nabz]

Are you using Frodo? I had to install fun_plug 0.7 because 0.5 version of mysql was not compatible.
Can you show me your my.cnf? Perhaps I missed some parameter...
Also , mysql was installed using the package manager (slacker)

Thanks!

[Ardalista]

yeah I'm using Frodo.
I tried using the exact same version you are using and I had neverending issues.. mainly with the initialisation of the database.. I suspect there was something not quite right.

in the end I wiped funplug, and reinstaleld it for a fresh start, then I installed Optware into funplug, you can find the turorials here..

I'm at work right now, so will have to remote into my NAS at home shortly and I'll grab the my.cnf contents for you, along with some changes I made to funplug.init

EDIT:

here is the contents of my.cnf from the optware version of MySQL.

Code:

# Example MySQL config file for small systems.
#
# $Id$
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /opt/var) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password    = your_password
port        = 3306
socket        = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
user = root
datadir = /opt/var/lib/
tmpdir = /ffp/tmpsql
port        = 3306
socket        = /tmp/mysql.sock
skip-locking
skip-name-resolve
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (using the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
server-id    = 1

# Uncomment the following if you want to log updates
#log-bin=mysql-bin

# Uncomment the following if you are NOT using BDB tables
skip-bdb

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /opt/var/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /opt/var/
#innodb_log_arch_dir = /opt/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
#log = /tmpsql/xbmc.log

[mysqldump]
quick
max_allowed_packet = 2M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 4M
sort_buffer_size = 4M

[myisamchk]
key_buffer = 4M
sort_buffer_size = 4M

[mysqlhotcopy]
interactive-timeout

on top of that I also added these lines to the funplug.init file

Code:

# fix /tmp permissions
chmod 1777 /tmp

# fix mysql permissions
chmod 0644 /ffp/opt/optware/etc/my.cnf

[nabz]

Hi,
I'll compare my cnf file as soon as I arrive home.
I'll let you know.

Thanks again!

[nabz]

Well... more clues.
I'm using innodb and, also, when I tried to do mysqldump, I got these:

Code:

Error: Couldn't read status information for table actorlinkepisode ()
mysqldump: Couldn't execute 'show fields from `actorlinkepisode`': Table 'XBMCVideo75.actorlinkepisode' doesn't exist (1146)

Any idea for this?

[nabz]

Well, finally I decided to recreate my video db from scratch and import again... and now I know what was going on : I've executed 'top' command on NAS ssh and MySQL was using 99'2% CPU and nearly all the memory was used, and I think this was the reason for the previous errors. Now, importing data, I leave NAS only doing this task (not using smb or other task) and after about 5 hours all the database was recreated and imported again...

Also I modified my.cnf for using innodb_file_per_table.

I'll let you know how it is working the next days.

Thanks again for your support.