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 20:28	Modified:	2 Mar 2005 16:15
Reporter:	Shelby Moore
Status:	Closed
Category:	Client	Severity:	S2 (Serious)
Version:	all	OS:	FreeBSD (FreeBSD)
Assigned to:	Mats Kindahl	Target Version:

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