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

[Mick]

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