RE: DB not restoring from dump file
Matt W mailto:[EMAIL PROTECTED] on Monday, October 27, 2003 5:29 PM said: As to why mysqldump would create a dump file with a syntax error in it, that's because *you* (or the application creator) used a reserved word for a column/index name (bad idea) and mysqldump I always try to be careful about this and I even compared all the column names in my db to the reserved word list on the MySQL site and didn't find any matches. Chris. -- Don't like reformatting your Outlook replies? Now there's relief! http://home.in.tum.de/~jain/software/outlook-quotefix/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB not restoring from dump file
On Wed, 2003-10-29 at 14:18, Chris W. Parker wrote: Matt W mailto:[EMAIL PROTECTED] on Monday, October 27, 2003 5:29 PM said: As to why mysqldump would create a dump file with a syntax error in it, that's because *you* (or the application creator) used a reserved word for a column/index name (bad idea) and mysqldump I always try to be careful about this and I even compared all the column names in my db to the reserved word list on the MySQL site and didn't find any matches. It may be best to always use the --quote-names option to mysqldump, which would avoid any problems you might encounter with reserved words being used in column and table names. -- Andy Bakun: when uselessness just isn't enough [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB not restoring from dump file
Andy Bakun mailto:[EMAIL PROTECTED] on Wednesday, October 29, 2003 12:27 PM said: It may be best to always use the --quote-names option to mysqldump, which would avoid any problems you might encounter with reserved words being used in column and table names. Already done. :) Chris. -- Don't like reformatting your Outlook replies? Now there's relief! http://home.in.tum.de/~jain/software/outlook-quotefix/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB not restoring from dump file
can you send the contents of your dumpfile up to this point. assuming its line 118 of hte dumpfile and 21 of this build table query Jeff Chris W. Parker [EMAIL PROTECTED]To: [EMAIL PROTECTED] .comcc: Subject: DB not restoring from dump file 10/27/2003 01:45 PM Hey everyone, First post to the MySQL list so please be gentle. I recently emptied some tables I shouldn't have in a db of mine and I want to restore the data from a dump file made a few days ago. MySQL version is 3.23. This is the command I used to create the dump: mysqldump --opt -u root --password=password dbname dbname.dump I'm trying to restore that file with: mysql -u root -ppassword dbname dbname.dump I'm getting the following error: ERROR 1064 at line 118: You have an error in your SQL syntax near 'unique (email) ) TYPE=MyISAM' at line 21 Someone suggested to me that it might be because I have a reserved word for a column name but I checked this option out and I do not. Where did I go wrong? Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB not restoring from dump file
---Original Message- --From: Chris W. Parker [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 10:46 AM --To: [EMAIL PROTECTED] --Subject: DB not restoring from dump file -- --Hey everyone, -- --First post to the MySQL list so please be gentle. -- -- --I recently emptied some tables I shouldn't have in a db of mine and I --want to restore the data from a dump file made a few days ago. -- --MySQL version is 3.23. -- --This is the command I used to create the dump: -- --mysqldump --opt -u root --password=password dbname dbname.dump -- --I'm trying to restore that file with: -- --mysql -u root -ppassword dbname dbname.dump -- --I'm getting the following error: -- --ERROR 1064 at line 118: You have an error in your SQL syntax near --'unique (email) --) TYPE=MyISAM' at line 21 -- -- --Someone suggested to me that it might be because I have a reserved word --for a column name but I checked this option out and I do not. -- --Where did I go wrong? - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 Look at that line and make sure there is not a DOS character line in it. This is what I can think of off the top of my head. Could you provide line 21 and a few lines above that? It could be a missed comma. -- -- --Thanks, --Chris. -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB not restoring from dump file
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] on Monday, October 27, 2003 10:52 AM said: can you send the contents of your dumpfile up to this point. assuming its line 118 of hte dumpfile and 21 of this build table query Line 118 is the beginning of the 'customers' table definition and line 21 is the last line of that definition. Thanks for you help so far. Chris. DUMP: -- MySQL dump 8.22 -- -- Host: localhostDatabase: aardcart - -- Server version 3.23.56 -- -- Table structure for table 'cart' -- DROP TABLE IF EXISTS cart; CREATE TABLE cart ( id int(10) unsigned NOT NULL auto_increment, phpsessid varchar(32) NOT NULL default '', cust_id int(10) unsigned NOT NULL default '0', created datetime NOT NULL default '-00-00 00:00:00', lastaccessed datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (id) ) TYPE=MyISAM; /*!4 ALTER TABLE cart DISABLE KEYS */; -- -- Dumping data for table 'cart' -- LOCK TABLES cart WRITE; INSERT INTO cart VALUES (1,'89ba2322b4b0723bf052ffc2758266a9',6,'2003-10-07 14:50:17','2003-10-10 16:27:49'),(2,'89ba2322b4b0723bf052ffc2758266a9',12,'2003-10-07 16:04:01','2003-10-07 16:05:09'),(10,'cdf88dc1c29adde03b6e3e79942f2a07',0,'2003-10-22 09:48:01','2003-10-22 09:48:01'); /*!4 ALTER TABLE cart ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table 'cart_contents' -- DROP TABLE IF EXISTS cart_contents; CREATE TABLE cart_contents ( id int(10) unsigned NOT NULL auto_increment, cart_id int(10) unsigned NOT NULL default '0', prod_id varchar(25) NOT NULL default '', price float(4,2) unsigned NOT NULL default '0.00', qty smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; /*!4 ALTER TABLE cart_contents DISABLE KEYS */; -- -- Dumping data for table 'cart_contents' -- LOCK TABLES cart_contents WRITE; INSERT INTO cart_contents VALUES (1,1,'testing013',99.00,4),(10,1,'MONUTB',145.00,1),(9,1,'MONUTB',145.00 ,1),(6,1,'MONUTB',145.00,2),(8,1,'ATITCB',194.00,1),(11,10,'testing019', 104.00,1),(12,10,'testing011',99.00,1); /*!4 ALTER TABLE cart_contents ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table 'cart_contents_attributes' -- DROP TABLE IF EXISTS cart_contents_attributes; CREATE TABLE cart_contents_attributes ( cart_cont_id int(10) unsigned NOT NULL default '0', attr_id smallint(5) unsigned NOT NULL default '0', option_id smallint(5) unsigned NOT NULL default '0', cart_id int(10) unsigned NOT NULL default '0' ) TYPE=MyISAM; /*!4 ALTER TABLE cart_contents_attributes DISABLE KEYS */; -- -- Dumping data for table 'cart_contents_attributes' -- LOCK TABLES cart_contents_attributes WRITE; INSERT INTO cart_contents_attributes VALUES (9,40,118,1),(8,24,4,1),(8,7,17,1),(8,6,14,1),(6,40,118,1),(8,2,8,1),(10 ,40,118,1),(12,39,113,10); /*!4 ALTER TABLE cart_contents_attributes ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table 'categories' -- DROP TABLE IF EXISTS categories; CREATE TABLE categories ( id int(10) unsigned NOT NULL auto_increment, name varchar(50) NOT NULL default '', lft smallint(5) unsigned NOT NULL default '0', rgt smallint(5) unsigned NOT NULL default '0', description tinytext NOT NULL, PRIMARY KEY (id) ) TYPE=MyISAM; /*!4 ALTER TABLE categories DISABLE KEYS */; -- -- Dumping data for table 'categories' -- LOCK TABLES categories WRITE; INSERT INTO categories VALUES (31,'Riot Gear',98,99,''),(30,'Entry Tools',96,97,''),(29,'Hydration',94,95,''),(28,'Load Bearing',78,93,''),(27,'Gloves',74,77,''),(26,'Apparel',70,73,''),(21,'A rmor',52,53,''),(20,'WMD',24,51,''),(19,'Less-Lethal',6,23,''),(25,'Flas hlights',68,69,''),(24,'Batons',62,67,''),(23,'Duty Gear',56,61,''),(22,'Holsters',54,55,''),(32,'Gear Bags/Cases',100,101,''),(38,'Root',1,102,''),(39,'Nuclear',49,50,''),(40 ,'Biological',45,48,''),(41,'Chemical',27,44,''),(73,'Force Protection',4,5,''),(74,'Batons',2,3,''); /*!4 ALTER TABLE categories ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table 'customers' -- DROP TABLE IF EXISTS customers; CREATE TABLE customers ( id int(10) unsigned NOT NULL auto_increment, fname varchar(20) NOT NULL default '', lname varchar(20) NOT NULL default '', address1 varchar(40) NOT NULL default '', address2 varchar(40) default '', city varchar(20) NOT NULL default '', state char(2) NOT NULL default '', zip varchar(10) NOT NULL default '', phone varchar(20) default '', fax varchar(20) default '', email varchar(32) NOT NULL default '', newsletter tinyint(1) NOT NULL default '0', password varchar(32) NOT NULL default '', signupdate datetime NOT NULL default '-00-00 00:00:00', lastvisit datetime NOT NULL default '-00-00 00:00:00', type tinyint(3) unsigned NOT NULL default '0', security smallint(6) NOT NULL default '0', company varchar(64) default '', PRIMARY KEY (id), UNIQUE KEY unique (email) ) TYPE=MyISAM;
RE: DB not restoring from dump file
Try changing the keyname unique to email. SO: UNIQUE KEY email (email) ---Original Message- --From: Chris W. Parker [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 11:04 AM --To: [EMAIL PROTECTED] --Cc: [EMAIL PROTECTED] --Subject: RE: DB not restoring from dump file -- --[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] --on Monday, October 27, 2003 10:52 AM said: -- -- can you send the contents of your dumpfile up to this point. -- assuming its line 118 of hte dumpfile and 21 of this build table query -- --Line 118 is the beginning of the 'customers' table definition and line --21 is the last line of that definition. -- --Thanks for you help so far. -- --Chris. -- -- --DUMP: -- MySQL dump 8.22 Host: localhostDatabase: aardcart --- Server version3.23.56 -- Table structure for table 'cart' -- --DROP TABLE IF EXISTS cart; --CREATE TABLE cart ( -- id int(10) unsigned NOT NULL auto_increment, -- phpsessid varchar(32) NOT NULL default '', -- cust_id int(10) unsigned NOT NULL default '0', -- created datetime NOT NULL default '-00-00 00:00:00', -- lastaccessed datetime NOT NULL default '-00-00 00:00:00', -- PRIMARY KEY (id) --) TYPE=MyISAM; -- --/*!4 ALTER TABLE cart DISABLE KEYS */; -- Dumping data for table 'cart' -- -- --LOCK TABLES cart WRITE; --INSERT INTO cart VALUES --(1,'89ba2322b4b0723bf052ffc2758266a9',6,'2003-10-07 --14:50:17','2003-10-10 --16:27:49'),(2,'89ba2322b4b0723bf052ffc2758266a9',12,'2003-10-07 --16:04:01','2003-10-07 --16:05:09'),(10,'cdf88dc1c29adde03b6e3e79942f2a07',0,'2003-10-22 --09:48:01','2003-10-22 09:48:01'); -- --/*!4 ALTER TABLE cart ENABLE KEYS */; --UNLOCK TABLES; -- Table structure for table 'cart_contents' -- --DROP TABLE IF EXISTS cart_contents; --CREATE TABLE cart_contents ( -- id int(10) unsigned NOT NULL auto_increment, -- cart_id int(10) unsigned NOT NULL default '0', -- prod_id varchar(25) NOT NULL default '', -- price float(4,2) unsigned NOT NULL default '0.00', -- qty smallint(5) unsigned NOT NULL default '0', -- PRIMARY KEY (id) --) TYPE=MyISAM; -- --/*!4 ALTER TABLE cart_contents DISABLE KEYS */; -- Dumping data for table 'cart_contents' -- -- --LOCK TABLES cart_contents WRITE; --INSERT INTO cart_contents VALUES --(1,1,'testing013',99.00,4),(10,1,'MONUTB',145.00,1),(9,1,'MONUTB',145 .00 --,1),(6,1,'MONUTB',145.00,2),(8,1,'ATITCB',194.00,1),(11,10,'testing01 9', --104.00,1),(12,10,'testing011',99.00,1); -- --/*!4 ALTER TABLE cart_contents ENABLE KEYS */; --UNLOCK TABLES; -- Table structure for table 'cart_contents_attributes' -- --DROP TABLE IF EXISTS cart_contents_attributes; --CREATE TABLE cart_contents_attributes ( -- cart_cont_id int(10) unsigned NOT NULL default '0', -- attr_id smallint(5) unsigned NOT NULL default '0', -- option_id smallint(5) unsigned NOT NULL default '0', -- cart_id int(10) unsigned NOT NULL default '0' --) TYPE=MyISAM; -- --/*!4 ALTER TABLE cart_contents_attributes DISABLE KEYS */; -- Dumping data for table 'cart_contents_attributes' -- -- --LOCK TABLES cart_contents_attributes WRITE; --INSERT INTO cart_contents_attributes VALUES --(9,40,118,1),(8,24,4,1),(8,7,17,1),(8,6,14,1),(6,40,118,1),(8,2,8,1), (10 --,40,118,1),(12,39,113,10); -- --/*!4 ALTER TABLE cart_contents_attributes ENABLE KEYS */; --UNLOCK TABLES; -- Table structure for table 'categories' -- --DROP TABLE IF EXISTS categories; --CREATE TABLE categories ( -- id int(10) unsigned NOT NULL auto_increment, -- name varchar(50) NOT NULL default '', -- lft smallint(5) unsigned NOT NULL default '0', -- rgt smallint(5) unsigned NOT NULL default '0', -- description tinytext NOT NULL, -- PRIMARY KEY (id) --) TYPE=MyISAM; -- --/*!4 ALTER TABLE categories DISABLE KEYS */; -- Dumping data for table 'categories' -- -- --LOCK TABLES categories WRITE; --INSERT INTO categories VALUES (31,'Riot Gear',98,99,''),(30,'Entry --Tools',96,97,''),(29,'Hydration',94,95,''),(28,'Load --Bearing',78,93,''),(27,'Gloves',74,77,''),(26,'Apparel',70,73,''),(21 ,'A --rmor',52,53,''),(20,'WMD',24,51,''),(19,'Less-Lethal',6,23,''),(25,'F las --hlights',68,69,''),(24,'Batons',62,67,''),(23,'Duty --Gear',56,61,''),(22,'Holsters',54,55,''),(32,'Gear --Bags/Cases',100,101,''),(38,'Root',1,102,''),(39,'Nuclear',49,50,''), (40 --,'Biological',45,48,''),(41,'Chemical',27,44,''),(73,'Force --Protection',4,5,''),(74,'Batons',2,3,''); -- --/*!4 ALTER TABLE categories ENABLE KEYS */; --UNLOCK TABLES; -- Table structure for table 'customers' -- --DROP TABLE IF EXISTS customers; --CREATE TABLE customers ( -- id int(10) unsigned NOT NULL auto_increment, -- fname varchar(20) NOT NULL default '', -- lname varchar(20) NOT NULL default '', -- address1 varchar(40) NOT NULL default '', -- address2 varchar(40) default '', -- city
RE: DB not restoring from dump file
Dathan Vance Pattishall mailto:[EMAIL PROTECTED] on Monday, October 27, 2003 11:28 AM said: Try changing the keyname unique to email. UNIQUE KEY email (email) Thanks, this worked. I ended up having to change two more instances of the same error in different tables. Why would the mysqldump command create a dump file with a syntax error in it? Chris. -- Don't like reformatting your Outlook replies? Now there's relief! http://home.in.tum.de/~jain/software/outlook-quotefix/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB not restoring from dump file
It dumps the contents of the db table in its original form. If you upgrade to a new version-mysql will respect the old table format even if some of the column / table / key names are reserved names in the new mysql version. BUT if you try to add back to the mysql server a dump table that has this reserve key / column / table name then it will see it as an error. Hope this makes sense. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Chris W. Parker [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 1:32 PM --To: Dathan Vance Pattishall; [EMAIL PROTECTED] --Cc: [EMAIL PROTECTED] --Subject: RE: DB not restoring from dump file -- --Dathan Vance Pattishall mailto:[EMAIL PROTECTED] --on Monday, October 27, 2003 11:28 AM said: -- -- Try changing the keyname unique to email. -- -- UNIQUE KEY email (email) -- --Thanks, this worked. -- --I ended up having to change two more instances of the same error in --different tables. -- --Why would the mysqldump command create a dump file with a syntax error --in it? -- -- -- --Chris. --Don't like reformatting your Outlook replies? Now there's relief! --http://home.in.tum.de/~jain/software/outlook-quotefix/ -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB not restoring from dump file
Hi, Well, UNIQUE is a reserved word in all versions of MySQL... As to why mysqldump would create a dump file with a syntax error in it, that's because *you* (or the application creator) used a reserved word for a column/index name (bad idea) and mysqldump, by default, does not put backticks around column/index names. Use the -Q or --quote-names option with mysqldump if you want it to. How was the reserved name allowed in the first place? Because it must have had backticks around it -- probably from something stupid like phpMyAdmin that always puts backticks around everything. :-( Matt - Original Message - From: Dathan Vance Pattishall Sent: Monday, October 27, 2003 4:02 PM Subject: RE: DB not restoring from dump file It dumps the contents of the db table in its original form. If you upgrade to a new version-mysql will respect the old table format even if some of the column / table / key names are reserved names in the new mysql version. BUT if you try to add back to the mysql server a dump table that has this reserve key / column / table name then it will see it as an error. Hope this makes sense. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Chris W. Parker [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 1:32 PM --To: Dathan Vance Pattishall; [EMAIL PROTECTED] --Cc: [EMAIL PROTECTED] --Subject: RE: DB not restoring from dump file -- --Dathan Vance Pattishall mailto:[EMAIL PROTECTED] --on Monday, October 27, 2003 11:28 AM said: -- -- Try changing the keyname unique to email. -- -- UNIQUE KEY email (email) -- --Thanks, this worked. -- --I ended up having to change two more instances of the same error in --different tables. -- --Why would the mysqldump command create a dump file with a syntax error --in it? -- -- -- --Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
