Archive for September, 2009
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
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 AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'FOREIGN KEY' ORDER BY tc.TABLE_NAME , kcu.column_name;
It’s a bit different then the
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 may 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
--user followed by the user name and
--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
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.
A couple students stumbled on the MySQL on-line documentation today. Specifically, they didn’t find instructions on how a non-
root user could change their password. I’ve also added this as an explanation on the MySQL documentation for 188.8.131.52.
SET PASSWORD Syntax page.
You reset your own password when you’re not the
root user with this syntax:
mysql> SET PASSWORD = PASSWORD('new_password');
This is simple syntax but carries a risk if you walk away from your terminal because somebody can run it without needing to know the original user password. Oracle requires that you know the original password if you use this syntax:
SQL> PASSWORD Changing password FOR PLSQL OLD password: NEW password: Retype NEW password: Password changed
If you think that you’re safe walking away from an Oracle SQL*Plus console, check this because it doesn’t require knowing the current password either.
SQL> SHOW USER USER IS "PLSQL" SQL> ALTER USER plsql IDENTIFIED BY plsql; USER altered.
Don’t walk away, always lock your terminal or use the
QUIT command to exit MySQL. This generally prevents somebody hijacking your password. You can check this post if you’re interested in changing the
root password when you don’t have it to begin with. Hope this helps a few folks.
Another faculty member asked me how to convert a DVD to an ISO on his Mac. Here are the instructions for everybody.
He needed to convert it because he wanted to deploy it on a Netbook from a USB drive. We all know you shouldn’t do this unless you’ve paid for the license and are only installing it on a single machine, like the Adobe license specifies.
The only tricky part to this is the last command-line step, which I borrowed from Mac OS X hints.
1. Open Disk Utility on your Mac OS X. It’ll look something like this:
2. Click the New Image icon in the tool bar, and save it as a compressed file. This writes it as a
.dmg file, which is an Apple format
It’ll take a while to write. The length of time is relevant to the size of the image on the DVD.
3. This is the first of two tricky steps. They’re tricky because you need to use the command-line. If you followed the instructions, you’ve saved the
.dmg file on the Desktop. Now, you need to convert the
.dmg formatted file to an
.iso formatted file. You open Terminal, which is found in your Applications folder. Once launched follow the steps below. Change the directory to the
Desktop or the folder where you put the file. You must do this before running this command because I’ve used relative file syntax (more or less only the file name). Alternatively, you could provide fully qualified file names.
MacPro:~ mclaughlinm$ cd Desktop MacPro:Desktop mclaughlinm$ ls *.dmg Acrobat9.dmg MacPro:Desktop mclaughlinm$ hdiutil convert Acrobat9.dmg -format UDTO -o Acrobat9.iso Reading Acrobat9 (Apple_ISO : 0)… ............................................................................................................... Elapsed Time: 27.655s Speed: 22.2Mbytes/sec Savings: 0.0% created: /Users/mclaughlinm/Desktop/Acrobat9.iso.cdr
4. Before you copy it to your Windows USB, you need to remove the trailing
.cdr from the file name. The syntax at the command-line is:
MacPro:Desktop mclaughlin$ mv Acrobat9.iso.cdr Acrobat9.iso
5. Open Finder, and copy the
.iso to your USB drive, and delete the temporary copy.
Hope this helps some folks.
The longest outstanding question I’ve received is on how you can aggregate the number of rows in a nested table (one inside another). If you need a refresher on joining non-collection columns to nested columns, you should check this earlier blog post.
The basic premise is boils down to two points. One are you trying to get a count of the elements in the nested collection, or a count of the elements in the nested collection based on one or more columns in the nested table. There are two ways to perform a basic count of the nested line numbers, and one way to perform the other. You find out how to perform both in the post.
1. You can run the following script to create a sample data environment. It is re-runnable, and designed to run on either Oracle Database 10g or 11g.
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
-- Conditionally delete objects in reverse dependency order. BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_type IN ('TABLE','SEQUENCE','TYPE') AND object_name IN ('TELEPHONE_TYPE','TELEPHONE_TABLE' ,'CONTACT_BOOK','CONTACT_BOOK_S1') ORDER BY object_type, object_name DESC) LOOP EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name; END LOOP; END; / -- Create a composite data type (structure) and collection. CREATE TYPE telephone_type AS OBJECT ( country_code VARCHAR2(3) , prefix_code VARCHAR2(5) , phone_number VARCHAR2(9)); / CREATE TYPE telephone_table AS TABLE OF telephone_type; / -- Create a table with a nested composite table and sequence. CREATE TABLE contact_book ( contact_book_id NUMBER , first_name VARCHAR2(30) , last_name VARCHAR2(30) , telephone_numbers TELEPHONE_TABLE ) NESTED TABLE telephone_numbers STORE AS telephone_struct; CREATE SEQUENCE contact_book_s1; -- Insert two rows of data. INSERT INTO contact_book VALUES ( contact_book_s1.NEXTVAL ,'Harry','Potter' ,telephone_table(telephone_type('44','20','7299 2127') ,telephone_type('44','17','8926 8115') ,telephone_type('44','18','3344 5566') ,telephone_type('44','72','9878 5436'))); INSERT INTO contact_book VALUES ( contact_book_s1.NEXTVAL ,'Weasley','Ronald' ,telephone_table(telephone_type('40','021','407 47 46') ,telephone_type('44','19','4088 1062') ,telephone_type('44','21','4075 5066')));
2. You can query the line numbers of the nested table by the scalar columns two ways. The first uses the
CARDINALITY function introduced in Oracle 10g. The second leverages a
CROSS JOIN and traditional aggregation tools.
2(a). Using the
CARDINALITY function is generally the simplest and best approach to this problem:
1 2 3 4
SELECT first_name , last_name , CARDINALITY(telephone_numbers) list FROM contact_book;
This yield the following data set:
FIRST_NAME LAST_NAME LIST ------------ ------------ ------ Harry Potter 4 Weasley Ronald 3
2(b). Using the traditional
CROSS JOIN and
GROUP BY clause:
1 2 3 4 5 6 7
SELECT cj.first_name , cj.last_name , COUNT(*) FROM (SELECT * FROM contact_book cb CROSS JOIN TABLE(cb.telephone_numbers)) cj GROUP BY cj.first_name , cj.last_name;
This yield the following data set:
FIRST_NAME LAST_NAME LIST ------------ ------------ ------ Harry Potter 4 Ronald Weasley 3
3. If you wanted to know how many numbers where in the nested table by country code, you need to solve the problem through a
CROSS JOIN and
GROUP BY clause, like this:
1 2 3 4 5 6 7 8 9
SELECT cj.first_name , cj.last_name , cj.country_code , COUNT(*) FROM (SELECT * FROM contact_book cb CROSS JOIN TABLE(cb.telephone_numbers)) cj GROUP BY cj.first_name , cj.last_name , cj.country_code;
This yield the following data set:
FIRST_NAME LAST_NAME COUNTRY_CODE LIST ------------ ------------ ------------ ------ Harry Potter 44 4 Ronald Weasley 44 2 Ronald Weasley 40 1
If I captured the basics of the question, great. If there are more questions, please let me know.