Re: [gentoo-user] [slightly O/T] mysql problems

[Mick <michaelkintzios@gmail.com>]

[-- Attachment #1.1: Type: Text/Plain, Size: 4446 bytes --]

On Tuesday 14 Oct 2014 21:15:48 Kerin Millar wrote:
> On 14/10/2014 19:54, Mick wrote:

> > # Uncomment this to get FEDERATED engine support
> > #plugin-load=federated=ha_federated.so
> > loose-federated
> > 
> > As far as I recall this is a default setting.  Should I change it?
> 
> No. I presume that you are not actively using the federated storage
> engine but let's put that aside because there is more to this error than
> meets the eye.
> 
> Check your MySQL error log and look for any anomalies from the point at
> which MySQL is started. If you don't know where the log file is, execute
> "SELECT @@log_error".


141014 19:41:37 [Warning] No argument was provided to --log-bin, and --log-
bin-index was not used; so replication may break when this MySQL server acts 
as a master and has his hostname changed!! Please use '--log-bin=mysqld-bin' 
to avoid this problem.
141014 19:41:37 InnoDB: The InnoDB memory heap is disabled
141014 19:41:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141014 19:41:37 InnoDB: Compressed tables use zlib 1.2.8
141014 19:41:37 InnoDB: Using Linux native AIO
141014 19:41:37 InnoDB: Initializing buffer pool, size = 16.0M
141014 19:41:37 InnoDB: Completed initialization of buffer pool
141014 19:41:37 InnoDB: highest supported file format is Barracuda.
141014 19:41:37  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
141014 19:41:37  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './website1@002dnew/actions.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html

> I have several questions:
> 
>    * Have you started MySQL with skip-grant-tables in effect?

Not knowingly.  How do I find out?


>    * Have you upgraded MySQL recently without going through the
>      documented upgrade procedure? [1]

I'm still on mysql-5.5.39

     Installed versions:  5.5.39(16:42:22 08/09/14)(community perl ssl -
bindist -cluster -debug -embedded -extraengine -jemalloc -latin1 -max-idx-128 
-minimal -profiling -selinux -static -static-libs -systemtap -tcmalloc -test)


>    * Have you copied files into MySQL's data directory that originated
>      from a different version of MySQL?

No, not manually.


>    * Have you otherwise removed or modified files in the data directory?

Not as far as I know.  I have suspicions of fs corruption though (it's been 
running out of space lately and I haven't yet found out why).


> > 2. A particular database which I have imported locally from a live site
> > gives me loads of this:
> The wording here suggests a broader context that would be relevant.
> Please be specific as to the circumstances. What procedure did you
> employ in order to migrate and import the database? What do you mean by
> "live site"? Which versions of MySQL are running at both source and
> destination? How are they configured?

mysql -u webadmin -h localhost -p website_test < website1_20141014.sql

The server is on 5.5.36.

website1 is the database name of the live site, and website_test is the local 
development database.

The server is a shared server, so I'm getting its vanilla configuration with 
no choice on the matter.  The local configuration is attached.


> > Is this some error imported from the live site, or is it due to something
> > being wrong locally?
> 
> MySQL believes that an InnoDB table named
> "webform_validation_rule_components" presently exists in a database
> named "website1@002dnew" but the corresponding tablespace file does not
> exist, relative to the MySQL datadir. The reason for this may become
> clear if you answer the questions posed above.

I'll check this when I get a minute and report back.

Right now the machine is locked up - no space left on the root partition for 
some obscure reason.  I need to start clearing stuff out.

Thanks for your help!

-- 
Regards,
Mick

[-- Attachment #1.2: my.cnf --]
[-- Type: text/plain, Size: 5444 bytes --]

# /etc/mysql/my.cnf: The global mysql configuration file.
# $Header: /var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-5.5,v 1.3 2014/04/25 00:43:46 jmbsvicetto Exp $

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

[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[myisamchk]
character-sets-dir=/usr/share/mysql/charsets

[myisampack]
character-sets-dir=/usr/share/mysql/charsets

# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log                                         = /var/log/mysql/mysql.err

# add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations
[mysqld]
character-set-server            = utf8
user                                            = mysql
port                                            = 3306
socket                                          = /var/run/mysqld/mysqld.sock
pid-file                                        = /var/run/mysqld/mysqld.pid
log-error                                       = /var/log/mysql/mysqld.err
basedir                                         = /usr
datadir                                         = /var/lib/mysql
skip-external-locking
key_buffer_size                                 = 16M
max_allowed_packet                      = 16M
table_open_cache                        = 64
sort_buffer_size                        = 512K
net_buffer_length                       = 8K
read_buffer_size                        = 256K
read_rnd_buffer_size            = 512K
myisam_sort_buffer_size         = 8M
lc_messages_dir                 = /usr/share/mysql
expire_logs_days                = 90
#Set this to your desired error message language
lc_messages                     = en_US

# security:
# using "localhost" in connects uses sockets by default
# skip-networking
bind-address                            = 127.0.0.1

log-bin
server-id                                       = 1

# point the following paths to different dedicated disks
tmpdir                                          = /tmp/
#log-update                             = /path-to-dedicated-directory/hostname

# you need the debug USE flag enabled to use the following directives,
# if needed, uncomment them, start the server and issue 
# #tail -f /tmp/mysqld.sql /tmp/mysqld.trace
# this will show you *exactly* what's happening in your server ;)

#log                                            = /tmp/mysqld.sql
#gdb
#debug                                          = d:t:i:o,/tmp/mysqld.trace
#one-thread

# the following is the InnoDB configuration
# if you wish to disable innodb instead
# uncomment just the next line
#skip-innodb
#
# the rest of the innodb config follows:
# don't eat too much memory, we're trying to be safe on 64Mb boxes
# you might want to bump this up a bit on boxes with more RAM
innodb_buffer_pool_size = 16M
# this is the default, increase it if you have lots of tables
innodb_additional_mem_pool_size = 2M
#
# i'd like to use /var/lib/mysql/innodb, but that is seen as a database :-(
# and upstream wants things to be under /var/lib/mysql/, so that's the route
# we have to take for the moment
#innodb_data_home_dir           = /var/lib/mysql/
#innodb_log_arch_dir            = /var/lib/mysql/
#innodb_log_group_home_dir      = /var/lib/mysql/
# you may wish to change this size to be more suitable for your system
# the max is there to avoid run-away growth on your machine
innodb_data_file_path = ibdata1:10M:autoextend:max:128M
# we keep this at around 25% of of innodb_buffer_pool_size
# sensible values range from 1MB to (1/innodb_log_files_in_group*innodb_buffer_pool_size)
innodb_log_file_size = 5M
# this is the default, increase it if you have very large transactions going on
innodb_log_buffer_size = 8M
# this is the default and won't hurt you
# you shouldn't need to tweak it
innodb_log_files_in_group=2
# see the innodb config docs, the other options are not always safe
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_file_per_table

# Uncomment this to get FEDERATED engine support
#plugin-load=federated=ha_federated.so
loose-federated

[mysqldump]
quick
max_allowed_packet                      = 16M

[mysql]
# uncomment the next directive if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer_size                         = 20M
sort_buffer_size                        = 20M
read_buffer                             = 2M
write_buffer                            = 2M

[myisamchk]
key_buffer_size                         = 20M
sort_buffer_size                        = 20M
read_buffer_size                        = 2M
write_buffer_size                       = 2M

[mysqlhotcopy]
interactive-timeout

[-- Attachment #2: This is a digitally signed message part. --]
[-- Type: application/pgp-signature, Size: 473 bytes --]