MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MAMP’ Category

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 , ,

MySQL Workbench Scripts

with 2 comments

It’s always interesting when somebody asks why they got an error message, and especially sweet when you’re working on something related that lets you answer the question. They were using MySQL Workbench and wanted to know why they couldn’t open a SQL script file by clicking on the Scripting menu option.

As I explained to the individual who asked, you should always click the Edit SQL Script link in the SQL Development section of the MySQL Workbench home page to work on SQL scripts. The Scripting menu option supports Python and Lua plug-ins development and scripts.

They did the following initially, which led down the rabbit warren and left them stumped because they don’t know anything about Python or Lua. This is provided to those who choose to experiment with this advanced feature of MySQL Workbench.

That presents you with a chooser dialog and it lets you pick any type of file. (You may wonder, as I did, why they didn’t restrict it to .py and .lua file extensions, which would preclude opening a .sql file. I actually logged an enhancement request to see if the development team may agree with me.) You get the following message when you choose something other than a Python or Lua script. You can click on any of the reduced size screen shots to enlarge them and make them readable.

As you may note, the dialog says the activity is unsupported by provides no cancellation button. Click the OK button and the unsupported file is loaded into a tab that is useless. All you can do is click to close the tab and dismiss the window.

After you dismiss (by clicking the x) the non-editable .sql file, you need to click on the Open Script file icon shown below.

This chooser really should open where the default is for the MySQL Workbench application script files but it doesn’t. It opens in the last accessed directory. You need to navigate to where your Python or Lua scripts are stored, which is the following directory on Windows:

C:\Users\<user_name>\AppData\Roaming\MySQL\Workbench\scripts

Please note that on a Windows system you can’t chose this directory option because it’s protected. You must enter the navigation bar and type it. Then, you should see any scripts that you saved from within MySQL Workbench.

The ReadFile.py below contains a rather simplistic and static program that reads a file and prints it to console (it’s small and fits in the screen). Obviously, it dispenses with a bunch to keep it small but check a Python website or book for the right way to manage a try block and handle exceptions.

Here’s the ReadFile.py file shown in the preceding and next screen shots. For those new to Python, watch out because tabs aren’t equivalent to spaces. I made a change in the script below to display the trailing semicolon because one of my students asked about it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# -*- coding: utf-8 -*-
# MySQL Workbench Python script
# ReadFile.py
# Written in MySQL Workbench 5.2.41
 
import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Parse string to avoid reading line return.
    if not line[len(line) - 1:len(line)] == ";":
      print(line[0:len(line) - 1])
    else:
      print(line)
    if not line:
        break

Life’s funny, and you can never please everyone. The latest question, “Why did I choose to use substrings when suppressing line returns from the print() function is easier?” Simple answer because the approach differs between Python 2.7 and 3.0 and I didn’t want this post to have a lot of Python nuance.

Python 2.7 (compatible with MySQL Workbench 5.2):

1
2
3
4
5
6
7
8
9
import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Suppress line return.
    print(line),
    if not line:
        print
        break

Python 3.0 (not-compatible with MySQL Workbench 5.2)

You should take note that both version require a print statement on line #8. Line #6 above shows that Python 2.7 uses a comma to suppress the line return, and below line #6 shows Python 3 requires you set end equal to an empty string. Line #8 below also has a set of empty parentheses, which works in Python 3.x but not in Python 2.7. Python 2.7 would print the parentheses unless you put an empty string inside of them, like a print('') statement.

1
2
3
4
5
6
7
8
9
import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Suppress line return.
    print(line, end = '')
    if not line:
        print()
        break

Hopefully, everyone concurs the parsing was simpler than explaining all these Python nuances. Although, it’s nice somebody was so curious.

If your script complies with the Python 2.7 rules (that’s what is deployed in MySQL Workbench), click the lighting bolt and your code will run and display the results. That’s shown in the last screen shot.

If you’re interesting in developing plug-ins, check this summary page or this nice example of executing a query to text. Although, rumor has it that certain features may mature over the next year …

Naturally, I hope this helps those experimenting but personally it’s a cool advanced feature of the MySQL Workbench.

Written by maclochlainn

August 18th, 2012 at 4:57 pm

PHP/MySQL Query

without comments

Somebody wanted an example of how to handle column values using PHP to query a MySQL database. While I thought there were enough examples out there, they couldn’t find one that was code complete.

Well, here’s one that works using a static query. If you want to use a prepared statement, check this earlier post.

<html>
<header>
<title>Static Query Object Sample</title>
<style type="text/css">
  /* HTML element styles. */
  table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
  th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
  td {border:solid 1px gray;}
 
  /* Class tag element styles. */
  .ID {min-width:50px;text-align:right;}
  .Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
  // Assign credentials to connection.
  $mysqli = new mysqli("localhost", "student", "student", "studentdb");
 
  // Check for connection error and print message.
  if ($mysqli->connect_errno) {
    print $mysqli->connect_error."<br />";
    print "Connection not established ...<br />";
  }
  else {
 
    // Declare a static query.
    $query = "SELECT au.system_user_id, au.system_user_name FROM system_user au" ;
 
    // Loop through a result set until completed.  
    do {
 
      // Attempt query and exit with failure before processing.
      if (!$stmt = $mysqli->query($query)) {
 
        // Print failure to resolve query message.
        print $mysqli->error."<br />";
        print "Failed to resolve query ...<br />";
      }     
      else {
 
        // Print the opening HTML table tag.
        print '<table><tr><th class="ID">ID</th><th class="Label">User Role Name</th></tr>';
 
        // Fetch a row for processing.
        while( $row = $stmt->fetch_row() ) {
 
          // Print the opening HTML row tag.
          print "<tr>";
 
          // Loop through the row's columns.
          for ($i = 0;$i < $mysqli->field_count;$i++) {
            // Handle column one differently.
            if ($i == 0)
              print '<td class="ID">'.$row[$i]."</td>";
            else
              print '<td class="Label">'.$row[$i]."</td>";
          }
          // Print the closing HTML row tag.
          print "</tr>"; 
        }
      }
    } while( $mysqli->next_result());
 
  // Print the closing HTML table tag.
  print "</table>"; 
 
  // Release connection resource.
  $mysqli->close(); }
?>
</script>
</body>
</html>

It prints the following image:

While you shouldn’t embed CSS, I’ve done it to keep this as simple as possible. You can also use the procedural approach to the MySQLi library, like this:

<html>
<header>
<title>Static Query Procedural Sample</title>
<style type="text/css">
  /* HTML element styles. */
  table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
  th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
  td {border:solid 1px gray;}
 
  /* Class tag element styles. */
  .ID {min-width:50px;text-align:right;}
  .Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
  // Assign credentials to connection.
  $mysqli = mysqli_connect("localhost", "student", "student", "studentdb");
 
  // Check for connection error and print message.
  if (mysqli_connect_errno()) {
    print mysqli_connect_error()."<br />";
    print "Connection not established ...<br />";
  }
  else {
 
    // Initialize a statement in the scope of the connection.
    $stmt = mysqli_stmt_init($mysqli);
 
    // Declare a static query.
    $query = "SELECT au.system_user_id, au.system_user_name FROM system_user au" ;
 
    // Loop through a result set until completed.  
    do { 
 
      // Attempt query and exit with failure before processing.
      if (!$stmt = mysqli_query($mysqli,$query)) {
 
        // Print failure to resolve query message.
        print mysqli_error($stmt)."<br />";
        print "Failed to resolve query ...<br />";
      }     
      else {
 
        // Print the opening HTML table tag.
        print '<table><tr><th class="ID">ID</th><th class="Label">User Role Name</th></tr>';
 
        // Fetch a row for processing.
        while( $row = mysqli_fetch_row($stmt) ) {
 
          // Print the opening HTML row tag.
          print "<tr>";
 
          // Loop through the row's columns.
          for ($i = 0;$i < mysqli_field_count($mysqli);$i++) {
            // Handle column one differently.
            if ($i == 0)
              print '<td class="ID">'.$row[$i]."</td>";
            else
              print '<td class="Label">'.$row[$i]."</td>";
          }
          // Print the closing HTML row tag.
          print "</tr>"; 
        }
      }
    } while( mysqli_next_result($mysqli));
 
    // Print the closing HTML table tag.
    print "</table>"; 
 
    // Free system resources.
    mysqli_stmt_free_result($stmt);
 
    // Release connection resource.
    mysqli_close($mysqli); 
  }
?>
</script>
</body>
</html>

It produces the same output as the object oriented approach with one exception the title of the web page.

Hope this helps some folks.

Written by maclochlainn

July 14th, 2012 at 11:59 pm

Posted in LAMP,MAMP,MySQL,PHP

Tagged with , ,

MySQL and Java Tutorial

with 2 comments

This demonstrates how to create an Java infrastructure for reading and writing large text files to a MySQL database. The example provides:

  • A FileIO.jar library that lets you enter MySQL connection parameters through a JOptionPane, and a customized JFileChooser to filter and read source files from the file system.
  • A mysql-connector-java-3.1.14-bin.jar file, which is MySQL’s library for JDBC communication with the MySQL Databases.

The steps to compiling and testing this code are qualified below:

  1. Download and install the Java Software Development Kit (JSDK) for Java 6.
  2. Create a C:\JavaTest folder on Windows, or a /JavaTest directory from some mount point of your choice.
  3. Download and position the mysql-connector-java-3.1.14-bin.jar and FileIO.jar files in the JavaTest directory.
  4. Create a batch file to source your environment path (%PATH% on Windows and $PATH on Linux or Mac OS X) and the two Java Archive (JAR) files. A sample batch file is noted below:
set PATH=C:\Program Files\Java\jdk1.6.0_07\bin;%PATH%
set CLASSPATH=C:\JavaDev\Java6\mysql-connector-java-3.1.14-bin.jar;C:\JavaDev\Java6\FileIO.jar;.

You can run this file by simply typing the files first name. On Linux or Mac OS X, you first need to grant it privileges with the chmod command as 755.

  1. Copy the WriteReadCLOBMysql.java code from the bottom of this posting and also put it into the JavaTest directory.
  2. Compile the WriteReadCLOBMysql.java source code with the javac utility, as shown below:
javac WriteReadCLOBMysql.java

After you compile it, you should run it as follows:

java WriteReadCLOBMysql
  1. Before running the code, you’ll need to seed (INSERT) a row that meets the desired hard coded criteria. It requires an ITEM_TITLE value of 'The Lord of the Rings - Fellowship of the Ring' and an ITEM_SUBTITLE of 'Widescreen Edition' in the ITEM table.
  2. When it runs, you’ll see the following tabbed JOptionPane.

You need to enter the following values before clicking the OK button:

  • Host: The localhost key word, or hostname of your physical machine running the database.
  • Port: The port that the MySQL Listener is running on (the default value is 3306).
  • Database: The Oracle TNS Alias, which is sampledb for the full database sample database.
  • UserID: The user name with permissions to the database entered that can access an ITEM table.
  • Password: The password for the user’s account.

In the JFileChooser, select a file to upload to the database.

You should see what you uploaded displayed in a JFrame.

Written by maclochlainn

November 14th, 2011 at 4:49 pm

Posted in Java,LAMP,MAMP,MySQL

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 101 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

Create a synonym in MySQL

with 3 comments

A friend wanted to know how to create a synonym to a library of stored functions and procedures in MySQL. I had to deliver the sad news that you can’t create a synonym in MySQL 5.0, 5.1, or 5.5. You need to write a wrapper function or procedure.

A synonym (available in Oracle) would eliminate the need to call a function from another database through the component selector (or period). That’s because it maps a name to the reference and name instead of requiring you to include it with each call. The following shows a call from outside of the lib database:

SELECT lib.demo('Hello World!') AS "Statement";

He asked how to do it, so here’s how you do it below. Assume the following for this example:

  • You have a lib database where you define all your functions, procedures, and tables.
  • You have a app database that supports web connections.
  • You don’t want to allow web users to connect directly to the lib database because access to the tables should only be available through the functions and procedures.

This base function is simplified to avoid interaction with table data but illustrate the technique of definer rights functions. The function takes a string of up to 20 characters and returns it enclosed in double quotes. To mimic these behaviors, as the root user, you should create the app and lib databases, the dev and web users, and grant privileges to the dev user to act in the lib database and the web user to act in the app database. The example below does this as the root user, but in real life don’t use a trivial password like the example:

/* Create the two databases. */
CREATE DATABASE app;
CREATE DATABASE lib;
 
/* Create the two users, the developer can only connect locally. */
CREATE USER 'dev'@'localhost' IDENTIFIED BY 'dev';
CREATE USER 'web'@'%' IDENTIFIED BY 'web';
 
/* Grant privileges to be a definer in both databases. */
GRANT ALL ON app.* TO 'dev'@'localhost';
GRANT ALL ON lib.* TO 'dev'@'localhost';
 
/* Grant privileges to any function or privilege in the APP database to the WEB user. */
GRANT EXECUTE ON app.* TO 'web'@'%';

After creating and granting all the appropriate privileges, here are the steps to create the test case.

  1. You create and test the function as the dev user in the lib database.
/* Set the delimiter to something other than a semi-colon so they can be typed in statements. */
DELIMITER $$
 
/* Create a function that echoes back the string with double quotes. */
CREATE FUNCTION demo(str VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
  RETURN CONCAT('"',str,'"');
END;
$$
 
/* Reset the DELIMITER value. */
DELIMITER ;
 
/* Query the function. */
SELECT demo('Ciao amico!') AS "Statement";
  1. You create and test the wrapper function as the dev user in the app database.
/* Set the delimiter to something other than a semi-colon so they can be typed in statements. */
DELIMITER $$
 
/* Create a function that echoes back the string with double quotes. */
CREATE FUNCTION demo(str VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
  RETURN lib.demo(str);
END;
$$
 
/* Reset the DELIMITER value. */
DELIMITER ;
 
/* Query the function. */
SELECT demo('Ciao amico!') AS "Statement";

If you’re wondering why a GRANT wasn’t required from the lib database to the app database, it’s because the dev user has access to both databases and defined both objects.

  1. You can test the wrapper function as the web user in the app database.
SELECT demo('Yes, it works!') AS "Statement";

This is the closest to a synonym for a function or procedure that is possible. I know this solves his problem and hope it solves a couple others too.

Written by maclochlainn

February 5th, 2011 at 11:02 pm

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

PHP Database Authentication

with one comment

A few years ago I wrote a couple articles showing how to use PHP to connect to an Oracle Database 10g Express Edition instance. They’re still there on Oracle’s Technical Network but the source files are missing. It appears that Oracle may have migrated the articles to a new server but failed to migrate the source files.

Don’t forget that you’ll need to Install the Zend Community Server. Then, you need to create an IDMGMT1, IDMGMT2, and IDMGMT3 users and run the create_identity_db2.sql or create_identity_db3.sql seeding script for both database validation models. You’ll find links to the original articles, the source code in zip files. The newer version with CSS is the IDMGMT3 code.

The CSS sign-on form looks like this:

The portal page looks like this:

The add new user page looks like this:

The source files are as follows:

  1. Database Authentication #1IdMgmt1.zip
  2. Database Authentication #2IdMgmt2.zip
  3. Database Authentication #3IdMgmt3.zip includes the CSS but works with the same write-up as Idmgmt2.

Hope this helps those who wanted the files.

Written by maclochlainn

December 19th, 2010 at 1:44 am

MySQL Sunday

without comments

The merry-go-round is running and Oracle Open World 2010 has begun with MySQL Sunday. The merry-go-round is a fixture at the northwest corner of the Moscone South building, at the intersection shared between the Moscone North building.

The keynote was interesting because Oracle confirmed that they have and will continue to invest in MySQL. The MySQL 5.5 Candidate Release is now available for download. Key features that you’ll note are 200% to 300% improved performance, the InnoDB is now the default engine, backup and recovery are dramatically improved, and the enterprise model is integrated to provide more information about internals and performance.

A little research for the comprehensive new feature list for MySQL 5.5 found a well documented page in the MySQL 5.5 Reference. Examples of things left out of the presentation, not an inclusive list of all features, are: improved operation on the Solaris platform (surprise ;-)), support for semisynchronous replication, support for SQL standard SIGNAL and RESIGNAL statements, support for Unicode character sets, a LOAD XML statement, expanded partitioning options – including the ability to truncate only a partition of a table, and may new command options.

Ronald Bradford gave a good presentation on MySQL idiosyncrasies. I thought spelling out behaviors common to Windows and Mac platforms that differ from Linux and Unix platforms was great. He’s got a new book that he co-authored on MySQL and PHP. I’ve look into the book yet.

I also enjoyed Giuseppe Maxia’s presentation on partitioning. His regular web site is here, but beware if you’ve a slow web connection because the photos delay page loading. ;-)

Update on MySQL Sunday

Here are some links provided by Oracle to presentations:

  • Edward Screven’s presentation
  • Live Webinar: ‚ÄúDelivering Scalability and High Availability with MySQL 5.5 Replication Enhancements

Written by maclochlainn

September 19th, 2010 at 2:30 pm

Posted in InnoDB,LAMP,MAMP,MySQL