Fixing MySQL autostart in Mac OS X

I switched my work laptop – MacBook Air 3.2 – not without headaches… No Ubuntu at the time of writing. This will change.

But for now I needed a MySQL server running, Emacs not colliding with the Mac desktop and R all talking nicely to each other.

After downloading the MySQL-5.5.8-osx10.6-x86_64.dmg and installing MySQL-5.5.8 on MacOSX-10.6.6 (Snow Leopard) everything went fine first.

1. Install the database,
2. install the Startup script,
3. install the extension for the System Preference dialogue.

I did everything worked – until restart. OSX complained about a failing startup script (MySQL) and that was that. The menu in the system preferences did not help and trying to start the MySQL server from the shell did also not work (without throwing any error code).

The solution of debenriver (scroll down a bit) worked for me. In a nutshell:

  1. Fix the user rights of the startup script (that was what OSX complained about):
    sudo chown -R root:wheel /Library/StartupItems/MySQLCOM
    That should fix the problem, but it is not enough.
  2. Restart the computer
  3. Open a terminal and navigate to MySQL
    cd /usr/local/mysql
  4. Change to superuser mode for a while
    sudo echo
  5. Set a root password for your database
    sudo bin/mysqld_safe &
    hit enter after the & and continue
    bin/mysqladmin -u root password 'xxxxxxxx'
    to enter a root password for the MySQL database (replace xxxxxx with your chosen password. This should probably be different from your computer password. Don’t forget the single quotes).

UPDATE: No, 5.5.8 does not work. After a reboot (or something) MySQL refused again to startup. I followed the recommendations on the same thread I quoted, removed MySQL 5.5 and installed MySQL 5.1.54 which was also the current version in the Ubuntu repositories on my old workhorse. Works just fine.

Advertisement

MySQL Error 1366: Incorrect Integer Value for Column

Trying to load a colon separated file into a MySQL database table I ran into an error 1366.

1366 Error Incorrect Integer Value for Column 'MyIntegerColumn' at Row 1

The format of MyIntegerColumn was INT as you could imagine.

The point is that missing values in the .csv file where interpreted as an empty string (”) and MySQL 5.1 complains about putting a string record into an integer variable. I did not find any usefull solution via google: how to load missing values as NULL into an integer column.

The MySQL-Reference solved the problem:

Handling of NULL values varies according to the FIELDS and LINES options in use:

For the default FIELDS and LINES values, NULL is written as a field value of \N for output, and a field value of \N is read as NULL for input (assuming that the ESCAPED BY character is “\”).

Since the ESCAPED BY character is “\” by default, it is sufficient to open the text/csv file and substitute two consecutive fieldseparators (‘,,’ e.g.) with ‘,\N,’. Save the file and repeat your loading syntax. In my case it was:

LOAD DATA LOCAL INFILE '/path/to/YourLoadFile.csv' INTO TABLE YourTargetTable FIELDS TERMINATED BY ',' IGNORE 1 LINES;

Voila. Worked for me.

Modify MySQL Tables

After setting up my first small databases the next learning steps concern basic operation on existing tables in a MySQL database, such as deleting or creating additional variables/columns in a table. I found tech-recipes.com very helpful:

Delete a variable/column

To delete the column MyCol from the table MyTable, use the following SQL command:

ALTER TABLE MyTable DROP MyCol

Comment: Tech-Recipes.com used single-quotes around MyCol and MyTable which I found not necessary.

Add a column/variable

To add a column called email […] with a datatype of VARCHAR(60) to the table contacts, use the following SQL statement:

ALTER TABLE contacts ADD email VARCHAR(60);

This first statement will add the email column to the end of the table. To insert the new column after a specific column, such as name, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

If you want the new column to be first, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) FIRST;

Update/Change the values in a column

One would use the UPDATE statement so I looked it up in the reference

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

In order to update the column intensity in the table measures from “20%” to simply “20” one would
UPDATE measures SET intensity="20" WHERE intensity="20%";

Using ec2-api-tools

I am using ec2-api-tools with Ubuntu Lucid to connect and manage my Ubuntu Server on Amazon Web Services.

I followed closely the Ubuntu EC2 Starters Guide:

First one needs to install the ec2-api-tools

sudo aptitude install ec2-api-tools

The following requires that one has registered with AWS and has downloaded a keypair to the local computer. In order to use the ec2-api-tools from the shell one has to follow the EC2 Starter Guide to set up the private key in order to connect to ones AWS account.

Make sure you have the following environment variables set up in your shell profile. This is accomplished by adding the following lines to your ~/.bashrc if you use bash as your shell:

export EC2_PRIVATE_KEY=$HOME//pk-XXXXXXXXXXXXXXXXXXXXXXXXXXXX.pem
export EC2_CERT=$HOME//cert-XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.pem
export JAVA_HOME=/usr/lib/jvm/java-6-openjdk/

Having installed the ec2-api and set up the environment variables correctly one can look for one of the official Ubuntu Server Images published by Cannonical. The owner ID of Cannonical at AWS is 099720109477 so looking up only those Webservers reduces the flood of output:

ec2dim -o 099720109477

This was what I am interessted in:

  1. Ubuntu Lucid 10.04 webserver
  2. 32-bit architecture
  3. Elastic Block Store Image (EBS), which can be saved as a snapshot; I want to keep my configurations, when I terminate the server.

So to have a look at what is matching those criteria

ec2dim -o 099720109477 | grep 10.04-i386 | grep ebs | cut -f 2,3

At the time of writing the output was


ami-714ba518 099720109477/ebs/ubuntu-images/ubuntu-lucid-10.04-i386-server-20100427.1
ami-1234de7b 099720109477/ebs/ubuntu-images/ubuntu-lucid-10.04-i386-server-20100827
ami-6c06f305 099720109477/ebs/ubuntu-images/ubuntu-lucid-10.04-i386-server-20100923

The first record (e.g. ami-714ba518) is the image-ID of the instance which becomes important to start or stop the instance for oneself. The list gets constantly longer as Cannonical releases updates.

ami-6c06f305 was the latest release(2010/09/23) at the time of writing.

To check if the keys are ok
ec2dkey

Get keypair in case you have not done so before. Note: the name ‘ec2-keypair’ is arbitrary – choose what you like:
ec2addkey ec2-keypair

I downloaded the key to some folder on the local computer from the AWS site (open http://aws.amazon.com in your browser and sign in).

Here the ec2-api did not work for me: ‘ec2addkey ec2-keypair > ec2-keypair.pem’ as suggested in the EC2 Guide did not work! The problem seemed to be that the suggested code
ec2addkey ec2-keypair pasted the fingerprint on top of the key and the resulting file was then rejected.

Correct the permission for the keypair, so nobody else without superuser rights can access them (and so your EC2 account)
chmod 600 ec2-keypair.pem

Now open selected ports in your security setup for access with secure-shell, ftp, html and whatever you might want to set up:

For ssh (port 22)
ec2-authorize default -p 22

Open port 80 to access the apache2 server
ec2-authorize default -p 80

Open port 21 to access the ftp server
ec2-authorize default -p 21

Now we can start an instance. Remember the image-ID from above (ami-6c06f305)

ec2run ami-6c06f305 -k ec2-keypair

Note that it says just ‘ec2-keypair’ *without* ‘.pem’ extension. Important. The ec2run command without further options starts a ‘small’ instance.

Run ‘ec2din’ to get the external ip and the instance number. You need them for connecting via secure-shell and terminating the instance:

exip=`ec2din | grep INSTANCE | cut -f 4'`
inid=`ec2din | grep INSTANCE | cut -f 2'`

Of course you can as well just run ec2din and remember the external IP and the instance ID.

Connecting to the server
ssh -i ec2-keypair.pem ubuntu@$exip

Note, that you always connect as user ‘ubuntu’. If you did not initialize the ‘exip’ variable mentioned in the last step you would have to add the external IP manually, like
ssh -i ec2-keypair.pem ubuntu@75.101.240.200

On first time start one might want to add lamp-server and other desired services:
sudo tasksel install lamp-server
aptitude install vsftpd ddclient

If you registered with a dynamic DNS service like DynDNS and setup ddclient correctly you could also do
ssh -i ec2-keypair.pem ubuntu@yourname.dyndns.com
I will comment on this in another post.

Remember: in order to get apache2 up/down/restarted
sudo /etc/init.d/apache2 stop
sudo /etc/init.d/apache2 start
sudo /etc/init.d/apache2 restart

The server is terminated by
ec2kill $inid

Setup FTP on Amazon EC2

If your Amazon EC2 instance is finally running – which is another story – one would want to have ftp access to upload files and documents.

I got the inspiration to use vsftpd from curiousdeveloper.blogspot.com

  1. Open port 21 for ftp access on you running instances:
    ec2-authorize default -p 21
  2. Connect to your instance via ssh
    ip=`ec2din | grep I | cut -f17`
    ssh -i /path/to/yourkey.pem ubuntu@$ip
  3. Install vsftpd
    sudo aptitude install vsftpd
  4. Start the demon
    sudo /etc/init.d/vsftpd start

MS SQL calculating the number of days in a the month of a given date

I was looking for MS Access SQL to calculate the numbers of days in a certain month, specified by a date record. I did not find the solution but the neccessary hint in the right direction on Eggheadcafe (Hattip: John W. Vinson [MVP]).

SQL functions needed are:

  • DateDiff( "d" ; date1 ; date2)
    calculates the difference in days between data1 and date2, and
  • DateSerial( year ; month ; day )
    creates a datevariable representing the date given by a number for the year, the month, and the day.
  • Year( date )
    gets the number of the year out of a date variable. Year(#01.01.2010#) produces the number 2010. The functions Month() and Day() work the same way and are needed.

To get the number of days in the month given by the date “startTime” the following code worked for me with MS Access 2003:

DateDiff("d";DateSerial(Year([startTime]);Month([startTime]);1);DateSerial(Year([startTime]);Month([startTime])+1;1))

Declare character encoding in HTML

Writing documents in english, german and norwegian leaves you with a mess of different character encodings in documents and also webpages.

Just wrote a plain HTML page with norwegian special characters and Firefox, did not show the characters correctly, so I had to find out how to specify that the encoding of the norwegian æøå’s is UTF-8.

Fortunately there is a good explanation on the W3C Blog.

The code to specify the encoding is

http-equiv="Content-Type" content="text/html; charset=UTF-8"

I have no illusion about some browser (IE?) getting it wrong anyway. At least Firefox seems to comply…

Using gammu to connect to a mobile phone

After switching between mobile phones several times I always lost some data, contacts, media, so on…

There is a Linux tool called Gammu which allows to connect a selection of mobile phones. It seems that Gammus functionality is maximal for Nokia and Siemens, but I will give it a try on my Sony Ericsson…

The configuration is not trivial and I found some hints on JohnMcClumpha.org:

Install Gammu

Installing gammu is surprisingly easy (once again thanks to the wonders of apt-get), just use the following command:

sudo apt-get install gammu

Hard wasn’t it? 😉

OK now it’s time to plug your phone in and see if we can get things talking. With the phone connected, type the following command:

lsusb

you should now see your phone listed as a device – for example:

Bus 001 Device 002: ID 0421:0802 Nokia Mobile Phones

if not – make sure your cables and power are all good and try again.

The gammu installation comes with some example configuration files which are worth using as a starting point – if nothing else they help you to understand how gammu can be configured so that you can tailor a solution for your needs. These are located in
/usr/share/doc/gammu/examples (in gZip archives).

Copy the gammurc file to /etc/gammurc :

sudo cp /usr/share/doc/gammu/examples/config/gammurc /etc/gammurc

Now edit /etc/gammurc to specify your port and connection type (this will vary based upon where/how you have things plugged in and what sort of cable/interface your phone is using). The settings for mine are:

port = /dev/ttyACM0
connection = dku5

Save this config and from the shell type:

gammu --identify

you should now be presented with some information regaqrding your phone such as:

Manufacturer : Nokia
Model : 7200 (RH-23)
Firmware : 3.110 T (18-03-04)
Hardware : 0903
IMEI : 353363000813894
Original IMEI : 353363/00/081389/4
Manufactured : 04/2004
Product code : 0514143
UEM : 16

If this is the case then you have got gammu up and running and can send yourself a test message with the following command:

 echo "boo" | gammu --sendsms TEXT [recipient mobile number]