Archive for the ‘MySQL’ Category
Capture MySQL Foreign Keys
Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is based on a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.
You can capture foreign keys with a simple query when they’re determined by a single column value. However, this script creates ALTER statements that will fail when a table holds a multiple column foreign key value. The SELECT statement would look like this when capturing all foreign key values in a MySQL Server:
1 2 3 4 5 6 7 8 9 10 11 | SELECT CONCAT('ALTER TABLE',' ',tc.table_schema,'.',tc.table_name,' ' ,'ADD CONSTRAINT',' fk_',tc.constraint_name,' ' ,'FOREIGN KEY (',kcu.column_name,')',' ' ,'REFERENCES',' ',kcu.referenced_table_schema,'.',kcu.referenced_table_name,' ' ,'(',kcu.referenced_column_name,');') AS script 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; |
In MySQL 8 forward, you must make a case sensitive comparison, like:
9 | WHERE tc.constraint_type = 'FOREIGN KEY' |
You would add a line in the WHERE clause to restrict it to a schema and a second line to restrict it to a table within a schema, like this:
AND tc.table_schema = 'your_mysql_database' AND tc.table_name = 'your_table_name' |
Unfortunately, when the primary and foreign keys involve two or more columns you require a procedure and function. The function because you need to read two cursors, and the NOT FOUND can’t be nested in the current deployment of MySQL’s SQL/PSM stored programs. In this example the storedForeignKeys procedure finds the table’s foreign key constraints, and the columnList function adds the column detail. The command_list table stores the commands to restore foreign key constraints.
The command_list table that stores the values is:
CREATE TABLE command_list ( command_list_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , sql_command VARCHAR(6) NOT NULL , sql_object VARCHAR(10) NOT NULL , sql_constraint VARCHAR(11) , sql_statement VARCHAR(768) NOT NULL); |
This is the storedForeignKeys procedure:
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 | CREATE PROCEDURE storeForeignKeys ( pv_schema_name VARCHAR(64) , pv_table_name VARCHAR(64)) BEGIN /* Declare local variables. */ DECLARE lv_schema_name VARCHAR(64); DECLARE lv_table_name VARCHAR(64); DECLARE lv_constraint_name VARCHAR(64); DECLARE sql_stmt VARCHAR(1024); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor for foreign key table, it uses null replacement because the procedure supports null parameters. When you use null parameters, you get all foreign key values. */ DECLARE foreign_key_table CURSOR FOR SELECT tc.table_schema , tc.table_name , tc.constraint_name FROM information_schema.table_constraints tc WHERE tc.table_schema = IFNULL(lv_schema_name, tc.table_schema) AND tc.table_name = IFNULL(lv_table_name, tc.table_name) AND tc.constraint_type = 'FOREIGN KEY' ORDER BY tc.table_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Assign parameter values to local variables. */ SET lv_schema_name := pv_schema_name; SET lv_table_name := pv_table_name; /* Open a local cursor. */ OPEN foreign_key_table; cursor_foreign_key_table: LOOP /* Fetch a row into the local variables. */ FETCH foreign_key_table INTO lv_schema_name , lv_table_name , lv_constraint_name; /* Catch handler for no more rows found from the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_foreign_key_table; END IF; /* The nested calls to the columnList function returns the list of columns in the foreign key. Surrogate primary to foreign keys can be resolved with a simply query but natural primary to foreign key relationships require the list of columns involved in the primary and foreign key. The columnList function returns the list of foreign key columns in the dependent table and the list of referenced columns (or the primary key columns) in the independent table. */ SET sql_stmt := CONCAT('ALTER TABLE ',' ',lv_schema_name,'.',lv_table_name,' ' ,'ADD CONSTRAINT ',lv_constraint_name,' ' ,'FOREIGN KEY (',columnList(lv_schema_name,lv_table_name,lv_constraint_name)); /* Record the SQL statements. */ INSERT INTO command_list ( sql_command , sql_object , sql_constraint , sql_statement ) VALUES ('ALTER' ,'TABLE' ,'FOREIGN KEY' , sql_stmt ); END LOOP cursor_foreign_key_table; CLOSE foreign_key_table; END; $$ |
This is the columnList function:
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 | CREATE FUNCTION columnList ( pv_schema_name VARCHAR(64) , pv_table_name VARCHAR(64) , pv_constraint_name VARCHAR(64)) RETURNS VARCHAR(512) BEGIN /* Declare local variables. */ DECLARE lv_schema_name VARCHAR(64); DECLARE lv_table_name VARCHAR(64); DECLARE lv_constraint_name VARCHAR(64); DECLARE lv_column_count INT UNSIGNED; DECLARE lv_column_name VARCHAR(64); DECLARE lv_column_list VARCHAR(512); DECLARE lv_column_ref_list VARCHAR(64); DECLARE lv_referenced_table_schema VARCHAR(64); DECLARE lv_referenced_table_name VARCHAR(64); DECLARE lv_referenced_column_name VARCHAR(64); DECLARE lv_return_string VARCHAR(768); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor for foreign key column. */ DECLARE foreign_key_column CURSOR FOR SELECT kcu.column_name , kcu.referenced_table_schema , kcu.referenced_table_name , kcu.referenced_column_name FROM information_schema.key_column_usage kcu WHERE kcu.referenced_table_schema = lv_schema_name AND kcu.table_name = lv_table_name AND kcu.constraint_name = lv_constraint_name ORDER BY kcu.column_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Assign parameter values to local variables. */ SET lv_schema_name := pv_schema_name; SET lv_table_name := pv_table_name; SET lv_constraint_name := pv_constraint_name; /* Set the first column value. */ SET lv_column_count := 1; /* Open the nested cursor. */ OPEN foreign_key_column; cursor_foreign_key_column: LOOP /* Fetch a row into the local variables. */ FETCH foreign_key_column INTO lv_column_name , lv_referenced_table_schema , lv_referenced_table_name , lv_referenced_column_name; /* Catch handler for no more rows found from the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_foreign_key_column; END IF; /* Initialize the column list or add to it. */ IF lv_column_count = 1 THEN SET lv_column_list := lv_column_name; SET lv_column_ref_list := lv_referenced_column_name; /* Increment the counter value. */ SET lv_column_count := lv_column_count + 1; ELSE SET lv_column_list := CONCAT(lv_column_list,',',lv_column_name); SET lv_column_ref_list := CONCAT(lv_column_ref_list,',',lv_referenced_column_name); END IF; END LOOP cursor_foreign_key_column; CLOSE foreign_key_column; /* Set the return string to a list of columns. */ SET lv_return_string := CONCAT(lv_column_list,')',' ' ,'REFERENCES',' ',lv_referenced_table_schema,'.',lv_referenced_table_name,' ' ,'(',lv_column_ref_list,');'); RETURN lv_return_string; END; $$ |
You can call the procedure with a schema and table name, and you’ll get the foreign keys from just that table. You can create the following parent and child tables to test how multiple column foreign keys work in the script (provided because most folks use surrogate keys):
CREATE TABLE parent ( first_name VARCHAR(20) NOT NULL DEFAULT '' , last_name VARCHAR(20) NOT NULL DEFAULT '' , PRIMARY KEY (first_name, last_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE child ( child_name VARCHAR(20) NOT NULL , first_name VARCHAR(20) DEFAULT NULL , last_name VARCHAR(20) DEFAULT NULL , PRIMARY KEY (child_name) , KEY fk_parent(first_name, last_name) , CONSTRAINT fk_parent FOREIGN KEY (first_name, last_name) REFERENCES parent (first_name, last_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
You call the storeForeignKeys procedure for the child table with this syntax:
CALL storeForeignKeys('studentdb', 'child'); |
You call the storeForeignKeys procedure for all tables in a schema with this syntax:
CALL storeForeignKeys('studentdb', null); |
While unlikely you’ll need this, the following calls the storeForeignKeys procedure for all tables in all schemas:
CALL storeForeignKeys(null, null); |
You can export the command sequence with the following command to a script file:
SELECT sql_statement INTO OUTFILE 'c:/Data/MySQL/apply_foreign_keys.sql' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' FROM command_list; |
While preservation of tables and foreign keys is best managed by using a tool, like MySQL Workbench, it’s always handy to have scripts to do specific tasks. I hope this helps those looking for how to preserve foreign keys. You also can find a comprehensive treatment on how to write SQL/PSM code in Chapter 14 of my Oracle Database 11g and MySQL 5.6 Developer Handbook.
Add User Defined Types
Somebody asked me if there was a cheaper alternative to using the Embarcadero Data Architect (a data modeling tool). I said sure, you can use the MySQL Workbench. My friend laughed and said, it’s to model Oracle databases and they use different data types. I broke the news to him that he can create his own user defined types and use MySQL Workbench to model problems for the Oracle Database 11g.
For example, you can launch the MySQL Workbench, and click on the Model menu option, and in the menu window click on the User Defined Types choice, as shown in the following:
Choosing the User Defined Type option, launches the following form. You can enter customized user defined types in the User Defined Types module:
You enter user defined types by entering a name value and choosing valid MySQL type value before clicking the Add button. When you’ve added your last user defined type, click the OK button instead of the Add button. The next screen shot shows how you can create Oracle Database 11g native data types, specifically the NUMBER and VARCHAR2 data types.
Hopefully, this has shown that you can create User Defined Types let you use MySQL Workbench to create Oracle ERD models. Here’s an example of a table with Oracle’s NUMBER and VARCHAR2 data types:
Yes, MySQL Workbench is a marvelous tool with wide potential for use to solve problems with MySQL and other databases.
Relationship Notations
One of my students asked how to convert MySQL Workbench’s default Crow’s Foot (IE) diagram to one of the other supported formats – Classic, Connect to Columns, UML, and IDEF1X. Crow’s Foot is also known as the Information Engineering Model method (covered in Chapter 3 of my MySQL Workbench: Data Modeling & Development.
It quite simple, you open the Model Overview window, click on the Model menu choice. In the dialog, click on the Relationship Notation menu option. Click on one of the choices in the nested menu, like Column to Columns.
Hope this helps those working with MySQL Workbench.
MySQL Image Architecture
The LinkedIn MySQL DB Development group posed a questions on how to handle images. Naturally, the argument always goes: Should images be deployed in the database or the file system? I believe they should be stored in the database because the cost and time associated is too high with regard to managing files, a file naming schema, and backing up the file system discretely from the database.
Since there’s a significant difference between the backup of transactional data and image data, they should be placed in different databases. The imagedb database is where you would place the images and large text descriptions, as shown in the MySQL Workbench ERD:
The imagedb ERD splits the foreign key references back to the system_user table, which contains the individual user credentials. The system_user table serves as the Access Control List (ACL) for the application.
Until I get a chance to write the code for this model, you can refer to the generic PHP/MySQL solution from several years back (its code source was last tested with PHP 5.3). As always, I hope this helps.
Signal from a procedure
As I review with my students, a stored function works like a standalone program, while a stored procedure runs in the scope of another program unit. For example, you can compare the result of a function as an expression in an IF statement, like:
IF add_numbers(1,3) > 3 THEN ... ELSE ... END IF; |
You can’t call procedures inside an IF statement, but you can call the procedure and use a single OUT-mode (pass-by-reference) parameter from the procedure in a subsequent IF statement. You can implement a a wait procedure like that with the following example.
The example first creates two tables, the road_runner and coyote tables:
-- Drop road_runner table if exists. DROP TABLE IF EXISTS road_runner; -- Create roadrunner table. CREATE TABLE road_runner ( road_runner_id int unsigned auto_increment primary key , road_runner_text varchar(20) , CONSTRAINT road_runner_nk UNIQUE (road_runner_text)); -- Drop coyote table if exists. DROP TABLE IF EXISTS coyote; -- Create coyote table. CREATE TABLE coyote ( coyote_id int unsigned auto_increment primary key , coyote_text varchar(20) , road_runner_id int unsigned , CONSTRAINT coyote_nk UNIQUE (coyote_text)); |
The following creates a procedure that:
- Writes data to two tables when the values are unique, returning a value of zero when it works.
- Writes data to neither table when the values to either table are non-unique, returning a value of one when it fails.
The procedure uses a 0 as a false value and a 1 as a true value. The use of a 0 and 1 for truth is a consistent approach for languages where they don’t support a Boolean data type.
-- Change the delimiter to a "$$" DELIMITER $$ -- Drop the paired procedure. DROP PROCEDURE IF EXISTS two_table$$ -- Create the paired procedure. CREATE PROCEDURE two_table (IN pv_road_runner_text varchar(20) ,IN pv_coyote_text varchar(20) , OUT pv_confirm_it int) BEGIN /* Declare a variable to hold a sequence value for an auto incrementing value. */ DECLARE lv_road_runner_id int unsigned; /* Declare a condition variable for attempting to write a non-unique record to a table. */ DECLARE duplicate CONDITION FOR 1062; /* Declare an event handler for a duplicate condition variable, rollback transaction, and set 1 as a false condition. */ DECLARE EXIT HANDLER FOR duplicate BEGIN ROLLBACK to all_or_none; SET pv_confirm_it = 1; END; /* Start the transaction. */ START TRANSACTION; /* Set the save point for a multiple table transaction. */ SAVEPOINT all_or_none; /* Insert into road runner table. */ INSERT INTO road_runner (road_runner_text) VALUES (pv_road_runner_text); /* Capture the auto incrementing sequence value as a local variable. */ SET lv_road_runner_id := last_insert_id(); /* Insert into the coyote table. */ INSERT INTO coyote (coyote_text ,road_runner_id) VALUES (pv_coyote_text ,lv_road_runner_id); /* Commit the record. */ COMMIT; /* Set the control variable to a true value. */ SET pv_confirm_it := 0; END; $$ DELIMITER ; |
You can test the pass-by-reference procedure with the following code:
-- Set a control variable with a null value. SET @sv_control := null; -- Query the results from a join between the road_runner and coyote tables. SELECT * FROM road_runner r INNER JOIN coyote c ON r.road_runner_id = c.road_runner_id; -- Call the two_table procedure with unique results. CALL two_table('Road Runner 1','Coyote 1',@sv_control); -- Query the control variable result from the second call to the -- two_table procedure. SELECT @sv_control AS "1st Insert"; -- Reset the value for a second test. SET @sv_control := 0; -- Call teh two_table procedure with unique results. CALL two_table('Road Runner 2','Coyote 1',@sv_control); -- Query the results from a join between the road_runner and coyote tables. SELECT * FROM road_runner r INNER JOIN coyote c ON r.road_runner_id = c.road_runner_id; -- Query the control variable result from the second call to the -- two_table procedure. SELECT @sv_control AS "2nd Insert"; |
I hope this helps anybody trying to implement a pass-by-reference procedure with a control variable. You can find other examples in Chapter 14 of Oracle Database 11g and MySQL 5.6 Developer Handbook (pp. 446-449 and 450-451).
Cleanup a MySQL Schema
My students required way to remove all their tables, views, and constraints from a MySQL database (or the alias schema). Since they’re using referential or foreign key constraints, I also wrote one procedure to drop all foreign key constraints from a database. There’s also another to drop views. The final stored procedure calls the procedure that drops foreign keys, then calls the procedure to drop views before dropping all the tables.
Here’s the dropForeignKeys stored procedure, but if you want to capture ALTER statements that add these back later please check my follow-up Capture MySQL Foreign Keys post.
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 | -- Provide a log file debugging statement. SELECT 'DROP PROCEDURE IF EXISTS dropForeignKeys'; -- Conditionally drop the procedure. DROP PROCEDURE IF EXISTS dropForeignKeys; -- Provide a log file debugging statement. SELECT 'CREATE PROCEDURE dropForeignKeys'; -- Change delimiter to create procedure. DELIMITER $$ -- Create procedure. CREATE PROCEDURE dropForeignKeys ( pv_database VARCHAR(64) , pv_referenced_table VARCHAR(64)) BEGIN /* Declare local statement variables. */ DECLARE lv_stmt VARCHAR(1024); /* Declare local cursor variables. */ DECLARE lv_table_name VARCHAR(64); DECLARE lv_constraint_name VARCHAR(64); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor. */ DECLARE foreign_key_cursor CURSOR FOR SELECT rc.table_name , rc.constraint_name FROM information_schema.referential_constraints rc WHERE constraint_schema = IFNULL(pv_database,database()) AND referenced_table_name = pv_referenced_table ORDER BY rc.table_name , rc.constraint_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Open a local cursor. */ OPEN foreign_key_cursor; cursor_foreign_key: LOOP FETCH foreign_key_cursor INTO lv_table_name , lv_constraint_name; /* Place the catch handler for no more rows found immediately after the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_foreign_key; END IF; /* Set a SQL statement by using concatenation. */ SET @SQL := CONCAT('ALTER TABLE',' ',lv_table_name,' ','DROP FOREIGN KEY',' ',lv_constraint_name); /* Prepare, run, and deallocate statement. */ PREPARE lv_stmt FROM @SQL; EXECUTE lv_stmt; DEALLOCATE PREPARE lv_stmt; END LOOP cursor_foreign_key; CLOSE foreign_key_cursor; END; $$ -- Reset delimiter to run SQL statements. DELIMITER ; |
Here’s the dropViews stored procedure:
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 | -- Provide a log file debugging statement. SELECT 'DROP PROCEDURE IF EXISTS dropViews'; -- Conditionally drop the procedure. DROP PROCEDURE IF EXISTS dropViews; -- Provide a log file debugging statement. SELECT 'CREATE PROCEDURE dropViews'; -- Change delimiter to create procedure. DELIMITER $$ -- Create procedure. CREATE PROCEDURE dropViews ( pv_database VARCHAR(64)) BEGIN /* Declare local statement variables. */ DECLARE lv_stmt VARCHAR(1024); /* Declare local cursor variables. */ DECLARE lv_view_name VARCHAR(64); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor. */ DECLARE view_cursor CURSOR FOR SELECT v.table_name FROM information_schema.views v WHERE table_schema = IFNULL(pv_database, database()) ORDER BY v.table_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Open a local cursor. */ OPEN view_cursor; cursor_view: LOOP FETCH view_cursor INTO lv_view_name; /* Place the catch handler for no more rows found immediately after the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_view; END IF; /* Set a SQL statement by using concatenation. */ SET @SQL := CONCAT('DROP VIEW',' ',lv_view_name); /* Prepare, run, and deallocate statement. */ PREPARE lv_stmt FROM @SQL; EXECUTE lv_stmt; DEALLOCATE PREPARE lv_stmt; END LOOP cursor_view; CLOSE view_cursor; END; $$ -- Reset delimiter to run SQL statements. DELIMITER ; |
Here’s the dropTables stored procedure:
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 | CREATE PROCEDURE dropTables ( pv_database VARCHAR(64)) BEGIN /* Declare local statement variables. */ DECLARE lv_stmt VARCHAR(1024); /* Declare local cursor variables. */ DECLARE lv_table_name VARCHAR(64); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor. */ DECLARE table_cursor CURSOR FOR SELECT t.table_name FROM information_schema.tables t WHERE table_schema = IFNULL(pv_database, database()) ORDER BY t.table_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Drop the views. */ CALL dropViews(null); /* Open a local cursor. */ OPEN table_cursor; cursor_table: LOOP FETCH table_cursor INTO lv_table_name; /* Place the catch handler for no more rows found immediately after the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_table; END IF; /* Drop the tables. */ CALL dropForeignKeys(null,lv_table_name); /* Set a SQL statement by using concatenation. */ SET @SQL := CONCAT('DROP TABLE',' ',lv_table_name); /* Prepare, run, and deallocate statement. */ PREPARE lv_stmt FROM @SQL; EXECUTE lv_stmt; DEALLOCATE PREPARE lv_stmt; END LOOP cursor_table; CLOSE table_cursor; END; $$ -- Reset delimiter to run SQL statements. DELIMITER ; |
You put these in a rerunnable script, run it, and then call the dropTables stored procedure. You can pass a database (or schema) name or a null value. When you pass a null value, it uses the current database, like:
CALL dropTables(null); |
As always, I hope this helps others.
Mac Mini to the rescue
In teaching, I had a problem because my students have different base operating systems, like Windows 7, Windows 8, Linux, and Mac OS X. I needed a teaching and lecture platform that would let me teach it all (not to mention support their environments). That meant it had to virtualize any of the following with a portable device:
- Windows 7 or 8 hosting natively an Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6
- Windows 7 or 8 hosting a Fedora or Oracle Unbreakable Linux VM (3 or 4 GB) with Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6
- Mac OS X hosting a Fedora or Oracle Unbreakable Linux VM (3 or 4 GB) with Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6
- Ubuntu hosting a Fedora or Oracle Unbreakable Linux VM (3 or 4 GB) with Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6
I never considered a manufacturer other than Apple for a laptop since they adopted the Intel chip. Too many of the others sell non-hyperthreaded laptop machines that they market as i5 or i7 64-bit OS machines when they’re not. Some of those vendors disable the hyperthreading facility while others provide motherboards that can’t support hyperthreading. The ones I dislike the most provide a BIOS setting that gives the impression you can enable hyperthreading when you can’t. All Apple devices, MacBook, MacBook Pro, Mac Mini, and Mac Pro do fully support a 64-bit OS and their virtualization.
A MacBook Pro came to mind but the disk space requirements were 1 TB, and that’s too pricey. I went with the Mac Mini because with 16 GB of memory and a 1 TB drive it was only $1,200. Add a wireless keyboard and mighty mouse, and an HDMI and mini-DVI connections, and I had my solution. Naturally, my desktop is a one generation old Mac Pro with 64 GB of memory and 12 TB of disk space, which supports all the virtual machines used for testing. Note to Apple marketing staff: The prior version of the Mac Pro let you pay reasonable (3rd party) prices for the additional memory and disk drives.
The Mac Mini means I can travel anywhere and plug into the console and demo tools and techniques from a myriad set of platforms without the hassle of moving on and off to frequently VM images. It’s a great solution with only one downside, HDMI to DVI sometimes creates purple toned screens. It’s unfortunate because some venues have monitors that don’t support HDMI).
Open a port on Fedora
Since MySQL Workbench 6.0 isn’t available for Fedora, Version 20, I’m having my students install it on their local Windows and Mac OS X operating systems. You can configure the /etc/sysconfig/iptables file to enable port 3306 after installing MySQL on Fedora.
You can open a port by using the firewall-config utility (easy way) or by adding the following line to the /etc/sysconfig/iptables file (Fedora’s instructions on editing iptables [hard way]). The file won’t exist initially, but you can create it by running the following command as the root superuser or sudoer:
shell> service iptables save |
You you can run the following commands as the root superuser, which saves the line in the iptables file:
shell> iptables -A INPUT -m conntrack --ctstate NEW -m tcp -p tcp --dport 3306 -j ACCEPT shell> iptables-save |
After making the change to the /etc/iptables file you can change the firewall by running the following command as the root superuser:
shell> service iptables restart |
Just make sure you don’t inadvertently start both iptables and ip6tables as services. You can check that only one is running by using the following commands:
shell> service iptables status shell> service ip6tables status |
MySQL Workbench Configuration
- The first thing you need to do is click on the
+symbol in the circle to the right of the MySQL Connections text label. It launches the Setup New Connection dialog.
- The second thing you need to do is enter a Connection Name, Hostname, Port, and Username. Then, click the Test Connection button.
- The Test Connection button launches the Connect to MySQL Server dialog. Enter the password for the
studentuser (or whatever user you’re interested in), and then click the OK button.
- When the credentials in the Connect to MySQL Server dialog work, you see the following confirmation dialog message. Click the OK button to continue and you’ll see a new VMWare Fedora Instance connection icon.
- Click the VMWare Fedora Instance connection to start a new connection.
- The VMWare Fedora Instance button launches the Connect to MySQL Server dialog. Like you did when configuring the connection, enter the password for the
studentuser (or whatever user you’re interested in), and then click the OK button. It launches an interactive panel that lets you run, edit, or save the SQL script file.
- Type the following two lines in the Query1 panel (at least if you have a
studentdbdatabase:USE studentdb; SELECT DATABASE();
Fedora Install of MySQL
I built a new image on VMWare Fusion for my class, which required installing MySQL 5.6 on Fedora, Version 20. If you don’t know how to add your user to the sudoers list, you should check this older and recently updated blog post.
- Download the MySQL Yum Repository and launch the downloaded RPM.
- Install MySQL on Fedora, Version 20, which you can find with the following command:
shell> rpm -qa | grep mysql mysql-community-release-fc20-5.noarch |
The fc20-5 changes with point releases, but assuming that you’re installing the fc20-5 release:
shell> sudo yum localinstall mysql-community-release-fc20-5.noarch.rpm |
- Install MySQL on Fedora with the following command:
shell> sudo yum install mysql-server |
- Start the MySQL service on Fedora with the following command:
shell> sudo service mysqld start |
- Secure the MySQL installation with the following command:
shell> mysql_secure_installation |
- Set the MySQL Service to start with the Fedora operating system with the following command (not
chkconfig):
shell> sudo systemctl enable mysqld.service |
It sets the following two links:
ln -s '/usr/lib/systemd/system/mysqld.service' '/etc/systemd/system/mysql.service' ln -s '/usr/lib/systemd/system/mysqld.service' '/etc/systemd/system/multi-user.target.wants/mysqld.service' |
Restart the Fedora operating system to effect the changes.
- Reset the MySQL configuration file to enable external connections through Port 3306 with the following changes to the my:
Remark out the socket line, like this:
#socket=/var/lib/mysql/mysql.sock |
Add the bind-address and port lines below after you know the actual IP address of the server to the my.cnf file in the /etc directory.
You substitute the actual IP address for the nnn.nnn.nnn.nnn on the bind_address line with the actual IP address returned by the ifconfig command, like this:
shell> ifconfig |
Then, add these two lines to the my.cnf file.
bind-address=nnn.nnn.nnn.nnn port=3306 |
It’s actually easier to use localhost.localdomain than an IP address when you use DHCP, like:
bind-address=localhost.localdomain port=3306 |
If you plan to connect from a host system, like Windows or Mac OS X, to a virtual Linux environment using DHCP, change localhost.localdomain to 0.0.0.0:
bind-address=0.0.0.0 port=3306 |
- Restart the mysqld service with the following syntax:
shell> sudo service mysqld restart |
You can check whether MySQL is listening on Port 3306 with this syntax:
shell> sudo netstat –anp | grep 3306 |
It displays:
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 1311/mysqld |
Go to this page if you want to install MySQL Workbench.
Excel PowerPivot & DAX
I’ve worked with every release of Microsoft Excel, and I know it takes effort to keep up to date with certain releases. Clearly, the Data Analysis eXpression (DAX) Language introduced in Excel 2010 went unnoticed by many, which was sad. DAX is truly a powerful extension to the analytical and modeling approaches in Microsoft Excel.
I’d like to recommend Microsoft Excel 2013 Building Data Models with PowerPivot to those who haven’t learned how to use DAX in Excel 2010, 2011, or 2013. DAX works with tables but if you don’t use tables, I guess you can skip DAX because you must have infinite time to produce marginal analytical outcomes (tongue in cheek humor). However, if you’re like most folks, you want a book to get you up-to-speed quickly, and that’s what this book will do for you.
Just one caveat if you’re using an Oracle or MySQL database, use the prepackaged analytic functions before you download the data set. You should always pre-select data before applying analytics in Excel. Remember the more refined the data model you start with the easier it is to structure analytical tools to leverage the data model. While DAX is powerful, it doesn’t replace the speed and query optimized behaviors of effective Oracle or MySQL queries.
Subscribe via RSS