From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from lists.gentoo.org (pigeon.gentoo.org [208.92.234.80]) by finch.gentoo.org (Postfix) with ESMTP id 3CE631387CA for ; Thu, 16 Oct 2014 06:24:53 +0000 (UTC) Received: from pigeon.gentoo.org (localhost [127.0.0.1]) by pigeon.gentoo.org (Postfix) with SMTP id 3075EE088C; Thu, 16 Oct 2014 06:24:45 +0000 (UTC) Received: from smtpq2.gn.mail.iss.as9143.net (smtpq2.gn.mail.iss.as9143.net [212.54.34.165]) by pigeon.gentoo.org (Postfix) with ESMTP id 2158BE0885 for ; Thu, 16 Oct 2014 06:24:43 +0000 (UTC) Received: from [212.54.34.135] (helo=smtp4.gn.mail.iss.as9143.net) by smtpq2.gn.mail.iss.as9143.net with esmtp (Exim 4.71) (envelope-from ) id 1XeeUK-0002IP-35 for gentoo-user@lists.gentoo.org; Thu, 16 Oct 2014 08:24:40 +0200 Received: from 53579160.cm-6-8c.dynamic.ziggo.nl ([83.87.145.96] helo=data.antarean.org) by smtp4.gn.mail.iss.as9143.net with esmtp (Exim 4.71) (envelope-from ) id 1XeeUJ-0007uB-Hf for gentoo-user@lists.gentoo.org; Thu, 16 Oct 2014 08:24:40 +0200 Received: from andromeda.localnet (unknown [62.41.73.83]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by data.antarean.org (Postfix) with ESMTPSA id EE9C74B for ; Thu, 16 Oct 2014 08:24:13 +0200 (CEST) From: "J. Roeleveld" To: gentoo-user@lists.gentoo.org Subject: Re: [gentoo-user] [slightly O/T] mysql problems Date: Thu, 16 Oct 2014 08:24:43 +0200 Message-ID: <6256627.JaWHi3O9FL@andromeda> Organization: Antarean User-Agent: KMail/4.12.5 (Linux/3.14.14-gentoo; KDE/4.12.5; x86_64; ; ) In-Reply-To: <201410152141.06240.michaelkintzios@gmail.com> References: <201410141955.04196.michaelkintzios@gmail.com> <543E6B5F.8050900@fastmail.co.uk> <201410152141.06240.michaelkintzios@gmail.com> Precedence: bulk List-Post: List-Help: List-Unsubscribe: List-Subscribe: List-Id: Gentoo Linux mail X-BeenThere: gentoo-user@lists.gentoo.org Reply-to: gentoo-user@lists.gentoo.org MIME-Version: 1.0 Content-Transfer-Encoding: 7Bit Content-Type: text/plain; charset="us-ascii" X-Ziggo-spambar: ---- X-Ziggo-spamscore: -4.9 X-Ziggo-spamreport: ALL_TRUSTED=-1,BAYES_00=-1.9,PROLO_TRUST_RDNS=-3,RDNS_DYNAMIC=0.982 X-Ziggo-Spam-Status: No X-Spam-Status: No X-Spam-Flag: No X-Archives-Salt: 18ded796-579b-4104-9afe-d23c34ce1798 X-Archives-Hash: 40798f21ce833e2d23c4e62a2711b08f On Wednesday, October 15, 2014 09:40:56 PM Mick wrote: > On Wednesday 15 Oct 2014 13:41:03 Kerin Millar wrote: > > > 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`. > > Yes, there are loads of tables that it is complaining about. However, the > name of the database mentioned in the logs is not that of the local machine, > but of the remote. > > > 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. > > I lost you here. We have the local database, website_test. In it I can see > a number of tables. I also have other databases for different websites. > Where am I supposed to look for corresponding .idb files? > > > 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 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. > > Well, I still have the backup from the live website, I can restore from it > if I have to. However, what I find confusing is that the errors mention > the live website's database name, not the local database. Shouldn't the > import function import the tables into the local database? When you do it as you said: mysql -u webadmin -h localhost -p website_test < website1_20141014.sql then that is the expected result (that it uses tables in the local database.) Can you do a search in the SQL-file for references to the remote database and post some of those lines? (Preferably only a subset referencing a single table) -- Joost