Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
4.x is end of life? Planning to upgrade should be in your future. –  Zoredache Mar 27 '12 at 15:30

2 Answers 2

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.

share|improve this answer
    
Thanks dude! pretty good idea. I'm giving it a shot. –  Saif Mar 27 '12 at 13:18
    
Didn't worked :( I restored the dump to a mysql server 5.0.77 and took dump from it with --compatible=mysql40. But still mysql dump kept the "character_set_client" info. So unable to restore it. Damn! it's running production server and clients are all over me :( –  Saif Mar 27 '12 at 13:39
    
@saif Can you run your production off the 5x version of the database? –  Bart Silverstrim Mar 27 '12 at 13:47
    
On the plus side you know that that particular flag wasn't the issue... –  Bart Silverstrim Mar 27 '12 at 13:48
    
This just reminded me... I need to go test all my backups. –  Safado Mar 27 '12 at 13:50

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 */;
share|improve this answer
1  
I'd suggest doing this from a copy of the dump, just in case... –  Bart Silverstrim Mar 27 '12 at 13:50
    
@BartSilverstrim I'll just go ahead and bold that then :) –  Jeff Ferland Mar 27 '12 at 13:53
1  
Emphasis for backup and copies as it's an easy thing to overlook when panicking! –  Bart Silverstrim Mar 27 '12 at 13:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.