mysql problem

Top Page
Attachments:
Message as email
+ (text/plain)
Delete this message
Reply to this message
Author: George Toft
Date:  
Subject: mysql problem
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