Add support in ER Modeler for the new version.

Tom, I have following queries for features described in description:

1. In point no.2, do we have to use "CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';" instead of "GRANT USAGE ON *.* TO `jeffrey`@'localhost' IDENTIFIED BY 'password'"  while creating user using the "Create User" button? 

Yes. You will need to change the Create, Alter User gui. See the syntax here in the documentation.  You will have to change the scripting. See screenshot. You will have to change the DBAtools security manager. See screenshot.

2. Please elaborate more regarding support Roles, any mock-ups/screenshots for adding roles in security gui will be helpful.

You can use DB2 as an example of how to add roles in the schema tree and in DBAtools. See DB2RolesNode as a starting point. Here is the MySQL Role documentation

3. In point 4, I am not able to reproduce problem with columns out of order for given tables, however there was an exception being thrown which I fixed. Please elaborate more on that point.

Hmm. It seemed like it wasn't working when I tested the drivers. Let's not worry about it then.  I'm sure QA will catch it if there is a problem.

Thanks,

Tom

The following tasks are completed and checked-in to SVN repository (Revision no. 56002):

Create user and alter user tasks are completed and checked-in to SVN repository (Revision no. 56008)

1. Version 8 uses "CREATE USER <username>@'<hostname>' IDENTIFIED BY '<password>'" syntax instead of "GRANT USAGE ON *.* TO <username>@'<hostname>' IDENTIFIED BY '<password>'" syntax while creating user.

2. Version 8 uses "ALTER USER <username>@'<hostname>' IDENTIFIED BY '<password>'" syntax instead of "SET PASSWORD FOR <username>@'<hostname>' = PASSWORD('<password>')" syntax while modifying user.

Other usage of GRANT privileges are same.

These changes will be available in build no. ADS 19.5.0-dev-13

Tom,

What should be the layout of Roles in DBA Tools->Security Manager for MySQL 8.0?

In DB2, the tree nodes are grouped by Users, Roles and Groups and having individual tabs for each. Please refer

While in MySQL, there is dropdown and tree nodes are grouped by Hostname and Username. However, the roles in MySql 8.0 is like users and also getting saved in same table mysql.user.

Do we have to follow DB2 layout for MySql also? Or I have to add two root nodes under localhost when items are grouped by Hostname, like:

localhost
  |--Users
       |--root
       |--mysql.sys
  |--Roles
       |--MyRole1
       |--MyRole2
     

And when items are grouped by Username, then show UI as it is.

Also I will add "Role" item in dropdown and when items are grouped by Role, then show UI similar to that we are showing in Username and showing Rolename instead of username.

Please suggest.

[Nhi]

1. The above layout for "group by Hostname" is fine.
2. Please do not add a group by "Roles" in the dropdown. Instead, rename the "Username" ==> "Username/Rolename". For example, the Group by dropdown should look like the following:
   • Hostname
   • Username/Rolename
3. When Group by Username/Rolename, the layout should be as follows:
   • Users
     • user1
       • localhost
     • user2
       • localhost
     • basically same as before
   • Roles
     • same as layout for Users

I've add response to this comment.

Hello Tom,

For point1:

** When we are creating table through:- Database>table>create table> Insert some data like ID,Salary with datatype bigint > then create index for desc in "Salary" > execute: Right click on table and select "Select Top 1000 rows" and execute > In output we are able to see the salary in DESC order. click

But if we create table through query analyzer then we are not able to see the same result . click

1)How to check the result of DESC index , and on which area's  we need to check.

2)Can this DESC Index be applied on VARCHAR and other datatypes.

3)Can we create two DESC index on same table , if yes  then how the results should be displayed.

4)What is the use of invisible indexes and how to check it.

Thanks, Nisha

Hi Nisha,

Don't get confused by the order that the rows are displayed in the QA results window and indexing. Rows displayed in the QA results are usually displayed in the order that the data was entered in the database. Consider the following test case.

GO

When you do a select you will see the rows in the same order that they were entered.  See here. The one way to change that display order is by using an order by clause at the end of the select.

On the other hand,  indexes are a separate list kept in the database based on the column(s) used for the index. This index will help the database optimizer find a row in the table faster thereby speeding up transactions. This is important when it comes to gigabytes of data. Database indexing can be very complex but here is a short explanation about it. 

You can have multiple indexes on a table. See here. Not something that you real would want to do unless there is a specific access path that you are looking for. As you add indexes, you add overhead to inserts. 

The only way to check that the index was created is to look at the schema tree and verify that the index is attached to that table and column. The database takes care of all the rest behind the scenes.

Hope that helps,

Tom

Hi Nisha, 

INVISIBLE indexes are just like regular indexes except they are not used by the database optimizer to speed up the access path to the data.  You would test them the same way by looking at the schema tree to see if the index is attached to the table and column.

Also, please test that create index gui creates the index correctly by looking at the preview screen. For example, I see a blank in the visibility selection drop down. See here. This should not be like this. The default is visible and should be the first selection in the drop down. Invisible should be the second selection. If the user uses the default - visible or selects visible, the create should not have any additional clause. Only add it when invisible is selected. Please have Pankaj take a look at this.

Thanks, Tom

Please find below the test cases for implemented new features for MySQL 8.0.

Descending Indexes:

1. The DESCENDING option is now available in Order drop down in Create Index dialog. Please refer screenshot
2. Descending indexes are supported only for InnoDB storage engine.
3. Descending indexes are not supported for Hash indexes, for FULLTEXT or SPATIAL indexes.

Invisible Indexes:

1. The Visibility option is now available in Options tab in Create Index dialog with drop down having VISIBLE and INVISIBLE options. Please refer screenshot
2. By default, the index is VISIBLE, hence if user selects VISIBLE option or leave it then, in Preview SQL script does not include VISIBLE clause in query.
3. When user selects INVISIBLE option in Visibility drop down, then INVISIBLE keyword is included in the script in Preview SQL tab. Please refer screenshot 
4. This feature applies to indexes other than Primary keys (either explicit or implicit). The implicit primary key index means the table has unique index created on NOT NULL column.

Create/Alter User Syntax:

1. While creating user, the query is now using "CREATE USER <username>@'<hostname>' IDENTIFIED BY '<password>'" syntax instead of "GRANT USAGE ON *.* TO <username>@'<hostname>' IDENTIFIED BY '<password>'" syntax. Please refer screenshot
2. While modifying user, the query is now using  "ALTER USER <username>@'<hostname>' IDENTIFIED BY '<password>'" syntax instead of "SET PASSWORD FOR <username>@'<hostname>' = PASSWORD('<password>')" syntax. Please refer screenshot 
3. Other usage of GRANT privileges are same.

Roles Support:

The Roles are similar to Users in MySQL 8.0 

1. The Roles node is now available in Schema Tree under Security node as the sibling of Users node. Please refer Screenshot 
2. The expansion of Roles node shows roles available for current database connections
3. The popup menu of Roles node having Create Role option. Also, individual role node's pop up menu having all options like Create Role, Alter Role, Drop Role, Role properties and Scripting options.
4. The Details panel at the bottom shows table contains all roles and their privileges when Roles node is selected in Schema Tree
5. The Create Role dialog contains General, Database Access and Preview SQL tabs. The General tab contains text fields for Role name and Host. Please refer screenshot 
6. The Database Access tab contains Global Privileges and Database Privileges, the content of this tab as same as User's Database Access tab. Please refer screenshot 
7. The Preview SQL tab contains the queries for Create Role and Grant Privileges. Please refer screenshot 
8. The Alter Role allows to Grant or Revoke Privileges, the role name and host text fields are disabled.
9. The Drop Role option drops the role from database
10. The Role Properties option shows the role's properties that are configured during Create/Alter Role.
11. The Script Object options shows Create Role and Drop Role queries.
12. The Role could be Grant to User and Revoke from User
13. The Roles tab is now available in User dialog (Create/Alter User and User Properties). Please refer screenshot 
14. The Roles tab contains all roles available for the database connection.
15. The user can select Role to Grant role to user with or without admin option. The role is selected to Grant role to selected user without . Please refer screenshot  When role is selected with double tick in checkbox then user grant role with admin option
16. When the checkbox is deselected (with cross mark) then Role will be revoked from selected user. Please refer screenshot 
17. The DBA Tools -> Security Manager's toolbar now having buttons for Create Role, Alter Role, Drop Role and View Role. Please refer Screenshot 
18. The Group By drop down is now having Hostname and Username/Rolename options.
19. When Hostname is selected in Group By drop down, then the tree will have <hostname> root node and under <hostname> will have Users and Roles node. The Users node contains all the users for selected host and Roles node contains all the roles for selected host.
20. When Username/Rolename is selected in Group By drop down then the tree will have Users and Roles root node. The Users node contains all the users for database connection and each User contains the host. The Roles node contains all the roles for database connection and each Role contains the host. Please refer screenshot 
21. In the Group By Hostname view, the hostname node having popup menu with Create User and Create Role options. The Users root node having pop up menu with Create User option and each User node having popup menu with Create User, Alter User, Drop User and View User options. The Roles root node having popup menu with Create Role option and each Role node having popup menu with Create Role, Alter Role, Drop Role and View Role options.
22. In the Group By Username/Rolename view, the Users root node and each User node having pop up menu with Create User option. The hostname under a user having popup menu with Create User, Alter User, Drop User and View User options. The Roles root node and each Role node having popup menu with Create Role option. The hostname under a role node having popup menu with Create Role, Alter Role, Drop Role and View Role options.
23. When User node in Group By Hostname or hostname node under user in Group By Username/Rolename is selected then User details panel will be shown for selected User. Please refer screenshot 
24. When Role node in Group By Hostname or hostname node under role in Group By Username/Rolename is selected then Role details panel will be shown for selected Role. Please refer screenshot

Table Script:

Fixed exception "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'INFORMATION_SCHEMA.INNODB_SYS_TABLES' doesn't exist.". As per MySql 8.0 documentation, INNODB_SYS_* tables are renamed to INNODB_* tables, for example INNODB_SYS_TABLES renamed to INNODB_TABLES.

ER Modeler:

ER Modeler is now showing 8.0 version of the MySQL database. Please refer screenshot 

Test cases for new features for MySQL 8.0.

Descending Indexes:

Hi Nisha, Harish,

When I use the alter role or role show properties menu, I see a button for object permissions. This button takes me to the alter user object permissions. DBA tools has the same problem. I don't think that should be like that. Please have a look.

I noticed that there is an extra GO when scripting object to window. See here. Please have a look and make sure that it is not causing other issues. 

Hi Nisha, 

Please also regression test MySQL 5.7 in these area's and make sure that none of the changes affect the original function. 

Looks good...

Thanks, Tom

I am looking into it

Hi Harish,

If you open the SQL log and click on the object permissions, you will see that the query is returning the correct object information. It just doesn't appear to be displaying it. See here. Can you take a look and see why that isn't working? 

Thanks, Tom

Hi Tom,

The issue with displaying Object Permissions for Users and Roles is fixed. Also, the issue with displaying Column Permissions for Users and Roles is fixed. Please find below the SVN revision info:

Hi Harish,

Ok.. Looks good.

Thanks, Tom

Getting Exception

When we have created Role without specifying localhost and there is User with specified localhost and open DBA tool Security Manager then we get this exception. Click

Hi Harish,

Looks good.

Thanks, Tom

When trying to delete multiple Roles at a time its showing Error: "Failed to extract object: MYSQL8.0.11...Role2000k"  Click

Steps: Select multiple Roles ( Ctrl+click) 

           Right click and select Delete

Fixed issue with deleting multiple roles, please find revision info below:

Hi Harish,

When I tried object permissions for functions, it doesn't seem to display the permissions. Here is where I added them. When I re-display them using properties here, I don't see them. Please have a look. 

Hi Nisha, 

Please test this thoroughly across all four objects.

Thanks, Tom

Hi Tom,

Root Cause:

The problem was with case-sensitivity of the object (Table, View, Procedure or Function) name. The mysql.proc_priv table stores function/procedure name in lowercase always e.g. getfullname, while information_schema.ROUTINES table stores function/procedure name in the case in which user type the name e.g. GetFullName.

While fetching object permissions, we are fetching information from one table and create a map using object name as the key. Then fetching the list of functions/procedures from another table and trying to get value from the map using object name as the key. As both tables store object name in different cases, the value of a particular key coming as null.

The issue is not occurs when we are creating function/procedure with lowercase names.

I fixed it in below mentioned revision no.:

Hi Harish, Juhi, 

I found a problem when executing an explain in MySQL 8. It is caused because the explain statements have changed for MySQL 8. I attached a patch with a fix. Please install the patch and test the explain out. From our documentation:

ADS provides a "Show Execution Plan" option in the Main Application Toolbar. If this option is enabled an execution plan will be generated for each query executed in the results Execution Plan tab. Multi-execution plans are supported and executing a script will generate multiple execution plans. Aqua Data Studio also provides an "Execute Explain" button in the query window which will generate an execution plan for the current query or the highlighted queries in the Query Analyzer window.

Thanks in advance, Tom

Hi Tom,

I applied the patch and tested explain plans, its working fine. I committed changes in below mentioned revision:

Execution Plan is working fine

Tested on  build no. ADS 19.5.0-dev-46

Thanks 

Nisha

Hi Harish, 

I noticed that the column order when scripting for MySQL 8 is not always correct. See here. The order in the schema tree is correct but the scripting is not. Notice in the screenshot that the top create ddl is not correct when doing a "Script object to". I attached a patch that fixes the column order. Notice that the bottom create ddl is correct now. Please code review and install the patch. QA will need to retest the scripting in SchemaScriptGenerator, Schema browser "Script object to" and create table gui preview for MySQL 8 and MySQL 5.7.

Thanks, Tom