MySQL for Linux on POWER, Part 1: Introduction to creating a database

Learn about the availability of MySQL Database Server for Linux running on IBM POWER and PowerPC processor-based servers (collectively referred to as Linux on POWER). As a brief guide for application developers using MySQL on Linux on POWER, this paper is intended for MySQL developers and database administrators who are familiar with their system environment, networks, media devices, and disk resources.

In Part 2 of this article, read about developing applications for MySQL using PHP, C/C++, Java, Perl, and Python.

Share:

Nikolay Yevik, Technical Consultant, IBM 

Nikolay V. Yevik is a Technical Consultant in the IBM eServer Solutions Enablement organization, where he helps ISVs in enabling their applications for AIX and Linux on the IBM pSeries platform.



05 April 2005

Introduction

MySQL is one of the most popular Open Source databases available today. It is available under the dual commercial and Open Source licensing model. MySQL finds a wide variety of applications from embedded devices to cluster enterprise environments.

The POWER5™ processor is the latest 64-bit implementation of the PPC® AS architecture (version 2.02) from IBM. It is binary compatible with all PPC and PPC AS application level codes. The POWER5 chip has been designed for very high frequency operations of up to 2.0 GHz. The Power Architecture supports both 64-bit and 32-bit addressing. Early implementations used the 32-bit subset and supported only 32-bit applications. Since 1995, 64-bit processors have implemented the complete architecture, allowing full binary application compatibility with existing 32-bit applications and support for new 64-bit applications. The 64-bit POWER and PPC chips can be found in IBM® eServer™ iSeries™, pSeries®, OpenPower™, and BladeCenter™ JS20 servers, as well as G5 hardware from Apple.

The POWER5 architecture is an enhancement of the POWER4 architecture, but maintains binary and structural compatibility. The identical pipeline structure lets compiler optimizations targeted for POWER4 to work equally well on POWER5 processor-based systems. The key features of POWER5 include micro-partitioning and Simultaneous Multi-threading.

Linux has gained a significant presence in commercial and technical computing environments and has quickly gained acceptance among varied businesses. The IBM Linux Technology Center (LTC) is the premier organization for enterprise Linux development in the world today, and they have a major emphasis on developing, supporting, and enhancing all major Linux distributions on Power Architecture. Independent software vendors (ISVs) and customers can leverage this support, combined with traditional support from the Open Source community, to provide an unprecedented network for developer assistance.


MySQL and related components

MySQL has traditionally been included in all major Linux distributions. The mainstream Linux on POWER distributions, such as Red Hat Enterprise Linux and SUSE LINUX Enterprise Server, are no exception.

Though MySQL and MySQL product packaging and versions differ between Linux distributions, expect to see the following:

  • Database Server
  • Database Client
  • Interfaces and extensions for popular programming languages such as Java, C/C++, Python, Perl, PHP, and others
  • Libraries, modules, and plug-ins for various products and development APIs
  • Other tools related to MySQL

For a more detailed list and description of the products, please refer to the main MySQL hub, MySQL.com, listed under Resources. The MySQL.com Web site is the main source for MySQL information and product downloads, including downloads for Linux on POWER. The latest compiled versions of MySQL and related products can be downloaded from that site for Linux on POWER, as well the source code.

MySQL database comes in several trims. Some are available under dual commercial and Open Source licensing, and some are available only under a commercial license. For more information, please refer to the MySQL Database Server page. (See Resources.)

Operating systems and hardware

This article mainly refers to the two IBM supported Linux on POWER distributions for POWER hardware:

  • Red Hat Enterprise Linux
  • SUSE LINUX Enterprise Server

MySQL for Linux on POWER also runs on other Linux distributions, such as Yellow Dog Linux, Y-HPC, Debian, and many others that run on POWER and PPC hardware made by IBM, as well as Apple.

Installing MySQL on Linux on POWER

Installing MySQL on Linux on POWER is no different than installing it on a Linux OS for any other architecture.

If you use the MySQL packages that are included in your Linux on POWER distribution, installing MySQL is as easy as installing the needed rpm package from your distribution’s media. The MySQL.com Web site also offers downloads for Linux on POWER and PPC in the compressed formats commonly accepted in the Linux community.

The source code for MySQL itself and many tools are provided openly under the Open Source license and can be easily compiled on Linux on POWER using GNU Compiler Collection (GCC).

MySQL administration tools

There are several MySQL administration tools, which use intuitive graphical user interfaces that make administering a MySQL database easy.

MySQL Administrator

MySQL Administrator allows you to configure, backup/restore, and control your databases, as well as MySQL server environment. Figure 1 shows a screenshot of MySQL Administrator running on SLES 9 for Linux on POWER, showing a database structure we are to create in the next section.

Figure 1. MySQL Administrator window
MySQL Administrator window

MySQL Administrator can be compiled on Linux on POWER from the source code. For more information on MySQL Administrator, please see the MySQL Administrator page. (See Resources.)

MySQL Query Browser

MySQL Query Browser is an easy to use graphical tool used for creating, executing, and optimizing SQL queries in MySQL. Figure 2 shows a screenshot of the MySQL Query Browser window running on SLES 9 for Linux on POWER.

Figure 2. MySQL Query Browser
MySQL Query Browser

Similar to MySQL Administrator, MySQL Query Browser’s source code is available and can be compiled on Linux on POWER. For more information on MySQL Query Browser, please refer to the MySQL Query Browser page. (See Resources.)

phpMyAdmin

Another MySQL administration and control tool is phpMyAdmin. This tool is completely written in PHP and will work on any Linux distribution with the following components available:

  • PHP run time environment
  • Apache web server with PHP module
  • PHP-MySQL module

The Linux on POWER distributions ship these components by default. The configuration is easy and described in detail in phpMyAdmin’s accompanying documentation. Figure 3 shows a screenshot of phpMyAdmin running on SLES 9 for Linux on POWER.

Figure 3. phpMyAdmin
phpMyAdmin

For more information and to download phpMyAdmin, please see the phpMyAdmin Web site. (See Resources.)

Migrating MySQL

Migrating a MySQL database is very straightforward when you use the before mentioned administration tools because the MySQL database table structure is kept in files that can be backed up and copied to a different system. For more information, please refer to the MySQL documentation.


Creating MySQL database

This section of the paper examines a simple database design in order to introduce some of the key MySQL concepts and some of the basic tasks you can perform in MySQL

Database design

This example explores a simplified database that keeps track of the daily activities of an IT specialists contracting company. Figure 4 presents an Entity Relational Diagram (ERD) that gives you the macro view of the company’s simplified data requirements and operations.

Figure 4. IT Specialists contracting company database
IT Specialists contracting company database

The company contracts IT specialists to different clients’ projects and charges clients for the services provided based on established hourly rates. The JOB table keeps track of the hourly rates clients are charged for services with rates being dependent on the job type. The EMPLOYEE table keeps track of employee information, including the position an employee holds, which is kept as a foreign key to an entry in the JOB table. The JOB table has a 1:M optional relationship to the EMPLOYEE table. It is possible for a position to exist without being staffed. On the other hand, employees are required to hold exactly one major position.

The PROJECT table holds data about the projects in which this company is involved. A project must be managed by exactly one of the company’s employees. It is possible for an employee to manage more than one project. However, not every employee gets to manage a project.

Every employee is given an order form, on which they record, each day, how many hours they worked on a particular project for a client, at what hourly rate, as well as the total amount charged to a client.

An employee can be assigned to many projects at a time or none. A project can have many employees working on it, or none at all. This M:N relationship is implemented through the composite entity ORDERFORM.

All the tables in the database are normalized to a 3rd Normal Form (3NF), so we can start creating our database structure based on the above ERD.

Creating the database

First, you need to start the MySQL client and login into MySQL:

shell> mysql -h host -u user -p
Enter password: ********

After sucessfully logging in and getting the MySQL command line interface prompt, you can start creating your database as follows:

mysql> CREATE DATABASE CONTRACTING;
Query OK, 1 row affected (0.00 sec)

To verify that the CONTRACTING database was created, execute the following command and receive the following output:

mysql> SHOW DATABASES;
+-------------+
| Database    |
+-------------+
| CONTRACTING |
| mysql       |
| test        |
+-------------+
3 rows in set (0.00 sec)

Note that capitalization of the SQL code and MySQL internal commands is optional, but names of databases, tables, and columns are case sensitive. For example, a database named “TEST" is different than a database named “test".

You now need to change into the newly created database:

mysql> USE CONTRACTING
Database changed

At this time you are ready to start creating tables in the database. You can start with the JOB table, as follows:

mysql> CREATE TABLE JOB (
    -> JOB_CODE INTEGER NOT NULL UNIQUE,
    -> JOB_NAME LONG VARCHAR NOT NULL,
    -> JOB_HOUR_CHRG DECIMAL(10,2) NOT NULL,
    -> PRIMARY KEY (JOB_CODE)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

Specify JOB table’s type as InnoDB to enforce referential integrity. View the parameters of the created table, as follows:

mysql> describe JOB;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| JOB_CODE      | int(11)       |      | PRI | 0       |       |
| JOB_NAME      | mediumtext    |      |     |         |       |
| JOB_HOUR_CHRG | decimal(10,2) |      |     | 0.00    |       |
+---------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

The rest of the tables are created in a similar way. MySQL allows you to execute statements in the non-interactive, batch mode. You can put all of our database-creating SQL code into one text file and execute it from there. Below is an example of such a file:

-- Creating the database
CREATE DATABASE CONTRACTING;

-- Changing into the CONTRACTING database
USE CONTRACTING;

-- Creating table JOB
CREATE TABLE JOB (
        JOB_CODE INTEGER NOT NULL UNIQUE,
        JOB_NAME LONG VARCHAR NOT NULL,
        JOB_HOUR_CHRG DECIMAL(10,2) NOT NULL,
        PRIMARY KEY (JOB_CODE)) ENGINE=InnoDB;

-- Creating table EMPLOYEE
CREATE TABLE EMPLOYEE (
        EMP_SSN CHAR(9) NOT NULL UNIQUE,
        EMP_LNAME VARCHAR(50) NOT NULL,
        EMP_FNAME VARCHAR(50) NOT NULL,
        JOB_CODE INTEGER NOT NULL,
        PRIMARY KEY (EMP_SSN),
        FOREIGN KEY (JOB_CODE) REFERENCES JOB(JOB_CODE)
        ON UPDATE CASCADE
        ON DELETE RESTRICT) ENGINE=InnoDB;

-- Creating table PROJECT
CREATE TABLE PROJECT (
        PRJ_NUM INTEGER NOT NULL UNIQUE,
        PRJ_NAME VARCHAR(50) NOT NULL,
        EMP_SSN CHAR(9) NOT NULL,
        PRIMARY KEY (PRJ_NUM),
        FOREIGN KEY (EMP_SSN) REFERENCES EMPLOYEE(EMP_SSN)
        ON UPDATE CASCADE
        ON DELETE RESTRICT) ENGINE=InnoDB;

-- Creating table ORDERFORM
CREATE TABLE ORDERFORM (
        ORD_NUM INTEGER NOT NULL UNIQUE,
        ORD_DATE DATE NOT NULL,
        ORD_HOURS SMALLINT NOT NULL,
        ORD_CHRG_HOUR DECIMAL(10,2) NOT NULL,
        ORD_CHARGE DECIMAL(10,2) NOT NULL,
        PRJ_NUM INTEGER NOT NULL,
        EMP_SSN CHAR(9) NOT NULL,
        PRIMARY KEY (ORD_NUM),
        FOREIGN KEY (EMP_SSN) REFERENCES EMPLOYEE(EMP_SSN)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
        FOREIGN KEY (PRJ_NUM) REFERENCES PROJECT(PRJ_NUM)
        ON UPDATE CASCADE
        ON DELETE RESTRICT) ENGINE=InnoDB;

Note the order in which the tables are created. The referenced tables must be created before the referencing ones. Had you not specified the tables types as InnoDB, the order would not have been important. However, the referential integrity would not have been enforced.

For more on MySQL referential integrity, please see the section on foreign keys constraints in the MySQL product documentation.

To execute SQL statements from the above file, you can simply read from the file by passing it to mysql on the command line, like this:

mysql -u username < filename

For more on using MySQL in batch mode, please see the MySQL product documentation.

To view the table structure you created, enter the following command:

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_CONTRACTING |
+-----------------------+
| EMPLOYEE              |
| JOB                   |
| ORDERFORM             |
| PROJECT               |
+-----------------------+
4 rows in set (0.00 sec)

mysql> DESCRIBE JOB;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| JOB_CODE      | int(11)       |      | PRI | 0       |       |
| JOB_NAME      | mediumtext    |      |     |         |       |
| JOB_HOUR_CHRG | decimal(10,2) |      |     | 0.00    |       |
+---------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> DESCRIBE EMPLOYEE;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| EMP_SSN   | varchar(9)  |      | PRI |         |       |
| EMP_LNAME | varchar(50) |      |     |         |       |
| EMP_FNAME | varchar(50) |      |     |         |       |
| JOB_CODE  | int(11)     |      | MUL | 0       |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> DESCRIBE PROJECT;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| PRJ_NUM  | int(11)     |      | PRI | 0       |       |
| PRJ_NAME | varchar(50) |      |     |         |       |
| EMP_SSN  | varchar(9)  |      | MUL |         |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> DESCRIBE ORDERFORM;
+---------------+---------------+------+-----+------------+-------+
| Field         | Type          | Null | Key | Default    | Extra |
+---------------+---------------+------+-----+------------+-------+
| ORD_NUM       | int(11)       |      | PRI | 0          |       |
| ORD_DATE      | date          |      |     | 0000-00-00 |       |
| ORD_HOURS     | smallint(6)   |      |     | 0          |       |
| ORD_CHRG_HOUR | decimal(10,2) |      |     | 0.00       |       |
| ORD_CHARGE    | decimal(10,2) |      |     | 0.00       |       |
| PRJ_NUM       | int(11)       |      | MUL | 0          |       |
| EMP_SSN       | char(9)       |      | MUL |            |       |
+---------------+---------------+------+-----+------------+-------+
7 rows in set (0.00 sec)

Note that in the EMPLOYEE table, the attribute EMP_SSN type was converted from CHAR(9) to VARCHAR(9). If a table has a column of the VARCHAR type, all the CHAR types in the same table greater than CHAR(3) are converted to the VARCHAR type. This conversion improves performance when the CHAR and the VARCHAR types are present in one table.

Entering data and verifying referential integrity

At this point you are done creating tables in your database and can start populating the tables with data. You can do this using either INSERT statements, or loading data into a table from a tab delimited text file.

INSERT INTO JOB (JOB_CODE, JOB_NAME, JOB_HOUR_CHRG) 
VALUES (200, 'Application Designer', 38.50);

To view what entry has been made in the JOB table, enter the following:

mysql> SELECT * FROM JOB;
+----------+----------------------+---------------+
| JOB_CODE | JOB_NAME             | JOB_HOUR_CHRG |
+----------+----------------------+---------------+
|      200 | Application Designer |         38.50 |
+----------+----------------------+---------------+
1 row in set (0.00 sec)

To populate tables in your database from a text file, you need to create a tab delimited file. The following exemplary file can be used to load the JOB table:

200     Application Programmer  35.48
201     Database Administrator  38.50
202     Technical Support       27.00
203     Database Designer       49.99

To load data from this file, you need to execute a command similar to the following:

mysql> LOAD DATA LOCAL INFILE "job_data_load.txt" INTO TABLE JOB;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

Note that if the text file is not located in the same directory from which the mysql client was started, you need to specify the absolute path to the file. The column value separators can be changed from the defaults in the LOAD DATA statement. For more information on loading data from a text file, please refer to the MySQL product documentation.

To verify the result, print out all rows in the JOB table, like this:

mysql> SELECT * FROM JOB;
+----------+------------------------+---------------+
| JOB_CODE | JOB_NAME               | JOB_HOUR_CHRG |
+----------+------------------------+---------------+
|      200 | Application Programmer |         35.48 |
|      201 | Database Administrator |         38.50 |
|      202 | Technical Support      |         27.00 |
|      203 | Database Designer      |         49.99 |
+----------+------------------------+---------------+
4 rows in set (0.00 sec)

In the same manner, you can populate the rest of the tables, keeping in mind that since you are using the InnoDB tables type, referential integrity is enforced and the referenced tables, therefore, must be populated before the referencing ones.

Let’s say you populated the EMPLOYEE table with the following data:

mysql> SELECT * FROM EMPLOYEE;
+-----------+-----------+-----------+----------+
| EMP_SSN   | EMP_LNAME | EMP_FNAME | JOB_CODE |
+-----------+-----------+-----------+----------+
| 600000001 | Smith     | John      |      201 |
| 600000002 | Johnson   | James     |      200 |
| 600000003 | Jones     | Anne      |      203 |
+-----------+-----------+-----------+----------+
3 rows in set (0.00 sec)

The foreign key constraints you specified should not allow you, for example, to delete the entry in the JOB table with the JOB_CODE that equals 203 because there is an employee, Anne Jones, whose entry in the EMPLOYEE table has this job code.

mysql> DELETE FROM JOB WHERE JOB_CODE=203;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign 
key constraint fails

As you can see, the referential integrity rules do not allow you to delete an entry that is being referenced from a different table. However, if you make an update to the JOB_CODE in the JOB table, this update must propagate to the referencing table, as shown below:

mysql> UPDATE JOB SET JOB_CODE=207 WHERE JOB_CODE=203;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM JOB;
+----------+------------------------+---------------+
| JOB_CODE | JOB_NAME               | JOB_HOUR_CHRG |
+----------+------------------------+---------------+
|      200 | Application Programmer |         35.48 |
|      201 | Database Administrator |         38.50 |
|      202 | Technical Support      |         27.00 |
|      207 | Database Designer      |         49.99 |
+----------+------------------------+---------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE;
+-----------+-----------+-----------+----------+
| EMP_SSN   | EMP_LNAME | EMP_FNAME | JOB_CODE |
+-----------+-----------+-----------+----------+
| 600000001 | Smith     | John      |      201 |
| 600000002 | Johnson   | James     |      200 |
| 600000003 | Jones     | Anne      |      207 |
+-----------+-----------+-----------+----------+
3 rows in set (0.00 sec)

Developing for MySQL

Due to the popularity of MySQL, there is a large number of development communities writing applications for MySQL in most of the major programming languages. On Linux, including Linux on POWER, there are many APIs that allow you to write applications to communicate with MySQL in such programming languages as PHP, Java, C, C++, Perl, Python, and Ruby, to mention a few. Read more on developing for MySQL on Linux on Power in Part 2 of this series.


Summary

MySQL is widely accepted as one of the best performing Open Source enterprise-class Linux relational database servers available today. The IBM 64-bit POWER platforms meet all the requirements of today’s clients by delivering superior performance, reliability, and high availability. The combination of MySQL RDBMS, with its robust features and IBM POWER processor-based servers capable of Micro-partitioning, dynamically enabling and disabling SMT, provides one of the strongest Linux database servers platforms available in the market.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Linux on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Linux, Open source, Multicore acceleration
ArticleID=58480
ArticleTitle=MySQL for Linux on POWER, Part 1: Introduction to creating a database
publish-date=04052005