Losing TZ offset from TIMESTAMP values while exporting DB/table from one server to another

[fifonik]

Preconditions

• HeidiSQL version: 11.3.6337
• Database type and version: MySQL 5.7
• OS: Windows, Linux

Describe the bug

When exporting DB/table from one server to another timezone offset in TIMESTAMP values is lost.
For example, if source server is in UTC+11 and TIMESTAMP value is '2021-10-07 09:00:00', when the record with the value exported to another server the value will become '2021-10-06 22:00:00'.

IF you export the same data into sql file with HeidiSQL/mysqldump and then import it into another MySQL server using HeidiSQL/mysql CLI, everything will be as it should.

To Reproduce

CREATE TABLE `test` (
	`ts` TIMESTAMP NOT NULL,
	`dt` DATETIME NOT NULL
);
INSERT INTO `test` (`ts`, `dt`) VALUES ('2021-10-07 09:00:00', '2021-10-07 09:00:00');

2. Check data and notice that values are the same
3. Right click over the test table | Export database as SQL | Output: your dest server | Export
4. Check data on the dest server and notice ts value lost timezone offset and is different (exact value depend on timezone of your source server, as my source server is in UTC+11, the value become '2021-10-07 09:00:00' - 11 hours = '2021-10-06 22:00:00')
5. Right click over the test table | Export database as SQL | Output: 'Single .sql file', key in file name | Export
6. Check the sql file and notice presence of /*!40103 SET TIME_ZONE='+00:00' */; line and that ts value is stored in UTC (as it should)
7. Import the file into dest server using mysql CLI or HeidiSQL (connect to dest server | drag the file into Query | F9)
8. Check data on dest server and notice that ts value is correct. It will be the same if you import into dest server that is in the same timezone or might be a bit different if timezone is different. For example, if I import provided data into server that is in UTC+10, ts value will be 1 hour behind: '2021-10-07 08:00:00'.

Screenshots or Crash reports

[ansgarbecker]

If SET TIME_ZONE... is fired for reading and writing, shouldn't the times be correct?
See also:

• https://www.heidisql.com/forum.php?t=38078
• 2ea1939
• ee69a04

[fifonik]

Not sure if I understand your question.

If you issue set time_zone='+00:00' commands for both source and dest servers, everything should be fine.
However, in log I only see this:

-- Heidi issues the following commands when I selecting dest server localhost:3307 from dropdown:
/* [localhost:3307] Connecting to localhost via MariaDB or MySQL (TCP/IP), username root, using password: Yes ... */
[localhost:3307] SELECT CONNECTION_ID();
/* [localhost:3307] Characterset: utf8mb4 */
[localhost:3307] SHOW /*!50002 GLOBAL */ STATUS;
[localhost:3307] SELECT NOW();
[localhost:3307] SHOW VARIABLES;
/* [localhost:3307] Connected. Thread-ID: 648 */
[localhost:3307] SHOW TABLES FROM `information_schema`;
[localhost:3307] SHOW DATABASES;

-- Heidi issues these commands when I click 'Export' button
SELECT 'test' AS `Database`, 'test' AS `Table`, 1 AS `Rows`, 0 AS `Duration`;
/*!40101 SET @OLD_LOCAL_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
SELECT * FROM `test`.`test` LIMIT 104857600;
/*!40101 SET SQL_MODE=IFNULL(@OLD_LOCAL_SQL_MODE, '') */;
/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/* [localhost:3307] Connection to localhost closed at 2021-10-08 07:45:51 */

If I understand correctly, only single SET TIME_ZONE='+00:00' issued (for source server).

Thanks.

[ansgarbecker]

Fixed in the next build

[fifonik]

I do not see anything related to TZ in log (build 11.3.6344), however TIMESTAMP values exported correctly.
Thanks.