Hi,
I have an issue were I'm unable to restore any database containing a view.
I'm using DBForge Studio Standard and connecting to a DigitalOcean hosted MySql (version 8) database.
Whenever I try and restore I get the following message:
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 '."id" AS "id","table1"."name" AS "name" from "table1"' at line 5
To test I've created a simple database, taken a backup and tried to restore here's the SQL file:
--
-- Script was generated by Devart dbForge Studio 2020 for MySQL, Version 9.0.470.0
-- Product home page: http://www.devart.com/dbforge/mysql/studio
-- Script date 02/02/2021 00:03:24
-- Server version: 8.0.20
-- Client version: 4.1
--
--
-- Disable foreign keys
--
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
--
-- Set SQL mode
--
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
--
-- Set character set the client will use to send SQL statements to the server
--
SET NAMES 'utf8';
--
-- Set default database
--
USE Test_DB;
--
-- Drop table `table1`
--
DROP TABLE IF EXISTS table1;
--
-- Drop view `view1`
--
DROP VIEW IF EXISTS view1 CASCADE;
--
-- Set default database
--
USE Test_DB;
--
-- Create view `view1`
--
CREATE
DEFINER = 'doadmin'@'%'
VIEW view1
AS
select "table1"."id" AS "id","table1"."name" AS "name" from "table1";
--
-- Create table `table1`
--
CREATE TABLE table1 (
id int NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
PRIMARY KEY (id)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci;
--
-- Dumping data for table table1
--
-- Table Test_DB.table1 does not contain any data (it is empty)
--
-- Restore previous SQL mode
--
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
--
-- Enable foreign keys
--
/*!40014 SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS */;
Any help would be greatly appreciated.
Syntax error when restoring database containing views
Discussion of open issues, suggestions and bugs regarding database management and administration tools for MySQL
Jump to
- General Forums
- ↳ Announcements
- Database Tools
- ↳ dbForge for MySQL
- ↳ dbForge for Oracle
- ↳ dbForge for PostgreSQL
- ↳ dbForge for SQL Server
- ADO.NET Data Providers
- ↳ Entity Framework support
- ↳ LinqConnect (LINQ to SQL support)
- ↳ dotConnect for Oracle
- ↳ dotConnect for MySQL
- ↳ dotConnect for PostgreSQL
- ↳ dotConnect for SQLite
- ↳ dotConnect for DB2
- ↳ dotConnect for SQL Server
- ↳ dotConnect for Cloud Applications
- ↳ dotConnect Universal
- ↳ Entity Developer
- Delphi Data Access Components
- ↳ EntityDAC
- ↳ Universal Data Access Components
- ↳ Oracle Data Access Components
- ↳ SQL Server Data Access Components
- ↳ MySQL Data Access Components
- ↳ InterBase Data Access Components
- ↳ PostgreSQL Data Access Components
- ↳ SQLite Data Access Components
- ↳ VirtualDAC
- dbExpress Drivers
- ↳ dbExpress driver for Oracle
- ↳ dbExpress driver for SQL Server
- ↳ dbExpress driver for MySQL
- ↳ dbExpress driver for InterBase & Firebird
- ↳ dbExpress driver for PostgreSQL
- ↳ dbExpress driver for SQLite
- ODBC Drivers
- ↳ ODBC Drivers
- Network Security
- ↳ SecureBridge
- Developer Tools
- ↳ Code Compare
- ↳ Review Assistant
- ↳ LINQ Insight
- ↳ SSIS Data Flow Components
- ↳ Excel Add-ins