MSQLB Problem running mysqlimport with TIMESTAMP column type
am 20.09.2002 08:50:38 von taaThu 09-19-02 10:50 pm
Summary: A warning is generated when attempting to import a
TIMESTAMP column of zero or zeroes. SELECT INTO OUTFILE
gives identical content as the import file.
Poster: Todd Andrews / taa@pobox.com
============================================================ ====
OS: Windows 2000 Professional with SP2
PC: Gateway GP7-500 (Pentium III 500Mhz)
384MB RAM physical, 128MB-574MB virtual paging file size
============================================================ ====
Output from mysqldump --no-data BUGTEST BUG
-- MySQL dump 8.22
--
-- Host: localhost Database: BUGTEST
---------------------------------------------------------
-- Server version 3.23.51-nt
--
-- Table structure for table 'BUG'
--
CREATE TABLE bug (
BUG_MAST_REC_ID mediumint(6) unsigned zerofill NOT NULL default '000000',
BUG_SEQ mediumint(6) unsigned zerofill NOT NULL auto_increment,
BUG_UPDATE_DATE timestamp(14) NOT NULL,
PRIMARY KEY (BUG_MAST_REC_ID,BUG_SEQ)
) TYPE=MyISAM;
============================================================ ====
Options when starting programs:
MYSQLD: Set up as service with no start up parameters:
D:\mysql\bin\mysqld-nt
NOTE: My example below uses C: as I expect that is what most people
have.
MYSQL: See BUG.BAT file below.
MYSQLIMPORT: See BUG.BAT file below.
============================================================ ====
I have checked the reference manual and archive to try and solve this
problem myself. I ran several tests to pinpoint the problem and got it down
to the minimum below.
How-To-Repeat:
1. Create file C:\TEMP\BUG.DEF containing the following:
--------------------------------------------------------
DROP DATABASE IF EXISTS BUGTEST;
CREATE DATABASE BUGTEST;
USE BUGTEST;
DROP TABLE IF EXISTS BUG;
CREATE TABLE BUG (
BUG_MAST_REC_ID MEDIUMINT(6) UNSIGNED ZEROFILL NOT NULL,
BUG_SEQ MEDIUMINT(6) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
BUG_UPDATE_DATE TIMESTAMP,
PRIMARY KEY (BUG_MAST_REC_ID,BUG_SEQ)
);
2. Create file C:\TEMP\BUG.BAT containing the following:
--------------------------------------------------------
C:
CD\MYSQL\BIN
MYSQL -u root < C:\TEMP\BUG.DEF
MYSQLIMPORT -u root -L -d --fields-terminated-by="," --lines-terminated-by="\r\n" BUGTEST C:\TEMP\BUG.TXT
3. Create file C:\TEMP\BUG.TXT containing the following:
--------------------------------------------------------
000001,000001,00000000000000
4. Run BUG.BAT and you should get:
----------------------------------
BUGTEST.BUG: Records: 1 Deleted: 0 Skipped: 0 Warnings: 1
5. Export the table into a text file:
------------------------------------
mysql> select * from bug into outfile "c:/temp/bug2.txt"
> fields terminated by ",";
6. Do a file comparision on the import and export files:
--------------------------------------------------------
cd\temp
fc bug.txt bug2.txt
Comparing files BUG.TXT and BUG2.TXT
FC: no differences encountered
7. The questions I have are: Why is a warning being generated and what
is the warning?
It is my understanding that because no log file is generated from
mysqlimport, the only way to troubleshoot warnings is to export
the file and do a comparision to see what is different. In this
case, the import and export files are identical.
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread12568@lists.mysql.com
To unsubscribe, e-mail