1 |
On 14/10/2014 23:25, Mick wrote: |
2 |
> On Tuesday 14 Oct 2014 21:15:48 Kerin Millar wrote: |
3 |
>> On 14/10/2014 19:54, Mick wrote: |
4 |
> |
5 |
>>> # Uncomment this to get FEDERATED engine support |
6 |
>>> #plugin-load=federated=ha_federated.so |
7 |
>>> loose-federated |
8 |
>>> |
9 |
>>> As far as I recall this is a default setting. Should I change it? |
10 |
>> |
11 |
>> No. I presume that you are not actively using the federated storage |
12 |
>> engine but let's put that aside because there is more to this error than |
13 |
>> meets the eye. |
14 |
>> |
15 |
>> Check your MySQL error log and look for any anomalies from the point at |
16 |
>> which MySQL is started. If you don't know where the log file is, execute |
17 |
>> "SELECT @@log_error". |
18 |
> |
19 |
> |
20 |
> 141014 19:41:37 [Warning] No argument was provided to --log-bin, and --log- |
21 |
> bin-index was not used; so replication may break when this MySQL server acts |
22 |
> as a master and has his hostname changed!! Please use '--log-bin=mysqld-bin' |
23 |
> to avoid this problem. |
24 |
> 141014 19:41:37 InnoDB: The InnoDB memory heap is disabled |
25 |
> 141014 19:41:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins |
26 |
> 141014 19:41:37 InnoDB: Compressed tables use zlib 1.2.8 |
27 |
> 141014 19:41:37 InnoDB: Using Linux native AIO |
28 |
> 141014 19:41:37 InnoDB: Initializing buffer pool, size = 16.0M |
29 |
> 141014 19:41:37 InnoDB: Completed initialization of buffer pool |
30 |
> 141014 19:41:37 InnoDB: highest supported file format is Barracuda. |
31 |
> 141014 19:41:37 InnoDB: Operating system error number 2 in a file operation. |
32 |
> InnoDB: The error means the system cannot find the path specified. |
33 |
> InnoDB: If you are installing InnoDB, remember that you must create |
34 |
> InnoDB: directories yourself, InnoDB does not create them. |
35 |
> 141014 19:41:37 InnoDB: Error: trying to open a table, but could not |
36 |
> InnoDB: open the tablespace file './website1@002dnew/actions.ibd'! |
37 |
> InnoDB: Have you moved InnoDB .ibd files around without using the |
38 |
> InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? |
39 |
> InnoDB: It is also possible that this is a temporary table #sql..., |
40 |
> InnoDB: and MySQL removed the .ibd file for this. |
41 |
> InnoDB: Please refer to |
42 |
> InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html |
43 |
|
44 |
Nothing particularly interesting there. |
45 |
|
46 |
> |
47 |
>> I have several questions: |
48 |
>> |
49 |
>> * Have you started MySQL with skip-grant-tables in effect? |
50 |
> |
51 |
> Not knowingly. How do I find out? |
52 |
|
53 |
If you had, you would know. It disables the privilege handling system |
54 |
outright. Typically it's used in situations where the root password has |
55 |
been forgotten or just prior to executing mysql_upgrade. |
56 |
|
57 |
The reason for asking is that it may also prevent some storage engines |
58 |
from loading, in which case their options will not be recognized. In |
59 |
turn, this may result in confusing error messages such as the one that |
60 |
you encountered. |
61 |
|
62 |
However, with the benefit of being able to read your my.cnf, the |
63 |
explanation turns out to be much simpler. You have "loose-federated" |
64 |
specified as an option but you are not loading the corresponding storage |
65 |
plugin. There is also the possibility that the engine was not compiled |
66 |
in at all (whether as a plugin or not). |
67 |
|
68 |
Simply remove or comment the line specifying this option and the error |
69 |
should go away. |
70 |
|
71 |
> |
72 |
> |
73 |
>> * Have you upgraded MySQL recently without going through the |
74 |
>> documented upgrade procedure? [1] |
75 |
> |
76 |
> I'm still on mysql-5.5.39 |
77 |
|
78 |
OK. If it has always been running MySQL 5.5, there's nothing to be |
79 |
concerned about. |
80 |
|
81 |
> |
82 |
> Installed versions: 5.5.39(16:42:22 08/09/14)(community perl ssl - |
83 |
> bindist -cluster -debug -embedded -extraengine -jemalloc -latin1 -max-idx-128 |
84 |
> -minimal -profiling -selinux -static -static-libs -systemtap -tcmalloc -test) |
85 |
> |
86 |
> |
87 |
>> * Have you copied files into MySQL's data directory that originated |
88 |
>> from a different version of MySQL? |
89 |
> |
90 |
> No, not manually. |
91 |
|
92 |
Good. |
93 |
|
94 |
> |
95 |
> |
96 |
>> * Have you otherwise removed or modified files in the data directory? |
97 |
> |
98 |
> Not as far as I know. I have suspicions of fs corruption though (it's been |
99 |
> running out of space lately and I haven't yet found out why). |
100 |
|
101 |
Not good. Which filesystem, if I may ask? XFS is preferable, due to its |
102 |
very good performance with O_DIRECT, which ext4 coming in second. Other |
103 |
filesystems may be problematic. In particular, ZFS does not support |
104 |
asynchronous I/O. |
105 |
|
106 |
> |
107 |
> |
108 |
>>> 2. A particular database which I have imported locally from a live site |
109 |
>>> gives me loads of this: |
110 |
>> The wording here suggests a broader context that would be relevant. |
111 |
>> Please be specific as to the circumstances. What procedure did you |
112 |
>> employ in order to migrate and import the database? What do you mean by |
113 |
>> "live site"? Which versions of MySQL are running at both source and |
114 |
>> destination? How are they configured? |
115 |
> |
116 |
> mysql -u webadmin -h localhost -p website_test < website1_20141014.sql |
117 |
|
118 |
Ah, just using DDL. That shouldn't have caused any trouble. |
119 |
|
120 |
> |
121 |
> The server is on 5.5.36. |
122 |
> |
123 |
> website1 is the database name of the live site, and website_test is the local |
124 |
> development database. |
125 |
> |
126 |
> The server is a shared server, so I'm getting its vanilla configuration with |
127 |
> no choice on the matter. The local configuration is attached. |
128 |
> |
129 |
> |
130 |
>>> Is this some error imported from the live site, or is it due to something |
131 |
>>> being wrong locally? |
132 |
>> |
133 |
>> MySQL believes that an InnoDB table named |
134 |
>> "webform_validation_rule_components" presently exists in a database |
135 |
>> named "website1@002dnew" but the corresponding tablespace file does not |
136 |
>> exist, relative to the MySQL datadir. The reason for this may become |
137 |
>> clear if you answer the questions posed above. |
138 |
> |
139 |
> I'll check this when I get a minute and report back. |
140 |
|
141 |
Based on what you have written, I can't see how this could have come |
142 |
about through any deliberate course of action that you've taken. |
143 |
|
144 |
I see that you are using "innodb_file_per_table". This is a very good |
145 |
thing as it results in the tablespace for each InnoDB table being stored |
146 |
in a distinct file. |
147 |
|
148 |
It remains unclear as to how the tablespace file in question could have |
149 |
gone missing. I am concerned that your filesystem has run out of space, |
150 |
or is reporting as such. Should there be any indication that you are |
151 |
experiencing filesystem corruption, that would be worse still. You may |
152 |
want to shut down MySQL and backup the contents of /var/lib/mysql to |
153 |
somewhere else before doing anything else. |
154 |
|
155 |
In any case, go into /var/lib/mysql and check whether the file that it |
156 |
mentions exists. If it does not exist, try running: |
157 |
|
158 |
DROP TABLE `website1@002dnew`.`webform_validation_rule_components` |
159 |
|
160 |
If that does not work then try again, using DISCARD TABLESPACE as |
161 |
opposed to DROP TABLE. Note that the backtick quoting is necessary |
162 |
because of the presence of the @ symbol in the database name, which |
163 |
would otherwise be misinterpreted. |
164 |
|
165 |
> |
166 |
> Right now the machine is locked up - no space left on the root partition for |
167 |
> some obscure reason. I need to start clearing stuff out. |
168 |
> |
169 |
> Thanks for your help! |
170 |
|
171 |
You're welcome. |
172 |
|
173 |
Although this is tangential, I wish to mention something else. The |
174 |
default setting for "innodb_buffer_pool_size" in Gentoo is so low as to |
175 |
be cringe-inducing. The buffer pool acts as a memory cache for both data |
176 |
and indexes. Therefore, this option is the single most important one |
177 |
with respect to InnoDB performance. Setting it higher will vastly |
178 |
improve MySQL performance. |
179 |
|
180 |
http://www.percona.com/blog/2007/11/03/choosing-innodb_buffer_pool_size/ |
181 |
|
182 |
--Kerin |