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

[Kerin Millar <kerframil@fastmail.co.uk>]

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