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.

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.
 

I know there are already many pages about ProFTPD and MySQL, but all info I needed was scattered over the Internet.
Therefore I collected all info I needed and put into one page: Setup ProFTPD and MySQL.
 

I have added a new page to the 'How To'-list where you can find information about how to read an image from disk, store it into a MySQL blob-field and display that image on a web page. Reading and storing is done with PHP, displaying with Lasso 9. [Read more...]

I wrote a small script that cleans up Lasso 8's internal SQLite database from dynamically created tables in MySQL. It also deletes the dynamic tables from MySQL when older then 2 days, so you do not need a separate script for that.

In my case, the script has been written to clean my dynamically created temp tables, which names all begin with 'REP'. But you can copy the script and of course modify it to your needs.

Happy coding!

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.
 

Last week I finally had the time to take care of a backup server and set up a database backup scheme. I chose to use the built-in MySQL replication tools since I do not have to replicate between different database brands. I described the steps it took to get everything up-and-running in 'Setup MySQL Replication'.

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.