mysqldump creates conditional comments that lead to errors when restoring

Question

the mySQL Dumps created with mysqldump contain conditional comments like this:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_520_ci,
  PRIMARY KEY (`meta_id`),
  KEY `comment_id` (`comment_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

or this:

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

They lead to errors when importing the dump back to the db because

@saved_cs_client
@OLD_SQL_MODE
...

are null. "Cannot set character_set_client to null" or similiar.

When I remove all these conditional comments the import is no problem and everything is fine.

My questions: Is it save to remove these comments or to force the import in case of SQL errors? Which function do these statements have?

I understand that some variables are stored and after all is done are restored. But Why?

see if this helps http://stackoverflow.com/questions/1916392/how-can-i-get-rid-of-these-comments-in-a-mysql-dump#1917437 — treyBake, May 11 '17 at 10:43
But there's a bit in there about removing them and the advice on it (which is basically - don't) — treyBake, May 11 '17 at 10:59
None of these values should every be null, unless you are editing or manipulating dump files and doing so improperly -- losing the statements earlier in the file that should have set them. Are you modifying the dump file in any way? If not, which exact version of the mysqldump utility are you running? Find this with `mysqldump -V`. — Michael - sqlbot, May 11 '17 at 15:16
Does anyone has an idea what these conditional comments are for? And why are the values null? Additional information: I use my "mySqlDumper" for restoring. Maybe this program manipulates the values? — Jochen Kunze, May 15 '17 at 13:28

mysqldump creates conditional comments that lead to errors when restoring

0 Answers0