Problems with mysqldump

[ChrisMacPherson]

Hi All,

I have been using mysqldump for a while now, but have just come across a problem.

I am using the mysqldump program from PHP to dump the database into a file. This works fine.

I then try to feed that sql from the file back into mysql to recreate the database, like a backup procedure. This produces an error.

When I look at the dumped database file it looks like this which I have never seen before:

/*!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 */;
DROP TABLE IF EXISTS `chc_areainfo`;
CREATE TABLE `example_table` (
  `foo` varchar(25) NOT NULL default '',
  `bar` text NOT NULL,
.........

I only show the top as it is the code at the top that is causing the error:

MySQL Error : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101' at line 7

I ahve been trying to work out how to get rid of the stange lines at the top of the dumped file (i.e '/*!40101') but cant work out how.

I know these lines are for compatibility with other mysql versions.

This is all part of an automatic script to alter a database so I would not just be able to edit the sql file manually to remove these error causing lines.

Any help would be appreciated, cant find much help elsewhere!



Chris MacPherson

 

[TonyGroves]

I can't understand why you're getting a syntax error there; it looks perfectly normal. The syntax "/*!40101 ... */" is treated as a comment except by MySQL 4.1.1 (40101) or later, which executes the embedded SQL. Has the file been edited in some way?

You might be able to use the -f option in the mysql command, which forces execution to continue even after a SQL error.

 

[ChrisMacPherson]

hi, thanks for quick reply.

No the sql hasn't been changed in any way. I am running version 4.1.7.

I have actually tried to complete my solution using a different path now (which I should probably have been using anyway) because I cant find any information on this problem.

Instead of dumping the database, performing tasks and then recreating it on any errors, I have converted all my tables to innodb and am using a transaction. MUCH easier.

I think that -f option might have been useful though, I did read about that, but didn't think to use it though.

Chris MacPherson