Home / server / Questions / 373919
In Process
Minhaj
Asked: 2012-03-28 05:05:01 +0800 CST

Mysql dump restoring

  • 772

I ran into a big trouble. I made mysql dump using version 5.0.77 without setting the compatibility mode (I wasn't aware of this problem). Actual mysql server is version 4.0.18. Today our database crashed. Lots of tables lost. While trying to restore the dump, it's returning error "ERROR 1193 at line 18: Unknown system variable 'character_set_client'". Is there any way we can restore the db?

Thanks a lot for you help.

mysql

2 Answers

  1. It may well be worth installing, on another box if required, mysql 5.0.77, restoring, and then re-dumping with the appropriate options.

    This isn't an elegant solution, but if you're in "big trouble" and need something that works and fast, this is the first thing that jumped into my mind.

    • 2

  2. Hope your dump file isn't too big. Copy it and edit it. If it's really large, use sed or a script. If it's small enough, vim it.

    sed '/\/*!40101 SET character_set_client'/d < original > copy

    Anywhere you see the string /*!40101 SET character_set_client delete that line. The server doesn't understand it anyway and it won't affect your data.

    /*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `location` (
  `locationId` int(11) NOT NULL DEFAULT '0',
  `releaseDate` date NOT NULL DEFAULT '0000-00-00',
  `id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`releaseDate`,`locationId`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
    • 1