Tech Musings

Friday, December 16, 2005

MySQL command line essentials for OS X

I use phpMyAdmin for most of work with MySQL database tables. It's a fantastic GUI that helps me better conceptualize my databases's architectures which speeds my development.

However, I've found it's still important to understand how to work with MySQL in the OS X Terminal environment. For example, consider some of these useful gems the next time you find yourself in command-line mode:

You need to type /usr/local/mysql/bin in front of every mysql-related command in the Terminal. To avoid this, add /usr/local/mysql/bin into your shell's PATH environment: echo 'setenv PATH /usr/local/mysql/bin:$PATH' >> ~/.tcshrc (for the tchs shell). You can also add the line manually to your `$HOME/.tcshrc file. If no `.tcshrc' file exists, create one with pico or vi.

For the bash shell, the command is echo 'export PATH=/usr/local/mysql/bin:$PATH' >> ~/.bash_profile. This will create a file in your home directory named .bash_profile (to see it, you can issue ls -a in your home directory to see all hidden files). IMPORTANT: After issuing the command you'll need to quit the Terminal and re-launch to see it work. Remember, if you would like to change shells in Panther and above (OX 10.3+), type chsh -s /bin/tcsh (-s for superuser?) or set your default shell in OS X Terminal's preferences (Terminal >Preferences).

UPDATE 3/30/07
Forget about the Terminal > Preferences option for Jaguar 10.2.x. Use the Netinfo command line option:
niutil -createprop . /users/admin shell /bin/tcsh
Why?
"When you set the shell preference in the Terminal, you are only setting the shell that the Terminal program runs for you when it is invoked. Any other use of the shell by the system or through other means (such as, when you ssh into your box remotely) uses the shell specified in NetInfo. For instance, say you want to use the bash shell when you su to root. Setting the Terminal variable won't do that for you; when you run su, you'll find yourself as root but running the default shell of tcsh. Now, if you change the shell assigned to the root account in NetInfo, then when you su you get the shell you wanted. Same goes for other applications that can invoke shells like vi or whatnot."
- acet (Matt) macosxhints.com article

I've read that you can also add aliases to your shell's resource files:
alias mysql /usr/local/mysql/bin/mysql (/usr/bin/mysql on OS 10.2 Server!!!).
alias mysqladmin /usr/local/mysql/bin/mysqladmin.
For the bash shell use:
alias mysql=/usr/local/mysql/bin/mysql

Added 1/2/07!! Consider this article for configuring the OS X UNIX/shell environment.

Good instructions for MySQL setup under OS X including the OS X MySQL installation .pkg can be found on Marc Liyanage's www.entropy.ch site here! There are also instructions at entropy explaining how to re-initialize a database in case something gets screwed up (not that this has ever happened to me! :-)

To start MySQL under OS X, type cd /usr/local/mysql. Then type sudo chown -R mysql data/ (enter password) to start the server, issue sudo echo first, then type sudo ./bin/mysqld_safe &.

To stop your instance of mysql:
[norman:/usr/local/mysql]root# /usr/local/mysql/bin/mysqladmin -h norman.local -u root -p shutdown.

The very first thing to do (after a clean installation) is to secure your open master password: /usr/local/mysql/bin/mysqladmin -u root password new_password.
/usr/local/mysql/bin/mysqladmin -u root -h `hostname` password "newpasswd"
(the backtick character will replace `hostname` with the output of your system's hostname).

To log in to your box remotely, type ssh -l username my.hostname.com. To get to the mysql prompt (mysql>) type mysql (remember the setenv PATH stuff above, and don't forget that different paths to mysql exist between OS X and OS X Server! [heavy sigh]). To access the MySQL server, type mysql -u root -p. To access a specific database, type mysql databasename -u USER -pPASSWORD.

Other common commands:
create database databasename;
show databases;
use databasename;
show tables;
GRANT ALL ON databasename.* TO name@localhost IDENTIFIED BY "password"
Typing quit or exit will end your mysql session.

To import an sql dump, type the following from the shell (NOT the mysql prompt!):
mysql -u root -p password databasename < /Users/Desktop/name_of_file.sql

To dump a database, type:
mysqldump -u USER -pPASSWORD DATABASE >name_of_database.sql

I might add that a data *.sql dump using phpMyAdmin doesn't always work or behave the way you might expect when it's time to import it (perhaps on to another test or production server). First, make sure you are logged into the target MySQL server as root to ensure you have enough privileges to perform the import operation. Then, if you still experience problems, try opening the *.sql dump file in a text editor like BBEdit and then re-save it with the following options:
File Creator: (generic text file)
Save State: None
Line Breaks: Unix
Many times this will fix whatever was wrong with the file (an unrecognized character or line return?) and your import will work like it should (this applies to .csv files, too)!

Thursday, December 15, 2005

PHP queries on MySQL databases

Can you build a query on two MySQL databases?
I posted this question in the PHP Freaks Newbie Forum. Read about it here:
http://www.phpfreaks.com/forums/index.php?showtopic=77038&hl=

Apparently, you only connect to one MySQL "server" using mysql_connect. You then build your queries on multiple databases.

Can you use boolean fulltext searching on two tables at once?
Here's a query I found, don't know if it works yet.
SELECT *
FROM catalogue
JOIN manufacturer
ON catalogue.manufacturer_id = manufacturer.manufacturer_id
WHERE MATCH (model,engine,year,size,part,add_info) AGAINST ('$searchtext')
OR MATCH (manufacturer_make) AGAINST ('$searchtext')


I ran into a bit of trouble today as I was implementing the search feature for the teacher_exchange. I was getting repeating records returned, insufficient information returned, etc. It turns out I had to use DISTINCT in the query, and that I had to call each field individually rather than through the SELECT * like I usually do. This fulltext search queries two distinct tables in on database.

$query_search = "SELECT DISTINCT object_id, object_stamp, object_title, object_url, object_url_type, object_description, object_size, object_type, object_keywords, content.content_name, subject.subject_name, object_grade1, object_grade2, object_grade3, object_grade4 FROM objects left join content on objects.content_id = content.content_id left join subject on objects.subject_id = subject.subject_id left JOIN people on objects.people_number = people.people_number WHERE MATCH (object_title,object_description,object_keywords) AGAINST ('$colname_search' IN BOOLEAN MODE) OR MATCH (people.people_lastname) AGAINST ('$colname_search' IN BOOLEAN MODE)";

>>>>>>ADDED 5/24/06 >>>>>>>
I thought I'd list an example of how to perform a query on TWO 2 databases at one time on the same MySQL server (computer). This one turned out to be easier than I thought. Maybe that's why I couldn't find much information on how to do it! I'm still not sure how to do to do it if the two databases reside on separate or different servers (computers).

Of course, you will need to open connections to both databases first.
$db1_connection = mysql_pconnect('hostname', 'username', 'password');
$db2_connection = mysql_pconnect('hostname', 'username', 'password');

Then you can peform a query similar to this:
$query = mysql_query("SELECT * FROM db1.table, db2.table WHERE db1.table.field = 'something' AND db2.table.field = 'something'") or die(mysql_error());
$row_query = mysql_fetch_assoc($query);
$totalRows_query = mysql_num_rows($query);

One of the things that was causing me confusion was the mysql_select_db($db1_connection) statement I normally use before coding my query statements. PHP will set or use the current active database for this value, or the last link opened by mysql_pconnect. Thus, I do not make any references to either connection when coding these queries.

shell scripting, crontab and other fun stuff!

This a post that I knew I'd eventually get to, but I figured I'd wait to write about it until I had to undoubtedly re-read all the documentation I perused last year to perform another backup routine.

So, here goes. To create automated backups, it is imperative that you understand a little bit about shell scripting, the UNIX/LINUX crontab feature in OS X, backup tools or utilites (cp, ditto, rsync, psync), ftp using scp, MySQL dump procedures, tar and gzip, and some other little goodies thrown in for good measure.

I learned most of this by reading the incredibly helpful Learning the Terminal articles written by Chris Stone of the O'Reilly network.

crontab: The cron app runs continuously in the background executing commands in crontab files. Each user can have their own crontab file. The system crontab is located at /private/etc and belongs to root. You can "pico" the crontab file and change the times tasks are run. Click here for more information. To edit your user crontab, type crontab -e [crontab -l displays it and -r removes it]. Click here for another useful aid that explains how to schedule a cron task, although this was written for Sun Solaris 9 so the location of the crontab file is different.

sendmail: Sendmail needs to be configured properly in order to have reports e-mailed to you. Chris Stone's Part II article explains how to set up sendmail for this. There is another great, more detailed article written by James Duncan Davidson on how to configure sendmail located here. To mail a report, the command should read from the prompt ~/bin/backup.sh | mail -s "Daily Report" jim.

**Added 3/8/2006 ***
I came across a pretty good little article explaining how to create a crontab job to clear out the sendmail queue every so often here. The sendmail queue resides at /var/spool/clientmqueue/.

Copy Utilities:

cp preserves permissions by not resource forks
ditto preserves permissions including resource forks
rsync preserves permissions by not resource forks, plus syncs changes only
psync preserves permissions and resource forks (I think)

Example:
ditto -V rsrc ~/Documents/somefilename.pdf /Volumes/Macintosh\ HD2/somefilename.pdf

[the -V flag turns on verbose copying which prints a line for each file copied.]

shell scripts:
The conventional directory for storing user scripts is ~/bin [mkdir ~/bin if not there]
Scripts end in .sh like backup.sh
The first line of a shell script should be entered as #!/bin/sh
Be sure it is executable [chmod +x ~/bin/backup.sh]
To execute a shell script, just type backup.sh at the prompt. (example #1,
example #2, example #3)

You can set your editor with the command setenv EDITOR pico. To make this permanent, do this ........ (research).

To view running tasks with the process watcher, type top -u from the prompt.

scp (ftp):
NOTE! spaces in filenames are "No-No's" and will cause problems with this operation!

To copy a file from remote to local type:
scp user@remote:/remote/path/remotefile /local/path/remotefile

To copy from local to remote type:
scp -r /local/path/localdirectory user:password@remote:/remote/path/localdirectory

Wednesday, December 14, 2005

Pico vs. the vi editor

UPDATE! I have since discovered there is a difference between the vi editor and the Vim "Vi IMproved" editor. I was reproved on this point when I asked for help on the comp.unix.shell listserv. The braggadocio of UNIX folk has a certain charm to it, don't you think?


I admit I use pico for most text editing tasks in the terminal. UNIX jocks will laugh at me, but it's easier for me to remember how to use (as opposed to vi's esoteric syntax) pico because I don't engage in heavy terminal work on a daily basis. That said, I will use vi on occasion especially if I need to reference line numbers in a file, as I did on line 2642 in quiztest.cgi in order to add some white space between my generated quiz questions!! The other thing I like about vi is that it offers a level of protection from miscues and unwanted typos in your code due to an errant keystroke here and there. vi offers this safety because edits to the code can only occur after typing "I" and entering Insert mode.

Here is a decent vi article:
http://www.uic.edu/depts/accc/software/unixgeneral/vi101.html

You typically toggle between "Command" mode and "Insert" mode in vi. To insert text, press i (insert), then type your text. Use the backspace key to go back and then type over any mistakes.

Here are the vi commands I tend to use most frequently:

Esc exit insert mode; switch to command mode
:q quit vi
:q! quit vi without saving changes
u undo last change
. redo last command
:set number turn on line numbering
:w save current version of file
:w yyyy save current version of file, named yyyy
/aaaa search forward for pattern aaaa
?aaaa search backward for pattern aaaa
n repeat
k or up arrow move up one line
j or down arrow move down one line
i insert text before cursor position
x delete current character
dd delete current line
^f (Ctrl-f) move one page forward
^b (Ctrl-b) move one page back
^d (Ctrl-d) scroll down
^u (Ctrl-u) scroll up
^g (Ctrl-g) display current line number
:$ move to last line in file (or type capital G)
:# move to line number in file (e.g. :1 to move to top of file)

To find & replace all occurances of a particular text string, use the command:
:%s/text1/text2/g [RETURN ]
which replaces all occurances of text1 with text2. I use this one to clean up sitemap urls which are auto-generated using a Google python script. It makes it easy to replace those unwanted spaces (i.e. %20) which have a habit of finding their way into URLs referenced in Web access logs.

Useful UNIX/LINUX commands

Here are some useful commands I use frequently in the terminal:

Examples of how to use UNIX / Linux find command to locate files and directories here. Note, the find command is much slower than locate because it searches the file system in real time.

sudo tar -cvzf mytar.tar.gz mytar sourcefilesordir create tar file (check permissions if it fails)
tar -xvfz mytar.tar.gz destination extracts gzipped (compressed) tar file
tar -xvf mytar.tar destination extracts tar file
tar -yxf mytar.tar.bz2 need bzip2 installed (/usr/ports/archivers/bzip2)
gzip fileordir compress a file with gzip
gunzip file.gz decompresses a file with gzip

uname -v to determine which version (or flavor?) of unix / linux you are running (i.e. how to tell which underlying kernal type is installed on your OS X or OS 10 Server machine). You can also gather this information on the readout from your phpinfo() page.

less /etc/filename (displays text file one page at a time; scroll forward with "f", backward with "b", "h" to list commands, and "q" to exit).

cat /etc/filename (displays entire file; also use to create a new file cat>newfilename; close file using ^D).

head -5 /etc/filename (displays the first five lines of a file).

tail -5 etc/filename (display last five lines; use the -f option to cause tail not to stop when end of file is reached).

^C stops a current job while ^Z suspends the current job.
jobs list background processes
kill stops a running process
ps lists running processes and their status.
top -u also lists running processes (I think).

grep home /etc/filename.txt (displays all lines containing home in the file filename.txt).
grep -r -i home /etc/postfix (displays all files containing the word home in the directory /etc/postfix and ignore case). By default, GREP's pattern searches are case sensitive!
grep hello *.txt -in | more (reads all files ending in .txt in the current working directory looking for the word hello. It then prints out the file name and line number where the word hello was found and pipes the output into "more" in case there is A LOT of it! Thanks to Jason Lambert for this helpful example!).

shutdown -r now or sudo /sbin/reboot to restart OS X Server.

chown -R user /Path/To/Directory [changes ownership]
chgrp -R groupname * [changes if all files in current directory]
chmod -R 755 (a+x) /Path/To/Directory [changes permissions]
ls -a [shows hidden files]
ls -l [shows permissions of files]
ssh -l admin 10.5.67.987 [login to a remote computer as user admin]
su user changes user
rm -r -f directoryname dump entire directory of files regardless of whether or not the files are write protected (i.e. folders full of stubborn OS 9 locked files you can't empty from your GUI trash can!)
pwd print working directory; helpful when discerning a directory's full server path for use in things like upload scripts and the like.

UPDATE 8/3/07


To chmod or chown all files in the current directory use * as the wild card. If you need to recurse through subdirectories use chmod -R.

sudo chmod 755 -R *
sudo chown -R trevor /Users/trevor/Music/iTunes\ Music

Tuesday, December 13, 2005

Apache commands and configurations

It is helpful to know how to control the Apache Web Services via Terminal in OS X (or OS X Server). Use the graceful command if a configuration change was made and you only need to restart the httpd daemon. graceful will not disrupt current connections.

sudo apachectl start
sudo apachectl stop
sudo apachectl restart
sudo apachectl graceful

View running logs using the tail -f command:
tail -f /private/var/log/httpd/access_log (or error_log)

Wednesday, December 07, 2005

formatting date with PHP

<?php echo date ("n/j/Y G:i:s",strtotime($record['stamp']));  ?>


Used on teacher_exchange>details.php and eportfolio>portfolios.php

PHP date manual http://us3.php.net/date.

ADDED 4/18/06


On the VISIONS application, I wanted to return a message to the user that read something like this:

"...your file was submitted on Monday, January 13,2006 at 8:35 AM."

To accomplish this, I created 3 time variables ($time1, $time2, and $time3) using the following php date format parameters:

$time1 = date("l",strtotime($row_get_tid['stamp']));
$time2 = date("F j Y",strtotime($row_get_tid['stamp']));
$time3 = date("G:i A",strtotime($row_get_tid['stamp']));

and then inserted them into the body of my message appropriately.

"...your file was submitted on $time1, $time2 at $time3."

ADDED 7/17/07


To grab the current date using PHP for comparisons against a MySQL field formatted for DATE type 'YYYY-MM-DD':

<? $today = date("Y-m-d"); //get today's date ?>

I used this on the class_detail page when I updated it for cookies and cart action. Other potential MySQL date type properties might include:
DATETIME values in 'YYYY-MM-DD HH:MM:SS'

<? $today = date("Y-m-d H:i:s"); //get today's date ?>

Tuesday, December 06, 2005

symlinks (symbolic links)

I've found it's handy to know how to create symlinks via the Terminal in OS X. Here's an example:

ln -s ~/styles/stylesheet.css (source file) stylesheet.css (target or name of link)

I would issue the command above to create a symbolic link(s) in directories such as ~/New_ETR_site/styles/stylesheet.css and ~/UCCASS/styles/stylesheet.css to a style sheet I always remember to update located in /Users/me/styles/stylesheet.css.

In this example I would need to first "cd" to the corresponding directories where I wanted to place the symbolic links BEFORE issuing the command above! Or, if I wasn't in the directory where I wanted to place the symlink I could do the following...

Update 1-3-2007!!
Here is an example: To install the Oracle Instant Client and SDK package prior to creating a configuration build for php, I needed to create a symbolic link as (
or called, or the target) /usr/lib/instantclient10_1/libclntsh.dylib directing to (or the source) libclntsh.dylib.10.1. I "cd'd" to /usr/lib/instantclient10_1 and typed the following syntax:

ln -s
libclntsh.dylib.10.1 /usr/lib/instantclient10_1/libclntsh.dylib

Read This For Help

Servers use symlinks or pointers rather than aliases to allow system services (e.g. Apache) the ability to see and follow links to certain files. Obviously, system services do not have the ability to "double-click" on shortcuts or aliases in a GUI window like us!

Thursday, December 01, 2005

Alter MySQL table through PHP?

I'm wondering if it is possible to use an "alter table" SQL statement with PHP to dynamically insert a new field into a MySQL table with data directly inputted from a user who fills out a form?

Example:

ALTER TABLE `test` ADD `test_3` VARCHAR( 150 ) ;

I will need to research this to see if it is possible.