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
