Gentoo Archives: gentoo-user

From: Kerin Millar <kerframil@×××××××××××.uk>
To: gentoo-user@l.g.o
Subject: Re: [gentoo-user] [slightly O/T] mysql problems
Date: Wed, 15 Oct 2014 12:41:19
Message-Id: 543E6B5F.8050900@fastmail.co.uk
In Reply to: Re: [gentoo-user] [slightly O/T] mysql problems by Mick
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

Replies

Subject Author
Re: [gentoo-user] [slightly O/T] mysql problems Mick <michaelkintzios@×××××.com>