mysql restore not working with tables that have special characters (," ')

[esscher]

In my database that I need to backup and restore, I have a lot of tables that have special characters such as parenthesis, double and single quotes, and commas.  Here is an example of one that causes problems with restore:

 

2,,3,3',4,4',5,7-heptahydroxyflavanbioside

 

It seems like backing up using the standard command:

c:> mysqldump -u root -ppassword -databases mydb > mydb.sql

 

Works, because I can read through the sql file and its all there.

 

Problem is when i try to restore with the command:

 

C:> mysql -u root -ppassword mydb_to_restore_to < mydb.sql

 

It seems to process fine and returns to the command prompt with no errors.  However, after checking the database, nothing has been restored.  The whole process works fine with databases that don't have funky characters.

 

help?

 

Scott

[fenway]

You probably need to specify how to delimit the fields.

[esscher]

Do I need to do that during the dump phase or during the restore phase?  Any examples?

[fenway]

It's been a while since I've dealt with mysqldump, don't have access to my scripts, but yes, you need to specify the same set for in/out operations; check the refman page.

[esscher]

ok.. so in mysqldump there is an option --fields-terminated-by= but I can't seem to get it to work on the command line.

 

Here is the command I use:

c:> mysqldump -u root -ppassword --fields-terminated-by="|" db > db.sql

 

But get an error that I don't understand:

 

mysqldump: You must use option --tab with --fields-...

 

Scott

[fenway]

Hmm... shouldn't be an issue if you're dumping SQL only.... could you post some of the output from the dump initially, without any options?

 

Also, I hope you're not putting your password on the command line like that, it will end up in log files!

[esscher]

No.. its not a real password.  That would be a dumb password to have anyway!

 

I wasn't sure how much of sql file you want but here is the top portion until the end of the first table.

 

-- MySQL dump 10.11
--
-- Host: localhost    Database: phyto
-- ------------------------------------------------------
-- Server version       5.0.45-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `phyto`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `phyto` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `phyto`;

--
-- Table structure for table `1,3,20-trihydroxy-5,24-withadienolide`
--

DROP TABLE IF EXISTS `1,3,20-trihydroxy-5,24-withadienolide`;
CREATE TABLE `1,3,20-trihydroxy-5,24-withadienolide` (
  `id` int(11) NOT NULL auto_increment,
  `plant_source` varchar(255) NOT NULL,
  `part1` varchar(255) default NULL,
  `low1` varchar(255) default NULL,
  `high1` varchar(255) default NULL,
  `part2` varchar(255) default NULL,
  `low2` varchar(255) default NULL,
  `high2` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `1,3,20-trihydroxy-5,24-withadienolide`
--

LOCK TABLES `1,3,20-trihydroxy-5,24-withadienolide` WRITE;
/*!40000 ALTER TABLE `1,3,20-trihydroxy-5,24-withadienolide` DISABLE KEYS */;
INSERT INTO `1,3,20-trihydroxy-5,24-withadienolide` VALUES (1,'ashwagandha','Plant','unknown','unknown','none','none','none'),(2,'Ashwaganda plant',NULL,NULL,NULL,NULL,NULL,NULL);
/*!40000 ALTER TABLE `1,3,20-trihydroxy-5,24-withadienolide` ENABLE KEYS */;
UNLOCK TABLES;

 

 

[fenway]

I meant it's bad form to put *any* password on the command line.

 

I also didn't completely understand that it was the table names that had funky characters.

 

If you try and run these commands from phpmyadmin/CLI, what happens? Same thing?

[esscher]

I see what you mean about the pw.. good tip. 

 

The same thing happens with phpmyadmin, and with navicat. 

[fenway]

Interesting... I ran these statements on 4.1 (though not all of the server mode stuff), and it was fine.

[esscher]

Alright, so I think the problem was part human error, part, well, syntax related ---

 

The human part is that I realized that I had only tried to restore with phpmyadmin and not both backup and then restore. 

 

The syntax, for lack of better verbage, is that on the backup part in phpmyadmin there is an option to "Enclose table and field names with backquotes".  This option apparently was not used or needed to be initiated on the command line when I did my backup.  I haven't tested this out yet, but I guess I'll just assume for now. 

 

Regardless, when I did the backup and restore w/ phpmyadmin it worked.  Problem solved.

 

Esscher