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

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

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