Creating and filling MySQL database tables

In a previous posting I summarized the steps required to start your own private instance of the MySQL daemon. Hibernate needs a few things more than just the bare DBMS instance, however. You will need a database, at least one table in it, and a user with permission to access that table. The user is not strictly needed. The user «root» has full permission to do anything. And this is precisely the reason why to create a dedicated user.

All the stuff following is not fit for a production environment, due to the lax security precautions.

Creating a database and and user

Creating a database is very simple. Connect to your daemon:

mysql -u root mysql

This will only work if the Unix socket or IP-address and port are specified in either a configuration file or by a environment variable. Then, from within the mysql client, create a database,

create database simple;

and a user:

create user 'hibernate'@'localhost';

My user «hibernate» may only connect from the local host. He has no password set, though. I am not going to change that, as it is more convenient during development.

Then grant him full permission on the database you created earlier:

grant all privileges on simple.* to 'hibernate'@'localhost';

The MySQL manual explains the whole topic on creating and confining users in greater detail.

Filling the database

Initializing a database is a cumbersome and error-prone task. It usually involves sending quite a few SQL commands to the DBMS instance. All the repeatedly needed stuff better goes into a plain text file for re-use. By convention one with the file extension «sql». I have one sql file that creates the tables. It is named «simple_db_create_tables.sql» and looks something like this:

drop table if exists songs;
drop table if exists albums;

create table albums (
  id integer primary key auto_increment,
  title varchar(20),
  publisher varchar(20)
);

create table songs (
  id integer primary key auto_increment,
  title varchar(40),
  artist varchar(20),
  performer varchar(20),
  album integer,

  foreign key (album) references albums (id)
);

Yes, it is about music, as my Android example. Another sql script loads sample data from a text file. It utilizes the «load data» statement for that purpose. My sample data is arranged as list of comma-separated values, one file per table to be filled, e.g. in this example one for albums and one for songs. The album data file contains this:

Contains sample values for the "simple" database table "albums".
Values separated by comma. First two lines ignored.
1, Distraction Pieces, Speech Development
2, A Canticle for Leibowitz, "Blackstone Audio, Inc."

The «load data» statement is quite flexible as to the structure of the input data. My CSV files are loaded by statements like this:

load data local infile 'simple_db_albums.csv'
      replace
      into table simple.albums
      fields terminated by ','
      ignore 2 lines;

Did you wonder why the data file above explicitly sets the «id» field for each row? Should not «auto_increment» accomplish that? Well, it would. Just leave the first field empty and it kicks in. But «id» is a foreign key used by the table «songs». That means each song must give the exact id of its album. Furthermore, subsequent loading of the CSV-file would happily insert row after row of the same two albums into the database, each having a different id.

All scripts can be executed with the «mysql» command-line tool, e.g.:

mysql -u hibernate simple < simple_db_create_tables.sql

The tutorial chapter of the MySQL documentation explains creating and filling tables much more thoroughly.

Eclipse SQL Explorer

All the things above—and much more—can be accomplished conveniently from within Eclipse with the help of the Eclipse SQL Explorer extension. Installing the JDBC-driver for MySQL (or any other DBMS) is—while not overly complicated–also not exactly trivial. Mark E. DeYoung tells you what needs to be done.

SQL Explorer sucks however when it comes to executing «load data» statements, though not entirely to its own fault. The file-name given to that statement can be either an absolute or a relative path. In the former case the behavior of «load data» is pretty obvious. In the latter case however, the path is interpreted relative to the directory where the executing command—the mysql client or in this case Eclipse—has been started. With the mysql client you would simply change directory to where the data file resides, but as for Eclipse this is not within you power. Most likely Eclipse had been started with your home directory as current working directory, and this is where «load data» would resolve the relative path from. Setting the path of «load data» relative to your home directory will most likely not be viable because anyone else sharing the sql script would have to have the data file at the exact same relative path within their home directories too. For the same  reason absolute paths fail altogether.
In consequence, it means that for loading data the «mysql» command is still the best.

With tables created and filled, one can proceed to finally accessing them from a Java program. But not in this posting.

Updates

08/14/2012, 21:00 UTC Changed «grant all privileges on simple…» to «grant all privileges on simple.*…» Otherwise MySQL would interpret «simple» as a table name in the currently selected database, which would most likely be the «mysql» database.
Also, my MySQL user is no longer called «eclipse» because it is mostly not Eclipse accessing my database. Chose «hibernate» instead.

Running a private mysqld instance and keeping the databases in your home directory

I needed a DBMS running for some experiments with Hibernate. I wanted to use the
packages delivered by Fedora but did not want to run it as a system service,
because this usually requires special user permission; in the worst case root
permission. Running the DBMS as a system service has further
disadvantages. Programmers are of the lazy folk and tend to run the service at
boot time. It saves a few keystroke. It also wastes computing resources, opens
potential security holes into your computer, and slows down the start-up time of
the system. As a further detriment this approach fails on systems where you
do not have administrator permissions. You know, the ones at work. So, it is best
to run it as local user from the start.

I chose MySQL for my DBMS for no other reason that I have used it before
somewhere in far past. Installing it on Fedora is simple:

su -c "yum install mysql-server"

The MySQL manual contains detailed installation instructions for all major
distributions.

Then copy the default MySQL configuration file at /etc/my.cnf into your home
directory as ~/.my.cnf and adapt it. Mine is looking like this:

[mysqld]
datadir=/home/jhunovis/tmp/mysqld
socket=run/mysqld.sock

[mysqld_safe]
log-error=log/error.log
pid-file=run/mysqld.pid

It overrides the settings of the global configuration file. The «datadir» is the
location where MySQL stores its run-time data, like the actual databases, but
also log-files and the Unix socket. Note that all the file options are relative
to «datadir». Unfortunately, the tilde is not accepted for referring to a home
directory.

Update 08/09/12, 21:30 UTC: I just realized, that the command «mysqlshow» will show the directories «log» and «run» as databases, which is awkward, if not disturbing. I then decided to place all the non-database files into a sibling directory of the datadir. End update.

Update 08/10/12, 13:15 UTC: When I was filling some tables with sample data I
saw that my database used the character set «latin1» instead of
«utf8». Furthermore it used * Swedish* for collating, which was just weird (I am
far from Sweden). Adding the line «character_set_server=utf8» to the
configuration file fixed this. You can check your character settings by issuing:

mysqladmin variables | grep -E 'character|collation'

End update.

You will have to create those directories prior starting the MySQL
daemon:

mkdir -p ~/tmp/mysqld/{run,log}

Starting mysqld at this point will fail because the system databases are still
missing. Create them by issuing on the command line:

mysql_install_db

Now start the service:

mysqld_safe

Then you can connect to your database:

mysql -u root -S ~/tmp/mysqld/run/mysqld.sock

You have to pass the socket to the MySQL client to which it should connect to.
Alternatively, you may add a section like the following to your ~/.my.conf that
saves you the trouble:

[mysql]
socket=/home/jhunovis/tmp/mysqld/run/mysqld.sock

You will have to repeat this for every MySQL command you
use. Better point the shell environment variable «MYSQL_UNIX_PORT» to that
socket file and be done with it once and for all:

export MYSQL_UNIX_PORT=~/tmp/mysqld/run/mysqld.sock

Your private MySQL service instance is now ready. You may now test it, adjust
permissions, and create a database for your own purposes.

Update 08/10/12, 13:15 UTC: Changed title from «Starting a private mysqld instance» to «Running a private mysqld instance and the keeping databases in your home directory».