Andy Jarrett : MySQL

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 | 3588 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 | 7307 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

MySQL 5 is here

After a long time in alpha, and beta, MySQL 5 is here. Now including Stored Procedures, Triggers, and Views wich for years has been seen as its shortfall in the DB arena.

Also updated is the Query Browser and the Administration tool as well

Comments (0) | Print | Send | 1231 Views

MySQL Query Broswer and Administrator updates

It seems like im stumbling upon all kinds of updates at the moment. MySQL have updated their main two tools, the Query Browser and Administrator. If you haven't tried them and you use MySQL, shame on you. Both are good quality free tools for use with your server (local and remote). The Administrator also has a System Tray Monitor allow you to manage several instance from one place.

Query browser 1.1.15 - here
Administrator 1.1.3 - here

Note: I had to uninstall the old versions to upgrade

Comments (0) | Print | Send | 1179 Views

MySQL 5 .0 Beta Comp

MySQL are running a comp to get the bugs outta beta. Mainly looking for testers of the new features including (and much awaited) Stored Procedures, triggers and views.

Comments (0) | Print | Send | 948 Views

MySQL Query Browser 1.1.13

MySQL have released there Query Browser 1.1.13 for Windows only.

Hopefully they have fixed some glitches with the text area, though at the moment i cannot find a change log.

Comments (0) | Print | Send | 975 Views

MySQL alias, variables and the WHERE clause

What a nice morning i had remembering that you cannot reference an ALIAS or a variable in a WHERE clause. So for anyone else that has a memory like mine (i'm jealous of fish put it that way)

standard SQL doesn't allow you to refer to a column alias in a where cause. Which make total sense (now) as the column value is most likely not determined at time of execution. So....

SELECT
@total := date_add(datefield, INTERVAL 1 YEAR) AS newDate
FROM
mytable
#WHERE
# @total > now() /*Invalid*/
#WHERE
# newDate > now() /*Invalid*/

Comments (9) | Print | Send | 17103 Views

New MySQL System Tray Monitor

If you use the MySQL Administrator then did you know that you have access to the new System Tray Monitor (which beats the traffic lights as standard). You'll find the Monitor exe in C:\Program Files\MySQL\MySQL Administrator 1.0 (obviously if you have changed your install dir then this will need to reflect that).

The new monitor has the ability to list all installed MySQL services and their individual statuses. Along with that, per instance you have the following options

Configure Instance
Start Instance
Shutdown Instance
etc

It also doubles up as a CPU monitor which is just handy more than anything i.e. seeing your CPU spike when you run a long CF page

p.s. MySQL Administrator update is now available to v 1.0.21

Comments (0) | Print | Send | 2535 Views

Quantum DB 3 released

Looking like a time for releases Quantum DB have just released verion 3 of their Eclipse plugin.

If you haven't used QuantumDB, it a DB plugin for access to any database with a JDBC driver i.e. MySQL, Oracle, Postgres etc

Comments (0) | Print | Send | 1020 Views

BlogCFC 3.7, a new design, and upgrade SQL

I've finally gotten around to putting up BlogCFC 3.7 along with a new design again (i get bored of my own work quickly). The install as usual went smoothly with the only annoying bit was having to move my new design from the 3.6.3 code base to the 3.7 days before it was going to go live - cheers ray ;o)

For anyone else on MySQL who is just about to do the update the only DB change required i've put into a SQL script below.

# Upgrade from BlogCFC 3.6.3 to 3.7
#
# Disclaimer: This comes "as-is"

# Replace ****** with your database name
USE `andyjarrett`;

# Add `allowcomments`.
# Please note that i have defaulted this to ON ('1') so that all you old posts have comments allowed.
ALTER TABLE `tblblogentries` ADD COLUMN `allowcomments` tinyint(1) NOT NULL DEFAULT '1';

# Add the tblblogsubscribers table
CREATE TABLE `tblblogsubscribers` (
`email` varchar(50) NOT NULL default '',
`token` varchar(35) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Comments (0) | Print | Send | 1602 Views

Web Tools for Eclipse

If like me you've been using CFEclipse for coding, but when it comes to design bailing back to DWMX for its Js and CSS support - then its time to stop.

Eclipse.org has a Web Tools Project (WTP) which includes source editors for HTML, Javascript, CSS, JSP, SQL, XML and more.

To getting it up and running there are a few prerequisites

3.1 build only of Eclipse - personally i am having no troubles using the M7 build but they recomend M6
http://www.eclipse.org/downloads/index.php
EMF driver
http://download.eclipse.org/webtools/downloads/drivers/emf-sdo-xsd-SDK-I200504141117.zip
GEF driver
http://download.eclipse.org/webtools/downloads/drivers/GEF-SDK-3.1M6.zip
Java EMF Model Runtime driver
http://download.eclipse.org/webtools/downloads/drivers/JEM-SDK-I20050415.zip
and of course the Web Tools Platform it self (NON SDK)
http://www.eclipse.org/webtools/index.html

For more information about getting started with WTP go to: http://www.eclipse.org/webtools/index.html

Comments (0) | Print | Send | 3905 Views

MySQL Offline server books

MySQL Tools offers the MySQL Reference Manual in the Microsoft HTMLHelp (.chm) format for free.

Very handy to have on the desktop but just remeber to update them every now and then :o) hence the prompting on here for myself :o)

Comments (0) | Print | Send | 1402 Views