I got bitten by an implicit type conversion with MySQL recently. This article is about avoiding implicit type conversions, and the weird places you might find them. Introduction Here’s the setup: I was trying to select every day in the current month. Have you read my article about how stringifying dates is faster than comparing them as dates? At the time I wrote this query, I was experimenting with such things after seeing my co-workers do it a lot, for example, taking the left ten characters of a timestamp column to get the date part of it.

internals, optimization triggers One thing about TRIGGERs is that you can have 6 triggers per table:

That was brought up at the User Group meeting.

In other news, I have been reading the MySQL Internals Manual. The most interesting chapter yet is Chapter 3, the optimizer. This can be read out of order, and actually contains lots of information …

One common request I see is for better integration between MySQL and Visual Studio, allowing developers to manage MySQL databases from within VS. Well, the wait is over, announcement to follow:

Today we have released the first build of MySQL Tools for Microsoft Visual Studio — a downloadable plug-in for Visual Studio 2005 that allows Windows developers to quickly build MySQL data-driven applications with Visual Studio. With this plug-in, developers will be able to create, modify and manage MySQL database objects with an easy-to-use interface from within the Visual Studio IDE. This product is delivered as a package compatible with Visual Studio 2005 and delivers the following features:

• DDEX (Data Designer Extensibility) compatibility
• Ability to work with MySQL objects (tables, views, stored procedures, etc) from within Server Explorer

In a later release, we are planning on including tighter …

Today I was asked an interesting question: "Can we make MySQL to be case sensitive for SELECT queries and can we force a column in MySQL to be always lowercase?"

My response was that yes, we can have "instruct" MySQL to be case sensitive. One way to do that is to set the collation for the table (or column) to be either binary or case sensitive as shown below.

The naming convention for collation in mysql is as follows:

*_bin: represents binary case sensitive collation
*_cs: case sensitive collation
*_ci: case insensitive collation

###########
# Start binary collation example
###########
mysql> create table case_bin_test (word VARCHAR(10)) CHARACTER SET latin1 COLLATE latin1_bin;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO case_bin_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr');
Query OK, 5 rows affected (0.00 …

Back when I posted How “Open” Do You Have To Be To Be Open Source?, Mårten Mickos e-mailed me asking what I thought of various models of charging for open source, and how I thought they ‘rated’.

Or should we just say “whatever business model, as long as it works and users get what they want”?

Q: Why should businesses charge at all?
A: Because people work hard and deserve to get paid for that work, and folks should give up a part of their own hard-earned money because they get value out of that work.

Q: But why do we need to be paid at all?
A: There are expenses to meet — business expenses, and every employee has personal expenses.

In an ideal world, there would be a free exchange of ideas, services and goods. Unfortunately, this only works for …

Again and again the guys from Apache, MySQL, PHP, phpMyAdmin and finally from OpenSSL surprised us over the last weeks with new releases of their software packages. Now after a very quiet week we are proud to announce our own new version containing all these new releases.

New in this version of XAMPP are: Apache (2.2.3), MySQL (5.0.24a), phpMyAdmin (2.8.2.4), PHP (4.4.4 and 5.1.6) and OpenSSL (0.9.8c).

In case of Windows we also upgraded ADOdb (4.91) and FileZilla FTP Server (0.9.18).

Download XAMPP 1.5.4 here

database, mysql, routines, stored procedures, syntax, technology triggers

Question #2 from the September MySQL User Group was whether or not a TRIGGER can affect a different table. Apparently the documentation (perhaps for an earlier version??) specified this was not possible. Tom Hanlon, MySQL employee, put up this example (modified from the original, special thanks to Ralph Navarro for copying it down):

Basically, this …

bulk insert, bulk load, bulk load data, disable keys, enable keys, innodb, load data, myisam, performance, primary keys unique keys

…

If you've spent any amount of time on the forums, you probably will have recognized Brian among the forum users who most frequently answer posts -- particularly SQL and performance-related questions. Some time ago, I had a chance to ask Brian a little bit about his experience writing Phorum, about his thoughts on open source and MySQL in particular, and on his vision for Phorum in the future...

Again and again the guys from Apache, MySQL, PHP, phpMyAdmin and finally from OpenSSL surprised us over the last weeks with new releases of their software packages. Now after a very quiet week we are proud to announce our own new version containing all these new releases.

New in this version of XAMPP are: Apache (2.2.3), MySQL (5.0.24a), phpMyAdmin (2.8.2.4), PHP (4.4.4 and 5.1.6) and OpenSSL (0.9.8c).

In case of Windows we also upgraded ADOdb (4.91) and FileZilla FTP Server (0.9.18).

Download XAMPP 1.5.4 here