On 14/10/2014 23:25, Mick wrote:
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
Nothing particularly interesting there.
I have several questions:
* Have you started MySQL with skip-grant-tables in effect?
Not knowingly. How do I find out?
If you had, you would know. It disables the privilege handling system
outright. Typically it's used in situations where the root password has
been forgotten or just prior to executing mysql_upgrade.
The reason for asking is that it may also prevent some storage engines
from loading, in which case their options will not be recognized. In
turn, this may result in confusing error messages such as the one that
you encountered.
However, with the benefit of being able to read your my.cnf, the
explanation turns out to be much simpler. You have "loose-federated"
specified as an option but you are not loading the corresponding storage
plugin. There is also the possibility that the engine was not compiled
in at all (whether as a plugin or not).
Simply remove or comment the line specifying this option and the error
should go away.
* Have you upgraded MySQL recently without going through the
documented upgrade procedure? [1]
I'm still on mysql-5.5.39
OK. If it has always been running MySQL 5.5, there's nothing to be
concerned about.
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.
Good.
* 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).
Not good. Which filesystem, if I may ask? XFS is preferable, due to its
very good performance with O_DIRECT, which ext4 coming in second. Other
filesystems may be problematic. In particular, ZFS does not support
asynchronous I/O.
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
Ah, just using DDL. That shouldn't have caused any trouble.
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.
Based on what you have written, I can't see how this could have come
about through any deliberate course of action that you've taken.
I see that you are using "innodb_file_per_table". This is a very good
thing as it results in the tablespace for each InnoDB table being stored
in a distinct file.
It remains unclear as to how the tablespace file in question could have
gone missing. I am concerned that your filesystem has run out of space,
or is reporting as such. Should there be any indication that you are
experiencing filesystem corruption, that would be worse still. You may
want to shut down MySQL and backup the contents of /var/lib/mysql to
somewhere else before doing anything else.
In any case, go into /var/lib/mysql and check whether the file that it
mentions exists. If it does not exist, try running:
DROP TABLE `website1@002dnew`.`webform_validation_rule_components`
If that does not work then try again, using DISCARD TABLESPACE as
opposed to DROP TABLE. Note that the backtick quoting is necessary
because of the presence of the @ symbol in the database name, which
would otherwise be misinterpreted.
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!
You're welcome.
Although this is tangential, I wish to mention something else. The
default setting for "innodb_buffer_pool_size" in Gentoo is so low as to
be cringe-inducing. The buffer pool acts as a memory cache for both data
and indexes. Therefore, this option is the single most important one
with respect to InnoDB performance. Setting it higher will vastly
improve MySQL performance.
http://www.percona.com/blog/2007/11/03/choosing-innodb_buffer_pool_size/
--Kerin
