MySQL Bugs: #6530: mysqldump does not output sufficient data for timestamp(4) fields

Bug #6530	mysqldump does not output sufficient data for timestamp(4) fields
Submitted:	9 Nov 2004 19:28	Modified:	2 Mar 2005 15:15
Reporter:	Shelby Moore	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	all	OS:	FreeBSD (FreeBSD)
Assigned to:	Mats Kindahl	CPU Architecture:	Any

Description:
When doing mysqldump the resultant sql file contains INSERT statements that only have 4 digits of data for timestamp(4) fields.

Thus the data is corrupted/lost (inserted as '0000' values) when inserted because INSERT does not allow less than 6 digits for timestamps.

Assuming that INSERT will allow zero value days as legal, then mysqldump should output "YYMM00" for timestamp(4) fields.  Would be then fixed.

Else, the output of mysqldump would have to be an arbitrary valid day for timestamp(4) fields, which means the INSERT would be erroneous when inserting into timestamps wider than 4.  Thus the correct fix would also include a change to INSERT to allow zero day values when inserting into timestamp(4) fields.

How to repeat:
mysqldump on table with timestamp(4)

Suggested fix:
See Description as fix is incorporated.

Hi,

Thank you for the report, but I can't repeat it.
What exactly version of MySQL server do you use?

Hi,

Thank you for the report, but I can't repeat it.
What exactly version of MySQL server do you use?

This has been tested in both 3.23 and 4.0.  I don't have exact minor version numbers available at the moment.  Also, the initial problem was on FreeBSD, but has since been repeated in Redhat Linux 9.0 and Redhat Enterprise Linux 3.0.  Based on the behavior I have witnessed, I believe that it is a design flaw in mysqldump, and is therefore platform independent.

The bug with mysqldump is pretty obvious (at least in the case of timestamp fields): when you issue something to the effect of:
mysqldump --add-drop-table database_name > database_name.sql

Fields that are defined as timestamp(4) have only the first 4 characters output in the dump, although the full 14 character timestamp is stored in the database.  When you then do this:
mysql new_database_name < database_name.sql

the field is correctly defined as timestamp(4), however the INSERT operations give you invalid values and get set to '0000' because the previous dump only output YYMM (4 digits), and 4 digits of input is not valid for a timestamp INSERT.

A "correct" behavior for a database server on a data dump is to retain the full precision that is actually stored in the database, NOT to dump only the view length of a field.  If people want to limit the output to only the view length, they should have to specify a command flag to do so.

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".

Bullshit!

The requested feedback is provided in previous comment from me above dated:

[10 Nov 2004 5:28pm] Shelby Moore

This bug has been verified by both myself and Chris Ostmo of appideas.com.  He discovered it when doing some backups for me, and I verified it.  It is bug in all versions of mysqldump which I have tried.

DO NOT CLOSE THIS BUG!!!!!!  Adequate details have been provided in the comments already!!!!!

sorry for this. reopened.

Thank you and apologies for my lapse of civility.

Perhaps this is not the highest priority data loss bug in MySQL (in mysqldump not core database code), but please be reasonably confident that this bug has been verified independently by two different MySQL developers and we discussed the nature of the bug over several emails.

This is indeed a problem in 4.0.x, but starting with 4.1.x, the display width of TIMESTAMP fields are ignored. In preparing for transition to 4.1.x and later, the TIMESTAMP columns should use a width of 6 (or wider).

A solution for printing the date in the 2 and 4 width format, for example for external applications, has been added to the documentation in section 11.3.1.1. "TIMESTAMP Properties Prior to MySQL 4.1".