MacLochlainns Weblog

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.
# ------------------------------------------------------------
#   Find the machine's IP address with this command run as
#   the root user and use the port number specified in the
#   my.cnf file:
#   [root@localhost ~]# netstat -an | grep 3306
# ------------------------------------------------------------
 
bind-address=nnn.nnn.nnn.nnn
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. Here’s the source code for the book because I don’t know when it’ll be on the publisher’s web site.

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 from 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.

While preparing for next week’s classes, I ran across a new future feature of MySQL – the virtual column. It appears, according to the article, that MySQL will have virtual columns in MySQL 6.

The syntax is virtually identical to that found in Oracle databases. For example, here’s what suppose to work in the future:

1
2
3
4
5

CREATE TABLE salary
( salary_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, salary       DOUBLE NOT NULL
, bonus        DOUBLE
, compensation DOUBLE AS (salary + bonus));

It’s virtually (excuse the pun) how it would work in Oracle Database 11g. I wonder what else we see move over into MySQL. Just a note, virtual columns moved from Oracle RDB (a prior acquisition from Digital Equipment Corporation in 1996) to the Oracle Database 11g.

Equivalent syntax in Oracle Database 11g would be like this for concatenation:

1
2
3
4
5

CREATE TABLE employee
( employee_id NUMBER
, first_name  VARCHAR2(20)
, last_name   VARCHAR2(20)
, full_name   VARCHAR2(41) AS (first_name || ' ' || last_name));

And like this for a math operation (like the one noted above for a future release of MySQL):

1
2
3
4
5

CREATE TABLE salary
( salary_id    NUMBER       CONSTRAINT pk_salary PRIMARY KEY
, salary       NUMBER(15,2) CONSTRAINT nn_salary_01 NOT NULL
, bonus        NUMBER(15,2)
, compensation NUMBER(15,2) AS (salary + bonus));

Somebody wanted to know if you could add a NOT NULL column constraint in MySQL. That’s a great question and the answer is yes. The following example shows you how to do it.

1. Create a sample table without a NOT NULL constraint on a column that should have one. After creating this table, describe it and you’ll see that the testing_text column is

CREATE TABLE testing
( testing_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, testing_text VARCHAR(10));

2. Change the column definition from null allowed to not null for the TESTING_TEXT column. The only problem with this syntax is that it only works when there are no null values in the table or there are no rows in the table.

ALTER TABLE testing
CHANGE testing_text testing_text VARCHAR(10) NOT NULL;

3. Change the column definition from not null constrained to null allowed for the TESTING_TEXT column.

ALTER TABLE testing
CHANGE testing_text testing_text VARCHAR(10);

As always, I hope this helps.

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.

2. 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

3. 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.

4. 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.

5. 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.

6. 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:

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

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.