Gentoo Archives: gentoo-user

From: "J. Roeleveld" <joost@××××××××.org>
To: gentoo-user@l.g.o
Subject: Re: [gentoo-user] [slightly O/T] mysql problems
Date: Thu, 16 Oct 2014 06:24:53
Message-Id: 6256627.JaWHi3O9FL@andromeda
In Reply to: Re: [gentoo-user] [slightly O/T] mysql problems by Mick
1 On Wednesday, October 15, 2014 09:40:56 PM Mick wrote:
2 > On Wednesday 15 Oct 2014 13:41:03 Kerin Millar wrote:
3 > > > Database changed
4 > > > mysql> DROP TABLE `website1@002dnew`.`actions`;
5 > >
6 > > Is this a table for which it is also complaining that a corresponding
7 > > tablespace doesn't exist in database `website1@@002dnew`? Your original
8 > > post mentioned only a table named `webform_validation_rule_components`.
9 >
10 > Yes, there are loads of tables that it is complaining about. However, the
11 > name of the database mentioned in the logs is not that of the local machine,
12 > but of the remote.
13 >
14 > > Whichever table(s) it is complaining about, if you happen to find a
15 > > corresponding .idb file in a different database (sub-directory), you
16 > > might be able to satisfy MySQL by copying it to where it is expecting to
17 > > find it. If that works, you should then be able to drop it.
18 >
19 > I lost you here. We have the local database, website_test. In it I can see
20 > a number of tables. I also have other databases for different websites.
21 > Where am I supposed to look for corresponding .idb files?
22 >
23 > > Sometimes, directly copying an InnoDB tablespace into place requires a
24 > > more elaborate procedure but I won't muddy the waters by describing said
25 > > procedure just yet.
26 > >
27 > > > ERROR 1051 (42S02): Unknown table 'actions'
28 > > > mysql> DISCARD TABLESPACE `website1@002dnew`.`actions`;
29 > > > ERROR 1064 (42000): You have an error in your SQL syntax; check the
30 > > > manual that corresponds to your MySQL server version for the right
31 > > > syntax to use near 'DISCARD TABLESPACE `website1@002dnew`.`actions`' at
32 > > > line 1
33 > > > =========================
34 > > >
35 > > > I think in mysql-5.5 I should be using DROP TABLESPACE instead?
36 > >
37 > > My mistake. The correct syntax for discarding the tablespace would be:
38 > > ALTER TABLE <table> DISCARD TABLESPACE;
39 > >
40 > > I'm stating the obvious here, but be sure not to DROP or DISCARD
41 > > TABLESPACE on a table whose tablespace does exist and for which you do
42 > > not have a backup. Both commands are destructive.
43 >
44 > Well, I still have the backup from the live website, I can restore from it
45 > if I have to. However, what I find confusing is that the errors mention
46 > the live website's database name, not the local database. Shouldn't the
47 > import function import the tables into the local database?
48
49 When you do it as you said:
50 mysql -u webadmin -h localhost -p website_test < website1_20141014.sql
51
52 then that is the expected result (that it uses tables in the local database.)
53
54 Can you do a search in the SQL-file for references to the remote database and
55 post some of those lines? (Preferably only a subset referencing a single
56 table)
57
58 --
59 Joost

Replies

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