TheNitai

Thinking of a way to quickly search & replace data in a MySQL column got me to this solution:

update table
set 
column = replace(column,'thistext','thattext')

With this method I was quickly able to change millions of records today. Quick and easy.

If you happen to see the message “Failed to open the relay log…” in your MySQL error log file (sometimes it is good to look into it once in a while) then you either have your replication setup incorrectly or you forgot to remove the salve information in the master.

If the later is the case all you have to do is to login to the MySQL server and issue:

Stop Slave;
Reset Slave;

There is no need to restart MySQL. But if you want to see that it actually works now, then restart MySQL and look into the mysql.log file. You will now see that the error message is gone.

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.

Having just moved quite a large installation to a MySQL database I have encountered the following error when connecting to the MySQL server:

ERROR 2006 (HY000): MySQL server has gone away

After looking at countless solutions and none working (many say that the max_packet value has to be increased), I found a MySQL engineer stating that is has to do with the timeout values which could be 10 or 20 (by default).

Indeed, the problem was solved on our servers with a higher value for interactive_timeout and wait_timeout. You can set these values (and many others) in the my.cnf file.

Today I set out to get MySQL and PHP setup on my MacBook Pro. Since Apache2 already comes with MacOS X (mine is Snow Leopard and yours should be too!). Now, the funny thing is that I first searched on the web how to best install PHP and MySQL.

Surely, I came across MAMP (a package that gets you Apache2, PHP, MySQL and a couple libraries) in a nice one click application and some others. Being the guy who rather has things separated and controllable, I quickly shined away from those. Thought, I gave MAMP a try, but could not get MySQL to listen to anything else then the internal Apache2 server from the MAMP package (but guess that is another story and I’m really not doing this the first time.). Anyhow…

I then looked into getting Apache2, MySQL and PHP with MacPorts. Thought MacPorts has proven to be perfect in such circumstances, I had a hard time (and it took very long) to get this setup up and running. I’m sure, some of you have had successful installs and all works great, but at the end it did not work for me. There are even more instructions to get PHP running, with a lot of tweaking and such, but to be honest in the end…

Really the simplest and most straightforward method to get Apache2, MySQL and PHP running on MacOS X is;

MySQL

Now, this is really no brainer. All you need to do is to go to http://www.mysql.com and download the recent release. Within the download image you will find a nice installer and Preference pane which lets you start/stop MySQL. If you want to go all GUI, then also download the GUI tools from MySQL.

Apache2/PHP

The probably easiest of it all. Since MacOS X already comes with Apache2 and PHP all you need to do is to enable it. Thought, PHP is disabled in the httpd.conf, all there is to do is to edit httpd.conf and uncomment the mod for the php library.

That’s it!

Nothing to install, (almost) nothing to configure. Simple and easy.

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.