MySQL Bugs: #6497: Formatting of SQL Dump file

Bug #6497	Formatting of SQL Dump file
Submitted:	8 Nov 2004 13:12	Modified:	16 Nov 2004 9:36
Reporter:	Robbie Mappin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S3 (Non-critical)
Version:	1.0.14	OS:	Windows (WinXP)
Assigned to:	Mike Lischke	CPU Architecture:	Any

Description:
First of all, this new GUI version is far better than previous and has really come on leaps and bounds.  Issues that I have found however are as follows:

1) See bug in 'How to repeat' field.

2) The SQL backup/dump file that it creates has the following problems:

a) Line breaks are needed between the creation of the tables and the insert blocks.  The whole thing is a bit of a jumble at the moment.

b) All of the table names are set to lowercase when I want them to be uppercase.

c) Comment fields between the tables would be nice as well just to break up the statements.  If you need examples of this, please let me know.

d) It would be nice if the backup could be-recreated in the order that the tables were created.  I know this is a big ask, but when I'm cross-checking and sync-ing DDLs against the backup files its far easier when the order of the create table statements are the same.

How to repeat:
I can create a backup project without moving a database into the right-hand field (by double-clicking) and save it without being warned.  This means that when the schedule actually tries to run it goes pear shaped and warns me that there is something wrong with task scheduler.  I then can't click on/delete the project until I go in and delete the task schedule, which would be difficult for a newbie to understand - I think.

If I can't save a backup project without selecting at least one DB then this would trap all of this hassle.

Rgds

Robbie

Thank you Robbie. We are very glad to see the increasing number of satisfied users. The only thing I like to mention, though, is that in the future please split reports like this into two. See, there is the dump output problem and the bug. It might well be different people work on that. Thank you.

Hi Mike

Sorry for the dual-submit.  Didn't realise you actually take this as a proper bug list report!!!  That used to Microsoft 'into the ether' submit!!!

I don't think the little save project bug is a major issue, but the formatting of the SQL dump is something very important (well to me anyway).

Just to add to this, noticed that if I have the 'Add DROP TABLE Statements' checkbox selected then I do get a better output as this option includes extra line breaks, which at least splits up the tables/insert 'chunks'.

If you want me to email you an example of the layout that I'm thinking of then please let me know.

Regards

Robbie

The bug is fixed, but writing out the tables in their creation order is currently not possible. Wie need a little but important change in the UI for this. Yet there are more important tasks waiting currently.

Mike

Thanks Mike

Look forward to getting the update.

Could you please tell a bit more about what comments you expect between tables?

Hi Mike

The kind of format I was meaning is as follows:

# --------------------------------------------------
# Table structure for table 'CART'
# --------------------------------------------------

DROP TABLE IF EXISTS `CART`;
CREATE TABLE `CART` (
  `CARTID` INT NOT NULL AUTO_INCREMENT,
  `WEBUSERID` INT DEFAULT 0,
  `ACCOUNTID` INT DEFAULT 0,
  `YOURREF` VARCHAR(15),

  INDEX `indxCARTID` (`CARTID`),
  INDEX `indxWEBUSERID` (`WEBUSERID`)
);

# --------------------------------------------------
# Dumping data for table 'CART'
# --------------------------------------------------

LOCK TABLES `CART` WRITE;
INSERT INTO `CART` VALUES ('1','2','3','4');
INSERT INTO `CART` VALUES ('A','B','C','D');
UNLOCK TABLES;

# --------------------------------------------------
# Table structure for table 'COLOURS'
# --------------------------------------------------

DROP TABLE IF EXISTS `COLOURS`;
CREATE TABLE `COLOURS` (
  `COLOURID` VARCHAR(30) NOT NULL,
  `COLOURNAME` VARCHAR(50),
  `LANGID` VARCHAR(30),
  `COMMENT` VARCHAR(255),
  
  INDEX `indxCOLOURID` (`COLOURID`)
);

# --------------------------------------------------
# Dumping data for table 'COLOURS'
# --------------------------------------------------

LOCK TABLES `COLOURS` WRITE;
INSERT INTO `COLOURS` VALUES ('BLACK','Black','GB','');
INSERT INTO `COLOURS` VALUES ('SILVER','Silver','GB','');
UNLOCK TABLES;

The real biggie is being able to have the tables listed in the order that I want, ie either by giving me the option to set the order before a backup or by creation date, but you have already explained that this is an enhancement for future consideration.

With regard to the formatting, the comment lines just act as a visual delimiter to allow the viewer to differenciate between tables and data dumps.  Hopefully you will agree that the above is a more readable format than a straight dump.

Also notice that each row has it's own insert line as well, but I should probably get used to your multiple entries per line method as it is probably more efficient.

Thanks for your time.

Rgds

Robbie

Robbie, thank you for your example. I'm going to close this bug entry as the actual bug is fixed now. For the formatting I'm afraid this won't happen in the near future. We add a Todo list entry for it, but more than adding an empty line here and there is not yet planned. There are simply too many other things to do.

Mike

Hi Mike

Thanks for the feedback.

No probs re the formatting request - it is more of an enhancement than a bug/critical requirement.

As it is now it does the job.

Keep up the good work.

Rgds Robbie