MacLochlainns Weblog

Virtualization is important and Dave Welch from the House of Brick gave a great presentation of experiences with VMWare and Tier 1 databases. It was a comprehensive presentation, but the white paper was easier to follow. The slides were complete but the volume of information was a lot for an hour presentation. Well worth the time though.

Utah Oracle User Group (UTOUG) announced a call for Fall Symposium papers today. The Fall Symposium will be in Salt Lake City on 9/6/2012. If you’re interested in presenting on Oracle or MySQL, the call for presentations will be open until 6/15/2012.

The conference party was tonight, and it provided some nice orderves and pizza. The theme was a return to 1980s music, and some folks really dressed their parts. You can listen to a short snapshot of the band by clicking the image to launch a small video segment.

I’m looking forward to the APEX Behind the Scenes presentation at 8:30 a.m. tomorrow. When the conference is over, I won’t miss the smoke filled air that we walk through from the Luxor to the Mandalay. It’s really amazing that the complex is more than a mile in length. It runs from the Luxor to the Mandalay South Conference Center.

It seems the Titanic is everywhere, even inside the pyramid of the Luxor hotel. While the Luxor is within the Mandalay Bay complex, it’s about a half mile walk to the conference and a half mile back. We go by the Mandalay Conference Center’s aquarium. We thought it might be interesting but at $18 an admission, we opted to pass on it. It’s amazing to have an aquarium in the desert, but it’s probably not as nice as the Monterey Bay aquarium.

It was interesting to start the day listening to Rich Niemiec on partitioning tables and using Exadata in Oracle. The NoSQL (Not Only SQL) presentations were interesting, as was the upgrading of Oracle 11gR2 in an E-Business Suite environment presentation. Then, I finished the day with what’s new with the Oracle VM Server.

Checking out the exhibit hall I managed to get a signed copy of Rich Niemiec’s Oracle Database 11g Release 2 Performance Tuning Tips & TechniquesOracle Database 11g Release 2 Performance Tuning Tips & Techniques and a copy of MongoDB: The Definitive GuideMongoDB The Definitive Guide.

Collaborate 2012 started on Sunday but for me I began on Monday. I enjoyed Bob Burgess, SalesForce, presentation on shell scripting for MySQL Administration today. It preceded my presentation in the same room, which I thought was an interesting coincidence since we got our conference credentials together.

I presented on portable SQL between Oracle and MySQL. The presentation went well. Before I took questions, I got to ask them because I had three copies of my new Oracle Press book to give away: Oracle Database 11g and MySQL 5.6 Developer Handbook. Handing out the books served as a nice ice breaker for the audience to ask questions about the presentation.

My favorite question was, “Will Oracle continue to improve MySQL?” My answer to that is always simple because Oracle’s support for MySQL has and continues to be great, “Oracle only spends money on winners and that means MySQL wins.” Oracle product management was in attendance and they re-enforced Oracle’s commitment to MySQL.

At 6 p.m., the Exhibit Hall opened and I checked it out. Cisco hired Kathy Bailey to draw caricatures, and she drew mine as you can see at the left. I’m looking forward to more presentations tomorrow.

While working through prepared statements in MySQL, there was an interesting MySQL regular expression question raised. A student wanted to know how to address the following error message:

ERROR 1139 (42000): Got error 'repetition-operator operand invalid' FROM REGEXP

They had substituted * for a .+ in a metasequence. A metasequence is a parenthetical expression that evaluates based on multiple alternative conditions, and the pipe (|) acts as an OR operator. The full code example is found on page 482 of the Oracle Database 11g & MySQL 5.6 Developer Handbook. The student’s change would have worked without an error had he replaced the metasequence with .* instead of the solitary *.

The original call to the procedure passes the following well formed regular expression:

CALL prepared_dml('(^|^.+)war(.+$|$)');

Or, they could eliminate the metasequences and use:

CALL prepared_dml('^.*war.*$');

Either returns the following entries from a column with movie titles from the sample code:

Charlie's War
Star Wars I
Star Wars II
Star Wars III

The dot (.) means any possible character, and the plus (+) means one-to-many possible repeating characters of a preceding character. When the dot precedes the plus, it means one-to-many wildcard characters. The student replaced the metasequence with an asterisk by itself and generated the badly formed regular expression error.

The misunderstanding occurs because the asterisk (*) by itself doesn’t mean zero-to-many wildcard. The combination of the dot and asterisk creates a zero-to-many wildcard, which works when there is or isn’t a character before the first character of a string or after the last character of a string. It also eliminates the need for a metasequence.

Here’s a small test case outside of the book’s stored procedure:

-- Conditionally drop the table.
DROP TABLE IF EXISTS list;
 
-- Create the table.
CREATE TABLE list ( list_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, list_item VARCHAR(20));
 
-- Insert for rows.
INSERT INTO list (list_item) VALUES ('Star'),(' Star'),(' Star '),('Star ');
 
-- Query for zero-to-many leading characters.
SELECT   list_id AS "ID"
,        list_item AS "Zero-to-many leading characters "
,        LENGTH(list_item) AS "Length"
,        '^.*Star.*$' AS "Regular Expression"
FROM     list
WHERE    list_item REGEXP '^.*Star.*$';
 
-- Query for zero-to-many leading characters.
SELECT   list_id AS "ID"
,        list_item AS "One-to-many leading characters  "
,        LENGTH(list_item) AS "Length"
,        '^.+Star.*$' AS "Regular Expression"
FROM     list
WHERE    list_item REGEXP '^.+Star.*$';
 
-- Query for one-to-many leading characters.
SELECT   list_id AS "ID"
,        list_item AS "Zero-to-many trailing characters"
,        LENGTH(list_item) AS "Length"
,        '^.*Star.*$' AS "Regular Expression"
FROM     list
WHERE    list_item REGEXP '^.*Star.*$';
 
-- Query for one-to-many leading characters.
SELECT   list_id AS "ID"
,        list_item AS "One-to-many trailing characters "
,        LENGTH(list_item) AS "Length"
,        '^.*Star.+$' AS "Regular Expression"
FROM     list
WHERE    list_item REGEXP '^.*Star.+$';

The output from the scripts is:

+----+----------------------------------+--------+--------------------+
| ID | Zero-to-many leading characters  | Length | Regular Expression |
+----+----------------------------------+--------+--------------------+
|  1 | Star                             |      4 | ^.*Star.*$         |
|  2 |  Star                            |      5 | ^.*Star.*$         |
|  3 |  Star                            |      6 | ^.*Star.*$         |
|  4 | Star                             |      5 | ^.*Star.*$         |
+----+----------------------------------+--------+--------------------+
4 rows in set (0.02 sec)
 
+----+----------------------------------+--------+--------------------+
| ID | One-to-many leading characters   | Length | Regular Expression |
+----+----------------------------------+--------+--------------------+
|  2 |  Star                            |      5 | ^.+Star.*$         |
|  3 |  Star                            |      6 | ^.+Star.*$         |
+----+----------------------------------+--------+--------------------+
2 rows in set (0.00 sec)
 
+----+----------------------------------+--------+--------------------+
| ID | Zero-to-many trailing characters | Length | Regular Expression |
+----+----------------------------------+--------+--------------------+
|  1 | Star                             |      4 | ^.*Star.*$         |
|  2 |  Star                            |      5 | ^.*Star.*$         |
|  3 |  Star                            |      6 | ^.*Star.*$         |
|  4 | Star                             |      5 | ^.*Star.*$         |
+----+----------------------------------+--------+--------------------+
4 rows in set (0.02 sec)
 
+----+----------------------------------+--------+--------------------+
| ID | One-to-many trailing characters  | Length | Regular Expression |
+----+----------------------------------+--------+--------------------+
|  3 |  Star                            |      6 | ^.*Star.+$         |
|  4 | Star                             |      5 | ^.*Star.+$         |
+----+----------------------------------+--------+--------------------+
2 rows in set (0.02 sec)

Hope this helps.

Working with a Fedora 16 VM for my students (next term) and found that the MySQL Server’s my.cnf file worked with a Linux socket as opposed to a listener port, and that several configuration options where missing from the file. Here’s the default /etc/my.cnf file after the package installation from the Red Hat site:

[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Without rebuilding the log files, this seemed like the cleanest replacement for the MySQL Server my.cnf for a development instance running on Fedora 16. If you’ve other suggestions, please let me know.

[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
 
# Default directory.
datadir=/var/lib/mysql
 
# The TCP/IP Port the MySQL Server listens on.
port=3306
 
# The Linux Socket the MySQL Server uses when not using a listener.
# socket=/var/lib/mysql/mysql.sock
 
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
# The default storage engine that will be used when creating new tables.
default-storage-engine=INNODB
 
# Set the SQL mode to strict.
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
 
# Set the maximum number of connections.
max_connections=100
 
# Set the number of open tables for all threads.
table_cache=256
 
# Set the maximum size for internal (in-memory) temporary tables.
tmp_table_size=26M
 
# Set how many threads should be kept in a cache for reuse.
thread_cache_size=8
 
# MyISAM configuration.
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=52M
key_buffer_size=36M
read_rnd_buffer_size=256K
sort_buffer_size=256K
 
# InnoDB configuration.
innodb_data_home_dir=/var/lib/mysql
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=25M
innodb_log_file_size=5M
innodb_thread_concurrency=8
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

As always, I hope this helps somebody.

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.

5. Copy the WriteReadCLOBMysql.java code from the bottom of this posting and also put it into the JavaTest directory.
6. 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

7. 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.
8. When it runs, you’ll see the following tabbed JOptionPane.

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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263

// --------------------------------------------------------------------
// WriteReadCLOBMysql.java
// by Michael McLaughlin
//
// This code demonstrates reading an image file and displaying
// the image in a JLabel in a JFrame.
//
// The UPDATE and SELECT statements have dependencies on the
// create_store.sql script.
// --------------------------------------------------------------------
 
// Java Application class imports.
import java.awt.Dimension;
import java.awt.Font;
import java.awt.GridLayout;
import java.io.Reader;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
 
// Generic JDBC imports.
import java.sql.*;
 
// Mysql JDBC import.
import com.mysql.jdbc.Driver.*;
 
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
 
 
// Include book libraries (available at publisher website).
import plsql.jdbc.DataConnectionPane;
import plsql.fileio.FileIO;
// -------------------------------------------------------------------/
public class WriteReadCLOBMysql extends JFrame {
  // Define database connections.
  private String host;
  private String port;
  private String dbname;
  private String userid;
  private String passwd;
 
  // Define data connection pane.
  private DataConnectionPane message = new DataConnectionPane();
 
  // Construct the class.
  public WriteReadCLOBMysql (String s) {
    super(s);
 
    // Get database connection values or exit.
    if (JOptionPane.showConfirmDialog(this,message
          ,"Set Oracle Connection String Values"
          ,JOptionPane.OK_CANCEL_OPTION) == 0) {
 
      // Set class connection variables.
      host = message.getHost();
      port = message.getPort();
      dbname = message.getDatabase();
      userid = message.getUserID();
      passwd = message.getPassword();
 
      // Print connection to console (debugging tool).
      message.getConnection();
 
      // Create a JPanel for data display.
      ManageCLOB panel = new ManageCLOB();
 
      // Configure the JPanel.
      panel.setOpaque(true);
      setContentPane(panel);
 
      // Configure the JFrame.
      setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
      setLocation(100,100);
      pack();
      setVisible(true); }
    else
      System.exit(1); }
  // -------------------------------------------------------------------/
  private class ManageCLOB extends JPanel {
 
    // Define display variables.
    private String clobText;
    private JScrollPane scrollPane;
    private JTextArea textArea;
    // -----------------------------------------------------------------/
    public ManageCLOB () {
      // Set layout manager.
      super(new GridLayout(1,0));
 
      // Assign file read to String.
      clobText = FileIO.openFile(FileIO.findFile(this));
 
      // Insert record before querying it.
      if (clobText.length() > 0) {
        if (insertClob(host,port,dbname,userid,passwd,clobText))
          clobText = getQuery(host,port,dbname,userid,passwd);
        else
          clobText = null; }
      else
        System.exit(2);
 
      // Construct text area and format it.
      textArea = new JTextArea(clobText);
      textArea.setEditable(false);
      textArea.setFont(new Font(Font.SANS_SERIF,Font.PLAIN,14));
      textArea.setLineWrap(true);
      textArea.setRows(10);
      textArea.setSize(400,100);
      textArea.setWrapStyleWord(true);
 
      // Put the image in container, and add label to panel.
      scrollPane = new JScrollPane(textArea);
      add(scrollPane); }
    // ---------------------------------------------------------------/
    private Boolean insertClob(String host,String port,String dbname
                              ,String user,String pswd,String fileString) {
 
      try
      {
        // Define connection.
		DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + dbname, user, pswd);
 
        // Create statement.
		PreparedStatement prest;
		String sql = "UPDATE item SET item_desc = ? WHERE  item_title = 'The Lord of the Rings - Fellowship of the Ring' AND item_subtitle = 'Widescreen Edition'";
		prest = conn.prepareStatement(sql);
		prest.setString(1,fileString);
 
        // Execute query.
        if (prest.execute())
          conn.commit();
 
        // Close resources.
        prest.close();
        conn.close();
 
        // Return CLOB as a String data type.
        return true; } // End of connection try-block.
 
      catch (SQLException e) {
        if (e.getSQLState() == null) {
          System.out.println(
            new SQLException("mysql Client Net8 Connection Error.",
                      "mysql-" + e.getErrorCode() +
                      ": Incorrect Net8 thin client arguments:\n\n" +
                      "  database name [" + dbname + "]\n",
                      e.getErrorCode()).getSQLState());
 
          // Return an empty String on error.
          return false; }
        else {
          System.out.println(e.getMessage());
 
          // Return an empty String on error.
          return false; }}}
    // -----------------------------------------------------------------/
    private String getQuery(String host,String port,String dbname
                           ,String user,String pswd) {
 
      // Define method variables.
      char[] buffer;
      int count = 0;
      int length = 0;
      String data = null;
      String[] type;
      StringBuffer sb;
 
      try {
        // Define connection.
		DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + dbname, user, pswd);
 
        // Define metadata object.
        DatabaseMetaData dmd = conn.getMetaData();
 
        // Create statement.
        Statement stmt = conn.createStatement();
 
        // Execute query.
        ResultSet rset =
          stmt.executeQuery(
            "SELECT item_desc " +
            "FROM   item " +
            "WHERE  item_title = " +
            "'The Lord of the Rings - Fellowship of the Ring'"+
            "AND    item_subtitle = 'Widescreen Edition'");
 
        // Get the query metadata, size array and assign column values.
        ResultSetMetaData rsmd = rset.getMetaData();
        type = new String[rsmd.getColumnCount()];
        for (int col = 0;col < rsmd.getColumnCount();col++)
          type[col] = rsmd.getColumnTypeName(col + 1);
 
        // Read rows and only CLOB data type columns.
        while (rset.next()) {
          for (int col = 0;col < rsmd.getColumnCount();col++) {
            if (type[col] == "CLOB") {
              // Assign result set to CLOB variable.
              Clob clob = rset.getClob(col + 1);
 
              // Check that it is not null and read the character stream.
              if (clob != null) {
                Reader is = clob.getCharacterStream();
 
                // Initialize local variables.
                sb = new StringBuffer();
                length = (int) clob.length();
 
                // Check CLOB is not empty.
                if (length > 0) {
 
                  // Initialize control structures to read stream.
                  buffer = new char[length];
                  count = 0;
 
                  // Read stream and append to StringBuffer.
                  try {
                    while ((count = is.read(buffer)) != -1)
                      sb.append(buffer);
 
                    // Assign StringBuffer to String.
                    data = new String(sb); }
                  catch (Exception e) {} }
                else
                  data = (String) null; }
              else
                data = (String) null; }
            else {
              data = (String) rset.getObject(col + 1); }}}
 
        // Close resources.
        rset.close();
        stmt.close();
        conn.close();
 
        // Return CLOB as a String data type.
        return data; }
      catch (SQLException e) {
        if (e.getSQLState() == null) {
          System.out.println(
            new SQLException("mysql Client Net8 Connection Error.",
                      "mysql-" + e.getErrorCode() +
                      ": Incorrect Net8 thin client arguments:\n\n" +
                      "  database name [" + dbname + "]\n",
                      e.getErrorCode()).getSQLState());
 
          // Return an empty String on error.
          return data; }
        else {
          System.out.println(e.getMessage());
          return data; }}
        finally {
          if (data == null) System.exit(1); }}}
  // -----------------------------------------------------------------/
  public static void main(String[] args) {
    // Define window.
    WriteReadCLOBMysql frame = new WriteReadCLOBMysql("Write & Read CLOB Text"); }}

My new Oracle Database 11g & MySQL 5.6 Developer Handbook will be available at Oracle Open World 2011 (OOW2011). It’s a great book to compare and contrast approaches in Oracle and MySQL. It covers Oracle SQL & PL/SQL and MySQL SQL & SQL/PSM (Persistent Stored Modules – functions and procedures). Unfortunately, it seems like the book won’t be available on amazon.com until much later in the month, and not available from amazon.de until November. You can read about it at McGraw Hill Professional’s web site. They’ve made a special effort to get copies to OOW2011.

I’ll also be at OOW2011. They’ve scheduled me in the bookstore (probably 2^nd floor of Moscone North, as usual) from 10 to 10:30 A.M. on Monday and Wednesday for a book signing. If you’re at OOW2011 and you like to stop by and say hello, I look forward to meeting you. Many folks leave comments on the posts but only a few suggest what they’d like me to write on when I’ve got a chance, and you can do that if you stop by to chat.

It’s also interesting to know how many folks use both Oracle and MySQL (any updates on that are always appreciated). Last year at the Bioinformatics and Computation Biology (ACM-BCB 2010) Conference in Niagara Falls, I found it interesting to discover how many pharmaceutical companies and national labs were using both Oracle and MySQL. They appeared consistent about using Oracle for their systems governed by legal compliance rules and MySQL for actual research.

The pharmaceutical companies also had clear barriers between the researchers and professional IT staff, specifically the DBAs. It seems that the DBAs don’t want to cede any control over installed Oracle instances, and they place barriers to research by denying additional Oracle instances when their site licenses would allow them to do so at no incremental cost. On the other hand, the DBAs are fine with letting researchers host and pilot with the MySQL Community Edition databases. This book supports those trying to figure out how to write portable SQL and how to port solutions from MySQL to Oracle and vice versa.

Hope to meet a few new folks at OOW2011. The Kindle version of the book became available 11/25/2011.

As an addendum to this original post, some folks asked for the summary of content for the new book, and the location of the errata (the errors of omission and commission in the book). Below is a summary of the book from page XVIII of the Introduction, and the errata is in the second comment to this post:

Part I: Development Components

• Chapter 1, “Architectures,” explains the Oracle 11g and MySQL 5.6 development architectures and highlights the comparative aspects of both client and server environments.
• Chapter 2, “Client Interfaces,” explains and demonstrates the basics of how you use SQL*Plus and MySQL Monitor client software.
• Chapter 3, “Security,” explains the security barriers for database servers and Data Control Language (DCL) commands that let you manage user and account privileges in the database servers.
• Chapter 4, “Transactions,” explains the nature of ACID-compliant transactions and the Two-phase Commit (2PC) process demonstrated by INSERT, UPDATE, and DELETE statements.
• Chapter 5, “Constraints,” explains the five primary database-level constraints and covers the check, not null, unique, primary key, and foreign key constraints.

Part II: SQL Development

• Chapter 6, “Creating Users and Structures,” explains how you can create users, databases, tables, sequences, and indexes.
• Chapter 7, “Modifying Users and Structures,” explains how you modify users, databases, tables, sequences, and indexes.
• Chapter 8, “Inserting Data,” explains how you insert data into tables.
• Chapter 9, “Updating Data,” explains how you update data in tables.
• Chapter 10, “Deleting Data,” explains how you delete data from tables.
• Chapter 11, “Querying Data,” explains how you query data from a single table, from a join of two or more tables, and from a join of two or more queries through set operators.
• Chapter 12, “Merging Data,” explains how you import denormalized data from external tables or source files and insert or update records in normalized tables.

Part III: Stored Program Development

• Chapter 13, “PL/SQL Basics,” explains the basics of using PL/SQL to write transactional blocks of code.
• Chapter 14, “SQL/PSM Basics,” explains the basics of using SQL/PSM to write transactional blocks of code.
• Chapter 15, “Triggers,” explains how to write database triggers in Oracle and MySQL databases.

Part IV: Appendix

• Appendix, Covers the answers to the mastery questions at the end of the chapters.

Somebody asked how to work around an error message they got after converting one of their who-audit columns to a TIMESTAMP column. A TIMESTAMP column has a DEFAULT or ON UPDATE current timestamp. They wanted to have two TIMESTAMP columns in the same table, with the intention of:

1. Having the created column assign a current timestamp value on insert
2. Having the updated column assign a current timestamp value on insert and update

This is a sample table with two of the traditional four who-audit columns:

CREATE TABLE sample
( sample_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, created    TIMESTAMP NOT NULL 
, updated    TIMESTAMP NOT NULL
                       DEFAULT CURRENT_TIMESTAMP
                       ON UPDATE CURRENT_TIMESTAMP);

When they tried to create the table they got the following error:

ERROR 1293 (HY000): Incorrect TABLE definition; there can be ONLY one TIMESTAMP COLUMN WITH CURRENT_TIMESTAMP IN DEFAULT OR ON UPDATE clause

While you can define a table with two columns that have a TIMESTAMP data type, you can’t define a table with two TIMESTAMP columns when one holds a DEFAULT or ON UPDATE CURRENT_TIMESTAMP value. However, you can use a DATETIME data type for the created column provided it’s null allowed, like:

CREATE TABLE sample
( sample_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, created    DATETIME 
, updated    TIMESTAMP NOT NULL
                       DEFAULT CURRENT_TIMESTAMP
                       ON UPDATE CURRENT_TIMESTAMP);

You can then define an on insert database trigger that fires when you create a new row, like this:

CREATE TRIGGER sample_t
BEFORE INSERT ON sample
FOR EACH ROW
BEGIN
  SET NEW.created = CURRENT_TIMESTAMP();
END;
$$

An INSERT statement would look like this:

INSERT INTO sample VALUES (NULL, NULL, NULL);

You would see the following if you query the table:

+-----------+---------------------+---------------------+
| sample_id | created             | updated             |
+-----------+---------------------+---------------------+
|         1 | 2011-08-11 00:26:21 | 2011-08-11 00:26:21 |
+-----------+---------------------+---------------------+

Hope this helps other too.

Somebody was complaining that you couldn’t just get a Yes/No answer from a query. Yes when rows are found and No when rows aren’t found, like an “In-stock” or “Out-of-stock” message combo from a query. He didn’t like having to handle an Empty set by writing logic in PHP to provide that “Out-of-stock” message.

I told him he was wrong, you can get a a Yes/No answer from a query. You just write it differently, instead of a query like this, which get the “In-stock” message but forces you to handle the “Out-of-stock” message in the PHP code base on no records found in the query.

SELECT  'In-stock'
FROM    item
WHERE   item_title = 'Star Wars II'
LIMIT 1;

It’s simpler to write it like the one below. You gets a Yes/No answer from a query whether a row matches the query condition or not:

SELECT IF('Star Wars VII' IN (SELECT item_title FROM item)
         ,'In-stock','Out-of-stock') AS yes_no_answer;

You can also write it this more generic way, which works in Oracle and MySQL:

SELECT CASE
         WHEN 'Star Wars VII' IN (SELECT item_title FROM item)
         THEN 'In-stock'
         ELSE 'Out-of-stock'
       END AS yes_no_answer
FROM   dual;

There’s no Star Wars VII yet, but this returns the desired result when it’s not found in the data set. It also works when you find Star Wars II in the data set. Never, say never …

A more useful and complete approach with this technique is shown below with data fabrication.

SELECT inline.query_string
,      CASE
         WHEN inline.query_string IN (SELECT item_title FROM item)
         THEN 'In-stock'
         ELSE 'Out-of-stock'
       END AS yes_no_answer
FROM  (SELECT 'Star Wars II' AS query_string FROM dual
       UNION ALL
       SELECT 'Star Wars VII' AS query_string FROM dual) inline;

The query runs in an Oracle or MySQL database and returns the following result set:

+---------------+---------------+
| query_string  | yes_no_answer |
+---------------+---------------+
| Star Wars II  | In-stock      |
| Star Wars VII | Out-of-stock  |
+---------------+---------------+

Hope this helps somebody else too.

While reviewing some material and explaining list partitioning in MySQL, I heard a rumor that sounded false. The rumor was that you can’t partition on anything other than the primary key column. That’s untrue, you can partition on another column provided it’s an integer column.

It appears the culprit that led to the rumor is a misunderstanding around ERROR 1503, which returns the following:

ERROR 1503 (HY000): A PRIMARY KEY must include ALL COLUMNS IN the TABLE's partitioning function

The problem can be recreated by defining a table with an inline primary key constraint instead of an INDEX on the auto incrementing column. Here’s an example of how to create the error message:

1
2
3
4
5
6
7
8
9
10
11

CREATE TABLE franchise
( franchise_id     INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, franchise_number INT UNSIGNED
, franchise_name   VARCHAR(20)
, city             VARCHAR(20)
, state            VARCHAR(20))
PARTITION BY LIST(franchise_number)
( PARTITION offshore VALUES IN (49,50)
, PARTITION west VALUES IN (34,45,48)
, PARTITION desert VALUES IN (46,47)
, PARTITION rockies VALUES IN (38,41,42,44));

The table should be defined without the inline PRIMARY KEY constraint on the auto incrementing column when you want to partition on another column. An index on the auto incrementing column doesn’t conflict with primary key rules. The correct syntax for a list partition would be:

1
2
3
4
5
6
7
8
9
10
11
12

CREATE TABLE franchise
( franchise_id     INT UNSIGNED AUTO_INCREMENT
, franchise_number INT UNSIGNED
, franchise_name   VARCHAR(20)
, city             VARCHAR(20)
, state            VARCHAR(20)
, INDEX idx (franchise_id)) engine=innodb
PARTITION BY LIST(franchise_number)
( PARTITION offshore VALUES IN (49,50)
, PARTITION west VALUES IN (34,45,48)
, PARTITION desert VALUES IN (46,47)
, PARTITION rockies VALUES IN (38,41,42,44));

The same problem can manifest itself on range partitioning. The fix is the same and here’s a code example:

1
2
3
4
5
6
7
8
9
10

CREATE TABLE ordering
( ordering_id      INT UNSIGNED AUTO_INCREMENT
, item_id          INT UNSIGNED
, rental_amount    DECIMAL(15,2)
, rental_date      DATE
, INDEX idx (ordering_id))
PARTITION BY RANGE(item_id)
( PARTITION jan2011 VALUES LESS THAN (10000)
, PARTITION feb2011 VALUES LESS THAN (20000)
, PARTITION mar2011 VALUES LESS THAN (30000));

The MySQL 5.6 Partitioning Keys, Primary Keys, and Unique Keys article is the key reference. It’s unfortunate that MySQL can’t partition on something other than an integer with PARTITION BY LIST syntax (check the changes in MySQL 5.6 online documentation). As you’ll notice in my more recent comment back to Tim, you can when you change the SQL phrase to PARTITION BY LIST COLUMNS. MySQL now has another feature that the Oracle Database 11g supports, that is list partitioning on variable-length string columns.

There are a few required changes but here’s a working example. The index must work against the auto incrementing column. The example comes my reply to Tim, who raised a great question.

CREATE TABLE franchise
( franchise_id     INT UNSIGNED AUTO_INCREMENT
, franchise_number INT UNSIGNED
, franchise_name   VARCHAR(20)
, city             VARCHAR(20)
, state            VARCHAR(20)
, KEY idx (franchise_id)) engine=innodb
PARTITION BY LIST COLUMNS (franchise_name)
( PARTITION m VALUES IN ('McDonald','Maggiano')
, PARTITION p VALUES IN ('Pappa John','Pizza Hut')
, PARTITION t VALUES IN ('Taco Bell','Taco Time'));

While that works, the better approach creates two unique constraints. One unique constraint on the natural key of the franchise_number and franchise_name, which serves to optimize access to data sets looking data up in the table based on a natural key search. Another unique constraint on the surrogate (auto incrementing column) and the two natural key columns, which optimizes joins from foreign keys to the partitioned table’s primary key.

CREATE TABLE franchise
( franchise_id     INT UNSIGNED AUTO_INCREMENT
, franchise_number INT UNSIGNED
, franchise_name   VARCHAR(20)
, city             VARCHAR(20)
, state            VARCHAR(20)
, UNIQUE ink (franchise_number, franchise_name)
, UNIQUE enk (franchise_id, franchise_number, franchise_name)) engine=innodb
PARTITION BY LIST COLUMNS (franchise_name)
( PARTITION m VALUES IN ('McDonald','Maggiano')
, PARTITION p VALUES IN ('Pappa John','Pizza Hut')
, PARTITION t VALUES IN ('Taco Bell','Taco Time'));

Somebody asked for a composite partitioning (MySQL terminology is subpartitioning) example, so here it is:

1
2
3
4
5
6
7
8
9
10
11
12

CREATE TABLE ordering
( ordering_id      INT UNSIGNED AUTO_INCREMENT
, item_id          INT UNSIGNED
, store_id         INT UNSIGNED
, rental_amount    DECIMAL(15,2)
, rental_date      DATE
, INDEX idx (ordering_id))
PARTITION BY RANGE(item_id)
  SUBPARTITION BY HASH(store_id) SUBPARTITIONS 4
( PARTITION jan2011 VALUES LESS THAN (10000)
, PARTITION feb2011 VALUES LESS THAN (20000)
, PARTITION mar2011 VALUES LESS THAN (30000));

Hope this helps those researching ERROR 1503 on list or range partitioning.