MySQL Timestamp not exporting

Aug 7, 2013 at 4:18 AM
I am using MySQLBackup.NET for backup and restore the database.

I have coded as per instruction of this link https://mysqlbackupnet.codeplex.com/

For Backup
                Dim fn As String = ""

                Using sf As New SaveFileDialog()
                    sf.FileName = "test " & DateTime.Now.ToString("yyyyMMdd HHmmss") & ".sql"
                    If sf.ShowDialog() <> System.Windows.Forms.DialogResult.OK Then Exit Sub
                    fn = sf.FileName
                End Using

                Dim con As String = G_ConnString
                Dim file As String = fn
                Dim mb As New MySqlBackup(con)
                mb.ExportInfo.FileName = file
                mb.Export()
and for Restore
Dim fn As String = ""

        Using sf As New OpenFileDialog()
            'sf.FileName = "test " & DateTime.Now.ToString("yyyyMMdd HHmmss") & ".sql"
            If sf.ShowDialog() <> System.Windows.Forms.DialogResult.OK Then Exit Sub
            fn = sf.FileName
        End Using

        Dim con As String = G_ConnString
        Dim file As String = fn
        Dim mb As New MySqlBackup(con)
        mb.ImportInfo.FileName = file
        mb.Import()
The issue is while I am trying to restore an exception is appeared...

MySqlException was unhandled
Column count doesn't match value count at row 1
Image


When I see the backup file, I saw a table backup data does not match as per table structure.

my table structure is...
DROP TABLE IF EXISTS `userlogindetails`;
CREATE TABLE IF NOT EXISTS `userlogindetails` (
  `LoginId` int(11) NOT NULL AUTO_INCREMENT,
  `UserName` char(20) NOT NULL,
  `LastLoginDateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `LastLogoutDateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `LoginStatus` char(10) DEFAULT 'ACTIVE',
  `LastUsageTime` double(10,3) DEFAULT '0.000',
  PRIMARY KEY (`LoginId`)
) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=latin1;
and exported data is

--
-- Dumping data for table `userlogindetails`
--

/*!40000 ALTER TABLE `userlogindetails` DISABLE KEYS */;
INSERT INTO `userlogindetails` (`LoginId`,`UserName`,`LastLoginDateTime`,`LastLogoutDateTime`,`LoginStatus`,`LastUsageTime`) VALUES
(1,'bikram','INACTIVE',0),
(2,'bikram','INACTIVE',0),
(3,'bikram','INACTIVE',0),
How do I solve the above ?
Coordinator
Aug 7, 2013 at 4:34 AM
Edited Aug 7, 2013 at 4:34 AM
I see... there is a bug. This will be an urgent type of bug. I'll try to find the hole as soon as possible.
I received similar report earlier by another member, but his report and information provided are not as clear as yours.

I will create the table and try to backup + restore and try to find out the bug in a couple of days.

If you have found the bug and fixed codes, you can tell me too :)
Thanks dude.

I'll come back to this later.
Coordinator
Aug 19, 2013 at 8:06 AM
Edited Aug 19, 2013 at 8:19 AM
Negative. I did not encounter this problem as you described.

I have tried your database structure and insert a few rows as sample data:
INSERT INTO `userlogindetails` (`LoginId`,`UserName`,`LastLoginDateTime`,`LastLogoutDateTime`,`LoginStatus`,`LastUsageTime`) VALUES
(1,'aaa','2013-01-01 01:01:01','2013-01-01 01:02:02','active',30),
(2,'user1','2013-01-01 01:01:01','2013-01-01 01:31:00','ACTIVE',0.3),
(3,'user2',null,'0000-00-00 00:00:00','ACTIVE',0.3);
and I am able to export and import the data with no error.

However, I did notice there is one common issue regarding the usage of zero date time in MySQL:
0000-00-00 00:00:00
.NET Framework does not have zero date time value.
The possible minimum value of date time in .NET Framework is
0001-01-01 00:00:00
In order for MySql.Data.DLL (MySQL Connect/Net) which is the default connector used by MySqlBackup to retrieve zero date time value from MySQL, you need to specify an extra connection option of allowzerodatetime in your connection string.

Example of normal connection string:
server=localhost;user=root;pwd=1234;database=test;
Example of normal connection string with allowzerodatetime:
server=localhost;user=root;pwd=1234;database=test;allowzerodatetime=true;
If you initiate the connection without allowzerodatetime parameter, you will receive the exception/error of
MySql.Data.Types.MySqlConversionException: Unable to convert MySQL data/time value to System.DateTime
This is not a bug, it is an expected behaviour. This exception is created by MySql.Data.DLL.

If you initiate the connection with allowzerodatetime, MySQL Dot Net Connector (Connector/NET) will store the datetime value in a new data type, called MySql.Data.Types.MySqlDateTime. In other words, if the value in TIMESTAMP field is zero date time (0000-00-00 00:00:00), the Connector/NET will return data in MySqlDateTime. However, this is already been handled by MySqlBackup.NET.

You won't have to care about the data conversion, as long as you specify allowzerodatetime in the connection string, MySqlBackup.NET will work as normal.

Read more at here: Frequently Asked Questions (FAQ) - Commonly Seen Error

If this is does solve your problem, perhaps you have to provide more details that will lead to the error described by you.
Try to upload a C# or VB.NET project that will create the error.