MySQL command line with OS X 10.6.

After installing MySQL 5.1.54 on Mac OS X 10.6.6 (Snow Leopard) I tried to access the server in the terminal and got:

-bash: mysql: command not found

Bash could not find the path, so I had to add it. I am not that familiar with the internals of the unix terminal anyway, so I found a highly informative thread on superuser.com.

Especially the post of Jim Logan solved the issue:

Take a look at the file /etc/paths, which is used by /usr/libexec/path_helper, which is used by /etc/profile. For MacPorts, use “sudo” to append “/opt/local/bin” to the end of the /etc/paths file and reopen the terminal window.

So I opened the terminal and used nano:
sudo nano /etc/paths

and appended the line
/usr/local/mysql/bin

to the file, saved, exited, restarted the terminal …. and voila, solved.

Advertisements

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.

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%";

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))

Convert Character to Numeric in MS Access

Several times I had to correct database tables with a character primary key (which is ok, if not all other tables had numeric …). In order to get the necessary numeric format I inserted another variable with the correct format and transformed the character variable to numeric using the CDbl() Function.

The syntax for the CDbl function is:
CDbl([MyString])
I did forget it again and could not find any documentation. Googling the topic gave a lot of fruitless hits, so here it is.