I'm testing SQLyog 6.53RC2 under Wine on Ubuntu 8.04 and came across two problems during DB resore from SQL dump.

1. when I have two or more databases opened in one connection the data are always restored to the first database in the connection list. This can be potentially dangerous as data in the wrong database could be overwritten. The work around is to assign only one database per connection. If working with multiple databases the switching back and forth can be annoying.

2. During DB restore comment lines starting with '--' right after the first BEGIN statement are removed. Also comment lines that are enclosed with /* */ but start with '--' are also removed. In the following examples lines in red are removed during restore.

I am not sure I understand everything!

1) when I have two or more databases opened in one connection the data are always restored to the first database in the connection list .. No! It will restore to the currently selected database (unless there is a USE statement in the file). To select a database highlight in the Object Browser (the left pane) or use the dropwown for selection


2) I think you are referring to comments inside 'stored programs' (like Stored Procedures)? This is a server issue (and has been discussed here before - please search a little!). What is the server version? What happnes if you import the same script from command line client?

SQLyog *does not* remove anything from a script that is sent to a server. Not comments either. MySQL is desinged to handle comments server-side. And we do not do otherwise (for the last 2-3 years).


BTW: Do not use an beta or RC when a later stable version (6.56) is available!

Re: 1
I'm sorry, you were right about the USE statement in the SQL dump file. I should have checked that myself.

Re: 2
I didn't know that server is removing comments as these were accepted fine when the procedure was created. I did a little test on an XP machine with EMS MySQL Manager and this program has imported these procedures as they were - no comments or blank lines removed. EMS must be doing some pre-processing itself then.

see this

D:\MySQL\MySQL Server 5.0\bin>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1489
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test1;
Database changed
mysql> delimiter ##
mysql> create procedure sp2()
-> -- comment 1
-> Begin
-> -- comment 2
-> select 'Hello';
-> End##
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> show create procedure sp2;
+-----------+-----------------------------------------+-------------------------
-----------------------------------------------------+
| Procedure | sql_mode | Create Procedure
|
+-----------+-----------------------------------------+-------------------------
-----------------------------------------------------+
| sp2 | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER | CREATE DEFINER=`root`@`l
ocalhost` PROCEDURE `sp2`()
Begin
select 'Hello';
End |
+-----------+-----------------------------------------+-------------------------
-----------------------------------------------------+
1 row in set (0.00 sec)


this means the MySQL commandline client itself removing the comments! this is strange

no .. I believe it is the server that does remove comments inside a SP! I think I recall that server bug.
What is the server version?

Are you telling that EMS stores a copy locally? Or how should I understand?


But also the command line will remove some style of comments but not all (but should not inside a Stored Procdure).
This was alos discussed recently in bug report (I think I remember that the client strips --comments but not #comments)

To see what the server receives from various clients you could enable the general server log.

Please see attached. Comments are not lost on my system. Server 5.0.51b.
(I think the server will have to be *very recent* to prevent this to happen)

Click to view attachment

posting an example identical you yours:

Click to view attachment

DELIMITER $$
DROP PROCEDURE IF EXISTS `test30`.`myp1`$$
CREATE PROCEDURE `test30`.`myp1`()
-- blah
BEGIN
-- blih
DELETE FROM client WHERE Client = 'abcd';
-- bluh
SELECT * FROM client;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `test30`.`myp2`$$
CREATE PROCEDURE `test30`.`myp2`()
# blah
BEGIN
# blih
DELETE FROM client WHERE Client = 'abcd';
# bluh
SELECT * FROM client;
END$$
DELIMITER;

/*
.. now both
show create procedure `test30``myp1`;
show create procedure `test30``myp2`;

will retain blih and bluh commments (as they belong to SP definition), but blah comment is stripped (by the server) as it is *outside* SP definition (not between BEGIN .. END). This is correct client behaviour (and correct server behavior too wiht 5.0.51b). */

-- Now try command line:

DELIMITER $$
DROP PROCEDURE IF EXISTS `test30`.`myp3`$$
CREATE PROCEDURE `test30`.`myp3`()
-- blah
BEGIN
-- blih
DELETE FROM client WHERE Client = 'abcd';
-- bluh
SELECT * FROM client;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `test30`.`myp4`$$
CREATE PROCEDURE `test30`.`myp4`()
# blah
BEGIN
# blih
DELETE FROM client WHERE Client = 'abcd';
# bluh
SELECT * FROM client;
END$$
DELIMITER;

show create procedure `test30``myp3`; -- all comments stripped (client side)
show create procedure `test30``myp4`; -- all comments stripped (client side)


.. so please tell server version!

The comments are not removed when you create a procedure in SQLyog but when restoring from the SQL dump.
Attached is small SQL dump I tested with.

In EMS I open the SQL dump in the script editor and then run it. Then, when I look at the procedure in the SQLyog it looks exactly the same as in the dump, with all comments and even new lines retained.

I understand that SQLyog passes data to the server for processing. I'm just saying that EMS is doing something extra (or differently) as the procedure retains all its formatting.

Thanks for your clarification!

We will check details. Still please tell the server version!

MySQL servers I used were 5.0.45 and 5.0.51

HI,
Please see the screenshot,
1) Created a sp1 with comment starting from 1st column(without space).
2) Created a sp2 with comment starting from 2nd column(with leading space)

Dumped these two procedures and restored,
For sp1 comment does not show while for sp2 it shows correctly.
We ,Will look into this and get back to you.

In very old version while restoring we stripped most comments to save bandwidth. We stopped doing this, but this was not complete, and it must be considered a bug!

Again, Copy and paste the contents of dump to sqlyog editor and "Execute All" , works fine.