MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Mac OS X’ Category

Mac Mini to the rescue

with 7 comments

In teaching, I had a problem because my students have different base operating systems, like Windows 7, Windows 8, Linux, and Mac OS X. I needed a teaching and lecture platform that would let me teach it all (not to mention support their environments). That meant it had to virtualize any of the following with a portable device:MacMiniConsole

  • Windows 7 or 8 hosting natively an Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6
  • Windows 7 or 8 hosting a Fedora or Oracle Unbreakable Linux VM (3 or 4 GB) with Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6
  • Mac OS X hosting a Fedora or Oracle Unbreakable Linux VM (3 or 4 GB) with Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6
  • Ubuntu hosting a Fedora or Oracle Unbreakable Linux VM (3 or 4 GB) with Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6

I never considered a manufacturer other than Apple for a laptop since they adopted the Intel chip. Too many of the others sell non-hyperthreaded laptop machines that they market as i5 or i7 64-bit OS machines when they’re not. Some of those vendors disable the hyperthreading facility while others provide motherboards that can’t support hyperthreading. The ones I dislike the most provide a BIOS setting that gives the impression you can enable hyperthreading when you can’t. All Apple devices, MacBook, MacBook Pro, Mac Mini, and Mac Pro do fully support a 64-bit OS and their virtualization.

A MacBook Pro came to mind but the disk space requirements were 1 TB, and that’s too pricey. I went with the Mac Mini because with 16 GB of memory and a 1 TB drive it was only $1,200. Add a wireless keyboard and mighty mouse, and an HDMI and mini-DVI connections, and I had my solution. Naturally, my desktop is a one generation old Mac Pro with 64 GB of memory and 12 TB of disk space, which supports all the virtual machines used for testing. Note to Apple marketing staff: The prior version of the Mac Pro let you pay reasonable (3rd party) prices for the additional memory and disk drives.

The Mac Mini means I can travel anywhere and plug into the console and demo tools and techniques from a myriad set of platforms without the hassle of moving on and off to frequently VM images. It’s a great solution with only one downside, HDMI to DVI sometimes creates purple toned screens. It’s unfortunate because some venues have monitors that don’t support HDMI).

Written by maclochlainn

February 6th, 2014 at 12:17 pm

Mountain Lion Pre-MySQL

with 10 comments

While I try to contain everything about installing MySQL in a single post that I update from time-to-time, Mac OS X, Mountain Lion (10.8.x), requires some pre-steps. You must install XCode, and the Command Line Tools. This post provides the screen shots and step-by-step instructions.

Before you can do any of these steps, you must connect to the Apple Store and download XCode. Dependent on your internet speed this may take some time. Generally, it’s an hour or less.

  1. After installing XCode, click the Rocket Spaceship in the Mac Dock to launch a view of your Applications. Launch XCode by clicking on the hammer overlaying the blue background XCode icon shown below.

  1. After launching XCode, click the Install button on the lower right of the System Component Installation screen shown below.

  1. You’re prompted for your default user (actually a sudoer authorized user) password. Enter it in the dialog and click the OK button to continue.

  1. After entering the valid credentials and a couple minutes, you should see the System Component Installation screen shown below. Click the Start Using XCode button to continue.

  1. Inside XCode, click on the XCode menu option and select the Preferences menu option, as shown below.

  1. You should be on the General tab of XCode’s Preferences dialog. Click on the Downloads tab.

  1. You should see three choices (at the time of writing) in the Downloads tab. As shown, select the Command Line Tools item. It should take only a couple minutes to download. Click the Install button to continue.

  1. The Install button disappears and is replaced by an Installed label when the Command Line Tools are installed. You should see the following screen shot.

After completing these steps, return to the other post to install and configure MySQL. While it seems this exists already as content on Apple’s site, it seems some folks wanted me to add it with the step-by-step screen shots.

Written by maclochlainn

December 10th, 2012 at 8:55 pm

Posted in Mac,Mac OS X,MAMP,MySQL,Xcode

Tagged with , ,

Mac Disk Failure & Recovery

without comments

The first time I had a major failure on my Mac Pro, I lost a 1 TB Seagate drive and ten key virtual machines. It taught me to apply the principles from my production life to my private life. Swapping the fault-prone Seagate drives for Hitachi drives, I began the slow process of rebuilding those virtual environments.

My solution to minimize risk was two fold. First, I put the main 320 GB disk on a time machine backup. Second, I began weekly backups of my virtual machines to two external 2 TB disk drives. After all, I wanted to contain cost.

Paying more attention paid off this week, when I got the flashing drive folder error. It’s the equivalent on the Mac OS X to Microsoft’s Blue Screen of death. This error means the machine can’t find a healthy OS. The problem is that there a number of posts out there, and some aren’t as effective as they appear in solving the problem. More or less, when you see this screen you have two tests before getting a new disk and restoring the image from your time machine.

The first step requires you to reboot the machine that’s stuck looking for an operating system. You can do that by pushing the power button until a reset occurs while simultaneously holding down the Option and key. You’ll know it works if you don’t see the blinking file folder icon and you see a mouse arrow displayed in the screen. It should occur within 5 to no more than 10 seconds after you reboot.

Insert a valid Mac Operating System (OS) disk into the optical drive. It should launch the installation program within 30 to 60 seconds. Click the first proceed button and on the next screen launch the Disk Utility from the displayed menu. In the Disk Utility, click the First Aid tab. Look at the Total Capacity value in the bottom right of the screen. If it provides a numeric value, there’s a hope for your disk recovery. Click on the First Aid tab to try and recover the disk drive. If it provides a zero numeric value, there’s virtually no hope for your disk recovery.

Since the likelihood of recovering the disk at this point is low, buying a new disk is probably the best step. After you’ve ordered the replacement disk, you can boot your Mac in target mode. Target mode allows you to use another Mac and it’s operating system to run your disks (on a Mac Pro, there can be up to 4 disks).

Remove the CD copy of the operating system from the optical drive and push the power button to turn off your non-working Mac. Start the remote Mac and connect the two using a FireWire cable. After the new target machine has finished booting its copy of the OS, push the power button on the machine that can’t find the OS and hold down the T key. In the target machine, open the Finder and inspect which disks are found. If the base disk drive is excluded from the list, as it is in the screen capture on the right, your disk has failed.

At this point, you should definitely buy a new disk unless you’re covered by AppleCare. If the latter, log a ticket and let them fix it. It’s even possible they may be able to recover something from your failed disk. Although, it is unlikely your get any data back if the Mac OS X software can’t recognize the disk.

Your best bet is to recover the image from a time machine restoration. Hopefully, you had an active time machine image not too far before the failure. I did, and it fully recovered everything smoothly. The new disk arrived this morning, and I’m less than 30 minutes from a full recovery. Though that’s unimportant unless I tell you how.

Once you physically install the disk, you reboot the machine that’s stuck looking for an operating system. You push the power button while simultaneously holding down the Option key. Insert a valid Mac Operating System disk into the optical drive. It should launch the installation program within 30 to 60 seconds. Click the first proceed button and on the next screen launch the Disk Utility from the displayed menu. In the Disk Utility, click the Partition tab to partition the new disk. Then, choose to restore from a time machine in the installation program.

Bottom-line: I’m so happy that time machine works so well!!!

Written by maclochlainn

December 8th, 2012 at 6:07 pm

Delay or synchronize it?

with one comment

A couple students in one of my classes ran into a problem when competing Java threads tried to insert new rows in a table. They raised an error when they tried the DELAY keyword to avoid the race (collision) condition in an INSERT statement. It was simple to explain to them that the DELAY keyword doesn’t work with an InnoDB table. Any attempt throws the following error:

ERROR 1616 (HY000): DELAYED OPTION NOT supported FOR TABLE 'message'

Important Update: INSERT DELAYED is gone in MySQL 5.6.6 (announcement) and the whole issue comes down to synchronizing threads (some dislike the solution) or using the ON DUPLICATE KEY clause.

They retested their Java application after redefining the target table using the MyISAM engine. They found it worked but that’s a bad fix in Java (a brief Java/MySQL tutorial post). They really needed to synchronize the Java thread (line #22), leave out the DELAY keyword, and manage the table with the InnoDB engine. Here’s the modified Java code (by the way, they named their project VulcanTech if you’re wondering about the packages in the import statement):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
package vulcantech.vth.server.commands;
 
import java.io.IOException;
import java.net.Socket;
 
import vulcantech.vth.server.combeans.MessageBean;
 
public class MessageHandler implements Handler {
 
  @Override
  public void handleIt(Object... args) {
 
    MessageBean message = (MessageBean) args[0];
    Socket sock = (Socket) args[1];
 
    DatabaseConnection dbconnection = new DatabaseConnection();
 
    String update = new String("INSERT INTO message(message_timestamp, sender, recipient, message, checked) VALUES(\'" 
                  + message.getTimeStamp() + "\', \'" + message.getSender() + "\', \'" 
                  + message.getRecipient() + "\', \'" + message.getMessage() + "\', b\'0\')");
 
    synchronized (this) {
      dbconnection.executeUpdate(update);
    }
 
    try {
      sock.getOutputStream().write(1);
    } catch (IOException e) {
      e.printStackTrace();
    }
    dbconnection.close();
  }
}

Hope this helps those who encounter race conditions against MySQL when you’re writing Enterprise Java Beans (EJBs).

Written by maclochlainn

July 22nd, 2012 at 3:53 pm

Add Image Comment – VBA

with 9 comments

Quite some time ago, summer 2008, I wrote a post about how you could embed an image in a cell comment. It was for the then current version of the product – Excel 2007. Here’s a User-Defined Function (UDF) in VBA to perform that trick that works in Excel 2010/2011. A comment on that older post fed my disappointment that Excel 2011 doesn’t even support the navigation but it does perform it with VBA. This includes the UDF to add an image and an ordinary Excel Macro to remove the image when you want to preserve the text.

Let’s assume you have a list of image files in a directory and that you’ve entered their fully qualified or absolute path values column B of your worksheet. Now you want to load them as comment images in Column A and insert a value in each column A cell that describes the comment image.

Unfortunately, as I’ve explained before you can’t assign the image as a property of the cell (or more precisely, I’ve never found a way to do it). If this is wrong, please post the magic here for all to enjoy without a fee or registration. ;-)

The following UDF takes a string value to describe the image and a cell reference that holds a string value that holds an absolute file name, which is a logical drive letter (C:\), a file path, and file name.

Function InsertCommentImage(title As String, cellAddress As Range)
  Dim commentBox As comment
  ' Clear any comments before attempting to add them.
  Application.ActiveCell.ClearComments
  ' Define the comment as a local variable and assign the file name from the
  ' cellAddress input parameter to the comment of a cell.
  Set commentBox = Application.ActiveCell.AddComment
  With commentBox
    .Text Text:=""
    With .Shape
      .Fill.UserPicture (cellAddress.Value)
      .ScaleHeight 3#, msoFalse, msoScaleFormTopLeft
      .ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft
    End With
    ' Set the visible to True when you always want the image displayed, and
    ' to False when you want it displayed only when you click on the cell.
    .Visible = False
  End With
  InsertCommentImage = title
End Function

A fully qualified address for the cellAddress parameter on a PC would look like this in let’s say cell B1:

C:\Data\Images\WizardChess.png

While it would be like this for the cellAddress parameter on a Mac OS X installation in cell B1:

Macintosh HD:Users:mclaughlinm:Desktop:WizardChess.png

You would call this from a cell like this when the text is provided as a string and fully qualified file name is in cell B1 of a worksheet named ImageSource:

=InsertCommentImage("Wizard Chess",B1)

Alternatively, you rewrite InsertCommentImage() as follows, which takes a string for the cell value and a string for the absolute file name:

Function InsertCommentImage(title As String, absoluteFileName As String)
  Dim commentBox As Comment
  ' Clear any comments before attempting to add them.
  Application.ActiveCell.ClearComments
  ' Define the comment as a local variable and assign the file name from the
  ' cellAddress input parameter to the comment of a cell.
  Set commentBox = Application.ActiveCell.AddComment
  With commentBox
    .Text Text:=""
    With .Shape
      .Fill.UserPicture (absoluteFileName)
      .ScaleHeight 3#, msoFalse, msoScaleFormTopLeft
      .ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft
    End With
    ' Set the visible to True when you always want the image displayed, and
    ' to False when you want it displayed only when you click on the cell.
    .Visible = False
  End With
  InsertCommentImage = title
End Function

With the change of the second parameter, you would call the InsertCommentImage() function with two strings, like:

=InsertCommentImage("Wizard Chess","C:\Data\Images\WizardChess.png")

Here’s how it would look if you’d put the formula in cell A1:

Wizard's Chess Image

This is a simple Excel macro for which you could create a button. You run it when you want to delete only the image comment from a cell. The macro works by highlighting the cell and running it. Naturally, you could wrap it in another Excel macro to navigate through the list and clean up a bunch of comment images at one time.

Sub RemoveComment()
  Application.ActiveCell.ClearComments
End Sub

If you want to allow the macro to detach all comments for a range, you would rewrite it this way:

Sub RemoveComment()
  Application.Selection.ClearComments
End Sub

As always, I hope this helps and furthers sharing information.

Written by maclochlainn

March 8th, 2011 at 12:44 am

Excel 2011 Query MySQL

with 10 comments

I finally got around to finishing my testing of Excel 2011 querying MySQL. That meant installing and configuring Excel 2011 and MySQL 5.5.9 on Mac OS X (Snow Leopard). While installing Microsoft Office is pretty trivial, installing and configuring MySQL wasn’t. You can read about installing and configuring MySQL here. A quick test after this, led me to discover that you still need a third party ODBC, as covered in this earlier blog. You should take note that Microsoft’s future direction adopts Oracle ODBC, like the approach they’ve chosen with MySQL’s ODBC driver.

I downloaded and installed one of third party ODBC tool sets. I opted for OpenLink Software’s ODBC Driver. Instructions for the install with screen shots are in this blog page. The only downside of this was the discovery that Microsoft’s solution requires Rosetta, like Excel 2008. Rosetta enables Power PC application to run on Intel-based Mac OS X.

Launching the Database icon from Excel 2011, I configured the Data Source Name, which you can find here with screen shots. After you configure the Data Source Name, restarting Excel 2011 is the best choice because otherwise you may see several non-fatal errors.

The following screen shots show you how to establish a connection between Excel 2011 and MySQL, and how to query data from the MySQL database:

  1. After you click the Database icon, you see the following dialog. Select a Data Source Name and click the OK button to begin a query.

Querying MySQL from Excel 2010 #1

  1. The OpenLink MySQL Lite Login screen requires the user name and password. Click the Connect button to launch the Microsoft Query, which appears to be a native Power PC application that requires Rosetta to run it

Querying MySQL from Excel 2010 #2

  1. Microsoft Query appears to be a native Power PC application that requires Rosetta to run it. If you want to enter a query, click the SQL View button.

Querying MySQL from Excel 2010 #3

  1. Having clicked the SQL View button you now have a work area where you can enter a standard SQL SELECT statement, like the one below. Then, you click the Return Data button.

Querying MySQL from Excel 2010 #4

  1. This dialog lets you select where you want to put the return result set from the query. The default is the absolute cell reference of the top and left most cell, $A$1. Click the OK button to query and load the data into the worksheet.

Querying MySQL from Excel 2010 #5

  1. Now you can see the data in the worksheet. The only problem is the extraneous characters returned into the column headers of the table. While tedious, they’re easy to fix. The following illustrates the downloaded result set from the previous query:

Querying MySQL from Excel 2010 #6

  1. If you perform a query with a join operation, the column names are never displayed whether you provide aliases to the query or not. It means you have to convert the table to a range, remove the false headers, and recreate the table. This appears to be a limitation of Microsoft Query and unlike the behavior in Excel 2010 on Windows. Perhaps it’s all wrapped up in the emulation provided by Rosetta but I couldn’t find any information about what’s happening. That leaves me with pure speculation, which I never like. If you find the reason, post a comment with a link because everyone would benefit.

Querying MySQL from Excel 2010 #7

As always, I hope this helps those who want to work only in the Mac OS X environment. The risk is Rosetta because it will go away, the only question is when and whether the vendors will fix their dependency first or not. The problem with this solution is that Microsoft Query doesn’t return any tables when it appears that it should.

Written by maclochlainn

February 27th, 2011 at 2:51 am

Excel 2011 Needs ODBC Files

with one comment

I’d hoped for a better solution with Excel 2011 on Mac OS X but it appears we still need the third party drivers to connect Excel to MySQL. It’s the same as I reported earlier on Excel 2008. Here’s the message and you can click on it to go the web site and links to buy the drivers.

I tried getting away with installing the MySQL Connector/ODBC before downloading one of those recommended by Microsoft. I discovered that it didn’t work.

Therefore, I download and installed the OpenLink Software ODBC Software, instructions with screen shots are in this blog page. I discovered that this software support track isn’t purely native Mac OS X on Intel-based software because it requires Rosetta like Excel 2008. It also only connects with Microsoft Query, which appears to be a Power PC native application too, at least based on the interface and look and feel. I didn’t do much more research because I’ve concluded that connectivity isn’t ready for prime time on the Mac OS X platform. Microsoft still has more work to do.

Written by maclochlainn

February 10th, 2011 at 3:15 am

Mac OS X MySQL Install

with 100 comments

Installing MySQL 5.5.9 (updated for MySQL 5.5.16 and 5.5.18) on Mac OS X was on my list but it finally made the top. I needed to write instructions for a class that I teach because asking students who own a Mac to install VMWare and Windows before MySQL seems an unnecessary burden. Especially when you can install it directly on Mac OS X.

Mac OS X Mountain Lion (10.8.*) need to install XCode and Command Line Tools before installing MySQL. You can refer to this XCode installation and configuration post for those steps.

You can download MySQL for the Mac OS X. I downloaded the Mac OS X, Version 10.6 (x86, 64-bit) version for this installation. There were a couple shell syntax tricks beyond the instructions and then you need to configure database. That’s required because the database is installed with an unsecured anonymous and root account. After the step-by-step installation instructions, you’ll find the configuration steps to enable you to access the MySQL Monitor from a terminal session. It also configures your $PATH environment to enable you to start and stop the MySQL Daemon.

  1. After the download completes, open the file folder in the download directory.

  1. The open file folder and it will look like the following. Launch the mysql-5.5.9-osx 10.6-x86_64.pkg file, which installs the product.

  1. After launching the executable, you are now on the first page of the Install MySQL 5.5.9 installation application. Click the Continue button.

  1. This page contains the instructions, you can pause to read them or continue with these instructions. Click the Continue button to proceed.

  1. This page contains the General Public License (GPL). You agree or stop the installation. Click the Continue button to proceed.

  1. The following overlay dialog contains your agreement. Click the Agree button to proceed.

  1. There are fewer options in this installation than the Windows installation. While you can change the installation location, the software installs by default in the /usr/local/mysql directory. The installation requires that you have a mysql user account on the operating system, and you don’t need to do anything because one exists as part of the default Mac OS X installation. Click the Install button to proceed.

  1. This dialog requires the system administrator’s password. Enter the valid password and click the OK button to proceed.

  1. Depending on the system, this could take more than a minute. All you can do it wait.

  1. This page tells you that you’ve completed the installation. Click the Close button to proceed.

  1. This step requires that you return to the download folder, shown in Step #2 above. Launch the MySQLStartupItem.pkg and you’ll see the following MySQL Startup Item Installer dialog. Click the Continue button to proceed.

  1. This page contains the instructions for the MySQL Startup software, you can pause to read them or continue with these instructions. Click the Continue button to proceed.

  1. This page asks if you want to change the directory. I’d recommend you leave it as the default because it requires the System Administrator’s password to start and stop the database. It should also start for you every time you boot the machine. Click the Continue button to proceed.

  1. Like Step #8, this dialog requires the system administrator’s password. Enter the valid password and click the OK button to proceed.

  1. You could see a progress dialog box but generally it happens so fast you should land at the Installation was Successful dialog. Click the Close button to proceed.

  1. This step requires that you return to the download folder, shown in Step #2 above. Launch the MySQL.prefPane and you’ll see the following MySQL Preferences dialog. Click the Install button to proceed.

  1. Like Step #8 and #14, this dialog requires the system administrator’s password. Enter the valid password and click the OK button to proceed.

  1. Don’t click in the automatic start button unless you’re sure. Otherwise, there’s a lot of cleanup to be able to return to this point and start or stop the server as required. This is the last screen, you should click the Start MySQL Server button to start MySQL. While installing MySQL 5.5.18 I discovered that the service menu is no longer installed in the Preferences, and you must start it manually.

You can start and stop the service by opening your System Preferences, where you’ll find them in the bottom Other row. If the intent was to have it start automatically, sometimes the permissions are incorrect. You’ll get the following error in MySQL 5.5.9 but not in MySQL 5.5.16 because the MySQL DMG file is fixed. You can skip the instructions until you get to Configure User’s Shell Environment below:

"/Library/StartupItems/MySQLCOM" has not been started because it does not have the proper security settings.

You can fix this by changing the permissions manually in a Terminal Session. Launch a Terminal Session from your Utilities folder inside your Applications folder.

Change directory to the /Library/StartupItems/MySQLCOM directory. Then, list the files. These commands should do the trick for you:

cd /Library/StartupItems/MySQLCOM
ls -al

If you see these permissions, you have problem because the group for startup files should be wheel not staff:

drwxr-xr-x  4 root  staff   136 Jan 20 13:46 .
drwxr-xr-x  4 root  wheel   136 Feb  9 21:11 ..
-rwxr-xr-x  1 root  staff  1300 Jan 20 13:46 MySQLCOM
-rw-r--r--  1 root  staff   469 Jan 20 13:46 StartupParameters.plist

You can change the files with this command:

sudo chown root:wheel *

However, now you need to step up the directory tree one level to /Library/StartupItems, and change the ownership of:

drwxr-xr-x   4 root  staff   136 Jan 20 13:46 MySQLCOM

There are two commands to do this. The first changes directory by moving to the parent directory in the hierarchy (the parent directory is represented by two dots). The second changes the group ownership of the MySQLCOM directory.

cd ..
sudo chown root:wheel MySQLCOM

Now you need to configure your shell environment and harden the database. Hardening means securing accounts with passwords. They’re covered in the next two sections.

Configure User’s Shell Environment

Assuming you accepted the defaults, you should be able to copy the required instructions directly into a .bash_login file if one exists. Unless you’ve created one before there won’t be a file. Mac OS X doesn’t automatically create the file. If you don’t have the file, you can create one with the following syntax:

touch .bash_login

You can open the file with the vi editor or a text editor of your choice. MySQL 5.5.9 installed in /usr/local/mysql, which has changed to /usr/local/mysql-version as noted below. You can copy the following contents into the file for MySQL 5.5.16 but will need to change the file for earlier or later releases (added sudo per Don McArthur’s comment):

# Set the MySQL Home environment variable to point to the root directory of the MySQL installation.
export set MYSQL_HOME=/usr/local/mysql-5.5.16-osx10.6-x86_64
 
# Add the /bin directory from the MYSQL_HOME location into your $PATH environment variable.
export set PATH=$PATH:$MYSQL_HOME/bin
 
# Create aliases that make it easier for you to manually start and stop the MySQL Daemon.
alias mysqlstart="sudo /Library/StartupItems/MySQLCOM start"
alias mysqlstop="sudo /Library/StartupItems/MySQLCOM stop"

Changes between MySQL 5.5.16 and 5.5.18 introduce a new directory structure, you need to use the following in the .bash_login file (added sudo and status per Don McArthur’s comments):

# Set the MySQL Home environment variable to point to the root directory of the MySQL installation.
export set MYSQL_HOME=/usr/local/mysql-5.5.18-osx10.6-x86_64
 
# Add the /bin directory from the MYSQL_HOME location into your $PATH environment variable.
export set PATH=$PATH:$MYSQL_HOME/bin
 
# Create aliases that make it easier for you to manually start and stop the MySQL Daemon.
alias mysqlstart="sudo /Library/StartupItems/MySQLCOM/MySQLCOM start"
alias mysqlstop="sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop"
alias mysqlstatus="ps aux | grep mysql | grep -v grep"

As pointed out by Shashank’s comment, you should now use the following aliases:

alias mysqlstart='sudo /usr/local/mysql/support-files/mysql.server start'
alias mysqlstop='sudo /usr/local/mysql/support-files/mysql.server stop'

You need to save the file and close and restart a new Terminal session to place these environment variables in scope. You could also run the following to put them in scope without closing and opening the terminal:

. ./.bash_login

The preceding command sources the environment file into active memory. This should configure your environment. After restarting the shell, you should be able to run this command to confirm the new environment:

which -a mysql

It should return:

/usr/local/mysql-5.5.16-osx10.6-x86_64/bin/mysql

Secure the Database

This is presently necessary because of the different file structure in a Mac OS X MySQL install, which disables the mysql_secure_installation file from running successfully. You can manually edit the file or follow these steps.

You need to connect to the database as the privileged super user, root user. This is simple because the installation doesn’t set any passwords. You open another Terminal session to make these changes or you could install MyPHPAdmin or MySQL Workbench. The tools work as well in fixing the majority of issues.

mysql -uroot

Once connected to the database as the root user, you can confirm that passwords aren’t set and an insecure anonymous user account has been previously configured. You do that by connecting to the mysql database, which is the database catalog for MySQL. You do that by running the following command:

USE mysql;

You can query the result set with the following query:

SELECT USER, password, host FROM USER\G

You should see the following output plus the user’s name preceding the MacPro (or iMac.local) host name value:

*************************** 1. row ***************************
    user: root
password: 
    host: localhost
*************************** 2. row ***************************
    user: root
password: 
    host: MacPro.local
*************************** 3. row ***************************
    user: root
password: 
    host: 127.0.0.1
*************************** 4. row ***************************
    user: root
password: 
    host: ::1
*************************** 5. row ***************************
    user: 
password: 
    host: localhost
*************************** 6. row ***************************
    user: 
password: 
    host: MacPro.local

You now need to change the password for the root user. I would suggest that you do this with the SQL command rather than a direct update against the data dictionary tables. The syntax to fix the root user account require you enter the user name, an @ symbol, and complete host values, like:

SET PASSWORD FOR 'root'@'localhost' = password('cangetin');
SET PASSWORD FOR 'root'@'MacPro.local' = password('cangetin');
SET PASSWORD FOR 'root'@'127.0.0.1' = password('cangetin');
SET PASSWORD FOR 'root'@'::1' = password('cangetin');

You should be able to drop both anonymous user rows with the following syntax, but I did encounter a problem. Assuming you may likewise encounter the problem, the fix follows the first commands you should try:

DROP USER ''@'localhost';
DROP USER ''@'MacPro.local';

If either of the anonymous accounts remain in the USER table, you can manually drop them from the database catalog. This syntax will get rid of them:

DELETE FROM USER WHERE LENGTH(USER) = 0;

You’ve completed the configuration and can now type quit; to exit the MySQL Monitor. To reconnect, you’ll now need a password, like this:

mysql -uroot -pcangetin

Also, don’t forget to use a real password. The one shown here is trivial, which means easy to hack. Use something that others might not guess.

Configure my.cnf file

You can copy one of the sample configuration files as a starting point (as provided by Don McArthur’s comment):

sudo cp /usr/local/mysql/support-files/my-huge.cnf /etc/my.cnf

Starting and Stopping the Database

You can manually start the database server with the following command, which you defined as aliases in your .bash_login shell script:

mysqlstart

Stopping it is also straightforward, you do this:

mysqlstop

You can check it’s status with this command:

mysqlstatus

Written by maclochlainn

February 10th, 2011 at 2:57 am

Posted in Mac,Mac OS X,MAMP,MySQL

Prepared Statement Failure

with 3 comments

One of my students asked for some help on developing a MySQL stored procedure that would conditionally drop a foreign key constraint. I put together a quick example, which compiled fine but failed at run time. With some investigation it appears that either the MySQL 5.5 Documentation in Section 12.6 is incorrect or you can’t ALTER TABLE inside a MySQL Prepared Statement with placeholders.

A subsequent test showed me that you couldn’t use ALTER TABLE statement in a prepared statement outside of a stored procedure. I logged Bug #59604 with my other test case, and put the test case there. They closed the bug and validated what I suspected and clarified what I missed, you can’t use placeholders in prepared DDL statements.

The following is the test code example that failed:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
DROP PROCEDURE IF EXISTS dropForeignKey;
 
DELIMITER $$
 
CREATE PROCEDURE dropForeignKey
( pv_database   VARCHAR(64)
, pv_table      VARCHAR(64)
, pv_constraint VARCHAR(64))
BEGIN
 
  /* Declare a local variable for the SQL statement. */
  DECLARE stmt VARCHAR(1024);
 
  /* Set a session variable with two parameter markers. */
  SET @SQL := 'ALTER TABLE ? DROP FOREIGN KEY ?';
 
  /* Assign the formal parameters to session variables because prepared statements require them. */
  SET @sv_table := pv_table;
  SET @sv_constraint := pv_constraint;
 
  /* Check if the constraint exists. */  
  IF EXISTS (SELECT NULL
             FROM   information_schema.referential_constraints
             WHERE  constraint_schema = pv_database
             AND    TABLE_NAME = pv_table
             AND    constraint_name = pv_constraint)
  THEN
 
    /* Dynamically allocated and run statement. */
    PREPARE stmt FROM @SQL;
    EXECUTE stmt USING @sv_table, @sv_constraint;
    DEALLOCATE PREPARE stmt;
  END IF;
 
END;
$$
 
DELIMITER ;

Calling this with the following syntax:

CALL dropForeignKey(DATABASE(),'telephone','telephone_fk4');

It raised the following error message.

ERROR 1064 (42000): 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 '? DROP FOREIGN KEY ?' at line 1

Based on the comment from Bug #59604, I concluded that the ALTER TABLE statement doesn’t support using session variables. However, the CONCAT() function solves the problem. Given this is a DDL command, and any extraneous quoting would simply fail parsing rules because of the CONCAT function, SQL injection doesn’t appear a threat.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
DROP PROCEDURE IF EXISTS dropForeignKey;
 
DELIMITER $$
 
CREATE PROCEDURE dropForeignKey
( pv_database  VARCHAR(64)
, pv_table      VARCHAR(64)
, pv_constraint VARCHAR(64))
BEGIN
 
  /* Declare a local variable for the SQL statement. */
  DECLARE stmt VARCHAR(1024);
 
  /* Set a session variable with two parameter markers. */
  SET @SQL := CONCAT('ALTER TABLE ',pv_table,' DROP FOREIGN KEY ',pv_constraint);
 
  /* Check if the constraint exists. */    
  IF EXISTS (SELECT NULL
             FROM   information_schema.referential_constraints
             WHERE  constraint_schema = pv_database
             AND    TABLE_NAME = pv_table
             AND    constraint_name = pv_constraint)
  THEN
 
    /* Dynamically allocated and run statement. */
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
 
END;
$$
 
DELIMITER ;

A more useful approach would be to drop all foreign keys that reference a table. Here’s how you would accomplish that.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropForeignKeys;
 
-- Change delimiter to create procedure.
DELIMITER $$
 
-- Create procedure.
CREATE PROCEDURE dropForeignKeys
( pv_database          VARCHAR(64)
, pv_referenced_table  VARCHAR(64))
BEGIN
 
  /* Declare local statement variables. */
  DECLARE lv_stmt VARCHAR(1024);
 
  /* Declare local cursor variables. */
  DECLARE lv_table_name       VARCHAR(64);
  DECLARE lv_constraint_name  VARCHAR(64);
 
  /* Declare control variable for handler. */
  DECLARE fetched       INT DEFAULT 0;
 
  /* Declare local cursor. */
  DECLARE foreign_key_cursor CURSOR FOR
    SELECT   rc.TABLE_NAME
    ,        rc.constraint_name
    FROM     information_schema.referential_constraints rc
    WHERE    constraint_schema = pv_database
    AND      referenced_table_name = pv_referenced_table
    ORDER BY rc.TABLE_NAME
    ,        rc.constraint_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Open a local cursor. */  
  OPEN foreign_key_cursor;
  cursor_foreign_key: LOOP
 
    FETCH foreign_key_cursor
    INTO  lv_table_name
    ,     lv_constraint_name;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_foreign_key; END IF;
 
    /* Set a SQL statement by using concatenation. */
    SET @SQL := CONCAT('ALTER TABLE ',lv_table_name,' DROP FOREIGN KEY ',lv_constraint_name);
 
    /* Prepare, run, and deallocate statement. */
    PREPARE lv_stmt FROM @SQL;
    EXECUTE lv_stmt;
    DEALLOCATE PREPARE lv_stmt;
 
  END LOOP cursor_foreign_key;
  CLOSE foreign_key_cursor;  
 
END;
$$
 
-- Reset delimiter to run SQL statements.
DELIMITER ;

You would test it with this call:

CALL dropForeignKeys(DATABASE(),'system_user');

As always, I hope this helps somebody.

Written by maclochlainn

January 19th, 2011 at 1:39 am

Posted in Mac OS X,MAMP,MySQL,PSM

Handling Bash Parameters

with one comment

Bash shell or shells in general hang on details. An ex-student was trying to sort something out in Learning the bash Shell, 3rd Edition, which isn’t on my short list of good shell scripting books. I concur more or less with the comment on Amazon.com that there are too few examples in the book. I think the free examples here may serve folks in lieu of a book.

Anyway, the student’s problem involved processing multiple word parameters in an array. They were confused about how to handle ${*}, ${@} "${*}" and "${@}" when assigning them into an array for subsequent processing. By the way, I did find a decent explanation of the concept on Page 89 in my copy of the book. Though my copy is a 2nd Edition.

Here’s a dressed up sample of what they were attempting to do:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#!/bin/bash
 
# Print header information.
echo "Begin Program!"
echo ""
 
# Count the call parameters.
echo "Count call parameters [" ${#} "]"
echo ""
 
# Declare an array of delimited parameters.
ARRAY=(${@})
 
# Declare a numeric constant of array elements.
ELEMENTS=${#ARRAY[@]}
 
# Does the parameter account agree with array elements.
if [[ ${#} = ${#ARRAY[@]} ]]; then
  echo "Parameters match exploded array elements."
else
  echo "Parameters ["${#}"] don't match exploded array elements ["${ELEMENTS}"]."
fi
 
# Echo line break.
echo ""
 
# Echo the parameter list.
for (( i = 0; i < ${ELEMENTS}; i++ )); do
  echo "  ARRAY["${i}"]=["${ARRAY[${i}]}"]"
done
 
# Print footer information.
echo ""
echo "End Program!"

With the ARRAY=(${@}) assignment on line #12, they exploded the elements into individual words. They thought that the IFS (Internal Field Separator) environment variable was defined wrong but it wasn’t.

They called the program like this from the command-line:

sample.sh "Me too" "You too"

Then, they got this syntax and were surprised.

Begin Program!
 
Count call parameters [ 2 ]
 
Parameters [2] don't match exploded array elements [4].
 
  ARRAY[0]=[Me]
  ARRAY[1]=[too]
  ARRAY[2]=[You]
  ARRAY[3]=[too]
 
End Program!

They were close. The ARRAY=(${@}) assignment on line #12. There attempt to fix it with ARRAY=(${*}) led nowhere because it’s more or less the same and explodes into 4 words. To their credit, they put quotes around it like this ARRAY=("${*}") and got two parameters but one array element, as shown below:

Begin Program!
 
Count call parameters [ 2 ]
 
Parameters [2] don't match exploded array elements [1].
 
  ARRAY[0]=[Me too You too]
 
End Program!

What they needed was ARRAY=("${@}") on line #12 to explode quote delimited parameters. Here’s a complete working example of the final code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#!/bin/bash
 
# Print header information.
echo "Begin Program!"
echo ""
 
# Count the call parameters.
echo "Count call parameters [" ${#} "]"
echo ""
 
# Declare an array of delimited parameters.
ARRAY=("${@}")
 
# Declare a numeric constant of array elements.
ELEMENTS=${#ARRAY[@]}
 
# Does the parameter account agree with array elements.
if [[ ${#} = ${#ARRAY[@]} ]]; then
  echo "Parameters match exploded array elements."
else
  echo "Parameters ["${#}"] don't match exploded array elements ["${ELEMENTS}"]."
fi
 
# Echo line break.
echo ""
 
# Echo the parameter list.
for (( i = 0; i < ${ELEMENTS}; i++ )); do
  echo "  ARRAY["${i}"]=["${ARRAY[${i}]}"]"
done
 
# Print footer information.
echo ""
echo "End Program!"

Changing that one element yields their desired output:

Begin Program!
 
Count call parameters [ 2 ]
 
Parameters match exploded array elements.
 
  ARRAY[0]=[Me too]
  ARRAY[1]=[You too]
 
End Program!

As always, I hope this helps some folks.

Written by maclochlainn

January 2nd, 2011 at 1:40 am