MySQL Bugs: #51091: Forward engineer doesn't create view

Bug #51091	Forward engineer doesn't create view
Submitted:	11 Feb 2010 12:08	Modified:	4 Jun 2010 10:37
Reporter:	Paul Heaney	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.20	OS:	Linux
Assigned to:	Alexander Musienko	CPU Architecture:	Any

Description:
When you try and forward engineer a project which contains  a view it fails with the following error:

ERROR: Error 1046: No database selected

though running the create statement manually on the database is successful.

How to repeat:
Create two table, create view over these and forward engineer the project

My view is

CREATE  OR REPLACE VIEW `datawarehouse`.`viewname` AS 
SELECT t1.id AS lsnid, t1.info_id, t1.personal_id, t2.desc, t1.ranking, t1.startdate, t1.comment, lsn.needs
FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.type_id = t2.id

The view is at the end of the SQL so the tables have been created by this stage

Thank you for the problem report. Please, upload .mwb file to demonstrate this problem. I can not repeat it.

Hi,  

Copy of a cutdown .mwb which exhibits this problem.

I've found that if I try and create the table form a mysql client without setting the default db then I also get the problem though if I do "use datawarehouse" first it creates sucesssfully

Thanks
Paul

Please, specify what exact options do you set before generating forward engineer script. I can not repeat the problem with the model uploaded and default settings (none of the options checked).

Attached are a series of screenshots of my forward engineer proces

Attachment: steps.zip (application/zip, text), 247.12 KiB.

Hi,

I've just uploaded a series of screenshots showing the options I used whilst creating the forward engineer script.

I tried selecting "Omit schema qualifier in object names" and "generate use statements" and got the same error

I have upgraded to Workbench 5.2.16 Beta and the problem remains

Paul

Sorry, but I am not able to follow you.

You need to select a database before running the queries.

I am not able to see a bug here.

Hi,

From the project I have a schema called datawarehouse which does not yet exist on my MySQL server, in this project I create two tables and a view over these two tables.

I then choose to forward engineer the project onto my mysql server and it successfully creates the schema and the two tables though failes on creating the view with the previous error.  The view is created with the schema name (e.e datawarehouse.viewname) so I wouldn't have expected I'd need to do a user database first?  Unless I'm missing something with MySQL view support.

Cheers
Paul

Did you mark drop database on the forward engineering settings?

Hi,

Yes I choose both "drop objects before each create object" and "generate drop schema" when I get this error.

Interestingly if I now select "omit schema qualified in object names" and "generate use statements" the view is created sucessfully though the stored procedures I have aren't with the following error

Executing SQL script in server
ERROR: 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 '$$
CREATE PROCEDURE `datawarehouse`.`addidmevent` (tablename VARCHAR(64), 
					' at line 1
USE `datawarehouse`$$
CREATE PROCEDURE `datawarehouse`.`addidmevent` (tablename VARCHAR(64), 
													primarykey INT UNSIGNED,
													type ENUM('U','N','D'))
BEGIN
	INSERT INTO `idmevents` (id,tablekey,status,eventtype,eventtime,perpetrator) 
	VALUES (UUID(), CONCAT(tablename, '-', primarykey), 'N', type, NOW(), session_user());
END

SQL script execution finished: statements: 63 succeeded, 1 failed

Please, check with a newer version, 5.2.19, and inform about the results.

Hi,

Just tested with 5.2.19 and can report the following:

TEST1
Options: DROP objects before each create object, generate drop schema
Result: Executing SQL script in server
ERROR: Error 1046: No database selected

CREATE  OR REPLACE VIEW `dbtest`.`learnerspecialneeds_v` AS 
SELECT lsn.id AS lsnid, lsn.schoolinfo_id, lsn.learnerpersonal_id, snt.desc, lsn.ranking, lsn.startdate, lsn.comment, lsn.needs
FROM learnerspecialneeds AS lsn INNER JOIN specialneedstype AS snt ON lsn.specialneedstype_id = snt.id

SQL script execution finished: statements: 12 succeeded, 1 failed

TEST2
Options: DROP objects before each create object, generate drop schema, Omit Schema Qualifier in Object Names, Generate USE statements
Result: Success

According to http://dev.mysql.com/doc/refman/5.0/en/create-view.html it should be possible to create a view as per test1

Create a table and a view.
Forward engineering.

mysql> SHOW FULL TABLES;

the view not get forwarded.

Problem is that although tables and view itself are fully qualified, the view definition is not.
So, a USE statement is needed before each CREATE VIEW (and probably CREATE PROCEDURE as well).

fix confirmed in repository

An entry has been added to the 5.2.22 changelog:

When an attempt was made to forward engineer a project which contained a view, it failed with the following error:

ERROR: Error 1046: No database selected