Some time ago I did a MySQL upgrade without thinking ... that was not funny. So I retraced what I did to get the new version up and running and wrote it down as a sequence of steps to follow, when I need to upgrade to the next higher version.

I published these steps on the page [Repair] MySQL Upgrade (Mac OS X) , so they might be of help to you too.

CSVviewer (formerly known as TABviewer) is a tool which shows the contents of text files, where data of each line is equally separated by either a semicolon, a comma or a tab-character.

You can download it here.

Ik heb mijn FileMaker Pro 5 boekhoudprogramma's op verzoek geconverteerd naar recentere versies. U kunt de toepassingen nu downloaden voor alle versies vanaf versie 5. Voor elke compatibele versie heb ik een download-knop gemaakt, zoals in onderstaande afbeelding wordt getoond:

Again I needed to transfer all data from one server to another. I knew I documented the transfer of MySQL databases somewhere (it's deep inside in the Replication-how-to) and decided to post them again here, so they're quicker to find.

One can transfer MySQL databases in various ways:

1. Using mysqldump and zip + ftp
2. Zip the database itself + ftp (you might need to repair the tables after unzipping)
3. Use Navicat's Data Transfer module (not always good for tables with millions of records or blob data)

Personally, I prefer to use option 2, like below, in Terminal or an SSH session:

$ cd /var/mysql/ (or /var/lib/mysql/)
$ sudo zip -r ~/[database].zip [database]

Do this for each database that you want to copy. Then send all zip's per FTP to the new server.
Start an SSH session with the remote server and enter the following commands:

$ cd /var/mysql (or /var/lib/mysql/)
$ sudo unzip ~/[database].zip
$ sudo chown -R _mysql:admin [database]

For the above chown, check first with ls -l if _mysql:admin are the right owners. Then do this for each unzipped database.

Next, start Navicat and now you should see your databases in the connection of the new server. If not, you probably forgot to either do a Refresh Connection or the chown-command.

If you can access the tables and view data, good! If not, right click the table and choose Maintain->Repair Table->Quick or ->Extended and then try again.
 

In search for a good list with ISO-country names and relevant data, I ended at GeoNames. This site offers really super data, like even the RegExp to test a postcode value! Really great! And even more data of countries worldwide, like largest cities, highest mountains, capitals, postal codes, country statistics and much more.

And all for free. However, I can imagine what a job this must be to keep all that data up-to-date, so I donated for the data I downloaded (donate button is at the top of the donate & sponsoring page).

See also my earlier post about calculating postcode-to-postcode distances.
 

Looking for a SPLIT-function in MySQL, I came across this one. I tried it and I must have have done something not right, because MySQL threw an error at the function. I am not a MySQL guru and since this is a one time Q&D conversion-action, I only took the SUBSTRING code and created a query with which one can split the contents of an old field into separate columns and directly insert the results into a new, normalized table.

My example is about a TEXT-column I want to get rid of and of which I want to transfer the contents to a separate table. This column contains email addresses separated by a comma. Thus, first, I had to find the maximum number of email addresses used in that column, so I found this query and added MAX() around it.

select max(length(emails) - length(replace(emails, ',', ''))) as occurrences
from old_table
where emails<>''

With that number, I created that number+1 of unions, so I would end up with all email addresses in one column. That select statement is then used in a left join to retrieve the corresponding user name and feed the results at the same time into a new table, which uses an ID and a USER-ID, instead of an email address:

insert into new_table
select idnr, user
from (
  select idnr,
  trim(substring(substring_index(emails, ',', 1), char_length(substring_index(emails, ',', 1 -1)) + 1)) as email
  from old_table
  where emails<>''

  union

  select idnr,
  trim(substring(substring_index(emails, ',', 2), char_length(substring_index(emails, ',', 2 -1)) + 2)) as email
  from old_table
  where emails<>''

  union

  select idnr,
  trim(substring(substring_index(emails, ',', 3), char_length(substring_index(emails, ',', 3 -1)) + 2)) as email
  from old_table
  where emails<>''

  union

  select idnr,
  trim(substring(substring_index(emails, ',', 4), char_length(substring_index(emails, ',', 4 -1)) + 2)) as email
  from old_table
  where emails<>''
) as x
join users u on (u.email1=x.email or u.email2=x.email)
where x.email<>''

Now that I have all used email address associated with the IDs of the original rows, I can now delete the old column and change all my LIKE-queries into LEFT JOINs. Much better, because email addresses change.
 

A new project and a server in the basement, drove us to research how we could replicate MySQL databases of two other servers onto the one in the basement. Quest: we need multiple instances of MySQL on the server, with each its own setup. Our findings and how to set this up on Mac OS X Client (your regular desktop Mac) or Mac OS X Server (your co-located Xserve, Mac Pro or Mac Mini Server) are described on this page.