Virtual OS/2 International Consumer Education
VOICE Home Page:
March 2004

Newsletter Index
< Previous Page | Next Page >
Feature Index

In close collaboration: MySQL and OS/2
Part 2: Creating the database

By Wolfgang Draxler © March 2004, Translation: Menno Willemse

This is the second article in my series. This time, I'll describe how to create several users. Furthermore, we will create WPS objects that take functions out of your hands like for instance creating or deleting a database.

But before that, we will make one change to the database.

It is my opinion that programs and data should be stored on different drives. This makes it much simpler to create a backup. We will now reconfigure MySQL. To do this, we need to stop the server. There are several ways in which to do this:

  1. Simply kill the server. (Not the cleanest solution, but it works. It's better to use one of the other options.)

  2. Using the object Shutdown server

  3. or using the eCS window. To do this, type in the following command:

    [F:\os2_prog\datenbank\mysql\bin]mysqladmin -ppw -u root shutdown

    Note: Instead of the directory shown above, you need to be in your own installation directory. At the parameter -p you should replace pw with your own password.

Now, the database should no longer be in the window list.

By default, MySQL stores its data in the directory <installation directory\data>. (In my case: F:\OS2_Prog\Datenbank\MySQL\data) Copy this directory and its subdirectories to a different drive. The best way to do this is either using the WPS or using the xcopy command. I have decided on the directory G:\Work-OS2\MySQL\data, so this is the xcopy command to do it:

xcopy F:\OS2_Prog\Datenbank\MySQL\data\*.* G:\Work-OS2\MySQL\data\*.* /h/o/t/s/e/r/v

Now we need to tell MySQL about this directory on startup.

MySQL's configuration file is called my.cnf and lives in the directory <boot drive>:\MPTN\ETC (this is determined by the ETC environment variable). This is what's in that file:



Open the my.cnf file in an editor (such as e.exe) and change the value of the datadir attribute to the new directory. In my case, that would be:


Save the file and close the editor. The file should now look like this:



Now you can start the database.

Next, we will add some objects to the WPS.

Those would be:

To do this, we will create a user and create the objects mentioned above.

To create a user, you need to start the console.

Now, we need to switch to the database mysql. This is done using the command use.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Let's see what tables are in the mysql database:

mysql> show tables;
| Tables_in_mysql |
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
6 rows in set (0.00 sec)

user: This table contains all the users who are allowed to access the server.

db: This table describes database access.

host:  Description of the host-based access rights.

tables_priv:  User permissions at the single table level.

columns_priv: User permissions  at the column level.

func: This table stores user-defined functions. (We don't need this to set a user's privileges).

Now, to create the user WPS, we need the USER table. To get more information on this table, you use the command  desc <table>.

mysql> desc user;
| Field | Type | Null | Key | Default | Extra |
| Host | char(60) | | PRI | | |
| User | char(16) | | PRI | | |
| Password | char(16) | | | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Reload_priv | enum('N','Y') | | | N | |
| Shutdown_priv | enum('N','Y') | | | N | |
| Process_priv | enum('N','Y') | | | N | |
| File_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |

As you see, the desc command shows a list of all the fields in the USER table. 

To create the WPS user, a new record has to be entered into the table. To do this, we use the insert command:

mysql> insert into user
-> (host, user, password, Create_priv, Drop_priv)
-> values
-> ("localhost", "wps", password("wps"), "Y", "Y");
Query OK, 1 row affected (0.04 sec)

Now the record (i.e. the user) has been entered into the database. Because MySQL stores its permissions internally, we need to refresh the permissions.

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

To see if the record is really in the  database, we can start a query, using the select command.

mysql> select * from user;
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv |
| localhost | root | 28748dc05f058960 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| localhost | wps | 6867a41c29783e9f | N | N | N | N | Y | Y | N | N | N | N | N | N | N | N |
2 rows in set (0.00 sec)

As you see, we now have two users:

The user root, who has all the permissions, and the new user WPS, who only has permission to create or delete databases.

Normally, if you do not specify a field in an insert command, it will be filled with a NULL value (that is: no value; empty). In the USER table this is not the case because all fields are filled with N. The reason is that the field DEFAULT in the table description contains the value N.

Finally, we are going to create the corresponding WPS objects on the desktop.

To do this, we create two program objects with the following values:

  1. Object: Create Database


    Parameters: -uwps -pwps create [New Database:]

    Icon: F:\OS2_Prog\Datenbank\MySQL\bin\icons\mySQL-CreateDatabase.ico

  2. Objekt: Drop Database


    Parameters: -uwps -pwps drop [Drop Database:]

    Icon: F:\OS2_Prog\Datenbank\MySQL\bin\icons\mySQL-DropDatabase.ico

This concludes the second part of the article. In the next part, I am going to concentrate on SQL.


MySQL for OS/2 (Yuri Dario, German):
MySQL for OS/2 (Netlabs):

Wolfgang Draxler is living in Wien (Austria), together with his wife Yvonne. He works for a software development and consulting company in the field of organizational and database programming. At home, he busies himself with the further development of Sibyl for OS/2, a programming language that is similar to Delphi.

Feature Index
< Previous Page | Newsletter Index | Next Page >
VOICE Home Page: