MySQL QueryBrowser & mysql 5 install

December 2, 2009 § 1 Comment

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.

Advertisements

LOAD DATA INFILE must be enabled at mysql start

November 14, 2009 § 1 Comment

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

November 14, 2009 § Leave a comment

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

November 14, 2009 § Leave a comment

# 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

November 14, 2009 § Leave a comment

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

November 14, 2009 § Leave a comment

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.

 


									

Where Am I?

You are currently browsing the Admin category at Web Builders.