1 |
On 15/10/2014 13:05, Mick wrote: |
2 |
> On Wednesday 15 Oct 2014 02:14:37 Kerin Millar wrote: |
3 |
>> On 14/10/2014 23:25, Mick wrote: |
4 |
>>> On Tuesday 14 Oct 2014 21:15:48 Kerin Millar wrote: |
5 |
> |
6 |
>>>> * Have you upgraded MySQL recently without going through the |
7 |
>>>> documented upgrade procedure? [1] |
8 |
>>> |
9 |
>>> I'm still on mysql-5.5.39 |
10 |
>> |
11 |
>> OK. If it has always been running MySQL 5.5, there's nothing to be |
12 |
>> concerned about. |
13 |
> |
14 |
> No, sorry I wasn't clear. I have been upgrading mysql on this machine for |
15 |
> some years now, always running stable versions. After each update I run: |
16 |
> |
17 |
> mysql_upgrade -h localhost -u root -p |
18 |
> |
19 |
> |
20 |
>>>> * Have you otherwise removed or modified files in the data |
21 |
>>>> directory? |
22 |
>>> |
23 |
>>> Not as far as I know. I have suspicions of fs corruption though (it's |
24 |
>>> been running out of space lately and I haven't yet found out why). |
25 |
>> |
26 |
>> Not good. Which filesystem, if I may ask? XFS is preferable, due to its |
27 |
>> very good performance with O_DIRECT, which ext4 coming in second. Other |
28 |
>> filesystems may be problematic. In particular, ZFS does not support |
29 |
>> asynchronous I/O. |
30 |
> |
31 |
> ext4 |
32 |
> |
33 |
> |
34 |
>> In any case, go into /var/lib/mysql and check whether the file that it |
35 |
>> mentions exists. If it does not exist, try running: |
36 |
>> |
37 |
>> DROP TABLE `website1@002dnew`.`webform_validation_rule_components` |
38 |
>> |
39 |
>> If that does not work then try again, using DISCARD TABLESPACE as |
40 |
>> opposed to DROP TABLE. Note that the backtick quoting is necessary |
41 |
>> because of the presence of the @ symbol in the database name, which |
42 |
>> would otherwise be misinterpreted. |
43 |
> |
44 |
> Hmm ... I'm probably not doing this right. |
45 |
> |
46 |
> First of all, there is no local database /var/lib/mysql/website1, because this |
47 |
> is the live website name, on the shared server. I only have |
48 |
> /var/lib/mysql/website_test on the local dev machine. |
49 |
> |
50 |
> Then although I can see, e.g. |
51 |
> |
52 |
> -rw-rw---- 1 mysql mysql 8939 Oct 14 19:25 actions.frm |
53 |
> -rw-rw---- 1 mysql mysql 98304 Oct 14 19:25 actions.ibd |
54 |
> |
55 |
> in /var/lib/mysql/website_test, if I try to run DROP TABlE, logged in as |
56 |
> (mysql) root, I get an unknown table, error 1051. |
57 |
> |
58 |
> ========================= |
59 |
> mysql> USE website_test; |
60 |
> Reading table information for completion of table and column names |
61 |
> You can turn off this feature to get a quicker startup with -A |
62 |
> |
63 |
> Database changed |
64 |
> mysql> DROP TABLE `website1@002dnew`.`actions`; |
65 |
|
66 |
Is this a table for which it is also complaining that a corresponding |
67 |
tablespace doesn't exist in database `website1@@002dnew`? Your original |
68 |
post mentioned only a table named `webform_validation_rule_components`. |
69 |
|
70 |
Whichever table(s) it is complaining about, if you happen to find a |
71 |
corresponding .idb file in a different database (sub-directory), you |
72 |
might be able to satisfy MySQL by copying it to where it is expecting to |
73 |
find it. If that works, you should then be able to drop it. |
74 |
|
75 |
Sometimes, directly copying an InnoDB tablespace into place requires a |
76 |
more elaborate procedure but I won't muddy the waters by describing said |
77 |
procedure just yet. |
78 |
|
79 |
> ERROR 1051 (42S02): Unknown table 'actions' |
80 |
> mysql> DISCARD TABLESPACE `website1@002dnew`.`actions`; |
81 |
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual |
82 |
> that corresponds to your MySQL server version for the right syntax to use near |
83 |
> 'DISCARD TABLESPACE `website1@002dnew`.`actions`' at line 1 |
84 |
> ========================= |
85 |
> |
86 |
> I think in mysql-5.5 I should be using DROP TABLESPACE instead? |
87 |
> |
88 |
|
89 |
My mistake. The correct syntax for discarding the tablespace would be: |
90 |
|
91 |
ALTER TABLE <table> DISCARD TABLESPACE; |
92 |
|
93 |
I'm stating the obvious here, but be sure not to DROP or DISCARD |
94 |
TABLESPACE on a table whose tablespace does exist and for which you do |
95 |
not have a backup. Both commands are destructive. |
96 |
|
97 |
--Kerin |