[Suggestion] Custom Header / Footer for TMyDump?

Justmade · Post by **Justmade** » Thu 23 Dec 2010 04:29

This is only a suggestion.

I was wondering if it is OK to add a custom header / footer pair for TMyDump to insert in the header / footer part of the generated SQL.

What I need for now :
1. Disable 0 check for AutoInc Fields because some of my tables has 0 in the AutoInc field and when restore, the 0 change to 1 and the 1 cause duplication key.
2. Disable trigger because some of the case the backup is data only and when restoring data, should not use the trigger to reprocess those data.

Currently I can modify your source code every version but if there can be properties to contain those custom header / footer, it will be much more convenience.

Thanks in advance for your kind considerations.

AndreyZ · Post by **AndreyZ** » Fri 24 Dec 2010 11:48

Hello,

1. This change is performed by server and we can't influence that. You can check it in the following way:
- create a new table (testtbl) with one autoincrement field (ID);
- try to execute this SQL code: insert into testtbl(id) values(0)
2. To avoid this problem you should backup a table with the structure (for this set TMyDump.Objects.doTables to True). In this case triggers are created after data restoring and aren't executed.

Justmade · Post by **Justmade** » Mon 27 Dec 2010 01:03

1. That can be avoided by adding setting in header and footer :
'/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=''NO_AUTO_VALUE_ON_ZERO'' */;'

'/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

Actually, some other sql dumping tools are doing this and this is suggested in this former post # 5 message :
http://www.devart.com/forums/viewtopic.php?t=19739

2. The point of not backing up structure is that when a database are having frequent changing in table structures and store procedure / triggers and the like, if backup all structures, a restore had to be re-patched to the updated version, while a data only backup can avoid the re-patch.

This can be done with using variables
Check the following blog point 2
http://illya-keeplearning.blogspot.com/ ... ql-50.html
or the following mysql forum post :
http://forums.mysql.com/read.php?99,123 ... msg-239081
If there is an options flag that can generate below code before and after the inserts, it would be even better :
ALTER TABLE tablename DISABLE TRIGGER ALL
-- do inserts here
ALTER TABLE tablename ENABLE TRIGGER ALL

However, the key point of my suggestion is to add a custom script header and script footer properties so that everyone can customize their output script with special need.

AndreyZ · Post by **AndreyZ** » Mon 27 Dec 2010 16:13

We will add such functionality in one of the next MyDAC builds.

Dimon · Post by **Dimon** » Thu 13 Jan 2011 15:31

The point is that MySQL server doesn't allow to disable all triggers, therefore we can't add the 'ALTER TABLE tablename DISABLE TRIGGER ALL' statement to backup script.

Justmade · Post by **Justmade** » Thu 13 Jan 2011 16:07

Dimon wrote:The point is that MySQL server doesn't allow to disable all triggers, therefore we can't add the 'ALTER TABLE tablename DISABLE TRIGGER ALL' statement to backup script.

You are right, I think I had misread this web page

http://forge.mysql.com/worklog/task.php?id=2825
It is be a feature request rather then a current feature.