Stored Procedure Formatting Lost On Backup/restore

[Simeon]

I have carefully formatted my Stored Procedures for readability on my development system, but when I “Backup database as SQL Dump” and then restore it to another (or the same) database, all my tab indenting is lost.

Some of the stored procedures show in the backup dump file (as viewed in notepad) as having indentation still, others replace the indent with a 'box' character. Regardless of how they appear in the notepad file, all SP's appear in SQLYog (alter Stored Procedure, and objects tab as well) once restored as having an indent of one (and only one) space for everything after the 'BEGIN' keyword up to the 'DELIMITER ;' statement.

Several of my SP's are approaching 100 lines, and would normally have up to 5 layers of indent. No indentation makes maintaining and debugging these a real nightmare! MySQL stores the indentations internally with no issue, it's only once a backup and restore is done that things go haywire.

Could this please be changed?

(Currently running 6.06b, MySQL on both linux and windows)

Thanks Peter.

[peterlaursen]

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.

[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]

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?

[peterlaursen]

the 'box' character is probably a .

Upon request we will go deeper into the formatting thing!

About Forums:

Forums are HTML and multiple are stripped to one! Clik the icon 'wrap in code tags' for text that should be formatted. Almost all such software does the same!

Except for .txt and common and compressed image formats (.jpg, .gif, .png) I recommend that you zip attachements. For two reasons:

* to save disk space on the server

* because any format that can directly infect a computer with virus etc. and be used for 'code injection' we cannot accept! This is not to protect ourselves actually (because Linux does not care about file extensions) but in order to protect our users against malicious attacks!

[peterlaursen]

The formatting issue:

You were right and I was wrong. It is basically a SQLyog issue. It has been solved in the development tree.

However, with some (mostly early 5.0.x) server versions there may be server issues too.

[Author]

Posts