Restore From Dump To Different Db

[maxhugen]

I need to restore an sql dump, but to a different (or new?) database, so that I have a copy of the original. The reason I'm trying to do this is because some records from certain tables were deleted accidentally, and I have to restore just those records – not the entire database. Any advice would be most appreciated!

[edit] I dumped a test database using SQLyog, just to have a look at the syntax. It looks like the ONLY two references to the database name are:

CREATE DATABASE /*!32312 IF NOT EXISTS*/`taskplus` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

USE `taskplus`;

If I just change those two database names to, say, taskplus_restored, would that be sufficient?

[edit2] I just tried to restore a test db, but it failed, with error code 1064. 🙁 I don't understand why it would fail, after just dumping it using MySQLyog!

Max

[ashwin]

Hello,

Quote:

If I just change those two database names to, say, taskplus_restored, would that be sufficient?

Yes its enough, this will work as you expected.

Quote:

I just tried to restore a test db, but it failed, with error code 1064. I don't understand why it would fail, after just dumping it using MySQLyog!

No idea about this, We need to know which query is failed, error 1064 is syntax error.

The query and error is logged into:

Windows XP/2003:

C:Application Data{user}SQLyogsqlyog.err

Windows Vista/7:

C:Users{user}AppDataRoamingSQLyogsqlyog.err

First delete the above file, Do restore from SQLdump, check the error file(sqlyog.err)created, and you can see which query is failed.

[peterlaursen]

Also you should tell the server versions involved. 

[maxhugen]

The err.log showed this:

Query:

/*!50003 CREATE PROCEDURE `DeleteProject`( IN Fproject_id INTEGER, OUT Fresult INTEGER )
BEGIN DECLARE iBeforeCount INT; DECLARE iDeleteCount INT; Set Fresult = 0; START TRANSACTION; foo:BEGIN #element delete – should always be OK

SELECT COUNT(*) FROM element WHERE project_id=Fproject_id INTO iBeforeCount; DELETE FROM element WHERE project_id = Fproject_id; SET iDeleteCount = ROW_COUNT(); IF iBeforeCount <> iDeleteCount THEN leave foo; END IF; #elementtype delete – should always be OK

SELECT COUNT(*) FROM elementtype WHERE project_id=Fproject_id INTO iBeforeCount; DELETE FROM elementtype WHERE project_id = Fproject_id; SET iDeleteCount = ROW_COUNT(); IF iBeforeCount <> iDeleteCount THEN leave foo; END IF; #module element – should always be OK

SELECT COUNT(*) FROM moduleelement, module WHERE moduleelement.module_id = module.module_id AND module.project_id = Fproject_id INTO iBeforeCount; DELETE moduleelement FROM moduleelement INNER JOIN module ON module.module_id=moduleelement.module_id WHERE module.project_id = Fproject_id; SET iDeleteCount = ROW_COUNT(); IF iBeforeCount <> iDeleteCount THEN leave foo; END IF; #module delete – should always be OK

SELECT COUNT(*) FROM module WHERE project_id=Fproject_id INTO iBeforeCount; DELETE FROM module WHERE project_id = Fproject_id; SET iDeleteCount = ROW_COUNT(); IF iBeforeCount <> iDeleteCount THEN leave foo; END IF; #job delete – could be locked

SELECT COUNT(*) FROM job WHERE project_id=Fproject_id INTO iBeforeCount; DELETE FROM job WHERE project_id = Fproject_id; SET iDeleteCount = ROW_COUNT(); IF iBeforeCount <> iDeleteCount THEN leave foo; END IF; #projectmember delete – could be locked

SELECT COUNT(*) FROM projectmember WHERE project_id=Fproject_id INTO iBeforeCount; DELETE FROM projectmember WHERE project_id = Fproject_id; SET iDeleteCount = ROW_COUNT(); IF iBeforeCount <> iDeleteCount THEN leave foo; END IF; #project delete – could be locked

SELECT COUNT(*) FROM project WHERE project_id=Fproject_id INTO iBeforeCount; DELETE FROM project WHERE project_id = Fproject_id; SET iDeleteCount = ROW_COUNT(); IF iBeforeCount <> iDeleteCount THEN leave foo; END IF; #

INSERT INTO update_log (task_id, update_type) VALUES (Ftask_id, 3); COMMIT; SET Fresult = 1; END; If Fresult = 0 THEN ROLLBACK; END IF; END */$$
DELIMITER ;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

.. but I think I had SHOW CREATE returned by MySQL returned as single-line once.  Very weird.  Did you have the dump opened in an editor?

Anyway: Please

1) Execute SHOW CREATE PROCEDURE … . Do you get a single line or not (display in TEXT-mode in SQLyog) 

2) Could you try to backup again and see if it repeats itself?

The problem definitely is that all what follows # (on same line) will be a comment.  

[maxhugen]

1. Yes, the comment was on one line:

#element delete – should always be OK

2. Yes, this time the Restore was successful !

Peter, while researching this problem, I I think I did find a reference (by you) regarding possible issues with comments in stored procedures during Restore, but I can't find that post now!

Is there a potential problem using the # comment? Should I use /* … */ or — instead?

[peterlaursen]

I think you are referring to this one:

http://www.webyog.com/faq/content/26/174/en/nested-c_style-comments-fail-with-mysql-51.html

In MySQL 5.1.x before 5.1.35 you cannot use /* comment */ inside a 'stored program'.

Actually I have posted one more bug report to get full clarification: http://bugs.mysql.com/bug.php?id=57509

I have never heard about the problem with #comment before (and I mostly use — comment myself).

I think I will report this issue to MySQL as a 'hard to reproduce issue that SHOW CREATE sometimes randomly fails to linebreak after #comment'.

We (you and I) both experienced this once.

[peterlaursen]

I have posted http://bugs.mysql.com/bug.php?id=57861

I am not optimistic about it unless more people report the same!

[maxhugen]

Thanks for your help Peter. On a positive note, this was the first time I had to Restore something, so I've learnt a few things. 😎

[peterlaursen]

Actually we have an issue here: http://code.google.com/p/sqlyog/issues/detail?id=733

Let's say you have a SP-body reading

Code:

BEGIN

— comment

#comment

/* comment */

END

Now try to restore

1) from SQLyog editor: all comments are restored

2) from SQLyog 'restore SQL dump': only /* comment */ is restored

3) using 'source'-statement from command-line client: no comments are restored

It is documented (somewhere) that command-line strips out certain types of comments and not others.  I do not think we should today.  Basically I think this was originally done in order to save bandwidth, And such is not interesting any more. The server will handle all 3 types of comment.

[Author]

Posts