Andy Jarrett : MySQL

MySQL Workbench now available for Mac

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/

Comments (0) | Print | Send | 1252 Views

Change auto increments starting number

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

view plain print about

1ALTER TABLE tablename AUTO_INCREMENT = 12345

Comments (0) | Print | Send | 1275 Views

Case sensitive selects in MySQL

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.

view plain print about

1SELECT username, password
2FROM tablename
3WHERE password LIKE BINARY 'SOmE PAssWOrD'

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

Comments (1) | Print | Send | 1055 Views

My blog has moved

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

Comments (0) | Print | Send | 1261 Views

Getting remote access to MySQL

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.

Login via SSH
Edit the my.cnf. On Ubuntu you can do this

view plain copy to clipboard print about
$ sudo nano /etc/my.cnf 1$ sudo nano /etc/my.cnf
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
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
Save, close and restart your mysql service to take change in effect

view plain copy to clipboard print about
$ /etc/init.d/mysql restart 1$ /etc/init.d/mysql restart

Comments (4) | Print | Send | 1646 Views

Sequel Pro update

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

Comments (1) | Print | Send | 1260 Views

CocoaMySQL is reborn as Sequel Pro

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/

Comments (7) | Print | Send | 1416 Views

MySQL limit cffunction for Query and QoQ's

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:

view plain print about

1SELECT * FROM myTable LIMIT 0, 10

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

view plain print about

1SELECT * FROM myTable LIMIT 5, 5

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

[More]

Comments (4) | Print | Send | 2825 Views

MySQL now a part of Sun

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

Find and Replace in MySQL

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.

[More]

Comments (6) | Print | Send | 5314 Views

MySQL GUI tools update and Aqua Data studio

Anyone who has read (and paid attention) to my blog knows that I use MySQL. Personally I love the free database, so far I haven't met any project it couldn't handle. Where I think it has been let down is by its GUI tools. Though they have been there by either MySQL themselves or a 3rd party they always seem flaky, or never quite logical. This is especially true for MySQL recent re-incarnations of their GUI tools package.

[More]

Comments (2) | Print | Send | 3589 Views

MySQL, JDBC Driver and zero dates

I am in the middle of moving a lot of my datasources over to the updated Connector/J (JDBC) One error I have seen a lot in my development enviorment is

view plain print about

1Error Executing Database Query.<br/>
2Cannot convert value '0000-00-00 00:00:00' from column xxxx to TIMESTAMP.

This is caused by Java as it cannot handle zero dates ('0000-00-00 00:00:00'), it trys to do anything it can with them but convert them to the original date :o). To get round this you can set a parameter in the JDBC URL called zeroDateTimeBehavior to convertToNull. So in your Data Sources page you should have something similar to:

view plain print about

1jdbc:mysql://localhost:3306/mySqlDatabase?zeroDateTimeBehavior=convertToNull

Comments (9) | Print | Send | 7308 Views

MySQL GUI tool at r3

It looks like they have updated the MySQL GUI tools to 5.0-r3 for all platforms.

On the Mac this is definately a lot for stable, considering the Query Browser was crashing when you changed schema.

This might be me though but I think the Mac version is missing the shortcut keys. Well its that or I cannot find them? Anyone got any ideas?

Comments (0) | Print | Send | 2231 Views

MySQL updated GUI tools

This is a little of a double post. First off MySQL have finally gotten around to updating there Gui tools, and secondly they have released a Universal binaries for the Mac.

[More]

Comments (0) | Print | Send | 3870 Views

MySQL JDBC driver for Mac(or PC)

If you are installing MySQL 5 on your Mac(or PC) then you are gonna need the JDBC driver.

Adobe have got a Technote for setting this up, its actually quite easy to install.

You can download the driver at http://dev.mysql.com/downloads/

Comments (0) | Print | Send | 5895 Views

My mac and some important links

So i've spent this evening trying to get my Mac up to spec in regards to software. What has this envolved? Loads!!! If like me you are a newbie then everything seems just odd, like a twilight zone episode or something. So here are some links that have helped me in my first 24 hours

There's probably more, if you know any drop me a line and i'll update the list. Like I've mentioned, this is all new to me so even if you think of something obvious, just mention it.

Just wanna give a shout to Mark Drew for putting up with my stupid questions todays.

Comments (0) | Print | Send | 10028 Views

Model Glue and Reator at UKCFUG - getAll()

I traveled into London again last night to see the Model Glue and Reactor, a winning combination presentation with Mark Drew as the speaker, compere, organiser and the general setting up guy.

The presentation lasted for 2 hours and covered everything from setting up your development environment, to creating the beginnings of a blog app using both frameworks.

[More]

Comments (0) | Print | Send | 4500 Views

Multiple record counts in one MySql statement

I've been creating some reports recently and been needing a lot of count information. The below statement pulls multiple counts in one SQL statement.

n.b. This is for MySQL and will probably be different for other db's

view plain print about

1SELECT
2(SELECT count(*) FROM tblName) AS 'Total Records',
3(SELECT count(*) FROM tblName WHERE col = 'a') AS 'Count'

Comments (3) | Print | Send | 6958 Views

MySQL Tools update

For anyone out there using either MySQL Administrator or MySQL query Browser, they have both been recently updated.

MySQL Administrator - 1.1.7. Personally i can't see any changes here, though i've probably missed it.
MySQL query Browser - 1.1.19. They've finally removed the annoying highlight bug.

I can never see a change log for either of these products, does anyone know where/if they host it??

Comments (0) | Print | Send | 1486 Views

Blog CFC 4 MySQL update script

Im gonna be updating my blog in the next couple of days, including the look hopefully and with it came a new MySQL update script. I've only run basic tests on my local copy but i think i have covered everything. If i have missed something (most likely) drop me a line in the comments.

#
# Table structure for table tblblogtrackbacks
#

DROP TABLE IF EXISTS `tblblogtrackbacks`;
CREATE TABLE `tblblogtrackbacks` (
`Id` varchar(35) character set utf8 NOT NULL default '',
`title` varchar(255) character set utf8 NOT NULL default '',
`blogname` varchar(255) character set utf8 NOT NULL default '',
`posturl` varchar(255) character set utf8 NOT NULL default '',
`excerpt` text character set utf8 NOT NULL,
`created` datetime NOT NULL default '0000-00-00 00:00:00',
`entryid` varchar(35) character set utf8 NOT NULL default '',
`blog` varchar(50) character set utf8 NOT NULL default '',
PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

#
# Table structure for table tblblogsearchstats
#

DROP TABLE IF EXISTS `tblblogsearchstats`;
CREATE TABLE `tblblogsearchstats` (
`searchterm` varchar(255) character set utf8 NOT NULL default '',
`searched` datetime NOT NULL default '0000-00-00 00:00:00',
`blog` varchar(50) character set utf8 NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# Adds new column to the subscribers table
ALTER TABLE `tblblogsubscribers`
ADD COLUMN `blog` varchar(50) CHARACTER SET utf8 NULL;

# Adds new 'website' column to the commentss table
ALTER TABLE `tblblogcomments`
ADD COLUMN `website` varchar(255) CHARACTER SET utf8 NULL AFTER `email`;

Comments (0) | Print | Send | 2308 Views