I recently started in a new company where a database does not have any data dictionary at all. I'm now doing a brute force work where I am trying to create my own data dictionary but I was wondering what are your strategies in learning the data inside a database?

Last week I've participated in several Performance Conferences and Trainings in Kiev. Events mainly were dedicated to the latest improvements within MySQL performance, as well Solaris and Oracle tuning / performance analyzing and best practices. The week was very hot :-) but I've enjoyed every day discussing with creative and smart people! People hungry on knowledge and ready to stay 2 hours more overnight outpassing all allowed timing - it was a real pleasure to share all I know! :-))

So, I'd like to thank all participants! As well Sun@Kiev and i-Klass teams who organized and prepared all these events! I may only wish to see such events more and more often, and don't forget to invite me again! :-))

NOTE : similar conference about MySQL performance improvement will be organized soon in Paris - so if you're …

Welcome to the 164th edition of Log Buffer, the weekly review of database blogs.

SQL Server

We have a delicious assortment of technical posts from the SQL Server world this week.

Piotr Rodak writes, “While I always knew and imagined that ON DELETE CASCADE may be useful, I wondered, what scenarios would be suitable for ON UPDATE CASCADE. I still don’t have this answer, but I came across some interesting behavior which kept me occupied for quite a bit more time that I had intended to.”

On In Recovery…, Paul S. Randall pursued the answers to …

I spoke at EdUIConf 2009, a new conference in my hometown of Charlottesville, Virginia. My presentation was on web interface performance; it's basically a twist on front-end performance in general. I slanted the talk towards web developers, rather than assuming the audience has full control over their Apache configuration.

The conference was relatively short -- two days. The first day was full-day sessions. I skipped from Molly's session on emerging web standards (good; summary of what I witnessed: HTML5 cometh) to Dan's session on visual design, which was excellent. The second day I wasn't able to make it for the morning, and …

There is no centralized authentication for mysql. You have to set up your user accounts individually for each of your mysql servers. You can use replication, but that would mean, your master servers must have the same authentication privileges as your slaves have. What about security?

So, here is a little workaround that I did and I can't find the same anywhere.

How does it work?

This is an unofficial and unsupported solution. Let's have a master and one or more slave hosts. On your master, you have to create a database lets say slave_auth. Create the same tables in this database as you have in the 'mysql' database and needed for authentication (more details below), and set up a user who can read/write those tables.

When you are done, create a temporary database on your slaves lets say auth_temp with the exact structure of the tables on your master, but with federated engine connected to your …

This entry is a follow up to TOTD #95 and shows how to use the recent integrations of JSR 299 in GlassFish v3 to convert a JSF managed bean to a JSR 299 bean (aka Web Beans). The TOTD #95 describes a simple Java EE 6 web application that uses Java Server Faces 2.0 components for displaying the results of a database query conducted by EJB 3.1 and JPA 2.0 classes.

The EJB class, which …

This entry is a follow up to TOTD #95 and shows how to use the recent integrations of JSR 299 in GlassFish v3 to convert a JSF managed bean to a JSR 299 bean (aka Web Beans). The TOTD #95 describes a simple Java EE 6 web application that uses Java Server Faces 2.0 components for displaying the results of a database query conducted by EJB 3.1 and JPA 2.0 classes.

The EJB class, which …

Innodb monitors show information about innodb internal status - which could be used for performance tuning. Lets break down the output of show engine innodb status and get a look at what is happening and how it can be improved. Just fire the "Show engine innodb status" command and check the Output.mysql> show engine innodb status\G*************************** 1. row ***************************

Recently a colleague at Sun was asking me if MySQL can tell him to determine if we are currently in summer time or winter time. He was doing some data analysis of his house where he has installed solar panels.

I am not aware of what he wants to do exactly, but possibly he wants all the data in solar time. So UTC could help him because UTC does not change much over time.

Next thing which came to my mind is, that possibly the good place to do such math calculations is the application code and not the database.

But never the less I was interested in how to solve this IN the database.

By default your MySQL server relies on your servers time zone. [1]

So if your server is set-up correctly you should be capable to determine if you are in summer time or winter time by your current time, UTC time and the offset you have to UTC.

mysql> SELECT IF(ROUND(TIME_TO_SEC(SUBTIME(TIME(SYSDATE()), …

If you need to create a big database into MySQL Cluster with:

• A lot of tables indexes, columns, and tables
• A lot of records
  

there are a few things to think about:

• If a table has > ~90M records, you have to create the table with MAX_ROWS=<amount of records in table anticipating growth>:
  CREATE TABLE t1(...) ENGINE=ndbcluster MAX_ROWS=200000000;
  This way the data node will allocate more partitions for the table, since there is a limitation in how many records that can be stored in one partition (and the limit is around 90M records).
  
• Many tables / table objects --> Make sure you increase MaxNoOfTables (kernel limit is 20320 tables). This creates a table object pool of size MaxNoOfTables.
  
  • Each table you create will use one table object. …