distal-attribute
distal-attribute
distal-attribute
distal-attribute

Bulk-table-editor with timestamp column

User, date Message
Written by matmai
1 year ago
Category: Import/Export
4 posts since Wed, 17 Apr 13
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 */;

-- ------------------------------------------------------
-- ------------------------------------------------------
Written by jfalchMoney, Euro
1 year ago
380 posts since Sat, 17 Oct 09
what is the exact error message produced by above code ?
Written by matmai
1 year ago
4 posts since Wed, 17 Apr 13
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
Written by ansgar
1 year ago
4936 posts since Fri, 07 Apr 06
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?
Written by matmai
1 year ago
4 posts since Wed, 17 Apr 13
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.
Written by kalvaro
1 year ago
587 posts since Thu, 29 Nov 07
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;

Written by kalvaro
1 year ago
587 posts since Thu, 29 Nov 07
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.
Written by ansgar
1 year ago
4936 posts since Fri, 07 Apr 06
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.
Written by matmai
1 year ago
4 posts since Wed, 17 Apr 13
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.
Written by ansgar
1 year ago
4936 posts since Fri, 07 Apr 06
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?
Written by kalvaro
1 year ago
587 posts since Thu, 29 Nov 07
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.
 

Please login to leave a reply, or register at first.