Re: [gentoo-user] [slightly O/T] mysql problems

[J. Roeleveld]

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 <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