I saw this cause confusion twice on IRC today, so I think I'll make this a new FAQ entry, even though it is clearly spelled out in the Manual.

Integer columns can be created with an optional number in brackets after the integer declaration, for example:

CREATE TABLE intTest(
id INT(2)
);

Here's what the MySQL Reference Manual says:

Another extension is supported by MySQL for optionally specifying the display width of an integer value in parentheses following the base keyword for the type (for example, INT(4)). This optional display width specification is used to left-pad the display of values having a width less than the width specified for the column.

The display width does not constrain the range of values that can be stored in the …

That's the headline of this article in ComputerWorld. It continues:
The NSW Office of State Revenue (OSR) is taking a tough stance against Microsoft's decision to make an enterprise edition of Windows Vista only available to companies that have signed on to its Software Assurance program. The tax collection agency has declared it would rather switch desktop operating systems than lock itself into Microsoft's licensing regime.

[...]

Asked whether Microsoft's Software Assurance subscription licensing regime - under which volume users pay an annual fee for support, patches and upgrades - was influencing any potential shift on desktops, Babhoota said previous upgrade offers from Microsoft had provided a less than compelling economic case to his organization.I think that's interesting. …

db4free.net has just welcomed its 1,000th visitor! This project went online on 29th of June this year.

db4free.net is - as far as I know - the first and only database provider that offer's MySQL 5 databases for free. The server currently runs MySQL 5.0.13-rc-Max. So it also gives the opportunity to test storage engines like BerkeleyDB, Archive or Federated which are not available in the standard packages.

This project gives interested people a chance to test new features of MySQL 5 without the need to run a server at their own. I hope, people get used and comfortable with these new feature. Many users will probably move their databases to a commercial Internet Service Provider after testing at db4free.net, so they might probably prefer an ISP running MySQL 5 rather than an ISP running the older versions. Maybe that's a little contribution to accelerate the ISP's decision to update to MySQL 5.

More …

Today I did it - I've updated my MySQL installation on my production system to MySQL 5, to be more concrete, to MySQL 5.0.13-rc-nt-max.

That was not what I initially planned. I thought, I'd wait until MySQL becomes a Production Release, but everything works fine, I did a lot of things on my testing environments and I didn't find any bugs. So I asked to myself, why wait ;-)?

It was quite an easy move, even easier as from 4.0 to 4.1. From 4.0 to 4.1 I had to try hard to find the character set and collation settings that work best for me (as German speaking I need umlauts). But now from 4.1 to 5.0, everything was just smooth.

I imported my mysql privilege database from my 4.1 server to a test server with 5.0. I copied MySQL 5.0's mysql database and truncated the data from the db, tables_priv and user tables. Then I made SQL dumps from these tables from the mysql 4.1 database with extended inserts, but …

MySQL has supported FULLTEXT indexes since version 3.23.23. VARCHAR and TEXT Columns that have been indexed with FULLTEXT can be used with special SQL statements that perform the full text search in MySQL.

To get started you need to define the FULLTEXT index on some columns. Like other indexes, FULLTEXT indexes can contain multiple columns. Here's how you might add a FULLTEXT index to some table columns:

ALTER TABLE news ADD FULLTEXT(headline, story);

Once you have a FULLTEXT index, you can search it using MATCH and AGAINST statements. For example:

SELECT headline, story FROM news
WHERE MATCH (headline,story) AGAINST ('Hurricane');

The result of this query is automatically sorted by relevancy.

MATCH

The MATCH function is used to …

Well we had to setup some hefty realtime backup solution for a database server here at work. After much research i decided hotcopy and mysqldump would not suffice, its too load intensive across the system and ties up the tables with LOCKs for too long. So in walked replication, my beautiful angel of salvation! It was pretty smooth to get setup once i fumbled through it a bit, though i'm not a pro yet, it seems to be running pretty smoothly.

Basically the steps go as follow:
1. Install MySQL on your two servers (I did matching versions [5.x] don't know how diff versions will work)

2. Configure your master my.cnf add the following:

log-bin
server-id=1


3. login to mysql as root and run this statement:

mysql> GRANT SELECT , RELOAD , LOCK TABLES , REPLICATION SLAVE , REPLICATION CLIENT ON *.* TO username@"%" IDENTIFIED '';
mysql> …

There's a column in CIO magazine this month called Java Goes Wild. Eric Knorr, one of the editors at CIO, points out that open source has created a wide ranging of new and innovative solutions to the traditional problems of Java enterprise applications. (Think Spring, Struts, Ant, Jython, JRuby, Rhino, etc. etc.) So much so, in fact, that vendors such as Sun and BEA can no longer dictate the future of the Java language.

Hopefully this will help settle the on again, off again debates about whether open source can innovate. (See Age of Innocence) If open source can't innovate, then where did all these new Java technologies come from?

Die Ankündigung von dem MySQL 5.0 Release Candidate ist auch auf Deutsch verfügbar!

In theb last blog entry I looked at how to create a very simple stored procedure. While it demonstrated how quick and easy it was to create a stored procedure it wasn't particuarly usefull.

So today I'll be showing you two additional things (spread over two entries), you can do to expand on yesterdays example. First up it's creating a multiline procedure. On first consideration this might not seem to be something you need to deal with, why can't you just keep adding lines to the procedure. The problem with that is that MySQL needs to firstly know where the procedure code starts and ends (in our first example it wasn't a problem because it was a single line of code) and also it needs to know when a particular command ends.

Take for example the MySQL command line tool, when you enter an SQL statement you need to add a semicolon ; to the end of the SQL command to tell MySQL that there is no more to enter and to begin …

Thanks to the RSS feature of Google's blog search, I found a nice series of blog entries by Jeff Hunter, an Oracle DBA who recently attended a MySQL training course.

The series starts with a post on how Jeff's boss informed him that they would be using MySQL, then progresses through his experiences in the MySQL training course (looks like Using and Managing MySQL).

Part 1
Part 2
…