Installing and Running MySQL on Mac

Writing by on Friday, 27 of May , 2016 at 7:35 am

Painful process, but you will need to see what works for you.

Say you’ve done the following:

  • Downloaded the DMG and installed MySQL on your Mac (and it provided you a password)
  • Started it from System Preferences -> MySQL
  • Now you tried different ways to connect to MySQL but are not able to login to localhost
  • So, then you tried brew and it didn’t work. Go ahead and remove mysql from brew
    • sudo brew remove mysql
    • brew cleanup

Now, here’s what worked for me, thanks to the following:


# Start MySQL in Safe Mode
$ sudo /usr/local/mysql/bin/mysqld_safe –skip-grant-tables

# Connect to MySQL as root (without password)
$ sudo /usr/local/mysql/bin/mysql -u root

# Update the password
mysql>use mysql;
mysql>show tables;
mysql> update user set authentication_string=password(‘new_password’) where user=’root’;

# Kill the mysql safe process and start it from System Preferences -> MySQL
alias mysql=’/usr/local/mysql/bin/mysql -uroot -ppassword’

That worked for me and I was able to kill the mysql safe-mode process and connect to MySQL through CLI as well as a GUI like Sequel Pro.

Optionally, to add python drivers that will fail further if the following is not done.

# add this to your $PATH
export PATH=$PATH:/usr/local/mysql/bin

# pip install in the right venv
pip install MySQL-python

Leave a comment

Category: Mac,SQL DB

MongoDB for SQL users

Writing by on Thursday, 18 of December , 2014 at 11:42 pm

Here’s a Quick Reference to get started

Description SQL Mongo DB  
Lingo 1 Table; Relation Collection  
Lingo 2 Row; Record; Tuple Document  
List Databases SHOW databases show databases  
List Tables SHOW tables show collections  
Switch to a DB USE db use db  
Show Table Data SELECT * FROM table db.collection.find.pretty()  
Filter Table Data WHERE colum = 'value' db.collection.find({'column':'value'})  
Select Columns SELECT col1, col2 db.collection.find({},{col1:1, col2:1})  
Delete Rows DELETE FROM table WHERE col='val' db.collection.remove({'col':'val'})  
Get Rid of a Table DROP table db.collection.drop()  

Leave a comment

Category: MongoDB,SQL DB

Raising a number to a power in Java

Writing by on Wednesday, 23 of April , 2014 at 4:10 pm

In Java, use the Math.pow(a, b) to raise the value of ‘a’ to the power of ‘b’.
In Microsoft Excel and some other tools the Caret (^) symbol is used for this operation.
Note that in Java, the caret (^) symbol implies XOR of the two numbers.

Here’s the difference between the two.

2 raised to 4 = 2*2*2*2 = 16 (2 to the power of 4)
2 XOR 4 = 010 XOR 100 = 110 = 6 (Binary representation of 2 = 010 ; and 4 = 100)

Here’s a simple Java code snippet to try out.

public static void main(String[] args) {
	System.out.println("Math.pow(2, 4) = " + Math.pow(2, 4)); // returns 16
	System.out.println("2 ^ 4 = " + (2 ^ 4) ); // returns 6 (010 XOR 100 = 110)

And here’s a SQL flavor.

mysql> select power(2, 4);
| power(2, 4) |
|          16 |
1 row in set (0.00 sec)

And a Divide & Conquer algorithm in Java.

public double power(double a, long b) {
	// Base Cases
	if (a == 0) return 0;
	if (b == 0) return 1;
	// Divide & Conquer
	// 2*2*2*2 = (2*2) * (2*2) = ((2)*(2)) * ((2)*(2))
	double p = power(a, b/2);
	// Handle Negative Exponents
	double x = (b >= 0) ? a : 1/a;

	// Handle Odd & Even b's 
	// 2*2*2*2*2 = 2 * (2*2) * (2*2) = a * p * p
	return (b%2 != 0) ? x*p*p : p*p; 	

Leave a comment

Category: Excel,Java,SQL DB

Execute MySQL queries from Command Line

Writing by on Monday, 10 of March , 2014 at 9:00 pm

If say, you want to capture the results of MySQL query into a file, you might want to run use the command line option (-e) to execute the SQL query.

You can follow the syntax:
mysql -uuser -ppassword database -equery_to_execute

# Show on stdout
$ mysql -uuser -ppassword database -e ‘show tables’

# Redirect to a file
$ mysql -uuser -ppassword database -e ‘show tables’ > tables.txt

Leave a comment

Category: SQL DB

List columns in a table

Writing by on Tuesday, 15 of January , 2013 at 10:27 pm

For, Oracle

-- This is Case Sensitive
SELECT column_name, data_type
FROM user_tab_cols
WHERE UPPER(table_name) = UPPER('MY_TABLE') 

For, MySQL, you can either do a desc my_table or


For SQL Server

FROM information_schema.columns 
WHERE table_name = 'my_table'

Leave a comment

Category: SQL DB

MySQL : JDBC call into ResultSetMetaData returns incorrect Column Name instead of the Alias in the SQL

Writing by on Saturday, 15 of December , 2012 at 5:11 pm

Yeah, this is a tricky one. Is ResultSetMetaData.getColumnName(column) not returning the aliased column?

The simplest way to fix it to use ResultSetMetaData.getColumnLabel(column) which is apparently the “JDBC compliant” way of getting it.

I found the answer on this thread: Bug #43684: JDBC Metadata ColumnName Name is incorrect if using field alias

So, let’s look at this example.

-- Extremely Simplified Top N style nested SQL with aliasing
-- say "Name" and "Score" are two columns in the table "Scores"
-- With this type of SQL, I want to show there's no way to get to them
SELECT t.column1 "Name Alias", t.column2 "Score Alias"  -- alias them back
    (SELECT name "column1", AVG(score) "column2"  -- temporary alias 
     FROM scores 
     GROUP BY name ORDER BY AVG(score) DESC) t

Now if you look at the following Java Code Snippet

ResultSet rs = stmt.executeQuery(sqlQueryShownAbove);
ResultSetMetaData rsmd = rs.getMetaData();
String firstColumnName = rsmd.getColumnName(1);   // returns "column1"
String firstColumnLabel = rsmd.getColumnLabel(1); // returns "Name Alias"

Well, it’s an API thing. The MySQL JDBC driver has ensured that you can get to the Original Column Name (getColumnName) as well as the Aliased Column Name (getColumnLabel) and has left it to you to select what you need. Just that – it’s not obvious. However in the above example, we’re not getting the Original Column Name at all, but that is because in this case a temporary table was created in the inner SQL which was aliasing the “real” original columns thereby making them the original columns of the temporary table.

In my case, the tricky part was that I was sending the ResultSet to a 3rd party library, which after decompiling seemed to use getColumnName(column). BOOM !!! hack hack hack away
Luckily I had a method in the 3rd party library to override the column labels (PHEW).

But if getColumnLabel not an option as it could have been in my case, you could also try to set ?useOldAliasMetadataBehavior=true in the JDBC connection URL as documented in the MySQL JDBC URL Format.

Here’s the source for com.mysql.jdbc.ResultSetMetaData. Look for the getColumnName(int column) and getColumnLabe(int column) implementations.

Personally, I would have expected or liked to see getOriginalColumnName to get the original column name, but that of course is another discussion.

Leave a comment

Category: Java,SQL DB

Shivdev Kalambi's Blog

Shivdev Kalambi is a Software Development Manager, previously a Principal Software Engineer at ArcSight/HP. With over 16 years' experience in software development, he's worked on several technologies and played different roles and contributed to all phases of projects. Non-tech activies include Ping-pong, Rock Climbing and Yoga at PG, Golf, Skiing, Swimming & a beer enthusiast.