SSH Tunnel to Remote MySQL (Port Forwarding)

ssh -f -L 13306:localhost:3306 root@example.com -p 10022 -N

We block access to production MySQL servers over port 3306 for security reasons.  Here is an example connection string that proxies localhost port 13306 to port 3306 on the target system.

We use a few switched to make this easier to use:

Use -f to force SSH to go into background just before the SSH session starts.  This way you can get still be prompted for passwords but then run this in background.  We like this because then we don't have to have an open terminal all the time.

The -L switch indicates port:host:remoteport format. In this case, listen to socket 13306 on localhost and proxy to remote host port 3306.

The -p switch is special.  You might have to connect to SSH on a remote host on a port other than the standard 22.  It is starting to become common practice to change the SSH port on remote hosts to something else for security reasons.  In this example, the remote host only responds to SSH on port 10022.

Using -N tells SSH to NOT execute remote commands.  Useful when all you want this SSH session to do is port forwarding.

MySQL FOUND_ROWS() and OpenBD

We recently switched to OpenBD on a production website and it uses MySQL FOUND_ROWS().  There a couple of things to ensure it works properly on OpenBD.

You need to add this setting to the "Connection Settings" in the datasource

useDynamicCharsetInfo=false

The queries need to be in a cftransaction block otherwise they will use a different MySQL connection.   It would look something like:

<cftransaction>
  <cfquery name="foo" datasource="foo">
    SELECT SQL_CALC_FOUND_ROWS * FROM foo 
    WHERE id > 2 LIMIT 2
  </cfquery>
  <cfquery name="bar" datasource="foo">
    SELECT FOUND_ROWS() AS numRows
  </cfquery>
</cftransaction>

Illegal use of nonvirtual function call in Java / MySQL JConnector

My computer crashed today and after restart none of my Java based application via Tomcat that used MySQL would work.  I would get an error like this:

Could not verify datasource: java.lang.VerifyError: (class: com/mysql/jdbc/DatabaseMetaData, method: supportsRefCursors signature: ()Z) Illegal use of nonvirtual function call

No matter the amount of Googling did anything turn up useful.  After reinstalling MySQL, I realized that my Aqua DataStudio could still connect and query the MySQL databases.  I tried a new MySQL J/Connector which did not help.

In the end, the fix was that Tomcat was using Java8 weekly (via a .deb) and something in Java8 must be broken.  I downgraded Tomcat to use Java7 and everything worked.  I found out that there was an update to my system to Java8 on 2/13 and the restart made it effective because Tomcat had been running for at least a week.

The fix: Check your Java version and downgrade from Java8

Help keep the Internet free by saving MySQL (Monty Says)

A big part of the Internet is built on LAMP (Linux, Apache, MySQL and PHP/Perl/Python). Now Oracle is trying to buy Sun, which owns MySQL.
It's not in the Internet users interest that one key piece of the net would be owned by an entity that has more to gain by severely limiting and in the long run even killing it as an open source product than by keeping it alive. If Oracle were allowed to acquire MySQL, we would be looking at less competition among databases, which will mean higher license and support prices. In the end it's always the consumers and the small businesses that have to pay the bills, in this case to
Oracle.

via monty-says.blogspot.com

If this is the only blog post you read in 2010, then I highly encourage you to read the entire self-interview style blog post by clicking the "via" click above. "Monty" is the guy that started MySQL over 27 years ago.

I personally believe that Oracle can have Sun by should not have MySQL. The one critical part of the LAMP stack is at risk. Without MySQL, we only have Postgres which is great but does not have the same "business" community around it yet. So I encourage you to sign the Save MySQL petition at http://www.helpmysql.org which will be forwarded to the European Commission which must approved the Oracle / Sun deal. Considering the amount of time and money spent by Oracle to get this deal approved the future of MySQL is definitely at risk. Oracle could have quickly gotten the deal done if they had divested MySQL AB immediately, but they willingly have lost an estimated $1 billion in order to get MySQL.

Even if you don't use MySQL, you'll end up giving more money to Microsoft for their DB or pay Oracle for theirs then I highly suggest you sign the petition. The future of free internet is at stake!

EOL of MySQL Query Browser, MySQL Administrator, MySQL Migration Toolkit (MySQL Workbench Team Blog)

With the beta releases of MySQL Workbench 5.2 well under way, we recently announced the EOL (http://www.mysql.com/support/eol-notice.html) of the MySQL GUI Tools Bundle.

via wb.mysql.com

Wow, this is news to me and it looks like they have a DEB for Ubuntu. It's about time MySQL pays attention to Linux on their GUI tools. Maybe this will replace my the old DBDesigner tool (which was "bought" my MySQL to make room for the Workbench about 3 years ago). I'll blog when I get a chance to try out the Workbench. Hopefully I can say goodbye to the older MySQL tools soon!