Hidden Results Grid on MySQL Workbench and macOS High Sierra

MySQL Workbench with missing Results Grid

UPDATE: Oracle has released version 6.3.10!

I recently updated my MacBook Pro to macOS High Sierra. Like every other OS update, this one was fairly uneventful – with the exception of having to do a hard reboot to get the Mac to come back up after the upgrade. That was definitely a nail-biter!

Once the Mac rebooted, everything appeared to be as it should be. All was fine and dandy – or so I thought.

Though I have not had any other major issues, it would appear that High Sierra has broken the current version of MySQL Workbench (6.3.9). When trying to run a query, the Results Grid never appears. A status declaring that the query completed is shown, and the Action Output window will even tell how many rows were returned. However, the rows themselves cannot be seen.

MySQL Workbench with missing Results Grid

It took quite a bit of searching to find this – apparently not too many folks have encountered this problem as yet – but I did find confirmation on Stack Overflow that this is now a known issue, and that the next release should have it fixed.

It is interesting that Oracle has closed the official bug report, despite the fact that no patch or new version is available. A user has devised a workaround that involves installing his build of MySQL Workbench, but that is not ideal in many production environments, for obvious security concerns. The only other solution given was to downgrade to an older version of MySQL Workbench – 6.1.7.

An excerpt of the comments show several users’ reactions to the bug report having been closed with no solution offered:


[20 Oct 10:32] Bob Davenport
Changing back to open

[20 Oct 10:36] Bob Davenport
I’ve changed back to open as its not fixed till the release is available.
I never clsed it perhaps the pperson who closed it could come forward and explain how a bug rendering a product 99% unusable can be marked as closed?

[20 Oct 12:12] Mike Lischke
This bug entry was closed as part of our internal processes. Please don’t touch the status of a bug anymore, once it closed.

[20 Oct 15:25] Bob Davenport
Mike Lischke

Why is a bug closed when their is no fix ?
Or can you advides the date the fix was released?

Very disappointing that the attitude of yours is so poor.

Maybe next time i wont bother to post a bug report or how to fix.
Also mustn’t forget Mike Lischke who also wasted time with the “Works on My PC” attitude in the firs place.

A discredit to the community.

[20 Oct 15:34] Michael Brogley
I agree with Bob. Based on my experience running just such an effort, a critical status bug should only be changed to “Status: Closed” if a fix has been tested and released. Otherwise any Oracle manager that’s tracking open bugs will be misled on the product’s status. Playing bug status games like this basically cuts the nerves between the user community and management, leading to misallocation of resource problems downstream, and managers and executives getting blindsided by angry users.

Is there no “Pending” status in this system?

[20 Oct 15:37] Darren Coleman
Why would a bug be closed if the problem is not fixed?

I’ve never heard of a bug tracking system where people close bugs without actually resolving the problem??

Is it just inconvenient for Oracle to see this (very simple) bug on their TODO list? It’s ridiculous enough as it is that a community member has to go out of their way to patch a bug that existed and was reported more than once while macOS High Sierra was in BETA.. but to close the bug without resolution is just egregious.

[20 Oct 15:57] Mike Lischke
The typical workflow for a bug report in this bug database is like this:

  • The bug gets reported.
  • A support person checks the validity and clarifies things when necessary.
  • The bug is transfered to our internal bug system where the developers are notified.
  • The developers fix the bug.
  • QA tests the fix.
  • The documentation team takes a note for the change log and sets the bug to closed (both internally and externally).
  • Once all bugs for a release are closed the new version is released.

Hopefully this makes the process more clear.

[21 Oct 15:12] Bob Davenport
Mike Lischke/ Oracle Can you please advise:

How many bugs are currently open and closed and a release date please?

The bug renders the application unable to display any query results on high sierra. This isn’t an insignificant or cosmetic issue, it’s a show stopper.

If some other bugs are still unfixed could you advise a cut-off date when a release will be provided?

Is this also affecting Oracle Enterprise customers?
I think it is.

For what it’s worth…

I was performing some beta testing for apple and found the fault so decided to report to Oracle.

I provided Oracle with a suggested fix which is relatively easy to implement and is verified as working.

The fix would be effortless & without risk to deploy as a ‘patch’ if in an Agile development environment was utilised.

I would have thought Oracle would be up to date on this technology by now.

[22 Oct 22:19] Aaron Langley
It’s becoming apparent that Oracle’s senior management intends to starve MySQL of oxygen in the hope it’s user base with move on to other alternatives.

Obviously not an ideal situation. Hopefully, Oracle will release a patch soon!

Connecting to Oracle 12g with the Instant Client and Excel VBA

Excel References

One rather old application I’ve supported for several years loads data from Excel spreadsheets into a reporting database. These Excel spreadsheets have always been manually updated by several users. However, because the data that the users are entering into these spreadsheets are first being entered into another separate application database, these users have been doing double-entry – a redundant process which can be easily remedied by various means.

Ideally, the solution for this problem would be to extract the data from the application database and load it into the reporting database using an SSIS package. Unfortunately, that would require some redevelopment of the application which loads data into the reporting database, and we (and the customers) have no bandwidth for that. So I came up with a quick workaround that made everyone happy – using a VBA macro to automatically populate the spreadsheets with data when the users open them.

The tricky part here was getting Excel to connect to Oracle with the least amount of work having to be done on the users’ PCs, which are remote from my location.

First of all, since these users don’t require SQL Plus or any development tools for Oracle, the full client software was unnecessary. Also, the Oracle Instant Client could be pushed to the users with Altiris Deployment Solution.

I had the Instant Client software installed on the PCs, then I added the requisite database connection information to the tnsnames.ora file.

Nota bene: In the Instant Client (or at least in our setup, using version 11.2.0.4) the tnsnames file is in
C:\oracle\instantclient_11_2_0_4 rather than in C:\oracle\product\11.2.0.4\client_1\NETWORK\ADMIN as it often would be in the full Oracle client.

The connection in VBA was simple enough, but not immediately obvious – notice that the connection string includes “Microsoft ODBC Driver for Oracle” rather than an Oracle driver; even though this is used, no ODBC connection needs to be set up in the ODBC Data Source Administrator. It is only imperative that the proper entries exist in the tnsnames.ora file, and that the Microsoft ActiveX Data Object Library is installed and referenced in Excel. (Add References by navigating to Tools –> References in the VBA editor in Excel.)

Excel References

In a subroutine, this code was used to connect to the database and pull data.

    Dim SQL_String As String
    Dim dbConnectStr As String
    Dim con As New ADODB.Connection
    Dim recset As New ADODB.Recordset
    Dim strUid As String
    Dim strPwd As String
    Dim strEnv As String
    Dim strDSN As String
    Dim iRow As Integer    
    

    strEnv = "prod"
    strUid = "username"
    strPwd = "password"

    If strEnv = "prod" Then
        strDSN = "(prod database net_service_name* from tnsnames)"
    Else
        strDSN = "(dev database net_service_name* from tnsnames)"
    End If
          
    dbConnectStr = "Driver={Microsoft ODBC for Oracle}; " & _
            "Server=" & strDSN & ";" & _
            "uid=" & strUid & ";pwd=" & strPwd & ";"
       
    con.ConnectionString = dbConnectStr    
    con.Open   
    
    SQL_String = "(insert SQL query here)"
           
    recset.Open SQL_String, con

    iRow = 0 
    Do While Not recset.EOF
         'Have a loop here to go through all the fields
        Sheet1.Range("A" & iRow).Value = recset.Fields("colname") ' colname = Column Name from SQL query
        
        ' &c. ...

        iRow = iRow + 1
        recset.MoveNext
    Loop

    recset.Close
    con.Close

* net_service_name

ADODB Issues in VB.NET with an Oracle Database

During my recent VB6 to VB.NET Forms upgrade adventure, I had to wade through quite a bit of code that, while still functional, should be upgraded further when time permits.  Most of this code is used for data access, and being that it was written back in the VB6 / Classic ASP days, Active Data Objects (ADO) was used rather than ADO.NET, which is the newer .NET equivalent of ADO.
A common feature of ADO which was widely used in this project was the Recordset.  An ADO Recordset Object is defined by Microsoft as an object that “represents the entire set of records from a base table or the results of an executed command.  At any time, the Recordset object refers to only a single record within the set as the current record.” (Link)

One of the problems in using the recordsets object with VB.NET and Oracle is that if the recordset is not closed and garbage collected once the program is done with it, an Oracle error reporting too many open cursors (ORA-01000) is often thrown.  Many Web sites say that one possible fix to this error is to increase the number of allowed open cursors, but this solution only hides bad code.  Instead, you must make certain that the object is closed, set to Nothing, and garbage collected.

To resolve this problem, I created a module that contains methods to destroy serveral types of objects: ADODB Connections, ADODB Recordsets, Excel applications, and Scripting FileSystemObjects.


Module modDestroyObjects

Public Sub DestroyConnection(ByRef cn As ADODB.Connection)
If cn IsNot Nothing Then
If Not cn.State = 0 Then
cn.Close()
End If
cn = Nothing
CollectGarbage("ADODB.Connection")
End If
End Sub

Public Sub DestroyRecordset(ByRef rs As ADODB.Recordset)
If rs IsNot Nothing Then
If Not rs.State = 0 Then
rs.Close()
End If
rs = Nothing
CollectGarbage("ADODB.Recordset")
End If
End Sub

Public Sub DestroyExcelApp(ByRef xlApp As Microsoft.Office.Interop.Excel.Application)
If Not (xlApp Is Nothing) Then
For i As Integer = xlApp.Workbooks.Count To 1 Step -1
xlApp.Workbooks(i).Close(False)
Next i
xlApp.Quit()
xlApp = Nothing
CollectGarbage("Excel.Application")
End If
End Sub

Public Sub DestroyFileSystemObject(ByRef fso As Scripting.FileSystemObject, ByVal str As String)
If Not (fso Is Nothing) Then
If fso.FileExists(str) Then
fso.DeleteFile(str)
End If
CollectGarbage("FileSystemObject")
End If
End Sub

Private Sub CollectGarbage(ByVal strName As String)
GC.Collect()

'' Uncomment below to see a Message Box each time Garbage Collection is manually invoked.
'MsgBox("Object " & strName & " has been collected.", MsgBoxStyle.OkOnly)
End Sub

End Module

By calling the appropriate method, the object is removed from memory immediately, rather than waiting for automatic garbage collection to take place.  While this does increase system overhead somewhat, I noticed no decrease in performance.  On an older / slower computer, this increase in overhead could be noticeable.  However, this ensures that — at least until I can rewrite the code using ADO.NET — the program can run many queries without throwing the dreaded ORA-01000 error.