MacLochlainns Weblog

Working with MySQL Workbench, I was always curious why you couldn’t run a script with a sourcing command, like source or \. command. It raises a 1064 error code, like the one shown in the illustration.

It turned out that there’s a pending feature request to add the ability to run a sourcing command like the following:

SOURCE c:\DATA\some_script.sql

or,

\. c:\DATA\some_script.sql

I added my business reason to the bug. Let’s hope the product managers add it quickly.

Somebody asked how to solve a problem with the =AVERAGEIFS function and non-contiguous ranges. The solution requires a combination that uses a group of =SUMIFS function divided by an equivalent group of =COUNTIFS functions.

The following illustration shows the problem. It has groups of quarters, and within the quarter groups rows hold products and columns hold weeks. Unfortunately, the =AVERAGEIFS function disallows non-continguous source ranges. It does support multiple criteria, which may be non-contiguous ranges provided they match the number of elements in the source range.

While the sums can be calculated by adding the Total column, the average can’t. You would use the following to calculate the actual average:

=IFERROR(((SUMIFS(D5:P5,D5:P5,">0")+SUMIFS(D18:P18,D18:P18,">0")+SUMIFS(D31:P31,D31:P31,">0")+SUMIFS(D44:P44,D44:P44,">0"))/(COUNTIFS(D5:P5,">0") + COUNTIFS(D18:P18,">0") + COUNTIFS(D31:P31,">0") + COUNTIFS(D44:P44,">0"))),0)

The resolution adds a set of range sums and divides it by an equivalent set of range counts where both values are greater than zero. An =IFERROR function substitutes a zero in the event that the denominator is zero. This last step ensures that the an empty set of cells won’t raise an exception.

I know it helps the person who asks but hope it helps others too.

Somebody posed a question about starting and stopping the database from within the OEM tool as the SYSTEM rather than the SYS user. There’s actually a simple trick to do that, you connect to OEM as the SYSTEM user but when you startup or shutdown the database, you reconnect for those tasks as the SYS user.

You can use the Windows services (services.msc) to start or stop the OEM service. Alternatively, you can do it at the command-line. Instructions for starting and stopping OEM from the command-line are found in this older post.

I put together these screen shots from the point where the DBConsole and database are up and running. That means they show you how to shutdown the database before re-starting it.

1. Navigate to the DB Console home page and click the Shutdown button.

2. After clicking the Shutdown button, you’re prompted for an authorized host operating system credentials and database credentials.

3. You enter the host operating system user and password, and SYS user and password (which is often the same as the SYSTEM password). Click the OK button after entering the host and database credentials.

4. After validating credentials, you get a confirmation page. Click the Yes button.

5. Since a shutdown can take more than a few moments, you get the following screen. Click the Refresh button when you want to check whether the database is shutdown.

6. The Refresh button shows the following webpage. You can click the Startup button to restart the database.

7. Clicking the Startup button re-prompts you for the same host and database credentials as used during the shutdown operation. Click the OK button to continue.

8. After validating credentials, you get a confirmation page. Click the Yes button.

9. After validating credentials and starting the database, you are prompted for the database credential to sign on to OEM.

10. After validating credentials and starting the database, you are prompted for the database credential to sign on to OEM. Enter the credentials and click the Login button to connect to OEM.

Hopefully, this answers the questions and helps those who are curious to see the screens before trying the process.