[Simeon]

peterlaursen wrote on Sep 6 2007, 09:20 PM:

If I understand you right this is not reproducable for me with 6.06 and 6.07 final.

Please try 6.07 and tell if it is still an issue for you!

If it still is I probably do not understand and may need a more detailed description (screenshot?)

This is not reproducible in 6.07.

Thanks!

[Simeon]

Hmm.

It seems that the display side of this forum doesn't handle tab indents either!

If I edit my previous post, I can see the indents.. but they don't display. I'll attach a file instead!

Simeon.

P.S. how come, given this is a product for manipulating SQL, we're not allowed to upload .sql files?

[Simeon]

peterlaursen wrote on Sep 6 2007, 09:09 PM:

we cannot help that! MySQL itself does not save your formatting properly. When we copy we get what MySQL has stored!

There are several complaints about this in MySQL bugs page and Forums! And it is even worse with VIEWs!

Try execute SHOW CREATE PROCEDURE yourself and copy/paste into an editor.

Peter, on having a closer look at this myself, I am inclined to disagree!

If I create a stored procedure with new lines, and tabs, and then select the mysql.proc `body` column as text, it includes those new lines and tabs. This is also true using SHOW CREATE PROCEDURE. [as an aside, the 'Results as Text' output option is horribly slow for this, and right pads with spaces to some massive length..]

I have written a query to return the same structure as the 'output as SQLDump' and when I run the results [don't use the text result to do this.. or you'll be saving a lot of unneccessary 'space' characters into the mysql database.] of this directly in the query window against a different database, the Stored Procedures are created with the formatting included.

It seems logical then, to conclude that some combination of factors in the 'Backup database as SQL Dump' / 'Restore from SQLDump' tool is losing the formatting. Given that some of the SPs are visible (via an editor) in the .sql output file with formatting, I am inclined to suspect the Import part of the functionality, but not entirely, as none of the SPs have formatting once imported.

The query I have written to produce the output is:

SELECT concat('n','/*!50003 DROP PROCEDURE IF EXISTS ',specific_name,'*/; nDELIMITER $$n/*!50003 CREATE ', type,' ', specific_name,' (',param_list,')nn',p.body,'*/ $$ nDELIMITER ;n') sp_create

FROM mysql.proc p

WHERE p.db='dbname'

AND p.type = 'PROCEDURE'

A small example of a formatted SP is:

DELIMITER $$

DROP PROCEDURE IF EXISTS `deleteAssessor`$$

CREATE PROCEDURE `deleteAssessor`(IN assessoridIn int(10))

BEGIN

DELETE

FROM assessor_area

WHERE assessorid = assessoridIn;

IF ((SELECT COUNT(enrolmentid) FROM enrolment WHERE assessorid = assessoridIn) > 0) THEN

SELECT false status, 'This Assessor could not be deleted as they have been chosen by one or more students.' message;

ELSE

DELETE

FROM assessor

WHERE assessorid = assessoridIn;

SELECT true status, 'The Assessor has been deleted successfully' message;

END IF;

END$$

DELIMITER ;

I will use my 'work around' in the interim, but this functionality could do to be improved soon as it seems it's not entirely MySQL AB's fault!

Views, on the other hand, are just horrible, and that is entirely MySQL AB's fault!

Simeon.

[Simeon]

Marjolein Katsma wrote on Sep 5 2007, 03:11 AM:

In general, an uninstall process removes only what the install process has created (using a log of the installation process) and does not remove any directories if they are not empty; some uninstallers are even polite enough to mention this.

Actually, this makes sense, since directories created by the installer may now hold data files that are still valuable to the user – the best option in this case would be to issue a warning what could not be removed. In addition, an (un)installer never has any notion of directories created by the running program (or by the user for the running program), so it could not possibly give even a warning about those, let alone remove them.

And what goes for directories and files in them usually also goes (more or less) for registry keys and values: what an (un)installer doesn't know about it cannot remove, though here a whole tree often will be removed when a top-level key is deleted, even if new subkeys had been created by the program.

If I am un-installing a piece of software, my ideal expectation is that it will remove ALL of itself, and leave the system in a state like it had never been installed in the first place. Certainly, any folders created by the user I would expect to be left alone, but all configurations, connection details, preferences, reg keys etc should be removed. Like selling a house really – sure, some aspects of the property are still valuable to you, but you've decided to break your association with it. Having a bag full of keys, or tins of paint for a house you no longer own is not useful.

This seems a safer (and more realistic) approach than demanding that the software developers get their software perfect so that gllitches like the one I have identified can not occur. Obviously, complete removal is not a common approach, or we wouldn't have to re-install windows so regularly to remove left over 'junk' from software we have tried and then un-installed.

Failing that, there are a couple of solutions that I can think of off the top of my head-

A) Let the installer identify existing configuration files and ask if the user wants to retain, or overwrite them.

/:cool: shift any existing configuration files to a 'backup' folder somewhere, and notify the user so that the information can be 'salvaged' if required.

C) Only retain some parts of an existing configuration file (Only the connection details perhaps?)

[Simeon]

peterlaursen wrote on Sep 5 2007, 12:49 AM:

Sorry but what is THAT and what is IT?

did you copy the backup (and renamed it) to the new position? And old connections now appeared as well as you can now save connections?

If it was not like that then please explain in detail what you did and with what results!

Sorry Peter!

THAT = Finding the SQLYog.ini file in application data, deleting it's containing folder, then re-installing 6.06b

IT = my inability to see the results pane, and inability to re-install 6.06 to overwrite the existing 'glitch' in the ini file.

The last installation of 6.06b picked up the existing connections that I created in 6.05 and transfered them across (good..) As I had deleted all of the old SQLYog.ini files, there were no other connections for it to find.

Effectively I have performed a fresh upgrade from 6.05 to 6.06b.

[Simeon]

peterlaursen wrote on Sep 3 2007, 08:48 PM:

In SQlyog 6.06 sqlyog.ini is stored in user's 'application data' folder. Deleting the installation folder will not delete this file!

But this issue should not occur with 6.06 at all.

Once you find the (really used) sqlyog ini, can we have a copy of it? Create a ticket if it contains details you do not want to expose in public?

Thanks, that has solved it. Might need to look at adjusting the un-install process to clean up these left over folders too?

I've blanked out the passwords in the ini file, and attached it for you.

Interestingly, SQLYog 6.06b hasn't created a SQLYog.ini file in the application data folder yet, but seems to have created and updated one in the c:program filesSQLYog Community folder.

[Simeon]

rvps wrote on Aug 31 2007, 05:40 AM:

Is there a way to script create statments for tables? (I use this a lot for creating temp tables, I find myself opening mysql query tool) I could just not be seeing it though.

(Love the product so far)

Thanks!

Rich

I think I understand… you want to get the “CREATE TABLE” statement used to make a table so that you can re-use it to make an equivalent temp table with the same datatypes?

The easiest way I've found to do that is to select the table that you want, then in the results pane, tab across to the 'Objects' tab, and then scroll down a bit to the DDL information section.. The CREATE statement is nicely listed there for you to copy.

[Simeon]

peterlaursen wrote on Aug 30 2007, 09:48 AM:

verified!

Thanks for your research!

Peter, I just had a system lockup, and had to reboot. (Thanks Macromedia!)

Unfortunately for me, this happened while I had the results pane hidden. Now I can't get it back at all. I have uninstalled 6.06, deleted the folder from 'program files', rebooted, and re-installed it, but magically, it has managed to retain the connection list, and the fact that it doesn't want to show me the results pane.

I have un-installed 6.06, and now re-installed 6.05 which works nicely (but that doesn't help with the sqldump bug that 6.06 was created to resolve)

I had a brief snoop around the registry to see if I could find the 'artifacts' left behind by 6.06, but no luck yet.

Can you please give me details as to what I need to remove to get a 'clean' install of 6.06, or alternatively, if you have a version available that fixes both the 6.05 bugs, and the 'Results pane' bug I'd be keen to give that a try.

Thanks,

Simeon.

[Simeon]

This fault is reproducable in 6.06

Open a query pane. Ctrl+2 to hide the results pane. Ctrl+t to open a new tab. The tab opens with the results pane hidden. Now.. Ctrl+2 to unhide the results pane.

It doesn't un-hide..

The explorer pane seems to handle hiding/unhiding appropriately regardless.

Perhaps something is being inherited incorrectly?

To reproduce my original fault: Close SQLYog while the active query tab has the results pane hidden. Re-open SQLYog, and try to unhide the results pane. (take a copy of your sqlyog.ini file first though!)

[Simeon]

Actually.. please delete this thread – it turns out that the destination server isn't configured to support InnoDB, and is converting the create table statement to MyISAM all by itself.

[Simeon]

peterlaursen wrote on Aug 16 2007, 08:50 PM:

Actually there is a little bit silly consequence of what we are doing now!

If you have a database without a single table, but lot of ROUTINES, you cannot export anything at all. The program forces you to 'select at least one table'.

While you're there… would it be possible to add a further check box to turn off the “DEFINER” part of the statement? I develop locally (where I have permissions to do anything) and then deploy onto a third party hosted server (where I have the bare minimum permissions) and currently I 'find and replace' the DEFINER sections to remove them.

Not a biggie, but it would be nice to have.

[Simeon]

😀

peterlaursen wrote on Aug 18 2007, 12:57 AM:

Please verify the fix for this with

Enterprise (registered): http://www.webyog.com/downloads/betas/not_…QLyog606Ent.exe

Enterprise (trial): http://www.webyog.com/downloads/betas/not_…606EntTrial.exe

Community: http://www.webyog.com/downloads/betas/not_…d/SQLyog606.exe

Yes, this has fixed the problem outlined here, and also the issue raised in “Restore From Sql Dump 'glitch' For Stored Procedure”

Thanks for your prompt fix – this has already made life much easier!

[Simeon]

peterlaursen wrote on Aug 17 2007, 08:54 AM:

We had an almost similar report 20 hours ago! We are testing the fix for this now!

I think the problem the “comment inside conditional comment” conctruction inside the CREATE PROCEDURE statement like

/*!50003 …

….

— a comment

…

*/

Coul you please try to remove '– a comment' and verify if that solves the issue?

It's not that! Almost all of my SPs have comments inside (I like to call it 'documentation'!) and only a few of them have this problem. I guess it could be to do with the position of the comment somehow?

I have tried to see any common features between the affected SPs, but whatever it is, it's not obvious..

[Simeon]

Simeon wrote on Aug 16 2007, 09:21 AM:

100% sure that I wasn't Ctrl+F2ing.. and the divider was in the middle when I hid it.

I was completely locked, so I uninstalled, restarted, and reinstalled.. which was completely unnecessary now that I know about the sqlyog.ini!

I'll see if I can replicate the problem and let you know.

Hmm.

No, I can't seem to make it happen at the moment.

[Simeon]

peterlaursen wrote on Aug 15 2007, 08:18 PM:

very strange indeed! Ctrl+2 works perfectly for me! and we have no other report of such issue.

You are sure you don't Ctrl+F2 instead?

Also the divider has not been moved to lowest postion?

If your are totally 'locked' then you can try renaming the sqlyog.ini file before starting SQLyog. A new one will be created from scratch then

100% sure that I wasn't Ctrl+F2ing.. and the divider was in the middle when I hid it.

I was completely locked, so I uninstalled, restarted, and reinstalled.. which was completely unnecessary now that I know about the sqlyog.ini!

I'll see if I can replicate the problem and let you know.

[Author]

Posts