Lynn David Newton wrote: >
> Any mysql experts out there?
>
> I've got a large test database and a recent backup (1
> AM last night) made with mysqldump. I've been horsing
> with the contents of the database using forms, and in
> the process deleting records. The easiest way to get
> back where I started is to do a drop database, create
> database, and then input from the mysqldump backup
> file. Simple plan, right? Should work, right? This goes
> in a file:
>
> mysqladmin -u rradmin --password=r3g101 drop rrtest2
> mysqladmin -u rradmin --password=r3g101 create rrtest2
> mysql -u rradmin --password=r3g101 --database=rrtest2 < DATA.sql
>
> *BUT* ...
>
> Have you ever had the output of mysqldump not work as
> an input file? Fortunately, I tested this using a
> different database name first. Here's what I get when i
> try to run that third line:
>
> ERROR 1064 at line 679: You have an error in your SQL syntax near 'field(field),
> KEY type(type),
> KEY tsrc(tsrc)
> ) TYPE=MyISAM' at line 10
>
> The table in question looks like this:
>
> CREATE TABLE fnames (
> field varchar(40) NOT NULL default '',
> type varchar(10) NOT NULL default '',
> fieldorder int(4) default '0',
> longdesc varchar(200) default NULL,
> required int(1) default '0',
> tsrc varchar(40) NOT NULL default '',
> visible int(1) default '0',
> misc blob,
> KEY field(field),
> KEY type(type),
> KEY tsrc(tsrc)
> ) TYPE=MyISAM;
>
> It's the first KEY definition it doesn't like. If I
> comment out "KEY field(field)," it works, except of
> course then the field called 'field' is not set as an
> index.
>
> I suspect some name space pollution here. Perhaps some
> later version of mysql since this database was
> originally created came along in which they decided
> that you shouldn't have a field called 'field', or in
> which a field called 'field' shouldn't be an index
> (key).
>
> Any insights here? This sort of thing drives me crazy.
> I'm trying to accomplish some simple testing and am
> prevented by an increasing stack of sub-problems.
>
> --
> Lynn David Newton
> Phoenix, AZ
I recall having some kind of problem like this. My solution was to put
a "use" command at the top of the mysql dump. The important piece of my
script: