Tuesday, June 30, 2009

Managing the MySQL Instance

An Oracle DBA needs to learn how to manage a MySQL Instance. The MySQL instance has different memory buffers and caches like any database server. Part of MySQL's uniqueness is that it has memory areas to be configured that are associated with different types of storage engines.

Learning the MySQL Architecture

MySQL is an database server and anyone managing the MySQL database server has to learn the architecture, and feature functionality like any other database server. The biggest mistake to make with a new MySQL environment is thinking it is a toy and trivial to work with. A MySQL server needs to be properly configured, tuned and managed like any other database server.

The key to learning any database server is to learn its feature/functionality and the architecture.

Oracle vs. MySQL

As MySQL continues to explode in the marketplace, there are more Oracle and SQL Server DBAs learning MySQL. For Oracle DBAs, its important to understand that MySQL is a different type of database server.
  • Oracle is the aircraft carrier of database servers. Oracle has by far more features than any other database server. Oracle supports a number of database server solutions such as Real Application Clusters (RAC), Data Guard (standby database) and Oracle Streams (replication).
  • MySQL is the speed boat of database servers. MySQL is a very light weight database server that excels in web applications. MySQL is designed to be fast, easy to use and simple to install.
I'm always asked about porting Oracle applications to MySQL or MySQL applications to Oracle. Well if someone buys an aircraft carrier they probably bought it for specific reasons and the same for a speed boat. Its very unlikely that someone is going to switch from one to the other and be happy. However, each database server has areas where it excels.

MySQL Workbench 5.1.16 is GA!

MySQL Workbench Version 5.1.16 the General Availability build can be downloaded at http://dev.mysql.com/downloads/workbench/5.1.html.

Congratulations to the Workbench team for their great effort in this release.

Thursday, June 11, 2009

Converting an Oracle Schema to MySQL

Both Oracle and MySQL provide a number of sample schemas so users can build different schema environments and load them with data. So I thought I'd get one of the Oracle sample schemas and go through the exercise of converting it to a MySQL schema. I could have gone in either direction, since this is more of a MySQL blog I made MySQL the destination. Before I start let me introduce some of the Oracle and MySQL sample schemas.

Oracle Sample Schemas:
  • Human Resources: (HR) is a simple schema containing tables with basic primary and foreign key relationships such as: employees, departments, jobs, locations, regions, countrys, jobs, etc. This can be extended to support Oracle Internet Directory demos.
  • Order Entry (OE) is a more complex set of tables demonstrating the different datatypes supported by Oracle. Tables dealing with products, sales, customers, etc. are used in this schema.
  • Online Catalog (OC) is a supporting schema that demonstrates object-relational database objects built inside the OE schema.
  • Product Media (PM) is a schema that supports multimedia datatypes. Audio, video, images, large text data are all used in this schema.
  • Queued Shipping (QS) to demonstrate Oracle Advanced Queuing capabilities. Advanced Queuing and XML are used in this schema.
  • Sales History (SH) is a schema supporting a large number of tables. Features like partitioning and Oracle analytics can be used with this schema.

MySQL Sample Schemas:
  • World is a basic schema using three tables City, Country and CountryLanguages that use MyISAM tables.
  • Sakila is a schema that adds additional complexity using an online DVD store to demonstrate more of the MySQL 5.1 features.
  • Employees is a schema with a much larger data set containing employees, deparments, salaries, titles, etc that are very intuitive to work with. There are approximately 4 million records to work with. Different storage engines and partitioning are often benchmarked using this schema.
Summary of changes made to convert Oracle Schema to MySQL Schema

Here is a summary of changes made to convert the Oracle schema script to a MySQL schema script. The goal here is to provide a level of understanding of converting a schema.
  • Comment out SQL*Plus specific commands.
  • Change REM to --
  • Change VARCHAR2 to VARCHAR
  • Change NUMBER to INT
  • Change NUMBER(x,x) to DECIMAL(x,x)
  • Change CONSTRAINT keyword Syntax
  • For InnoDB tables, define PRIMARY KEY as part of the CREATE TABLE command and not a separate CREATE INDEX command.
  • Make sure tables are created as InnoDB. set storage_engine=innodb;
  • Modify constraint syntax for FOREIGN KEY definitions.
  • Fully quality PRIMARY KEY column in FOREIGN KEY constraint.
  • Remove READ ONLY clause with CREATE VIEW.

Converting the Oracle HR sample schema to MySQL

I decided the Oracle HR schema would be a good sample schema to convert to a MySQL schema to demonstrate things to consider in creating schema scripts. I started with the cre_hr.sql script and converted it to a cre_hr_mysql.sql script. The Oracle cre_hr.sql script I started with can be found at:
  • http://www.oracle.com/technology/obe/sqldev_obe/osdm/files/hr_cre.sql

MySQL equivalent of the Oracle HR schema script

Here is the corresponding MySQL create script. The only thing I did not address were the sequential incremental values used with Oracle sequences. I included the Oracle CREATE SEQUENCE commands in so it would show the adjustments I made to use autoincrement but also show that the incremental values would need to be addressed. A little more work needs to be done here to verify the data loads would create the same values in an Oracle environment. As I mentioned my main goal was to give someone a feel for the code differences in creating a schema between Oracle and MySQL.

-- hr_cre_mysql.sql

Notes of changes:
Comment out SQL*Plus specific commands.
Change REM to --
Change INT to INT
Change INT(x,x) to DECIMAL(x,x)
Change CONSTRAINT keyword Syntax
For InnoDB tables, define PRIMARY KEY as part of the CREATE TABLE command and not a separate CREATE INDEX command.
Make sure tables are created as InnoDB.
set storage_engine=innodb;
Modify constraint syntax for FOREIGN KEY definitions.
Fully quality PRIMARY KEY column in FOREIGN KEY constraint.
Remove READ ONLY clause with CREATE VIEW.

USE hr;

( region_id INT NOT NULL
, region_name varchar(25),
primary key (region_id)

CREATE TABLE countries
( country_id CHAR(2) NOT NULL
, country_name varchar(40)
, region_id INT
, PRIMARY KEY (country_id)
) ;

ALTER TABLE countries
ADD (FOREIGN KEY (region_id)
REFERENCES regions(region_id)
) ;

CREATE TABLE locations
( location_id INT(4)
, street_address varchar(40)
, postal_code varchar(12)
, city varchar(30) NOT NULL
, state_province varchar(25)
, country_id CHAR(2)
, PRIMARY KEY (location_id)
) auto_increment=3300;

CREATE SEQUENCE locations_seq\

ALTER TABLE locations
ADD ( FOREIGN KEY (country_id)
REFERENCES countries(country_id)
) ;

CREATE TABLE departments
( department_id INT(4)
, department_name varchar(30) NOT NULL
, manager_id INT(6)
, location_id INT(4)
, PRIMARY KEY (department_id)
) auto_increment=280;

ALTER TABLE departments
ADD ( FOREIGN KEY (location_id)
REFERENCES locations (location_id)
) ;

CREATE SEQUENCE departments_seq\

( job_id VARCHAR(10)
, job_title VARCHAR(35) NOT NULL
, min_salary INT(6)
, max_salary INT(6)
, PRIMARY KEY (job_id)
) ;

CREATE TABLE employees
( employee_id INT(6)
, first_name varchar(20)
, last_name varchar(25) NOT NULL
, email varchar(25) NOT NULL
, phone_INT varchar(20)
, hire_date DATE NOT NULL
, job_id varchar(10) NOT NULL
, salary DECIMAL(8,2)
, commission_pct DECIMAL(2,2)
, manager_id INT(6)
, department_id INT(4)
, UNIQUE KEY (email)
, PRIMARY KEY (employee_id)
) auto_increment=207;

IF (salary <= 0 ) THEN call unknown(); END IF; END// DELIMITER ; ALTER TABLE employees ADD ( FOREIGN KEY (department_id) REFERENCES departments(department_id)); ALTER TABLE employees ADD ( FOREIGN KEY (job_id) REFERENCES jobs (job_id)); ALTER TABLE employees ADD ( FOREIGN KEY (manager_id) REFERENCES employees(employee_id)) ; ALTER TABLE departments ADD ( FOREIGN KEY (manager_id) REFERENCES employees (employee_id)) ; /* CREATE SEQUENCE employees_seq\ START WITH 207\ INCREMENT BY 1\ NOCACHE\ NOCYCLE;\ */ CREATE TABLE job_history ( employee_id INT(6) NOT NULL , start_date DATE NOT NULL , end_date DATE NOT NULL , job_id VARCHAR(10) NOT NULL , department_id INT(4) , PRIMARY KEY (employee_id, start_date) ) ; DELIMITER // CREATE TRIGGER jobhist_dates BEFORE INSERT ON job_history FOR EACH ROW BEGIN IF (end_date > start_date ) THEN call unknown();


ALTER TABLE job_history
REFERENCES jobs(job_id));

ALTER TABLE job_history
ADD ( FOREIGN KEY (employee_id)
REFERENCES employees(employee_id));

ALTER TABLE job_history
ADD (FOREIGN KEY (department_id)
REFERENCES departments(department_id)) ;

CREATE OR REPLACE VIEW emp_details_view
(employee_id, job_id, manager_id,
department_id, location_id, country_id,
first_name, last_name, salary,
commission_pct, department_name, job_title,
city, state_province, country_name, region_name)
e.employee_id, e.job_id, e.manager_id,
e.department_id, d.location_id, l.country_id,
e.first_name, e.last_name, e.salary,
e.commission_pct, d.department_name, j.job_title,
l.city, l.state_province, c.country_name, r.region_name
employees e, departments d, jobs j,locations l,
countries c, regions r
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id
AND c.region_id = r.region_id
AND j.job_id = e.job_id ;

Tuesday, June 9, 2009

MySQL Workbench - Export Features

MySQL Workbench has some nice export features. They include:
  • Forward engineer SQL Create Script
  • Forward Engineer SQL ALTER Script
  • Synchronize With SQL Create Script
  • Export as PNG
  • Export as SVG
  • Export as Single Page PDF
  • Export as Single Page PostScript File
Export options (just choose File Menu then Export option):

MySQL Workbench 5.2 - Some nice features

MySQL Workbench 5.2 Alpha

My understanding is the MySQL Workbench 5.2 has the core 5.1 modeling features as well as the new Query Browser. The Query Browser has been easy to use but the feature functionality is still in the alpha phase. Adding the Query Browser in the 5.2 release adds a nice dimension of being able to look at the data model, physical table and data very easily. So far I have found the tool very intuitive and have not had to spend any time figuring things out. So far I have not done any benchmarking on the product.

New Query Browser

Reverse Engineering Feature

I used the reverse engineering feature with the Sakila sample schema and it was point and click and the data model was built in just a second. Notice the ERD diagram in the top left, hierarchy of schemas and objects on right side and detailed table definition at bottom left.

MySQL Workbench - Visual Database Design Tool

What is MySQL Workbench?

MySQL Workbench is a visual database design tool available on Window, Linux and Mac OS. The MySQL Administrator and Query Browser tools have been deprecated. The database GUI interfaces will be rolled into the MySQL Workbench 5.2 release (currently alpha). MySQL Workbench is available in a Community (free) and Enterprise edition (paid license). Below are a few good links to look at:

Always looking at new tools

I've been looking for a better tool to work in a MySQL environment when I want to demo relationships between data models, physical tables, data and SQL code. It has to be a tool that the students can pick up easily without spending a lot of time understanding how it works. It's always an investment to learn another tool. So I've downloaded the MySQL Workbench 5.2 alpha release and really like what I've seen so far. I like it so much I'm going to start giving demonstrations for my work using the Workbench 5.2 release. I understand it's alpha but for my needs it was really easy to pick up and start using it right away. Anyone using Workbench for a production environment should be using 5.0 (GA) or 5.1 (Beta but soon to go GA).

Why I was looking for a new visual database design tool

I actually did not have it on my top three list of tools to look at initially. I just had some spare time this afternoon and downloaded the latest release of MySQL Workbench. I picked the alpha release because I wanted to look at the latest features. I just wanted to play with it but it was so easy to use that I starting playing with it seriously. I was doing a complex example this last week of looking at a customer issue from a number of perspectives to show problems that come up in database environments.

Really easy to build a data model, physical schema and use query browser

Before I knew it, I had built a data model, the physical schema, populated the key tables with data and did some query benchmarks. I realized in this personal exercise I had built an easy to follow demo that I wanted to use to show students some specific examples of database servers and troubleshooting problems. I also seen that I could have students new to MySQL Workbench be able to easily follow me by using the tool as well. I then started reading the whitepapers and documentation to understand the tool better. Before I knew it I was liking the product more and more. I realized that in about ten minutes I can easily convert a lot of users using SQL GUI tools in a MySQL environment easily to the MySQL Workbench 5.2 alpha release. But for how I am using it, its okay that it is alpha. Obviously before using any alpha release make sure you do your due diligence and understand if it is okay to use an alpha product for your situation. I'm just doing demos so I want a tool that meets my needs for easy to use demonstrations and a tool that is easy for students to use as well in following my demos.

MySQL Workbench Features

MySQL Workbench is a visual database design tool. Some of the key features available in this release:
  • Manage database schema objects: tables, views, indexes, stored procedures, functions and triggers.
  • Easy to use query browser tool.
  • Choice of diagramming notation.
  • Model validation.
  • Schema validation.
  • Forward and reverse engineering.
  • Change management.
  • Reporting functionality.
I've liked what I've seen in MySQL Workbench so I will be demoing with it and using the functionality in my future classes.