mysql problem

Jim plug-discuss@lists.plug.phoenix.az.us
15 Jan 2003 15:01:59 -0700


Yup - the answer is to edit the dumped file and put in a USE command. 
This must be a problem with the way MYSQL dumps data because I use
phpMyAdmin to do the dumps and it does not create a USE statement
either.


On Wed, 2003-01-15 at 12:48, 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
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change  you mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
> 
-- 
 Jim

 Freedom is all we really want