Dariusz on Software Quality & Performance


The simplest MySQL setup, ever

Filed under: en,pl — Tags: , — dariusz.cieslak @

logo-mysqlFirst of all: you have to install the server (I assume you use Debian / Ubuntu):

apt-get install mysql-server

Then you have to create database, user and password (for sake of simplicity, not security, name, login and password are the same):

N=<DB_NAME>; echo "create database $N; grant usage on *.* to $N@localhost identified by '$N'; grant all privileges on $N.* to $N@localhost ;" | mysql -u root

Then you should configure your program:

  • db_name=<DB_NAME>
  • db_user=<DB_NAME>
  • db_password=<DB_NAME>
  • host=localhost

Beware (SECURITY): I assume you have MySQL external connection closed (only local) and you trust every user from your Linux installation, they will have access to your database easily in such setup.


Secure PostgreSQL setup on Debian/Ubuntu

Filed under: en — Tags: , — dariusz.cieslak @

postgresqlPostgreSQL is an OpenSource database server that is sometimes slower, but more powerful when compared to MySQL for example. For typical MySQL user complicated authentication system of PostgreSQL may be a bit confusing (they expect just login/password) but it has some advantages over password-based mechanisms: security. I'll show you how to setup ident-based authentication for your applications (authentication method that uses underlying operating system mechanisms).

Installation, assuming version 8.4 is available for your OS (may be different):

$ sudo apt-get install postgresql-8.4

Switch to postgres operating system account for database management, all the following commands are executed from postgresql account:

$ sudo su - postgres

First, create database as a namespace for your tables:

postgres:$ createdb mydatabase

Secondly, create application PostgreSQL user. We do not want to grant too much permissions to this user:

postgres:$ createuser myuser --no-superuser --no-createdb --no-createrole

Right now we have to grant access to newly created database access to PostgreSQL user (just access to single database):

postgres:$ psql mydatabase -c 'GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser'

Finally we have to allow connections from operating system account darek to PostgreSQL account myuser:

 mymap  darek   myuser
 local   all         all                               ident map=mymap

In order the changes to have effect you have to reload PSQL configuration files:

postgres:$ /etc/init.d/postgresql reload

What is happening here requires a bit of explanation:

  • A mapping is created from local Linux user (darek) to PostgreSQL account (myuser) called "mymap"
  • Local connections by socket allow to authenticate user darek using mapping "mymap"

As a result "darek" can login as myuser from his account, let's test this:

$ psql mydatabase myuser -c "CREATE TABLE mytable(x int)"

As you probably have noticed no passwords were set. Authentication is done by operating system. This is much safer than storing passwords in configuration files of application.


SQL Error: 17006, SQLState: null Invalid column name in Hibernate

Filed under: en — Tags: , , — dariusz.cieslak @

Recently I've got the following error during SQL named query execution:

SQL Error: 17006, SQLState: null Invalid column name

In order to track the problem down I enabled SQL logging and collected SQL query issued, then run it under SQL monitor and … it was working without error! I was surprised.

In order to debug what's going on I enabled detailed logging in Hibernate. What was happened then?

[INFO] could not read column value from result set: entityKey215_0_; Invalid column name

This message showed me that the problem was not related to generated SQL query but to column names expected by Hibernate. Generated recordset didn't has entityKey215_0_ column. I added:

entityKey AS {list.entityKey}

to named SQL query and error dissapeared.

This error is not-very-obvious kind of error because Oracle hides column / table names in error messages returned. Why? I don't get this cryptic error messages idea. In order to track an error under Oracle I had to enable verbose logging (error message standalone din't give anything useful). Much simpler database MySQL has better error reporting than "fat" Oracle.


Recreate Derby Database Under WebSphere

Filed under: en — Tags: , , , — dariusz.cieslak @

WebSphere uses SQL databases for internal managment of MQ queues (Derby database engine under the covers). Sometimes you need to reset their state. Here's the script that erase and recreate BPEDB database state (tested under WS 6.1.2):

rm -rf $WID_HOME/pf/wps/databases/BPEDB
echo "CONNECT 'jdbc:derby:$WID_HOME/pf/wps/databases/BPEDB;create=true' AS BPEDB;"|\
    $WID_HOME/runtimes/bi_v61/derby/bin/embedded/ij.sh /dev/stdin


Second Level Cache For SQL Queries Under Hibernate

Filed under: en — Tags: , , , , , — dariusz.cieslak @

Second level cache in Hibernate allows to greatly speed-up your application by minimizing number of SQL queries issued and serving some results from in-memory cache (with optional disk storage or distributed cache). You have option to plug in different cache libraries (or to Bring Your Own Cache – an approach popular among my colleagues from India ;-) ). There are caching limits you must be aware of when implementing 2nd level cache.


Older Posts »

Powered by WordPress