MySQL QueryBrowser & mysql 5 install

Using mac as development server is not a very easy thing. Support on official mysql.com on mac osx latest version is not sufficient to troubleshoot unexpected problem areas.

Most Web 2.0 developers use macports for managing install & uninstall. Again most macports tutorial on mysql 5 is out-of-date. Leave me with no choice but to share my notes here.

If you continue to run into mysqld.sock socket connection issues, follow me and uninstall all mysql.

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/opt/local/var/run/mysql5/mysqld.sock’ (2)

$ sudo port -f uninstall mysql5

Install both mysql 5 and mysql 5 server (Must be done together with this latest version)

$ sudo port install mysql5 mysql5-server

Install mysql system database

$ sudo -u mysql mysql_install_db5

Start mysql server

$ sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql5.plist

$ sudo /opt/local/lib/mysql5/bin/mysqladmin -u root password ‘password’

Start mysql

$ mysql -u root -p

Enter password:

If you restart your computer and run into sock connection errors, create symbolic link again.

$ sudo ln -s /opt/local/var/run/mysql5/mysqld.sock /tmp/mysql.sock
$ sudo mkdir /var/mysql
$ sudo ln -s /opt/local/var/run/mysql5/mysqld.sock /var/mysql/mysql.sock

Your MySQL Query Browser may complaint the same sock connection errors even though your terminal is running mysql.

Server Hostname: localhost

Port: 3306

Username: root

Password: password (or blank)

Query Browser > Preferences > Connections > Advance Options

Connect using socket: /opt/local/var/run/mysql5/mysqld.sock

Once you enter the mysqld.sock path, Query Browser & Workbench will connect mysql. All existing databases & tables will appear.

To view your databases:

$ ls /opt/local/var/db/mysql5

Note: If you install mysql5 from tar ball, your installation & mysql paths are totally different. Macports assigned its own file directory on mysql installation.

Sending email without and with HTML formatting in Objective-C

This is the standard of sending email in Objective-C iPhone App.

Use a mailto url + stringByAddingPercentEscapesUsingEncoding + HTML.

Simple Email without any sentences or any HTML formatting:

NSURL *url = [[NSURL alloc] initWithString;@”mailto:myname@gmail.com?subject=Hello&body=Hi”];
[[UIApplication sharedApplication] openURL:url];

Email with HTML formatting:

NSString *emailBody = @”<table>
<tr><td style=’text-align:right’><b>FirstName</b>:</td>
<td>Michael</td></tr><tr>
<td style=’text-align:right’><b>LastName</b>:</td><td>Jackson</td></tr>
<tr><td style=’text-align:right’>
<b>Job<b/>:</td><td>Artist</td></tr></table>”;
NSString *encodedBody =
[emailBody stringByAddingPercentEscapesUsingEncoding:NSUTF8StringEncoding];
NSString *urlString =
[NSString stringWithFormat:@"mailto:myname@gmail.com?subject=Hello&body=%@", encodedBody];
NSURL *url = [[NSURL alloc] initWithString:urlString];
[[UIApplication sharedApplication] openURL:url];

iPhone simulator does not have a Mail.app. You can only email url in device. However, you can consider to use compiler statements to create specific simulator/device code. You can use UIAlertView to preview the email string. Consider using UITextView to preview the text and UIWebView to preview HTML in the simulator.

#if TARGETIPHONESIMULATOR
    //compiler specific code
#else
    // device specific code
#endif

URL must be url-encoded inside a NSString in Objective-C

When you consider putting url into a string that contains mailto:, you would have to url-encode the string in Objective-C in iphone app.

iPhone Mail app expects & supports HTML in the email body of mailto:. If you use text formats such as /t /r /n, it won’t support text format in the email body.

[NSString stringWithFormat:@"mailto"me@gmail.com?subject=hellow&body=urlencode];

How to url-encode the string
Reference: http://www.w3schools.com/TAGS/ref_urlencode.asp
1) replace space to %20
2) replace & with %26
3) replace < with %3C
4) replace > with %3E
5) replace ! with %21
6) replace ( with %28
7) replace ) with %29
8 ) replace ; with %3B
9) replace : with %3A
10) replace / with %2F
11) replace = with %3D
12) replace ” with %22
13) . remain the same single dot .
14) – remain the same as single dash -

To display “<” & “>” symbol inside HTML you must use &lt; and &gt;
&lt; => %26lt%3B
&gt; => %26gt%3B

Instruction:
1) url-encode the string
2) escape any entities

Examples
From HTML url:
&lt;http://www.google.com/&gt;
After url-encoding:
%26lt%3Bhttp%3A%2F%2Fgoogle.com%26gt%3B

From HTML url:
&lt;http://www.amazon.com/o/ASIN/0545010225/karelsofwa-20&gt;
After url-encoding:
%26lt%3Bhttp%3A%2F%2Fwww.amazon.com%2Fo%2FASIN%2F0545010225%2Fkarelsofwa-20%26gt%3B

Example of HTML:

Check this out!<br><br>Harry Potter and the Deathly Hallows (Book 7)
by J. K. Rowling.<br><br>&lt;http://www.amazon.com/o/ASIN/0545010225/
karelsofwa-20&gt;<br><br>… or from your iPhone, &lt;http://
TeleMoose.com/d_0545010225&gt;

becomes….:

Check%20this%20out%21%3Cbr%3E%3Cbr%3EHarry%20Potter%20and%20the%
20Deathly%20Hallows%20%28Book%207%29%20by%20J.%20K.%20Rowling.%3Cbr%3E
%3Cbr%3E%26lt%3Bhttp%3A%2F%2Fwww.amazon.com%2Fo%2FASIN%2F0545010225%
2Fkarelsofwa-20%26gt%3B%3Cbr%3E%3Cbr%3E…%20or%20from%20your%20iPhone
%2C%20%26lt%3Bhttp%3A%2F%2FTeleMoose.com%2Fd_0545010225%26gt%3B

Sending image in iPhone Mail App

Embedded image is the only way to include image within Mail App inside iPhone App.

No API is allowed to access Mail attachments
Mail URL does not support attachments

Other Email Image Options:
1) Create your build-in SMTP client
2) Send image to a 3rd party server and send the email from web server
3) Save image to Photo library where you can email your photo

Use <b> before and after <img> tag to avoid Gmail or Hotmail to strip your <img> image tag after sending email out from iphone

LOAD DATA INFILE must be enabled at mysql start

LOAD DATA INFILE works only if you enable –local-infile at the start of mysql. In mac osx use the terminal’s drag and drop capability to insert the full path of the import file.

Note: WordPress automatically remove the double dash in blog post. Be sure you enter 2 dashes in front of local and a single dash in front of infile in your command line.

Syntax:
mysql –local-infile -u <username> -p <DatabaseName>
mysql> load data local infile ‘<drag input file here>’ into table <TableName>;

Use LOCAL only if you are adding the import file into MySQL’s data folder. Be aware you would have to figure out where the MySQL database directory. Defaulted MySQL’s directory structure is very different from macport installed MySQL.
mysql> LOAD DATA LOCAL INFILE ‘<dir>/pet.txt’ INTO TABLE pet FIELDS terminated by ‘<delimiter>’;

Note: WordPress automatically remove the double dash in blog post. Be sure you enter 2 dashes in front of local and a single dash in front of infile in your command line.

Code Example:
mysql –local-infile -u root -p sample
Enter password:
mysql> use sample;
Database changed
mysql> LOAD DATA INFILE ‘/Users/<Username>/database/company.txt’ INTO TABLE company (name, title, company);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select * from company;

Enable LOAD DATA INFILE:
–local-infile must contain 2 dashes instead of 1 dash
If you don’t enable it at the start of mysql, you’ll receive this error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘/Users/<Username>/database/country INTO TABLE country’ at line 1

Give full permission on .txt file
sudo chmod -R 777 country
If you don’t give permission, you’ll receive this error

ERROR 13 (HY000): Can’t get stat of ‘/Users/MediNexus/database/country.txt’ (Errcode: 2)

Must include single quote ‘ after dragging the file into terminal and close it with single quote ‘
If you don’t include single quote before and after dragging the file, you’ll receive this error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘/Users/<Username>/database/country INTO TABLE country (country, country_code)’ at line 1

mysql> LOAD DATA INFILE ‘/Users/<Username>/database/country’ INTO TABLE country (country, country_code);
Query OK, 248 rows affected (0.00 sec)
Records: 248  Deleted: 0  Skipped: 0  Warnings: 0

LOAD DATA INFILE won’t work as it is

LOAD DATA LOCAL INFILE won’t work if you start mysql as it is. In order to enable local-infile=1 into my.cnf, you would have to consider re-installing that requires a lot of work.

The standard configuration for shared and dedicated hosting today with the Plesk server administration system sets “set-variable=local-infile=0″ in “/etc/my.cnf”. Thus, LOAD DATA LOCAL is disabled on such servers.

# Log into mysql using root
mysql -u root -p
Enter password: (skip password as blank)
# Select existing database named “sample”
mysql> USE sample;
mysql> LOAD DATA LOCAL INFILE ‘/Users/<Username>/database/company.txt’ INTO TABLE company (name, title, company);

If the statement fails, it is likely that your MySQL installation does not have local file capability enabled by default. See Section 5.3.4, “Security Issues with LOAD DATA LOCAL”, for information on how to change this.

http://dev.mysql.com/doc/refman/5.0/en/loading-tables.html

ERROR 1148 (42000): The used command is not allowed with this MySQL version

• If LOAD DATA LOCAL is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:

  ERROR 1148: The used command is not allowed with this MySQL version

http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html

Official MySQL.com is under documented in server setup on Mac OSX.

Insert data file into mysql5 on mac osx

# Start mysql
mysql -u root -p
# Show all databases within mysql
SHOW DATABASES;
# Create a database named “sample”
CREATE DATABASE sample;
# Select database sample
USE sample;
# Create a database table named “company”
CREATE TABLE company (
user_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
name VARCHAR(45) NOT NULL,
title VARCHAR (45) NOT NULL,
company VARCHAR (45) NOT NULL,
PRIMARY KEY (user_id)
);
# Show existing tables
SHOW TABLES;

mysql> show tables;
+—————-+
| Tables_in_sample |
+—————-+
| company        |
+—————-+
1 row in set (0.00 sec)

# Show table definitions
DESCRIBE company;
mysql> describe company;
+———+——————+——+—–+———+—————-+
| Field   | Type             | Null | Key | Default | Extra          |
+———+——————+——+—–+———+—————-+
| user_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(45)      | NO   |     | NULL    |                |
| title   | varchar(45)      | NO   |     | NULL    |                |
| company | varchar(45)      | NO   |     | NULL    |                |
+———+——————+——+—–+———+—————-+
4 rows in set (0.00 sec)

To import data using Default MySQL located in the path of /usr/local/mysql/bin:

sudo /usr/local/mysql/bin/mysqlimport -u root -p -c name,title,company –verbose –fields-terminated-by=”\t” –lines-terminated-by=”\n” sample ‘/Users/<Username>/database/company.txt’
# skip password as blank for mysql

To import data using macports installed MySQL located in the path of /opt/local/bin:

/opt/local/bin/mysqlimport5 -u root -p -c name,title,company –verbose –fields-terminated-by=”\t” –lines-terminated-by=”\n” sample ‘/Users/<Username>/database/company.txt’

Note: The data file must be a Tab Delimited .txt without any line ending characters from Mac OSX or Window OS. .txt must be located in a folder where permission is not restricted.

\t refers to Tab Delimited. \n refers to new line break. <Username> is home directory ~.

Resolve macport installed mysql from defaulted mysql from mac osx

Using mac osx MacPorts to install latest version of MySQL are challenging as the macports version is buggy on MySQL5.0 or later. You may have a defaulted installed MySQL on your mac osx on the path /usr/local/mysql/bin. Macports install MySQL5 to another path /opt/local/bin.

Determine mysql version:
mysql -v

Go to the path where the MySQL is installed to verify the mysql version.

If you never create a password for user root, then you skip password by leaving the password blank. You would have to use sudo in order to view all the database within mysql. Defaulted installed mysql is located at /usr/local/mysql/bin.

Run mysql:
sudo /usr/local/mysql/bin/mysql

Or Run mysql:
/usr/local/mysql/bin/mysql -u root -p

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.84 MySQL Community Server (GPL)

Quit mysql:
mysql> exit;

Run mysql from macports installed ports:
/opt/local/bin/mysql5 -u root -p

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/opt/local/var/run/mysql5/mysqld.sock’ (2)

To view the database from Defaulted MySQL:

cd /usr/local/mysql
sudo chown -R mysql data/
sudo echo
sudo ./bin/mysqld_safe &
/usr/local/mysql/bin/mysql test


If you do not want to have to type /usr/local/mysql/bin in front of every single mysql-related command, then you have to add the /usr/local/mysql/bin directory to your PATH environment variable in your shell’s login script.

echo 'export PATH=/usr/local/mysql/bin:$PATH' >> ~/.bashrc

Create a password for user root:
/usr/local/mysql/bin/mysqladmin -u root password new_password_here

To view the database from macports installed MySQL:
cd /opt/local/var/db/mysql5
ls -l

Macports version of mysqld.sock should be located here
/opt/local/var/run/mysql5

To resolve this mysqld.sock error:
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/opt/local/var/run/mysql5/mysqld.sock’ (2)

Create a symbolic link of mysql.sock from any possible source:
ls /tmp/mysql.sock
sudo ln -s /opt/local/var/run/mysql5/mysqld.sock /tmp/mysql.sock
ls -a /opt/local/var/run/mysql5/mysqld.sock
mysql -u root -p

Note: skip the password as blank if you haven’t created a password for user root

Mac OSX Macports profile

MacPorts is required to store environmental variables in the shell. Usually .bash_profile or .bash_login will replace .profile in home directory ~. You can consider remove .profile or add content from .profile.

Again I would point .bash_profile to .bashrc as a single source for better MacPorts management.

You can view all the environment variables in terminal:
$ env

MacPorts paths are appended at the front of PATH so the MacPorts libraries will take precedence over vendor-supplied libraries for ported software at runtime.

Inside ~/.bashrc:
export PATH=/opt/local/bin:/opt/local/sbin:$PATH

To add directory /data/script to the beginning of the $PATH environment variable

PATH=/data/script:$PATH

To add that directory to the end of the path:
PATH=$PATH:/data/script

Note: Start a new terminal every time you update any profile file.

 

Mac OSX MacPorts command line

Default MacPorts location is /opt/local/

Display all installed ports
port installed

Display all contents on specific installed port
port contents <package>

Runs a selfupdate operation to update the ports tree and MacPorts base with the latest release.
sudo port selfupdate

Display outdated installed ports against the MacPorts repository to see if updated Portfiles have been released since your ports were installed.
port outdated

Upgrades installed ports and their dependencies
sudo port upgrade <package>

Upgrade all outdated ports
sudo port upgrade outdated

Uninstall ports
port uninstall <package>

Uninstall ports with dependency of another port. To override this behavior, use the -f (force) switch
port -f uninstall <package>

Deletes all intermediate files that MacPorts creates while building a port. A port clean is also often necessary to remove corrupted tarballs after a failed fetch phase.
port clean -all <package>

Shows you the dependencies of a port; dependencies are explicitly declared in Portfiles
Port deps <package>

To uninstall MacPorts from the default location /opt/local/. Be sure you check your path before deleting or uninstalling MacPorts
cd /opt/local/
sudo rm -rf \

Reference:
http://guide.macports.org/