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

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

[Mick]

[-- Attachment #1: Type: Text/Plain, Size: 1512 bytes --]

Hi All,

This may be slightly off topic, but I thought of asking here first.  I noticed 
two problems, one specific to a particular database, the other more general.  
In reverse order:


1. I am getting this error when I start mysqld

141014 19:41:38 [Warning] /usr/sbin/mysqld: unknown option '--loose-federated'

Sure enough I seem to have this in /etc/mysql/my.cnf:

# 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?


2. A particular database which I have imported locally from a live site gives 
me loads of this:

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/webform_validation_rule_components.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.


Is this some error imported from the live site, or is it due to something 
being wrong locally?

Any ideas how to fix this?

-- 
Regards,
Mick

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

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

[Daniel Frey]

On 10/14/2014 11:54 AM, Mick wrote:
> Hi All,
> 
> This may be slightly off topic, but I thought of asking here first.  I noticed 
> two problems, one specific to a particular database, the other more general.  
> In reverse order:
> 
> 
> 1. I am getting this error when I start mysqld
> 
> 141014 19:41:38 [Warning] /usr/sbin/mysqld: unknown option '--loose-federated'

Did you compile with the 'extraengine' USE flag? It's required for
federated engine support.

> 141014 19:41:37  InnoDB: Error: trying to open a table, but could not
> InnoDB: open the tablespace file 
> './website1@002dnew/webform_validation_rule_components.ibd'!
> 

Does this file (and directory) exist?

> 
> Is this some error imported from the live site, or is it due to something 
> being wrong locally?

Most likely the live website stores its files in a different place. I'm
not sure how to fix that, though. I would imagine the error text gives
hints. (DISCARD TABLESPACE and IMPORT TABLESPACE - I've never used
them.) Then again, I only use mysql for pretty basic stuff.

Dan

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

[Mick]

[-- Attachment #1: Type: Text/Plain, Size: 712 bytes --]

On Tuesday 14 Oct 2014 20:21:27 Daniel Frey wrote:
> On 10/14/2014 11:54 AM, Mick wrote:
> > Hi All,
> > 
> > This may be slightly off topic, but I thought of asking here first.  I
> > noticed two problems, one specific to a particular database, the other
> > more general. In reverse order:
> > 
> > 
> > 1. I am getting this error when I start mysqld
> > 
> > 141014 19:41:38 [Warning] /usr/sbin/mysqld: unknown option
> > '--loose-federated'
> 
> Did you compile with the 'extraengine' USE flag? It's required for
> federated engine support.

I had emerged mysql with USE="-extraengine" it seems.  However the 
configuration includes the federated option regardless.

-- 
Regards,
Mick

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

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

[Kerin Millar]

On 14/10/2014 19:54, Mick wrote:
> Hi All,
>
> This may be slightly off topic, but I thought of asking here first.  I noticed
> two problems, one specific to a particular database, the other more general.
> In reverse order:
>
>
> 1. I am getting this error when I start mysqld
>
> 141014 19:41:38 [Warning] /usr/sbin/mysqld: unknown option '--loose-federated'
>
> Sure enough I seem to have this in /etc/mysql/my.cnf:
>
> # 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".

I have several questions:

   * Have you started MySQL with skip-grant-tables in effect?
   * Have you upgraded MySQL recently without going through the
     documented upgrade procedure? [1]
   * Have you copied files into MySQL's data directory that originated
     from a different version of MySQL?
   * Have you otherwise removed or modified files in the data directory?

>
>
> 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?

>
> 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/webform_validation_rule_components.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.
>
>
> 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.

--Kerin

[1] 
https://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html 
(and its predecessors)

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

[Mick]

[-- 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 --]

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

[Kerin Millar]

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

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

[Mick]

[-- Attachment #1: Type: Text/Plain, Size: 2998 bytes --]

On Wednesday 15 Oct 2014 02:14:37 Kerin Millar wrote:
> On 14/10/2014 23:25, Mick wrote:
> > On Tuesday 14 Oct 2014 21:15:48 Kerin Millar wrote:

> >>     * 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.

No, sorry I wasn't clear.  I have been upgrading mysql on this machine for 
some years now, always running stable versions.  After each update I run:

mysql_upgrade -h localhost -u root -p


> >>     * 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.

ext4


> 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.

Hmm ... I'm probably not doing this right.

First of all, there is no local database /var/lib/mysql/website1, because this 
is the live website name, on the shared server.  I only have 
/var/lib/mysql/website_test on the local dev machine.

Then although I can see, e.g.

-rw-rw----  1 mysql mysql     8939 Oct 14 19:25 actions.frm
-rw-rw----  1 mysql mysql    98304 Oct 14 19:25 actions.ibd

in /var/lib/mysql/website_test, if I try to run DROP TABlE, logged in as 
(mysql) root, I get an unknown table, error 1051.

=========================
mysql> USE website_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DROP TABLE `website1@002dnew`.`actions`;                                    
ERROR 1051 (42S02): Unknown table 'actions'
mysql> DISCARD TABLESPACE `website1@002dnew`.`actions`;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use near 
'DISCARD TABLESPACE `website1@002dnew`.`actions`' at line 1
=========================

I think in mysql-5.5 I should be using DROP TABLESPACE instead?

PS. This is the only database I have problems with.  There are another dozen 
or so which I have imported in a similar fashion and there are no errors in 
the logs about them.
-- 
Regards,
Mick

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

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

[Kerin Millar]

On 15/10/2014 13:05, Mick wrote:
> On Wednesday 15 Oct 2014 02:14:37 Kerin Millar wrote:
>> On 14/10/2014 23:25, Mick wrote:
>>> On Tuesday 14 Oct 2014 21:15:48 Kerin Millar wrote:
>
>>>>      * 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.
>
> No, sorry I wasn't clear.  I have been upgrading mysql on this machine for
> some years now, always running stable versions.  After each update I run:
>
> mysql_upgrade -h localhost -u root -p
>
>
>>>>      * 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.
>
> ext4
>
>
>> 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.
>
> Hmm ... I'm probably not doing this right.
>
> First of all, there is no local database /var/lib/mysql/website1, because this
> is the live website name, on the shared server.  I only have
> /var/lib/mysql/website_test on the local dev machine.
>
> Then although I can see, e.g.
>
> -rw-rw----  1 mysql mysql     8939 Oct 14 19:25 actions.frm
> -rw-rw----  1 mysql mysql    98304 Oct 14 19:25 actions.ibd
>
> in /var/lib/mysql/website_test, if I try to run DROP TABlE, logged in as
> (mysql) root, I get an unknown table, error 1051.
>
> =========================
> mysql> USE website_test;
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
>
> Database changed
> mysql> DROP TABLE `website1@002dnew`.`actions`;

Is this a table for which it is also complaining that a corresponding 
tablespace doesn't exist in database `website1@@002dnew`? Your original 
post mentioned only a table named `webform_validation_rule_components`.

Whichever table(s) it is complaining about, if you happen to find a 
corresponding .idb file in a different database (sub-directory), you 
might be able to satisfy MySQL by copying it to where it is expecting to 
find it. If that works, you should then be able to drop it.

Sometimes, directly copying an InnoDB tablespace into place requires a 
more elaborate procedure but I won't muddy the waters by describing said 
procedure just yet.

> ERROR 1051 (42S02): Unknown table 'actions'
> mysql> DISCARD TABLESPACE `website1@002dnew`.`actions`;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use near
> 'DISCARD TABLESPACE `website1@002dnew`.`actions`' at line 1
> =========================
>
> I think in mysql-5.5 I should be using DROP TABLESPACE instead?
>

My mistake. The correct syntax for discarding the tablespace would be:

   ALTER TABLE <table> DISCARD TABLESPACE;

I'm stating the obvious here, but be sure not to DROP or DISCARD 
TABLESPACE on a table whose tablespace does exist and for which you do 
not have a backup. Both commands are destructive.

--Kerin

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

[Mick]

[-- Attachment #1: Type: Text/Plain, Size: 2318 bytes --]

On Wednesday 15 Oct 2014 13:41:03 Kerin Millar wrote:


> > Database changed
> > mysql> DROP TABLE `website1@002dnew`.`actions`;
> 
> Is this a table for which it is also complaining that a corresponding 
> tablespace doesn't exist in database `website1@@002dnew`? Your original 
> post mentioned only a table named `webform_validation_rule_components`.

Yes, there are loads of tables that it is complaining about.  However, the 
name of the database mentioned in the logs is not that of the local machine, 
but of the remote.


> Whichever table(s) it is complaining about, if you happen to find a 
> corresponding .idb file in a different database (sub-directory), you 
> might be able to satisfy MySQL by copying it to where it is expecting to 
> find it. If that works, you should then be able to drop it.

I lost you here.  We have the local database, website_test.  In it I can see a 
number of tables.  I also have other databases for different websites.  Where 
am I supposed to look for corresponding .idb files?


> Sometimes, directly copying an InnoDB tablespace into place requires a 
> more elaborate procedure but I won't muddy the waters by describing said 
> procedure just yet.
> 
> > ERROR 1051 (42S02): Unknown table 'actions'
> > mysql> DISCARD TABLESPACE `website1@002dnew`.`actions`;
> > ERROR 1064 (42000): You have an error in your SQL syntax; check the
> > manual that corresponds to your MySQL server version for the right
> > syntax to use near 'DISCARD TABLESPACE `website1@002dnew`.`actions`' at
> > line 1
> > =========================
> > 
> > I think in mysql-5.5 I should be using DROP TABLESPACE instead?
> 
> My mistake. The correct syntax for discarding the tablespace would be:
> 
>    ALTER TABLE <table> DISCARD TABLESPACE;
> 
> I'm stating the obvious here, but be sure not to DROP or DISCARD 
> TABLESPACE on a table whose tablespace does exist and for which you do 
> not have a backup. Both commands are destructive.

Well, I still have the backup from the live website, I can restore from it if 
I have to.  However, what I find confusing is that the errors mention the live 
website's database name, not the local database.  Shouldn't the import 
function import the tables into the local database?
-- 
Regards,
Mick

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

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

[J. Roeleveld]

On Wednesday, October 15, 2014 09:40:56 PM Mick wrote:
> On Wednesday 15 Oct 2014 13:41:03 Kerin Millar wrote:
> > > Database changed
> > > mysql> DROP TABLE `website1@002dnew`.`actions`;
> > 
> > Is this a table for which it is also complaining that a corresponding
> > tablespace doesn't exist in database `website1@@002dnew`? Your original
> > post mentioned only a table named `webform_validation_rule_components`.
> 
> Yes, there are loads of tables that it is complaining about.  However, the
> name of the database mentioned in the logs is not that of the local machine,
> but of the remote.
> 
> > Whichever table(s) it is complaining about, if you happen to find a
> > corresponding .idb file in a different database (sub-directory), you
> > might be able to satisfy MySQL by copying it to where it is expecting to
> > find it. If that works, you should then be able to drop it.
> 
> I lost you here.  We have the local database, website_test.  In it I can see
> a number of tables.  I also have other databases for different websites. 
> Where am I supposed to look for corresponding .idb files?
> 
> > Sometimes, directly copying an InnoDB tablespace into place requires a
> > more elaborate procedure but I won't muddy the waters by describing said
> > procedure just yet.
> > 
> > > ERROR 1051 (42S02): Unknown table 'actions'
> > > mysql> DISCARD TABLESPACE `website1@002dnew`.`actions`;
> > > ERROR 1064 (42000): You have an error in your SQL syntax; check the
> > > manual that corresponds to your MySQL server version for the right
> > > syntax to use near 'DISCARD TABLESPACE `website1@002dnew`.`actions`' at
> > > line 1
> > > =========================
> > > 
> > > I think in mysql-5.5 I should be using DROP TABLESPACE instead?
> > 
> > My mistake. The correct syntax for discarding the tablespace would be:
> >    ALTER TABLE <table> DISCARD TABLESPACE;
> > 
> > I'm stating the obvious here, but be sure not to DROP or DISCARD
> > TABLESPACE on a table whose tablespace does exist and for which you do
> > not have a backup. Both commands are destructive.
> 
> Well, I still have the backup from the live website, I can restore from it
> if I have to.  However, what I find confusing is that the errors mention
> the live website's database name, not the local database.  Shouldn't the
> import function import the tables into the local database?

When you do it as you said:
mysql -u webadmin -h localhost -p website_test < website1_20141014.sql

then that is the expected result (that it uses tables in the local database.)

Can you do a search in the SQL-file for references to the remote database and 
post some of those lines? (Preferably only a subset referencing a single 
table)

--
Joost

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

[Mick]

[-- Attachment #1: Type: Text/Plain, Size: 2092 bytes --]

On Thursday 16 Oct 2014 07:24:43 J. Roeleveld wrote:
> On Wednesday, October 15, 2014 09:40:56 PM Mick wrote:

> > Well, I still have the backup from the live website, I can restore from
> > it if I have to.  However, what I find confusing is that the errors
> > mention the live website's database name, not the local database. 
> > Shouldn't the import function import the tables into the local database?
> 
> When you do it as you said:
> mysql -u webadmin -h localhost -p website_test < website1_20141014.sql
> 
> then that is the expected result (that it uses tables in the local
> database.)
> 
> Can you do a search in the SQL-file for references to the remote database
> and post some of those lines? (Preferably only a subset referencing a
> single table)

Thank you both for your help.  I think I have fixed whatever it was that had 
gone sideways, but I can't explain it with any certainty.

So, here is what happened.  The local database name more than a year ago had a 
hyphen in the name; e.g. "website-new".  When listing /var/lib/mysql it was 
shown as: website@002dnew

However, 9 months ago I had dropped that database and created a new database 
with an underscore instead of a hyphen; e.g. website_new.  Imported the tables 
from the remote database into it and carried on with my work.

Suddenly, I notice all these errors in the log.  They were definitely not 
there before and in any case the website-new directory was no longer listed in 
/var/lib/mysql, while website_new was there.

I dropped website_new, recreated website-new and the errors in the logs 
stopped.  Finally, I dropped website-new again, recreated website_new and 
still no errors in the logs.  :-)

The only problem is that now I can't load the website from the recreated 
website_new database!  LOL!  I will look at it later, but wanted to report 
that the errors I posted about have thankfully gone.  I blame it all on 
filesystem corruption of some sort (ext4), as it was running out of space, but 
can't be sure.

Thanks again.

-- 
Regards,
Mick

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