0

I use MySQLBackup to have a live backup of my MySQL-database. It worked perfectly with MySQL Community Server 5.7. After I upgraded to 8.0 the TIMESTAMP-column simply disappeared in the backup. (There seem to be even a difference between versions 8.0.11 and 8.0.14: Replacing TIMESTAMP by DATETIME and leaving CURRENT_TIMESTAMP as default value, the backup was still usable in 8.0.11).

I need the timestamp as the database is fed by a Raspberry which doesn’t have the real date/time (not connected to the internet) I looked at the export options, but none seem to have to do with the problem.

This is the log file of the backup:

-- MySqlBackup.NET 2.0.9.2
-- Dump Time: 2019-02-07 09:22:47
-- --------------------------------------
-- Server version 8.0.14 MySQL Community Server - GPL

-- 
-- Create schema db_1
-- 

CREATE DATABASE IF NOT EXISTS `db_1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */;
Use `db_1`;


-- Definition of tab_1
DROP TABLE IF EXISTS `tab_1`;
CREATE TABLE IF NOT EXISTS `tab_1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `W_1` int(11) DEFAULT '0',
  `W_2` int(11) DEFAULT '0',
`Time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,  
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table tab_1
-- 

/*!40000 ALTER TABLE `tab_1` DISABLE KEYS */;
INSERT INTO `tab_1`(`ID`,`W_1`,`W_2`) VALUES          
 (12,2,2),
(13,3,3,),
(14,4,4,);
/*!40000 ALTER TABLE `tab_1` ENABLE KEYS */;


-- Dump completed on 2019-02-07 09:22:48
-- Total time: 0:0:0:0:136 (d:h:m:s:ms)

Does anybody know what’s going wrong? I would be very grateful for help!

  • This is the log file of the backup: This backup file is syntactically incorrect. It is extremely strange. – Akina Feb 12 '19 at 8:57
  • Please read the rules about formatting, and correct your post accoring. – davidbaumann Feb 12 '19 at 10:12
  • Sorry for the missing poper formatting! – rlinner Feb 13 '19 at 8:39
  • This is the copy of what I get when I open the backup file either using MySQL Workbench or MS SQL Server Managment Studio 17. I have anonymized the table name and reduced columns. Everything else is what MySQL Backup seems to do. What is incorrect? – rlinner Feb 13 '19 at 8:47
  • I have tested this at db-fiddle.com with a column of timestamp, it seems to be ok. Please be specific and provide more details to simulate your problem. – mjb Jul 4 '19 at 1:28
0

Today I discovered the element that causes MySQLBackup.NET to exclude TIMESTAMP type columns from dumps.

For Date / DateTime / TimeStamp type columns, remove the default CURRENT_TIMESTAMP, otherwise these columns will be discarded.

I just did the test with MySQLBackup.NET 2.3.3.1 and MySQL server 8.0.17. No matter what options I placed in my C# code, my TIMESTAMP column did not appear in the dump. After removing the DEFAULT CURRENT_TIMESTAMP, the column now appears in the dump.

  • This was posted as an answer, but it does not attempt to answer the question. It should possibly be an edit, a comment, another question, or deleted altogether. – Ramhound Jan 13 at 21:10
  • This literally answered the question... – Karu Jan 13 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.