TheNitai

Oracle recently released the latest MySQL 5.5.x versions. Despite of having tones of fixes and supposedly running much faster and (finally) making InnoDB the default storage engine, it is always good to keep up to date with latest releases.

But, as in the past, MaxOS X users always have to battle with getting it run correctly on their platform of choice. With this release it is no difference. This time, we are hit by permission issues and wrong path settings that prohibits MySQL to start at all and also to make it automatically startup during boot time.

Fixing startup

After installing the preference pane, you should be able to simply click the button to start/stop the MySQL server. Point is that “it should”, but it wont. This is due to a wrong path setting in the startup file. In order to make it work you have to edit a file. Jump into your favorites tool (no, it is not iTunes), but the Terminal application and enter:

nano /usr/local/mysql/support-files/mysql.server

(Nano is a editor in your shell, you can also use “vi” or “emacs”, but I guess “nano” is easy to use)

Then hit “ctrl + W” (for search) and enter “basedir=”, then hit Enter”. This will search for the string entered. Once found, change it to:

basedir=/usr/local/mysql

Once changed, hit “ctrl + W” again and search for “mysqld_pid_file_path=$datadir/`hostname`.pid” and change it to:

mysqld_pid_file_path=$datadir/`/bin/hostname`.pid

After these two changes simply press “ctrl + X” and enter “y” and then enter. This will save the changes you just made and will close the editor.

Fixing Startupitem

During reboot you might have seen the error message:

“Insecure Startup Item disabled. /Library/StartupItems/MySQLCOM has not been started because it does not have the proper security settings.”

This is because the StartUpItem has been installed with the improper permission settings. This can simply be solved with the following commands you have to enter in the terminal:

chown -R root:wheel /Library/StartupItems/MySQLCOM

That’s it. After next reboot the MySQL server will startup as expected and you will also be able to start/stop the server from the preference panel.

Running an Oracle database is great, because it is stable and just runs, but sometimes you are hit with unexpected errors when you restart your machine. Thought, I know Oracle quite well, I’m always surprised at some things. Here are two errors (and solution) I was just confronted with:

ORA-27101: shared memory realm does not exist

To be honest, I don’t know why I was confronted with this error, since we haven’t changed anything to the machine or to the environment variables. Metalink suggest to check that the ORACLE_HOME and ORACLE_SID are correct. Funny thing is that this system runs for over a year without a change to the path, nevertheless I checked the ORACLE_HOME path and sure enough I had trailing slash at the end.

So, the solution was to see that ORACLE_HOME does NOT have a trailing slash.

echo $ORACLE_HOME
/opt/oracle/product/11/ <--- WRONG!

echo $ORACLE_HOME
/opt/oracle/product/11 <--- CORRECT!

Remember to log out of your current shell session in order to reapply the new settings.

ORA-28002: the password has expired

Now, this error caught be even worse, because it happened right within a production environment. Also, here I was unaware of this setting. In any case, Oracle seams to want you to reset your password after one year or so. In case, you want to disable this on a user without changing the users password you need to issue the following commands in sqlplus:

alter profile {user} limit password_verify_function null;

This will set the verification for this user to null. If you want to do this for every user in your system you would use this (this is applied to the DEFAULT profile):

alter profile DEFAULT limit password_verify_function null;

Once done you can then reset the password for the user with the same password or with another one with:

alter user {user} identified by {password};

Exit sqlplus and your changes should have been applied.

I have to admit, I love databases. To hold data in a central place and be able to hook up any application from wherever you are to it, is just convenient to say the least. But there are moments I wish that vendors would “follow” a standard. I guess, when it came down to paging, every vendor thought his way of doing it is the best. No wonder, paging is the one SQL syntax that differs the most. Thus I dedicate, this post to paging and outlining the difference, meaning the solution to achieve it with Oracle, MySQL and MS SQL.

Paging with Oracle

Oracle knows of the internal column “ROWNUM”. Thus with “rownum” we can page trough the records, but have to do this with subselects. Look at the example;

SELECT rn, columns
   FROM (
  SELECT ROWNUM AS rn, columns
    FROM (
      SELECT columns
      FROM table
      WHERE id = 1
      AND something = 2
      ORDER BY columns
      )
    WHERE ROWNUM <= 10
  )
WHERE rn > 0

In this example we select the first 10 records, if you want to select the next 10 records then you will need to increase the values for “ROWNUM” with 20 and for “rn” to 10.

Paging with MySQL

There is one thing I like MySQL, that is the “LIMIT” clause. Not only can you limit how many records to retrieve, but can also add a offset to the limit clause. Thus we can do very elegant paging with “LIMIT offset, number”. Again, we limit the query below to return us 10 records:

SELECT columns
FROM table
WHERE id = 1
AND something = 2
ORDER BY columns
LIMIT 0, 10

Now, this will return the first 10 records. So, what if we want to do a paging here? Actually, quite easy, all we have to do is to increase the offset by one. It is important to know, that the offset start with a “0″ (think of it as the FIRST page), thus if we want to retrieve the next 10 records we use the limit with “LIMIT 1, 10″. This will give us the next 10 records. Elegant, no?

Paging with MS SQL

Now paging with MS SQL took me some time to figure out, since some said that MS SQL know of “rownumber” as well, but honestly, I couldn’t get anything to work with it. So the most easy method to do paging in MS SQL is to use the TOP syntax combined with a NOT IN subselect. Makes sense, right? Here is the example;

SELECT TOP 10 columns
FROM table
WHERE xxxx
AND id NOT IN (
  SELECT TOP 0 id
  FROM table
  WHERE xxxx
  )

As in the Oracle example above, if you would like to retrieve the next 10 records you would have to increase the TOP values by 10 records, meaning the FIRST TOP would have a value of 20 and the inner select TOP a value of 10.

So here you have it. How paging works for Oracle, MySQL and MS SQL.

Oracle Database 11g brings lot of new enhancements, especially in the Memory management. Oracle 11g allows you to allocate one chunk of memory, which Oracle uses to dynamically manage both the SGA and PGA.

Automatic memory management is configured using two new initialization parameters:

• MEMORY_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit. The default value is “0″.
• MEMORY_MAX_TARGET: This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.

If you are using UNIX/Linux, before you consider using AMM you should check the current size of your shared memory file system. On Linux you do this by issuing the following command.

df -k /dev/shm
Filesystem           1K-blocks      Used Available Use% Mounted on
tmpfs                 3072000   1276532   1795468  42% /dev/shm

To adjust the shared memory file system size issue the following commands, specifying the required size of shared memory. First unmount the current mounted volume and set the new one (in this example we set it to 3GB).

umount tmpfs
mount -t tmpfs shmfs -o size=1200m /dev/shm

Now, the above commands will only last until the next reboot. If you want to make this permanent you need to add the adjustment to the fstab file. To do this just edit the file /etc/fstab and add the line:

none                    /dev/shm                tmpfs   size=3000m        0 0

This is by far the easiest way to manage memory in Oracle Database 11g.