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:

• https://www.variphy.com/support/knowledge-base/mac-os-x-reset-mysql-root-password
• http://stackoverflow.com/questions/30692812/mysql-user-db-does-not-have-password-columns-installing-mysql-on-osx

# 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

Here’s a Quick Reference to get started

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; 	
}

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 -e ‘query_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

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

SHOW COLUMNS FROM my_table 

For SQL Server

SELECT * 
FROM information_schema.columns 
WHERE table_name = 'my_table'

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
FROM 
    (SELECT name "column1", AVG(score) "column2"  -- temporary alias 
     FROM scores 
     GROUP BY name ORDER BY AVG(score) DESC) t
LIMIT 3

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.