Bulk-table-editor with timestamp column

Great program with many useful features, especially the bulk-table-editor.

But I have a table, that I can not transfer from one server to another, beacuse there is an primary key collision. It seems to be an issue with daylight saving time (that ended on 2012-10-28 in europe) and duplicate hours. 02:20:00 and 02:50:00 are duplicate, but in fact they are not because they are saved as timestamp and not as date_time.

Here the table and its data:

-- ------------------------------------------------------
-- ------------------------------------------------------

CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;
-- MySQL dump 10.13 Distrib 5.5.16, for Win32 (x86)
--
-- Host: XXXXXX Database: test
-- ------------------------------------------------------
-- Server version 5.1.68-community

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `ts_test`
--

DROP TABLE IF EXISTS `ts_test`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ts_test` (
`date_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`date_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `ts_test`
--

LOCK TABLES `ts_test` WRITE;
/*!40000 ALTER TABLE `ts_test` DISABLE KEYS */;
INSERT INTO `ts_test` VALUES ('2012-10-27 22:20:00'),('2012-10-27 22:50:00'),('2012-10-27 23:20:00'),('2012-10-27 23:50:00'),('2012-10-28 00:20:00'),('2012-10-28 00:50:00'),('2012-10-28 01:20:00'),('2012-10-28 01:50:00'),('2012-10-28 02:20:00'),('2012-10-28 02:50:00'),('2012-10-28 03:20:00'),('2012-10-28 03:50:00');
/*!40000 ALTER TABLE `ts_test` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- ------------------------------------------------------
-- ------------------------------------------------------

what is the exact error message produced by above code ?

If I use SQL export function to transfer data to another server with the options:
database(s): [ ] delete [ ] create
table(s): [ ] delete [X] create
data: INSERT
ouput: Server XXXXX
Database: test

I got the following output with primary key error:

SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='test';
SHOW TABLE STATUS FROM `test`;
SHOW FUNCTION STATUS WHERE `Db`='test';
SHOW PROCEDURE STATUS WHERE `Db`='test';
SHOW TRIGGERS FROM `test`;
SHOW EVENTS FROM `test`;
/* [XXXXXX user] Verbindung zu XXXXXX über MySQL (TCP/IP), Benutzername user, benutze Passwort: Yes ... */
/* [XXXXXX user] Verbunden. Prozess-ID: 121911 */
[XXXXXX user] SHOW STATUS;
[XXXXXX user] SHOW VARIABLES;
[XXXXXX user] SHOW DATABASES;
SELECT 'test' AS `Database`, 'ts_test' AS `Table`, 12 AS `Rows`, 0 AS `Duration`;
/*!40101 SET @OLD_LOCAL_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
[XXXXXX user] /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
[XXXXXX user] /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
[XXXXXX user] USE `common`;
SHOW CREATE TABLE `test`.`ts_test`;
[XXXXXX user] CREATE TABLE IF NOT EXISTS `ts_test` (
`date_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`date_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
[XXXXXX user] /*!40000 ALTER TABLE `ts_test` DISABLE KEYS */;
SELECT * FROM `test`.`ts_test` LIMIT 76819;
[XXXXXX user] INSERT INTO `ts_test` (`date_time`) VALUES
('2012-10-28 00:20:00'),
('2012-10-28 00:50:00'),
('2012-10-28 01:20:00'),
('2012-10-28 01:50:00'),
('2012-10-28 02:20:00'),
('2012-10-28 02:50:00'),
('2012-10-28 02:20:00'),
('2012-10-28 02:50:00'),
('2012-10-28 03:20:00'),
('2012-10-28 03:50:00'),
('2012-10-28 04:20:00'),
('2012-10-28 04:50:00');
/* [XXXXXX user] SQL Fehler (1062): Duplicate entry '2012-10-28 02:20:00' for key 'PRIMARY' */
[XXXXXX user] /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
[XXXXXX user] /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_LOCAL_SQL_MODE, '') */;
/* [XXXXXX user] Verbindung zu XXXXXX beendet um 2013-04-17 14:11:04 */

MySQL 5.1.68, HeidiSQL 7.0.0.4389

You have two equal values for your "date_time" column:

('2012-10-28 02:20:00'),
...,
('2012-10-28 02:20:00'),

How did you manage to insert these on your source server?

As I described above they just look the same, but they are not because they are in different timezones (dalight saving time and normal time). To insert the values for testing just use the MySQLdump Script I provided in the first entry above.

The date is 2012-10-28, which is the last Sunday in October. I guess that's when we changed from CEST (+02:00) to CET (+01:00) in the European Union. So that day it was 2:20 twice. From docs:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

For this reason, primary key isn't violated (the internal value points to different moments in time).

The problems is that the default value for MySQL time zone seems to be SYSTEM:

The server's current time zone. The global time_zone system variable indicates the time zone the server currently is operating in. The initial value for time_zone is 'SYSTEM', which indicates that the server time zone is the same as the system time zone.

Apparently, it doesn't mean system's current zone; it means system's zone when the date was stored. As a consequence, a select query prints the same value:

To reproduce:

TRUNCATE TABLE ts_test;
SET TIME_ZONE='+00:00';
INSERT INTO ts_test VALUES ('2012-10-28 00:20:00'),('2012-10-28 01:20:00');
SET TIME_ZONE=SYSTEM;
SELECT * FROM ts_test ORDER BY 1;

Clarification: the result of the select is correct (you can verify with any other client). The tricky bit is how to generate a valid dump when TIME_ZONE is set to SYSTEM.

You could delete the responsible rows if that's uncritical for consistence. Or even better, change the primary key to some auto increment value instead of a date/time. In many situations a datetime value can be of the same second as another value - and that happens alone without the above discussed timezone problem. So, datetime columns should not be in a primary key I guess.

Sure there might be some workarounds, but I need that key the way it is. (This was just an example to clarify the issue, the original table has much more columns.) Other tools like mysqldump can create a valid dump and transfer the data without errors (see script above), so in my opinion that is a HeidiSQL problem.

kalvaro: wouldn't it solve matmai's problem if Heidi would wrap the export dump in a

SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
... [table data] ...
SET TIME_ZONE=@OLD_TIME_ZONE;

At least that's how mysqldump seems to solve that. I guess '+00:00' must not be hardcoded but somehow retrieved from the server?

I'd need to do further testing in order to be sure but mysqldump can definitively be used as authoritative source for these kind of issues.

- For TZ aware columns, a hard-coded UTC will generate accurate and normalized dumps for everyone.

- For non-TZ aware columns, it'll have no effect.