MySQL Workbench 5.2.16 beta is now available for Mac after a long stale development period. After moving the windows version forward its good to see they are concentrating back development for other OS's. You can learn and find out more via their blog at http://wb.mysql.com/

If you ever have the need to change the starting number on a auto increment column (on a MySQL table) its pretty simple

There has been a couple of reasons recently that I needed to do a look up on a MySQL table using a case-sensitive Select statement. Its actually as easy as using "LIKE BINARY" instead of "LIKE" e.g.

Obviously if you are on a CFML engine don't forget
<cfqueryparam cfsqltype="cf_sql_varchar" value="SOmE PAssWOrD" />

Please update your bookmarks and feeds for my site.

I now have a Mango Blog at:

http://www.andyjarrett.com/blog

Feed URL: http://feeds.feedburner.com/andyjarrett

After setting up my server yesterday I had issue with getting remote access to MySQL it was like it wasn't even running. After some digging around and annoying a linux user (always make sure you know a *nix admin guy, and buy him a scotch or two to say thanks) I found out is was running and it just wasn't bound to the machine's IP address but instead to localhost. For security reasons remote access disabled, but sometimes you need it for one reason or another.

1. Login via SSH
2. Edit the my.cnf. On Ubuntu you can do this
   view plain copy to clipboard print about

     ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
   $ sudo nano /etc/my.cnf 1$ sudo nano /etc/my.cnf
   
3. Once file open look for bind-address=127.0.0.1 and change it for your host machine IP address. So if you host machine is 10.0.1.1 your configuration file would look like:
   view plain copy to clipboard print about

     ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
   # Instead of skip-networking the default is now to listen only on
   # localhost which is more compatible and is not less secure.
   bind-address = 10.0.1.1 1# Instead of skip-networking the default is now to listen only on
   2# localhost which is more compatible and is not less secure.
   3bind-address = 10.0.1.1
   
4. Save, close and restart your mysql service to take change in effect
   view plain copy to clipboard print about

     ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
   $ /etc/init.d/mysql restart 1$ /etc/init.d/mysql restart
   

Sequel pro, which is my prefered MySQL GUI have just released a 0.9.4. You can download it now at sequelpro.com/download.html.

Whats covered?

• Interface (loads of changes)
• Connection
• Field type improvements
• Table structure
• Import / Export
• Encodings
• And Others. Check them all out at http://www.sequelpro.com/release-notes.html

CocoaMySQL was an old project on sourceforge.net which as the title implies is a Cocoa GUI client for MySQL. Being Cocoa means it looks/feels like an OSX application and also as a native app made very quick to open and interrogate data.

It did have a couple of bugs so recently I was happy to find out that the CocoaMySQL had been abandoned (I knew that bit) and it's source code has been used to create a new project called Sequel Pro which has hosted its code on Google Code.

You can see the CocoaMySQL influence within the client but the look has been very much updated. 0.9.3 is the current release and they are towards a 1.0 release which should include an updated SQL framework, bug fixes etc. Check out www.sequelpro.com and code.google.com/p/sequel-pro/

Query of a Query functionality was introduced in ColdFusion 5 and it gives the developer the ability to re-query a returned recordset. While it does implement the core set of SQL SELECT commands the one bit of functionality I miss is MySQL's Limit function (I miss this in ANY relational database thats not MySQL as it makes paginiation a doddle).

Limit in MySQL works as such:

The above code will display the first 10 results from your your (table is 0 indexed)

Starting from the 5th record this will bring back rows 6, 7, 8, 9, and 10

The idea behind the my <cffunction> is to mimic Limit with a passed in query. Of course this function can be used on any returned recordset but a lot of DB's have there own way of handling this which you might want to investigate first.

[More]

The company behind Java is acquiring MySQL for $1 billion in cash and stock options. From Jonathan Schwartz,Chief Executive Officer and President Sun Microsystem

"Sun will be unveiling new global support offerings into the MySQL marketplace. We'll be investing in both the community, and the marketplace - to accelerate the industry's phase change away from proprietary technology to the new world of open web platforms."

Hopefully this will mean that some money will go into the flaky MySQL Query Browser + Administrator that support MySQL

Read more about it here and here

I needed to do a find and replace on a couple of fields in a table and was stuck on the best way to this. Initially I was going to download a database dump and via a text editor find/replace and then re-upload but then I found out that MySQL has a Replace() function.

All you need to do is

The "from_str" performs a case-sensitive match when searching! Obviously you can also expand this to add any other logic like a WHERE clause etc.

This probably works with other DB's ... I would of thought anyway

[More]