MacLochlainns Weblog

All this time I thought MySQL was so progressive by eliminating the DUAL pseudo table when selecting a string literal. I also thought it interesting that they also supported selecting a string literal from the DUAL pseudo table. While playing around with examples for my students, I found out that there is a use case when MySQL requires you to use the DUAL pseudo table.

You must use the DUAL pseudo table when you select a literal that includes a WHERE clause. For example, this fails:

mysql> SELECT 'Truth' WHERE 'STRING1' >= 'string1';

with the following error:

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 'WHERE 'string1' >= 'STRING1'' at line 1

It’s complaining that you haven’t designated a table. However, this succeeds when you add the FROM dual and it does case promotion or demotion to resolve whether the strings are case insensitive matches.

mysql> SELECT 'Truth' FROM dual WHERE 'STRING1' >= 'string1';

It looks so much like Oracle. The only difference is that Oracle does compare them on the basis of their ASCII values, and would return no rows for this query.

One of my students asked how you validate the foreign keys in a MySQL database. First off, this only works if the database engine supports referential integrity (the fancy word for foreign keys as database level constraints). InnoDB and Falcon support referential integrity. The answer can be found by leveraging the data catalog in the INFORMATION_SCHEMA.

Here's the query:

SELECT   CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint"
,        CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key"
,        CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key"
FROM     information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu
ON       tc.constraint_name = kcu.constraint_name
WHERE    tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name
,        kcu.column_name;

It's a bit different then the USER_CONSTRAINTS and USER_CONS_COLUMNS views in Oracle, which are covered in this prior post.

I recently ran across another interesting detail on MySQL foreign keys creation and removal that has to do with the case sensitivity of constraints. If you create the constraint in lower case and then try to drop the foreign key constraint in upper case, you'll encounter the following error:

ERROR 1025 (HY000): Error on rename of '.\database_name\table_name' to '.\database_name\#sql2-79c-1' (errno: 152)

The reason appears to be that MySQL can't find the table with the constraint name, and therefore throws an error that appears related to failure writing the new structure. It can be misleading.

I always tell my students how to protect their Oracle credentials but hesitated to do the same for MySQL because it involves Windows batch shell scripting (albeit simple one line scripting). Oracle is nice and clean about hiding credentials. If you only provide the USER name at the command prompt, then your PASSWORD is never displayed. Only the user name is displayed in the DOS command prompt session’s window frame. You do that in Oracle with a student user name, as follows:

C:\Data\Oracle> sqlplus student
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 21 09:20:25 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Enter password:

MySQL isn’t quite so convenient as a command-line tool. You must enter the -u or --user followed by the user name and -p or --password followed by the password when connecting to the database. For example, this shows a typical command prompt window frame:

As noted by Stephen in his comment, you can also mimic Oracle by using the following syntax:

C:\Data\MySQL> mysql -ustudent -p
Enter password:

Unless you use that syntax, anybody walking up can see your USER and PASSWORD in clear text. Not exactly the best security practice, eh? You can also suppress the USER name from displaying by writing a small batch file with your text editor or at the command-line, like this:

C:\Data\MySQL>COPY CON mysqlcmd.bat
@mysql -ustudent -p
^Z

While you’ll need to enter your password because you really don’t want to store that in a file. The @ symbol suppresses echo of the command, which means you won’t display your USER name or PASSWORD when you start the mysql client tool. It will only display the batch file name and the welcome message:

Hope this helps you when you’re working at the MySQL command-line. However, I’m often stunned by how few use it. They prefer phpMyAdmin or Quest’s Toad for MySQL.

While SQL Developer is a nice tool and free, Toad is still an awesome development platform, albeit for Windows. In fact, it was annoying to have to install the Microsoft .NET Framework before installing it. It is free for MySQL!

Since my students have to do all their work in Oracle and then port it to MySQL, I demonstrate Quest’s Toad for MySQL’s at the end of the term. I don’t want them to leverage the automatic ERD diagramming while they’re learning how to do it.

There’s only one real trick to making automatic ERD diagramming work. That trick requires that you write your loading scripts for the Inno DB and use referential integrity constraints. My sample Video Store scripts for my database class are updated for MySQL referential integrity.

Unlike the friendly CASCADE CONSTRAINTS clause you can use in Oracle, MySQL won’t let you create a re-runnable script with only DDL statements. Actually, the constraint comes from the InnoDB engine. You must issue a specialized InnoDB command before running your script:

11
12
13

-- This enables dropping tables with foreign key dependencies.
-- It is specific to the InnoDB Engine.
SET FOREIGN_KEY_CHECKS = 0;

Primary keys are a bit different from Oracle and it appears you can’t name them, at least I couldn’t see how to do it. Here’s an example of primary and foreign key constraints in MySQL. The primary key is inline and the foreign key constraints are out of line. This example from the downloadable scripts uses self referencing foreign key constraints.

24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

CREATE TABLE system_user
( system_user_id              INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, system_user_name            CHAR(20)     NOT NULL
, system_user_group_id        INT UNSIGNED NOT NULL
, system_user_type            INT UNSIGNED NOT NULL
, first_name                  CHAR(20)
, middle_name                 CHAR(20)
, last_name                   CHAR(20)
, created_by                  INT UNSIGNED NOT NULL
, creation_date               DATE         NOT NULL
, last_updated_by             INT UNSIGNED NOT NULL
, last_update_date            DATE         NOT NULL
, KEY system_user_fk1 (created_by)
, CONSTRAINT system_user_fk1 FOREIGN KEY (created_by)
  REFERENCES system_user (system_user_id)
, KEY system_user_fk2 (last_updated_by)
, CONSTRAINT system_user_fk2 FOREIGN KEY (last_updated_by)
  REFERENCES system_user (system_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Once installed (instructions are here) and connected to the MySQL database, you simply click the ERD icon in the top panel and drag the tables onto the canvas. You’ll see something like this (by the way click on the image to see its full size):

Have fun with it. It’ll be interesting to see how Oracle positions MySQL when they own it. My hunch is that they’ll continue to sell it and provide it as an open source product.

Sometimes you chalk something up as straightforward because you’ve done it a while. I did that in a lab assignment recently. It asked my students to upload a large text file and image to the MySQL database, store them in a TEXT and MEDIUMBLOB column, and read them back out of the database, like this:

The trick was that I wanted them to read the file into a string and then load the string. There wasn’t a single code example to do this out there, except some that might exist behind an account and credit card payment. I put together a complete example like the Oracle LOB processing page. You can find it here in the MySQL LOB processing blog page. More or less, it shows you how to stream an image into a MySQL database in chunks (I chose 8 K chunks).

The general tricks to upload a string require you enclose to enclose them with the addslashes() function before assigning a binary stream to a variable, then stripslashes() function by segment before you load it to the database. You really don’t need to do that. It’s a myth. The binary stream doesn’t require that extra handling. In fact, you can corrupt certain images when you use the addslashes() and stripslashes() functions; they should be avoided in this context.

You should do it in streams (at least when they’re larger than 1 MB), I chose the procedural mysqli to demonstrate it because there wasn’t an example that I or my students could find on the web. Just for information, some laptops don’t have the resources to accommodate LARGEBLOB datatypes on the Windows OS. The BLOB or MEDIUMBLOB should work fine, especially for this little example.

46
47
48
49
50
51
52
53
54
55
56
57
58

    // Declare a PL/SQL execution command.
    $sql = "UPDATE item SET item_blob = ? WHERE item_id =  ?";
 
    // Prepare statement and link it to a connection.
    if (mysqli_stmt_prepare($stmt,$sql)) {
      mysqli_stmt_bind_param($stmt,"bi",$item_blob,$id);
 
      $start = 0;
      $chunk = 8192;
      while ($start < strlen($item_blob)) {
        mysqli_send_long_data($stmt,0,substr($item_blob,$start,$chunk));
        $start += $chunk;
      }

You can find the code in that blog page referenced. Hope it helps some folks.

I just wanted to see how Zend Server Community Edition might be broken. Performing a full feature install on Windows x64, I confirmed that Zend Server’s Java Bridge depends on the 32-bit JRE (common sense prevails). Installing it against the JRE 64-bit jvm.dll did raised an exception but none of the instructions address the problem.

It’s one of those simplifying assumptions – everybody knows 32-bit software works with 32-bit software. Anybody running on Windows XP x64 should know that they may need a JDK 64-bit and both a JRE 64-bit and JRE 32-bit for some applications. For those who don’t know this, like my students and other newbies, when you run Windows XP the 64-bit stuff goes in the C:\Program Files directory and the 32-bit stuff goes in the C:\Program Files (x86) directory. This lets you develop 32-bit or 64-bit Java applications on the same 64-bit machine.

Another tidbit of interest, don’t choose a full install if you’ve already installed MySQL. The Zend Community Server isn’t smart enough to alter the configuration to another port, and their my.ini points to a 3306 listener port. This causes the MySQL_ZendServer51 service to fail. It also doesn’t uninstall well. If you don’t want to clean the Windows Registry, don’t choose to install a second MySQL.

As an FYI, the Zend installation of MySQL doesn’t put a password on the root account. Don’t forget to add one after the install if you go down the full product road. This has the Zend Server Community Edition installation instructions.

The Zend Core Server replaces the deprecated Zend Core for Oracle. I’ve put a quick installation guide here. It’s much nicer, and the licensed server is now the recommended direction from Oracle.

The community edition also installs MySQL, phpMySQLAdmin, and a brand new console. You should try it out.

I finally got around to creating a blog page that shows you how to install Zend Core for Oracle, verify the installation of PHP, your connection to an Oracle XE database with PHP, and your connection to a MySQL database with PHP.

It’s using the folding concepts I’ve adopted in the blog. One section unfolds to display screen shots for the installation of Zend Core for Oracle. Another section shows you how to confirm your PHP and Apache installation. Two other sections show you how to confirm your connection to an Oracle or MySQL database.

I apologize to those who don’t like to click through to another page, but it was a long page with 20+ images. Hope it helps a few folks, I know it’ll help my students.

My students have to do their group labs in Oracle and then port them individually to MySQL. Most of the conversion resolves around dates because Oracle spoils us with their simplicity in their SQL dialect.

For example, if we wanted to add 9 days to today’s system date (June 27, 2009) we can do this in Oracle:

SQL> SELECT SYSDATE + 9 FROM dual;

It prints

06-JUL-09

If we tried the equivalent in MySQL, we get a null because it treats any day value over 31 as a null. The maximum date in any month is 31, regardless of month. If you add more days than the maximum number minus your current date, you return a null. This is because adding a day never changes the month, and that yields invalid dates. In MySQL, you need to make this calculation with the adddate() or date_add() functions because they’ll increment months and years.

By way of example, if we only added four to today’s date (June 27, 2009) and formatted the output as a date, it works

mysql> SELECT str_to_date(utc_date() + 4,'%Y%m%d');

We’d get this:

+--------------------------------------+
| str_to_date(utc_date() + 4,'%Y%m%d') |
+--------------------------------------+
| 2009-06-31                           |
+--------------------------------------+

I always wanted an extra day in June.

In short, there are three possible non-dates in February that format as dates, and one day in every thirty day month. I’ve a hunch this is a bug (I’ve logged a bug and they’ve verified it and moved it to triage).

The correct way to perform this calculation in MySQL is to use either the adddate() or date_add() functions. They take the same arguments. Here’s a quick example:

mysql> SELECT adddate(utc_date(),INTERVAL 9 DAY);

It yields the correct date:

+------------------------------------+
| adddate(utc_date(),INTERVAL 9 DAY) |
+------------------------------------+
| 2009-07-06                         |
+------------------------------------+

I know this answers a student email but I hope it helps a few others too.

Somebody asked me why you must provide the IDENTIFIED BY password clause on a grant after you’ve already created the user. This is a very good question, I wondered it myself a while back. This blog provides the what, why, and how of it.

The reason you typically must provide the IDENTIFIED BY password clause is that typically you’ve enabled the NO_AUTO_CREATE_USER value in your sql_mode. That parameter is there by default from MySQL 5.0.2 forward. It’s what requires you to use the IDENTIFIED BY password clause.

The parameter enforces a match against user, host, and password columns in the user table of the mysql database. If you disable it, the match is on user and host columns in the user table. If you make a grant to a user without specifying a host and a wildcard (%) host doesn’t exist for that user, the grant will insert a duplicate user. The same user name with a wildcard host trumps the one with a localhost host value for access privileges, which means that user no longer needs a password to gain entry to the database.

The following steps will show and explain what happens in MySQL’s security model.

1. Sign on as the root user, then create a new myuser user:

mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'myuser'@'localhost';

If you attempt to grant permissions to the myuser without a password value, like this

mysql> GRANT ALL ON sampledb.* TO 'myuser'@'localhost';

You should see an outcome like the following. At least, you will when the NO_AUTO_CREATE_USER value is set in your sql_mode variable.

ERROR 1133 (42000): Can't find any matching row in the user table

If it let you set it, then the NO_AUTO_CREATE_USER value isn’t set. You can check what’s set with this query:

mysql> SELECT @@sql_mode;

You should see this:

+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+

2. Reset the sql_mode to disable the NO_AUTO_CREATE_USER value, like this (presuming you’ve got the generic MySQL 5.1 settings):

mysql> SET sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

3. After disabling the restriction, you can grant privileges on a sampledb to the myuser user without the IDENTIFIED BY password clause. You’d use the following syntax:

mysql> GRANT ALL ON sampledb.* TO 'myuser'@'localhost';

Before you sign off and sign on to test your new user, you should now query the USER table. Here’s the query to focus on the three important columns:

mysql> SELECT user, host, password
    -> FROM user
    -> WHERE user = 'myuser';

If you return more than one row, you made an error and most likely left off the @'localhost' or used @'%'. If you made that mistake, you’ll find that disabling the NO_AUTO_CREATE_USER value, can yield multiple entries in the user table for the same user. Worse yet, the user no longer can sign in with their password.

+--------+-----------+-------------------------------------------+
| user   | host      | password                                  |
+--------+-----------+-------------------------------------------+
| myuser | %         |                                           |
| myuser | localhost | *CBA73BBE5D9AF59311C3F4D7E8C20AA847F7B188 |
+--------+-----------+-------------------------------------------+

If you were to sign off and attempt to sign on with the myuser account password, you’d get the following error message:

ERROR 1045 (28000): Access denied FOR user 'myuser'@'localhost' (USING password: YES)

You can now sign on as the myuser user only without a password. Fixing it is simple, just drop the user for any host:

mysql> DROP USER 'myuser'@'%';

While disabling the sql_mode parameters makes your job of granting privileges easier, it risks compromising your database. Therefore, avoid it or be careful. If I missed something or got it wrong, let me know.