#12283: In MySQL, In Schema Script Generator, object qualifying name is not generated for “Table/Views/Procedure/Function”.

deepalisalve reported 2014-06-19T07:13:49Z · last modified 2017-08-01T05:33:58Z

In MySQL, In Schema Script Generator, object qualifying name is not generated for “Table/Views/Procedure/Function”.

Dev	JennyNishimura
QA	ashwini.chavan

Priority	Low
Complexity	Unknown

Component	DB - MySQL
Version	19.0

Aqua Data Studio 15.0.0-dev-329
Build #: 38608
Built on: 2014-Jun-18 05:52:13 PM

Operating Environment : Windows, Linux
Issue : In MySQL, In Schema Script Generator, object qualifying name is not generated for “Table/Views/Procedure/Function”.

Steps to Reproduce :
1.In ADS, connect to MySQL 5.7.4 Database, expand it
2.Go to Databases node, right click and select option “Tools->Schema Script Generator”
3. In “Schema Script Generator” window,
  i)Check objects “Table/Views/Procedure/Function” from Objects Types pane.
  ii)Then check any “Table/Views/Procedure/Function” from Objects pane.
  iii)Click on “Next” button
4.In Options tab,
   i)Select “Preview Tab” from “Save as” drop down
ii)Check option “Generate the CREATE”
iii)Check option “Qualify Objects”
iv)Click on Next button
5.In preview tab, we can see that qualifying objects are missing in script. In MySQL it should show database name before table/view..etc name.
6. Whereas, for Triggers it shows qualifying name successfully. It shows database name before trigger name.
(Follow same steps, just select “Triggers” instead of “Table/Views/Procedure/Function”)
7.If we create (“Table/Views/Procedure/Function”) in query analyser then it takes database name before these objects and it runs also.

10 attachments

objects_qualify.png

2014-06-19T07:13:49Z

222 KB
reopened_12283.png

2017-06-22T09:22:13Z

86 KB
scenario2.png

2017-06-22T12:24:54Z

273 KB
ADS-19-alpha-57.png

2017-06-23T12:18:36Z

134 KB
issue_12283_Alter_UI.png

2017-07-31T11:03:15Z

176 KB
issue_12283_verify.png

2017-07-31T11:03:15Z

178 KB
issue_12283_case_1.png

2017-07-31T11:03:15Z

287 KB
issue_12283_Schema_Script_Generator.png

2017-07-31T11:14:25Z

121 KB
issue_12283.sql

2017-07-31T11:21:33Z

3 KB
issue_closed_2nd_12283.png

2017-08-01T05:31:09Z

121 KB

All Comments (15) Change History

Shavi57 2015-07-10T06:18:47Z · (edited)

In MySQL qualifying objects are missing in script for Indexes and Events.

SachinPrakash 2017-04-20T21:31:40Z

@pranav: has this issue been addressed?

pranav.jadhao(*) 2017-04-21T07:41:48Z

@Sachin Sir,

This issue has not been addressed yet. But for Schema sync, as the query analyzer is opened with target database we do not face any issues.

ivangron 2017-06-21T16:53:28Z

I have made the needed changes so that the objects are qualified with there schema field now. The schema field was never stored until v19. For compare, objects will never qualify with schema for mysql. Database & Schema are synonyms, but in the compare dialog the user will not have a list of schemas to choose from, but instead will have the <All Schemas> selected.

@Tariq : I have tested this with all objects. The schema qualification will only be done for versions 5.0 and above, so no need to test before that version. We need a full regression test scripting objects for this.

Committed revision: 54812

nareshnasha(*) 2017-06-22T12:28:20Z · (edited)

Product: Aqua Data Studio

Version: 19.0.0-alpha-53

Build #: 54823

Build Date: 2017-Jun-21 10:07:41 PM

Operating Environment: Linux (4.4.0-81-generic, amd64) / UTF-8 / en / IN / Oracle Corporation 1.8.0_102-b14

Memory: Max=704,643,072; Total=432,013,312; Free=334,250,496; CPUs=8

Summary :- Cannot Create objects from GUI due to incorrect Schema name.

Steps to Reproduce:-

1. Launch ADS

2. Connect to MySQL 5.7

3. Create table through GUI.

Result:-

Cannot Create table through GUI. "Unknown Database <default>" error popup message is getting displayed. Please see attached reopened_12283.png

Observations:-

1. Tables, Views, Functions, Procedures, Events and Triggers are not getting created through GUI.

2. Broken Build:- ADS 19 alpha-52

3. In ADS 19 alpha-49, its working Fine and Build between alpha 49 and 52 are not available.

nareshnasha(*) 2017-06-22T12:25:32Z · (edited)

SCENARIO 2:-

Product: Aqua Data Studio

Version: 19.0.0-alpha-53

Build #: 54823

Build Date: 2017-Jun-21 10:07:41 PM

Operating Environment: Linux (4.4.0-81-generic, amd64) / UTF-8 / en / IN / Oracle Corporation 1.8.0_102-b14

Memory: Max=704,643,072; Total=439,877,632; Free=302,069,264; CPUs=8

Steps To Reproduce:-

1. Launch ADS

2. Connect to MySQL 5.7

3. Right click on Database treenode and select Query Analyser.

4. In Query analyser Execute below Script.

Create database naresh

Use naresh

CREATE TABLE t1 (

c2 int(11) NULL,

c3 text NULL

)

ENGINE = MyISAM

AUTO_INCREMENT = 0

CREATE INDEX ind USING BTREE

ON t1(c3 (25), c2)

CREATE TRIGGER TRIGGER_EMP21

BEFORE UPDATE ON t1

FOR EACH ROW

BEGIN

INSERT INTO t1(`C2`, `C3`) VALUES (100, 'RAM') ;

END

5. Now Right click on the Created table and Select Tools -> Schema Script Generator.

6. In Script Generator Window, Under Object Types Select Tables and Trigger object type and Select the created table and trigger Checkbox. Then Click on Next.

7. Select Preview tab in Save as option and also Make Sure "Qualify Objects" option is unchecked. Click on Next

Result:- You will observed that table name is getting displayed with database name as well. Is this the Expected behaviour? Please see attached scenario2.png

ivangron 2017-06-22T16:01:36Z

Table, view, procedure, function and trigger dialog code has been modified to handle this correctly now. There is no GUI for event objects. Modifications made to the scripting of triggers so that qualify object flag will also be taken into account when scripting its target object. Code checked into US trunk with Committed revision: 54829

tomconrad 2017-06-22T16:05:56Z

I tested the Database Schema and Data Exporter project to make sure that the objects in the ddl were getting qualified correctly when "Script Qualify Object" is checked and unchecked. This is working correctly. Additionally, I tested the ddl extraction before and after the fix and now all the objects are getting qualified when "Script Qualify Object" is checked. I tested this with MySQL 5.1, 5.5, 5.6 and 5.7.

ashwini.chavan 2017-06-23T12:19:25Z

ADS build: 19.0.0-alpha-57

Screenshot: ADS-19-alpha-57.png

Steps:

1. Create a table with view on MySQL DB.

CREATE TABLE `view_dep_table_01`  (
  `Id`   int(11) NOT NULL,
  `Eid`  integer(11) NOT NULL,
  `Name` text NULL
)
GO
CREATE VIEW `view_dep_table_02`
AS
SELECT `Id`, `Eid`, `Name` FROM `view_dep_table_01`
GO

2. Go to Views schema node and expand it.

3. Select view and right click select 'Alter View'.

4. In 'Alter View' window go to 'Preview SQL' tab.

Result: Following message is displayed:

/* Invalid: View name and schema name

must match the editing object */

5. Now go to 'General' tab and remove database name displayed just before to view_name in 'CREATE OR REPLACE VIEW' script.

6. Now go to 'Preview SQL' tab, following script gets displayed. But schema_name is displayed as `<DEFAULT>`

CREATE OR REPLACE VIEW `<DEFAULT>`.`view_dep_table_02`

select `sm1`.`view_dep_table_01`.`Id` AS `Id`,`sm1`.`view_dep_table_01`.`Eid` AS `Eid`,`sm1`.`view_dep_table_01`.`Name` AS `Name` from `sm1`.`view_dep_table_01`

7. Click on Alter button.

Result: Error message pop up appears with following message.

Unknown database '<DEFAULT>'

JennyNishimura 2017-06-27T22:18:14Z

The code changes made in SVN 54812 and 54829 have caused regression issues. I've reverted those code changes. The problem reported in this comment should now be fixed.

I'm resetting this issue to New to look into the original problem reported in the issue description.

JennyNishimura 2017-06-28T06:16:18Z

Refer to issue VA:#9586. Trigger names are no longer qualified with the database name in v19.

JennyNishimura 2017-07-29T04:57:02Z

5.In preview tab, we can see that qualifying objects are missing in script. In MySQL it should show database name before table/view..etc name.

Checking ADS code from previous releases, we intentionally make separate code paths for MySQL to not include database or schema name even when the qualify object flag is on. See AFBaseDBObject.getQualifiedName.

This is expected behavior. Won't Fix.

6. Whereas, for Triggers it shows qualifying name successfully. It shows database name before trigger name.

In v19, we've already fixed the scripting code for Trigger such that the database name is no longer included. All scripting code is now consistent for MySQL.

ashwini.chavan 2017-07-31T11:30:29Z · (edited)

Tested On: ADS 19.0.0-beta-27

Database: MySQL 5.1, 5.4, 5.5, 5.6, 5.7

I have verified following four scenarios for all objects like (Table/View/Trigger/Index/Procedure/Function/Event):

1. Create object by executing script from QA and select 'Script Object to Window As->CREATE'(issue_12283.sql,issue_12283_case_1.png)

2. Create object through CREATE GUI and verify script generated in 'Preview SQL' tab.(issue_12283_verify.png)

3. Open ALTER object GUI, and do some changes then check script generated in 'Preview SQL' tab.(issue_12283_Alter_UI.pn)

4. Verify 'Schema Script Generator' option and check script generated.(issue_12283_Schema_Scri)

Observations:

1. Database or schema name is not listed before to object name in script.(Table/View/Trigger/Index/Procedure/Function/Event)

2. But, this comment behaviour is still observed. The database name is displayed just preceding to table name in CREATE Trigger script. Can you guide us about expected behaviour or current behaviour is expected?

Reopened this issue for the above-highlighted scenario?

JennyNishimura 2017-07-31T22:06:28Z

Resolution for main issue description: Won't Fix

Resolution for highlighted scenario in this comment: Fixed in beta-28

ashwini.chavan 2017-08-01T05:33:42Z

Verified above-highlighted scenario in ADS 19.0.0-beta-28. The issue is no longer reproduced.(issue_closed_2nd_12283.png)

Search Tips

Aqua Data Studio / nhilam

In MySQL, In Schema Script Generator, object qualifying name is not generated for “Table/Views/Procedure/Function”.

10 attachments

Issue #12283

Completion

2 issue links

Issue #15339

Issue #15347

Search Tips

Aqua Data Studio / nhilam

Title

In MySQL, In Schema Script Generator, object qualifying name is not generated for “Table/Views/Procedure/Function”.

10 attachments

Issue #12283

Completion

2 issue links

Issue #15339

Issue #15347