Bug #55532 .sql file generated by Visual Studio 2010 does not execute
Submitted: 24 Jul 2010 17:25 Modified: 24 Aug 2010 17:49
Reporter: David Sandborne Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.5.5 m3 OS:Microsoft Windows (Vista)
Assigned to: CPU Architecture:Any
Tags: Connector/Net, MySQL, workbench

[24 Jul 2010 17:25] David Sandborne
Description:
I am using MySql 5.5.5 m3, Connector/Net 6.3.2, with MySql Workbench 5.2.25.6302

I am accessing from Visual Studio 2010 Professional on Windows Vista Home Premium.

I created a simple data model with two tables and one many to many association, and the created a database This normally creates a sql command file with extension .sql.  I tried to execute this with MySql Workbench.  The script had errors and would not execute.

When I try to do the same thing using MS SqlServer the procedure is much the same with the exception of using the SqlServer Manager.

I don't know whether the problem is in Connector/Net, MySql, or MySql Workbench.

How to repeat:
From Visual Studio connect to MySql.  Create a project.  Add a Ado Data Entity Model.  Connect a database on the MySql server.    Select update the entity from the database.  Add a table with a key and an association. Right click and select generate database from model.  

In MySql Workbench, open the .sql file and execute it.  There are errors and the execution can not proceed.

This is the normal development cycle using the Visual Studio 2010. It should work

This is the script that was produced.

-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 07/24/2010 10:10:51
-- Generated from EDMX file: C:\Visual Studio 2010 Projects\Playgrounds\MySqlPlayground\Studies\FirstTest\FirstTest\Models\Model2.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[test].[person]', 'U') IS NOT NULL
    DROP TABLE [test].[person];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'people'
CREATE TABLE [dbo].[people] (
    [FirstName] nvarchar(1000)  NULL,
    [LastName] nvarchar(1000)  NULL,
    [PersonID] int  NOT NULL
);
GO

-- Creating table 'Companies'
CREATE TABLE [dbo].[Companies] (
    [CompanyID] int IDENTITY(1,1) NOT NULL,
    [CompanyName] nvarchar(1000)  NOT NULL
);
GO

-- Creating table 'Companyperson'
CREATE TABLE [dbo].[Companyperson] (
    [Companies_CompanyID] int  NOT NULL,
    [people_PersonID] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [PersonID] in table 'people'
ALTER TABLE [dbo].[people]
ADD CONSTRAINT [PK_people]
    PRIMARY KEY CLUSTERED ([PersonID] ASC);
GO

-- Creating primary key on [CompanyID] in table 'Companies'
ALTER TABLE [dbo].[Companies]
ADD CONSTRAINT [PK_Companies]
    PRIMARY KEY CLUSTERED ([CompanyID] ASC);
GO

-- Creating primary key on [Companies_CompanyID], [people_PersonID] in table 'Companyperson'
ALTER TABLE [dbo].[Companyperson]
ADD CONSTRAINT [PK_Companyperson]
    PRIMARY KEY NONCLUSTERED ([Companies_CompanyID], [people_PersonID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Companies_CompanyID] in table 'Companyperson'
ALTER TABLE [dbo].[Companyperson]
ADD CONSTRAINT [FK_Companyperson_Company]
    FOREIGN KEY ([Companies_CompanyID])
    REFERENCES [dbo].[Companies]
        ([CompanyID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [people_PersonID] in table 'Companyperson'
ALTER TABLE [dbo].[Companyperson]
ADD CONSTRAINT [FK_Companyperson_person]
    FOREIGN KEY ([people_PersonID])
    REFERENCES [dbo].[people]
        ([PersonID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Companyperson_person'
CREATE INDEX [IX_FK_Companyperson_person]
ON [dbo].[Companyperson]
    ([people_PersonID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------
[24 Jul 2010 17:31] David Sandborne
edmx file with the model

Attachment: Model2.edmx (application/octet-stream, text), 7.91 KiB.

[24 Jul 2010 17:49] Valeriy Kravchuk
Based on syntax in your .sql file, I'd say that it is created for SQL Server, NOT for MySQL. MySQL does not support this syntax.

Please, check all the settings. It is not a bug in MySQL server or Workbench in any case.
[26 Jul 2010 7:07] Tonci Grgin
David, Valeriy, I am almost 100% sure EntityFW models are not meant to generate scripts compliant with MySQL server but with EntityFW! EF simply refuses notation suitable for MySQL server.

This is not a bug but intended behavior compliant with EF.
[24 Aug 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".