Restore DB problem

Oct 15, 2015 at 5:05 AM
Hi,

I created a backup with this settings.
            using (MySqlConnection conn = new MySqlConnection(ConnectionStringBuilder.GetConnectionString(true)))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    using (MySqlBackup mb = new MySqlBackup(cmd))
                    {
                        cmd.Connection = conn;
                        conn.Open();
                        mb.ExportInfo.ExportProcedures = false;
                        mb.ExportInfo.ExportEvents = false;
                        mb.ExportInfo.ExportTriggers = false;
                        mb.ExportInfo.ExportFunctions = false;
                        mb.ExportInfo.ExportViews = false;
                        mb.ExportInfo.ExportRoutinesWithoutDefiner = true;
                        mb.ExportToFile(FilePath);
                        conn.Close();

                        return FilePath;
                    }
                }
            }
The commands works well, but It produces a backup with some syntax error (see below the export). When I try to restore the DB it produces this error:
_#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"tbl_address" (
"idAddress" int(11) NOT NULL AUTO_INCREMENT,

"idPerson" int(' at line 1_

-- MySqlBackup.NET 2.0.9.2
-- Dump Time: 2015-10-15 00:00:02

-- Server version 5.6.23-log Source distribution

/!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 /;
/!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 /;

-- Definition of tbl_address

DROP TABLE IF EXISTS tbl_address;
CREATE TABLE IF NOT EXISTS "tbl_address" (
"idAddress" int(11) NOT NULL AUTO_INCREMENT,
"idPerson" int(11) NOT NULL,
"address" varchar(200) DEFAULT NULL,
"cap" varchar(10) DEFAULT NULL,
"city" varchar(100) DEFAULT NULL,
"phonenumber" varchar(30) DEFAULT NULL,
PRIMARY KEY ("idAddress"),
KEY "fk_tbl_address_tbl_person_idx" ("idPerson")
);


Thank you for your support. Really good job with this library!!!

Kind regards.
Coordinator
Oct 15, 2015 at 5:27 AM
You might enabled SQL MODE of
ANSI_QUOTES
Try to reset SQL MODE to standard mode, which is:
ANSI
by executing
SET SESSION sql_mode = 'ANSI';
In your case,
using (MySqlConnection conn = new MySqlConnection(ConnectionStringBuilder.GetConnectionString(true)))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        using (MySqlBackup mb = new MySqlBackup(cmd))
        {
            cmd.Connection = conn;
            conn.Open();

            cmd.CommandText = 'SET SESSION sql_mode = 'ANSI';';
            cmd.ExecuteNonQuery();

            mb.ExportInfo.ExportProcedures = false;
            mb.ExportInfo.ExportEvents = false;
            mb.ExportInfo.ExportTriggers = false;
            mb.ExportInfo.ExportFunctions = false;
            mb.ExportInfo.ExportViews = false;
            mb.ExportInfo.ExportRoutinesWithoutDefiner = true;
            mb.ExportToFile(FilePath);
            conn.Close();

            return FilePath;
        }
    }
}
Oct 15, 2015 at 5:42 AM
The name is tbl_address without quote.

---


Oct 15, 2015 at 5:42 AM
The name is tbl_address without quote.

---


Oct 15, 2015 at 5:42 AM
The name is tbl_address without quote.

---


Oct 15, 2015 at 6:00 AM
Thank you.
I tried you hint but it doesn't work. The message error is the same.

To restore the db I use this code.
            using (MySqlConnection conn = new MySqlConnection(ConnectionStringBuilder.GetConnectionString(true)))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    using (MySqlBackup mb = new MySqlBackup(cmd))
                    {
                        cmd.Connection = conn;
                        conn.Open();
                        mb.ImportInfo.TargetDatabase = ConnectionStringBuilder.Database;
                        //mb.ImportInfo.IgnoreSqlError = true;
                        mb.ImportFromFile(fileName);
                        conn.Close();

                        return true;
                    }
                }
            }
Oct 15, 2015 at 6:36 AM
I tried to restore the db using this

cmd.CommandText = "SET SESSION SQL_MODE = ANSI_QUOTES;"; //as described in mysql_docs.
cmd.ExecuteNonQuery();

but it doesn't work.

Kind regards.
Coordinator
Oct 15, 2015 at 6:52 AM
Did you already try recreate the backup file, and restore with new file with the new settings?

And you might need to do the same thing while you're trying to import:
using (MySqlConnection conn = new MySqlConnection(ConnectionStringBuilder.GetConnectionString(true)))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        using (MySqlBackup mb = new MySqlBackup(cmd))
        {
            cmd.Connection = conn;
            conn.Open();

            cmd.CommandText = "SET SESSION sql_mode = 'ANSI';";
            cmd.ExecuteNonQuery();

            mb.ImportInfo.TargetDatabase = ConnectionStringBuilder.Database;
            mb.ImportFromFile(fileName);
            conn.Close();

            return true;
        }
    }
}
Oct 15, 2015 at 7:12 AM
Yes. This is the new code used for the last try with the error

Backup

            using (MySqlConnection conn = new MySqlConnection(ConnectionStringBuilder.GetConnectionString(true)))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    using (MySqlBackup mb = new MySqlBackup(cmd))
                    {
                        cmd.Connection = conn;

                        //Apro la connessione
                        conn.Open();

                        cmd.CommandText = "SET SQL_MODE = 'ANSI';";
                        cmd.ExecuteNonQuery();

                        mb.ExportInfo.ExportProcedures = false;
                        mb.ExportInfo.ExportEvents = false;
                        mb.ExportInfo.ExportTriggers = false;
                        mb.ExportInfo.ExportFunctions = false;
                        mb.ExportInfo.ExportViews = false;
                        mb.ExportInfo.ExportRoutinesWithoutDefiner = true;

                        mb.ExportToFile(FilePath);
                        conn.Close();

                        return FilePath;
                    }
                }
            }

Restore

            using (MySqlConnection conn = new MySqlConnection(ConnectionStringBuilder.GetConnectionString(true)))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    using (MySqlBackup mb = new MySqlBackup(cmd))
                    {
                        cmd.Connection = conn;

                        conn.Open();

                        cmd.CommandText = "SET SQL_MODE = 'ANSI';";
                        cmd.ExecuteNonQuery();

                        mb.ImportInfo.TargetDatabase = ConnectionStringBuilder.Database;
                        //mb.ImportInfo.IgnoreSqlError = true;

                        mb.ImportFromFile(fileName);

                        conn.Close();

                        return true;
                    }
                }
            }
Coordinator
Oct 16, 2015 at 8:28 AM
I have tried to execute the codes too.
either I use SET SESSION sql_mode = 'ANSI'; or SET SESSION sql_mode = 'ANSI_QUOTES';
both happens the same. the table's name and column's name are wrapped by double quotes.

Everything goes normal if I never execute the codes above.

Therefore, I am unsure and have no idea of the cause of your problem.
Oct 19, 2015 at 5:18 AM
Hi,

I don't want to generate the backup with double quotes. How can I do it? I try all the settings.

Kind regards.
Oct 19, 2015 at 5:25 AM
Edited Oct 19, 2015 at 5:27 AM

The thing is that only the create code is surrounded with double quotes. It's correct?

-- Definition of tbl_address

DROP TABLE IF EXISTS `tbl_address`;
CREATE TABLE IF NOT EXISTS "tbl_address" (
  "idAddress" int(11) NOT NULL AUTO_INCREMENT,
  "idPerson" int(11) NOT NULL,
  "address" varchar(200) DEFAULT NULL,
  "cap" varchar(10) DEFAULT NULL,
  "city" varchar(100) DEFAULT NULL,
  "phonenumber" varchar(30) DEFAULT NULL,
  PRIMARY KEY ("idAddress"),
  KEY "fk_tbl_address_tbl_person_idx" ("idPerson")
);
-- 
-- Dumping data for table tbl_address
-- 

/*!40000 ALTER TABLE `tbl_address` DISABLE KEYS */;
INSERT INTO `tbl_address`(`idAddress`,`idPerson`,`address`,`cap`,`city`,`phonenumber`) VALUES
(2,2,'','','',''),
......
Kind regards.
Oct 19, 2015 at 6:19 AM
Last thing. If I add the line SET sql_mode='ANSI_QUOTES'; after the default settings generated the restore works well.
/*!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 */;
/*!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 */;

SET sql_mode='ANSI_QUOTES';
Oct 28, 2015 at 7:07 AM
Edited Jan 22, 2016 at 4:17 AM
Hi Adrian, the problem comes only when I restore the db. It seems that the alter session are not "accepted". When I make a backup the table's name are wrapped by double quotes but the error comes when I restore the db. Now I solve the problem using this command SQL_MODE = "NO_ENGINE_SUBSTITUTION" in backup and restore. Kind regards, Sacha [email removed] 27 ottobre 2015 23:10 From: adriancs I have tried to execute the codes too. either I use SET SESSION sql_mode = 'ANSI'; or SET SESSION sql_mode = 'ANSI_QUOTES'; both happens the same. the table's name and column's name are wrapped by double quotes. Everything goes normal if I never execute the codes above. Therefore, I am unsure and have no idea of the cause of your problem.
Coordinator
Jan 7, 2016 at 12:00 AM
Thanks for the info netclick,
I have learnt new thing in your post.
Thanks agian.
Jan 21, 2016 at 2:34 PM
Hi.
I have the same problem.
this problem arose when I change Windows regional settings to Persian.
Jul 12, 2016 at 6:24 AM
Thanks netclick !
      cmd.CommandText="set SQL_MODE = 'NO_ENGINE_SUBSTITUTION'";
      cmd.ExecuteNonQuery();
      mb.ExportToFile(filePhysicalPath);
makes me able to reimport back (using MySql Workbench on windows with a MySql on Linux) by making it use the right quotes
CREATE TABLE IF NOT EXISTS `logs` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,