MySQL Bugs: #50016: RFE: --truncate-table in mysqldump

Bug #50016	RFE: --truncate-table in mysqldump
Submitted:	31 Dec 2009 20:54	Modified:	9 Dec 2010 19:01
Reporter:	Kevin Benton	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S4 (Feature request)
Version:	All	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump

Description:
Please add --add-truncate-table option to mysqldump.  This would be useful when all that is desired is a data refresh from one host to another especially if the desire is to not recreate the target table.  Use of --add-truncate-table should be incompatible with and automatically turn off all DDL creation options.

Please consider this RFE medium-high priority.

How to repeat:
See description.

Suggested fix:
See description.

Why not just to use --add-drop-table in this case? What benefits we may get as a result of this feature?

Note that for most storage engines TRUNCATE is implemented and DROP and re-CREATE anyway (see http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html), and for InnoDB, where it is implemented differenly, you may end up with slowdown when a table you truncate is referenced by foreign keys...

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

One example: if you need to replicate from a standalone instance to the mysql cluster. To put table into the cluster you have to create it with "engine=ndb". So basicly you replicate between tables with engine innodb,myisam ... etc on one side  and tables with engine=ndb on the other side. In this scenario, create-drop in a dump file would be replicated to the cluster and change the ndb engine to something else - practically pull out the table from the cluster.
In this case, the possibility of truncate would be appreciated.

There are times when I need to copy data from one table to another when I want to truncate the table but not drop it (because the definition is slightly different than what I plan to import in mysqldump).  I get that I could import to a temporary table then insert select but why do the extra work?

I also agree with Stefan.  Truncate should be transaction safe.  If truncate is truly implemented as drop and create, then it raises the question if truncate is safe to do in a production NDB system.

I tool would like to see this feature.
i regularly export data from production using mysqldump and load it into a development database. There may have been some small changes made to the development db tables so i would not want to drop them. I am currently developing an admin tool to automate this but i have to execute and truncate table command first. I don't think that the question should be why would you want this feature but more like why would you not.

Hi, I agree that this feature is a must for the above cases and to make our life easier. Also I would like to know if this feature is implemented and if yes on which version of mysql. Thanks in advance

It also could work nice together with '--add-locks' option to truncate locked tables before importing data into already existing table structures.