Find out the creation date of a MySQL table

Sometimes it’s useful to know when a certain database table was created. For those cases the following statement can be used:

SELECT create_time
FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'DB_NAME'

In case you are interested in the creation time of a particular table, just add the appropiate WHERE-clause:

SELECT create_time
FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'DB_NAME'
AND table_name = 'TABLE_NAME'

+---------------------+
| create_time         |
+---------------------+
| 2011-12-22 12:32:34 |
+---------------------+
1 row in set (0.00 sec)

Update: As pointed out below in the comments, it is not possible to know the creation date of a database without access to the directory structure of MySQL.

How to change password expiration in Oracle 11g

Another quick hint, this time for Oracle database administration.

Starting Oracle 11g the default profile that users belong to, if no other profile is specified, forces the password to be changed within 180 days. Previous versions didn’t have this “feature”, so this might result in a nice surprise when you find out that your application isn’t working anymore after 6 months.

Although security-wise it’s a good idea to change passwords now and then, sometimes it’s necessary for the password to remain the same. To change the expiration time to unlimited for the default profile, execute the following statement:

alter profile default limit password_life_time unlimited;

How to log Apache requests conditionally

Today a small Apache configuration trick.

I’m using a fancy Amazon ELB (http://aws.amazon.com/elasticloadbalancing/) to loadbalance (duh) two Apache nodes. In order to check whether the nodes are alive, the load balancer requests a static html page every few seconds. In my case this is elb-ping.html. Soon you will notice that this results in a your Apache acces.log being flooded with those ping requests.  Don’t worry, just tell Apache to log the requests conditionally.

To do so add the following lines to your virtualhost or apache configuration in general:

SetEnvIf Request_Uri “elb-ping.html” dontlog
CustomLog logs/access_log common env=!dontlog

Restart Apache and voila!

The importance of disabling auto deploy in JBoss

Recently I was investigating the memory usage of a JBoss application server (I have a post about this pending btw), and while I was preparing everything to launch a stress test, I was surprised to see how the Eden Space kept growing around 80MB each 5 seconds while JBoss was totally idle.

Garbage collection Eden Space

In the above image you can observe how the Eden Space fills up continuously when the autodeployer is activated. Changing the autodeployer’s check period to 60 seconds, we can observe that the Eden Space is filling at a very different rate. Disabling it completely results in almost no growth…

Conclusion: Disable the autodeployer on a production server!

To disable it (at least in JBoss 5), simply delete the following file:

JBOSS_HOME/server/<CONF>/deploy/hdscanner-jboss-beans.xml

Downloading a file from a redirecting URL with cURL

The other day I launched an Amazon EC2 instance of OpenSuse 11.3 (I know it’s old) and needed to download a file from SourceForge.

To my surprise, the EC2 instance didn’t carry “wget”, so I had to use cURL.

The thing is that cURL didn’t like the SourceForge download URL as it redirects to another URL.

To make cURL follow redirects execute:

curl -L -o filename URL

How to change the encoding in Ubuntu

Say we wish to change the locale to Spanish UTF-8, what do we need to do?

Step 1: List the available/installed locales in the system:

bash$> locale -a
C
en_US.utf8
POSIX

As you can observe, there is no Spanish locale installed, so we will first need to generate/compile it. The reason why not all locales are installed is to save space. Each locale approximately occupies 50MB,  and since most users only need a few locales, it would be a huge loss of space to install them all..

Step 2: Install the locale Since the locale we want to set is not installed on our system, we need to install it.

bash$> /usr/sbin/locale-gen es_ES.UTF-8

Generating locales… es_ES.UTF-8… done Generation complete.

Note: To know which locales you can generate, take a look at the file /usr/share/i18n/SUPPORTED. I.e:

es_ES.UTF-8 #UTF-8
es_ES # ISO-8859-1

After installing the locale, it should now be available on our system:

bash$> locale -a
C
en_US.utf8
es_ES.utf8
POSIX

Step 3: Update the locale

bash$> /usr/sbin/update-locale LANG=es_ES.UTF-8

This actually only changes the contents of /etc/default/locale. You might as well update that file  manually to change the encoding.

Step 4: Open a new terminal / log in again

bash$> locale
LANG=es_ES.UTF-8 # Default value used in case one of below is not set
LANGUAGE=
LC_CTYPE=”es_ES.UTF-8″ # Character encoding to be used
LC_NUMERIC=”es_ES.UTF-8″ # How to format numbers. I.e decimal separator as dot or comma.
LC_TIME=”es_ES.UTF-8″ # How to format date and time. For example 12 or 24 hour clock
LC_COLLATE=”es_ES.UTF-8″ # Defines how strings are alphabetically sorted.
LC_MONETARY=”es_ES.UTF-8″ # The currency (name and symbol) for the selected country
LC_MESSAGES=”es_ES.UTF-8″ # Language in which the messages (terminal) should be printed
LC_PAPER=”es_ES.UTF-8″ # Paper formats for the country
LC_NAME=”es_ES.UTF-8″ # How names or persons are formatted (firstname lastname)
LC_ADDRESS=”es_ES.UTF-8″ # Format of addresses
LC_TELEPHONE=”es_ES.UTF-8″ # Idem for telephones
LC_MEASUREMENT=”es_ES.UTF-8″ # Defines whether to use pounds, kilograms, etc
LC_IDENTIFICATION=”es_ES.UTF-8″ # Nobody knows!
LC_ALL= # Overrides all the previous!!