mysql problem

[George Toft]

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:

echo "use $DBNAME;" >backup.sql
echo "" >>backup.sql
mysqldump -h $DBHOST -P $DBPORT -u $DBUSER -p$DBPASSWORD --opt $DBNAME
>>backup.sql

(the variables are defined previously - allows me to share code without
exposing usernames/passwords).

The resulting dump is this:

use stocks;

# MySQL dump 8.16
#
:
:
:
#
# Table structure for table 'stock'
#

DROP TABLE IF EXISTS stock;
CREATE TABLE stock (
  symbol char(7) NOT NULL default '',
  name varchar(30) NOT NULL default '',
  init_margin float unsigned zerofill default NULL,
  maint_margin float unsigned zerofill default NULL,
  conversion float unsigned zerofill default NULL,
  PRIMARY KEY  (symbol)
) TYPE=MyISAM;
:
:
:

This takes care of everything for me.  Maybe it will help.

George