I understand that MySQL as a company wants to recruit paying customers. However, as a community user I have a hard time finding what I want on the MySQL website. Today’s frustration is brought to you by trying to find the documentation.

Go ahead, hit http://www.mysql.com. From there, where do you go to find the documentation?

It’s not Services, not even Services -> Support.

According to Products, the community server is not even a product. How is a potential new user, who wants to learn about MySQL, supposed to know a community version exists? Here are the products listed on the Products page:

MySQL Enterprise
MySQL Enterprise Monitor
MySQL Cluster
MySQL Embedded Database
MySQL Database Drivers
MySQL Database Tools

Where’s “MySQL Database” on that list? A website user basically has to know what they’re looking for, since that page does not help find “the mysql database”. You can guess it’s the “MySQL Enterprise” — but you’d be wrong. Imagine if you’re a person unfamiliar with MySQL who has been told “go to the MySQL website and get the free version of the database, it’s great!”

OK, OK, I know, you’re saying “click on Downloads”. Which takes me to the “choose which version you want to download.” OK, nice for newbies, but really annoying for the experienced. Why aren’t the links to download at the top, with the explanations just underneath? A newbie would be presented with the links, think “I don’t know which to choose!” but then see that the descriptions are just underneath. An experienced user can just click and go.

This of course is made even more silly when you realize that the community download link simply scrolls down. Yes, that’s right, the community downloads immediately follow that chart of explanations. Which means that in the current state, when the link is at the bottom of the chart, the link points you to the next line. If the link is at the top of the page, at least there’s a reason for it — to scroll down past the explanations.

Now recall that the exercise was to find documentation. There is no way to download the documentation here.

I am a non-paying user, not a developer. Going to the “Developer Zone” is not intuitive for me. However, that is exactly where I need to be:

* DevZone
* Downloads
* Documentation
* Articles
* Forums
* Bugs
* Forge
* Blogs

Given that list of topics, why on earth is this section called the “Developer Zone”? (it’s a rhetorical question!) Sure, if I’m a software developer I might think it’s useful to me. On any other website “developer zone” is relegated to advanced users, or folks using some different part of the product than what most people use (think Apple’s Developer Connection).

Take a look at that list. On any other website, it would be under “Support” or “Help” or “Learn”.

No wonder folks have no idea that the forums exist, much less the Forge or Planet MySQL. If I was a new user to MySQL and I wanted to find the documentation, I’d be very unhappy.

Heck, I’m very unhappy anyway — what kind of company has a huge community of people supporting each other and contributing back to the company, and does not give people an easy way to find the community?

So today Roman, one of the developers on the Doctrine project, pointed me to an article that was discussing the misuse of DISTINCT. This article went so far as to say that "A SELECT DISTINCT query is frequently a "code smell". The article pointed to another article hosted at onlamp.com. That author was advocating the use of subqueries to more efficiently filter out redundant rows. Immediately I began to wonder if this is really a feasible approach since MySQL's subquery handling is very slow. I could imagine this being faster on RDBMS where the subquery implementation is more mature. That being said comments in the onlamp.com article point to the fact that even on Oracle things get slower with the subquery approach. The original article does however talk about how rewriting similar queries with a subquery improved performance on PostgreSQL.

Anyways, I briefly wrote up similar queries using the Sakila sample database for MySQL and ran them on my local system. I used version 0.8.0 of Sakila. I did execute them a few times to prime caches (but the query cache was of course disabled), but it was running on my desktop machine, so results could have been scewed, but with a few runs the results always come out consistently:

mysql> SELECT DISTINCT c.customer_id, c.last_name FROM customer c, rental r > WHERE c.email LIKE '%sakilacustomer.org' AND r.rental_date >= '2005-08-23 20:44:36' AND c.customer_id = r.customer_id > ORDER BY c.last_name; +-------------+-------------+ | customer_id | last_name | +-------------+-------------+ | 505 | ABNEY | | 504 | ADAM | .. | 107 | WOODS | | 496 | WREN | | 28 | YOUNG | +-------------+-------------+ 201 rows in set (0.01 sec) mysql> SELECT c.customer_id, c.last_name FROM customer c > WHERE c.email LIKE '%sakilacustomer.org' AND EXISTS > (SELECT NULL FROM rental r WHERE r.rental_date >= '2005-08-23 20:44:36' AND c.customer_id = r.customer_id) > ORDER BY c.last_name; +-------------+-------------+ | customer_id | last_name | +-------------+-------------+ | 505 | ABNEY | | 504 | ADAM | .. | 107 | WOODS | | 496 | WREN | | 28 | YOUNG | +-------------+-------------+ 201 rows in set (0.04 sec) mysql> SELECT customer_id, last_name FROM customer > WHERE email LIKE '%sakilacustomer.org' AND customer_id IN > (SELECT customer_id FROM rental WHERE rental_date >= '2005-08-23 20:44:36') > ORDER BY last_name; +-------------+-------------+ | customer_id | last_name | +-------------+-------------+ | 505 | ABNEY | | 504 | ADAM | .. | 107 | WOODS | | 496 | WREN | | 28 | YOUNG | +-------------+-------------+ 201 rows in set (0.04 sec)

I did not do much more digging, I did not check the EXPLAIN output or verify what indexes are being used. I guess I was satisfied with the answer, since I was expecting exactly this result anyways. Maybe if I get poked enough I will try to run this query again Pagila.

One of the most frequently needed functionality in the MySQL Proxy is the need to know which server you are on. This is not given, on purpose, by the proxy, because the proxy is supposed to be transparent. It is not supposed to matter which back-end server you are on.

However, for testing purposes we often want to know which back-end server we’re on. Thus I developed functionality for SHOW PROXY BACKEND [INDEX ADDRESS OTHER].

SHOW PROXY BACKEND INDEX — gives the index of the server you’re on (backend_ndx, ie 1)

SHOW PROXY BACKEND ADDRESS — gives the address of the server you’re on (ie, foo.bar.com:3306)

SHOW PROXY BACKEND OTHER — gives the address of all the other servers except those you’re not on, in multiline format.

Note that I was pretty lazy and the commands are case-sensitive. But I figured that since this is supposed to be used mostly in testing circumstances, it did not really matter.

The code is on the MySQL Forge Wiki at http://forge.mysql.com/tools/tool.php?id=139

Interestingly enough, this script is actually being used in production — a site has a primary and failover server, and wants to check that when the primary server is in use, there are no connections on the failover. I wrote that check as well, but as the logic is somewhat particular, I am not sure it would be useful to many. The logic is:

1. Run SHOW PROXY BACKEND INDEX.
2. if it fails, I can’t connect to the proxy, log an error, exit.
3. if it !=1, I’m on the failover server, log a warning, exit.
4. if it =1, I’m on the primary server.
5. Run SHOW PROXY BACKEND OTHER.
6. if it is empty, either I can’t connect to the proxy or there are no failovers defined, log a warning, exit
7. For each OTHER address, connect to them and find what that connection’s host looks like (sometimes it looks like foo.bar.com:4325, other times it looks like 1.2.3.4:4573). Get the value of “my host” by stripping off the port.
8. Connect to the OTHER address again, killing off connections from “my host” except for the “system user” and a few other special accounts (replication slave being one of them). Log each kill with the thread number and a warning.
9. If there are no connections to be killed, log OK.

Let me know if you’d like to see that…it’s a shell script, and it requires the mysql client and bintools like grep and cut.

I need help from my fellow mysql users.  I know some of the people who read this are alot better then me with mysql so hopefully you can help So today we decided that we are going to migrate one of our master database servers to new hardware.  Since we got the hardware this [...]

Let me prefix this post by stating that I’m a developer and not a business analyst, but I wanted to point out something that strikes me as a bit contradictory. In recent posts like these by Savio Rodrigues and Matt Asay, there are references that say the commercial plugins MySQL/Sun was planning on releasing were part of an OSS business model. While the plugins would have been tied to an OSS project, the products themselves would have been commercial*. I think these products (and other current products MySQL/Sun offers only as closed source) should actually be considered as commercial product offerings (under a commercial business model), and not associated with a pure OSS business model. To me (and some may disagree) an OSS business model involves only software that provides the source to everyone, not just those who pay to license the source. For example, from a commercial business model perspective, how would those MySQL plugins have differed from a commercial Microsoft Outlook plugin? Sure, the MySQL plugin would have been plugging into an open source product, but the plugin product itself is just as closed (well, I suppose with Microsoft you need to buy the development environment if you wanted to write your own).

Also, I don’t believe “the community” was hurting the OSS business model in any way; on the contrary, it was helping preserve it. As you could see from the many responses to the MySQL announcement, this was only going to push some community members away. Were they hurting the commercial/hybrid business model? Of course! Who want’s to see their favorite open source database company venturing further into closed source commercial offerings? The community is going to speak up to keep everything open.

In summary, I’m not sure what the best business model would be for open source or commercial (like I said, I’m a developer), but when talking about “OSS Business Models”, just be clear what is actually open source and what is commercial. Perhaps I’m being a bit naive, but I’m still hoping OSS businesses find a way to thrive while keep all products 100% open.

* Even if they had given the source code away with the product, it would not have necessarily been “Open Source”, but rather just a source code license for a commercial product. If it really was going to be released under open source, then the first customer could have legally redistributed the source to the community, thus negating the product to begin with.

The 97th edition of Log Buffer, the weekly review of database blogs, has been published on Brian “Krow” Aker’s Idle Thoughts.

We have Jeff Smith and Ward Pond standing by for two upcoming editions. And if you’d like to contribute, make yourself known in the DBA community-at-large (and have some fun in the process), you too can do Log Buffer! Read the homepage and send me, the Log Buffer coordinator, an email.

And now, Brian Aker’s Log Buffer #97.

Welcome to the 97th week of Log Buffer. I am Brian Aker, and I will be your cruise director :)

Synchronisation in via scripting in the Database? Jonathan Lewis has a write up on making use of a locking package for Oracle. How about some published release notes for Oracle 10gR2?

On the MySQL side we have a post about "Time Delayed Replication", or what I often refer to as "we break our databases a lot, so we would like some more length in the rope please".

Ever seen a DBA run through a data center in the hopes to pull the ethernet cable out of the back of a machine before "DROP DATABASE <>" was replicated?

I have :)

Fritz Hoogland announces a new statistics package for Postgres. You have heard of the MySQL Pluggable Storage Engine Interface right? Have you played with Postgres's pluggable language support?

Prepared statements were in the news this week. Does Postgres have an issue? Or is it the result of bad application designs? From using these with MySQL, I question the value of them in applications with dynamic queries.

Free e-book on SQL Server announced, I hear that Amazon has the Kindle back in stock!

Ever been interested in the page compression features for SQL Server? Linchi Shea has an article outlining what was found after compressing several databases.

Have you seen the inside of a Modular data center?

If you have one you need a proxy. I made a request this week to find out which is the best proxy. Turns out there are more of them then what I knew existed. Dormando wrote up an article on the DPM Proxy.

Hacking perl? Patrick Galbraith released a new DBD::mysql.

Want to get MySQL and Hibernate to play nicely? Mark Matthews goes over the particulars with how to make "ON DUPLICATE KEY UPDATE" work.

From the world of semi-structured there was a batch update on the status of CouchDB. Check out the work on the new geo-data bits. Further work is being done on incremental reduce by Damien.

Finally, lets congratulate Pythian on their successful invasion of Europe.

EnterpriseDB is sponsoring a survey to track which open-source databases people use. Make your voice heard: Vote today. It takes less than 30 seconds to answer, and covers such questions as why you use an open-source database, with which operating system do you use it, etc.

If implementing ‘SHOW INNODB LOCKS’ took half an hour (literally, includes compiling and testing) for a non-developer type of guy (cause most of code was written anyway), why the heck it takes ten years (or more?) to get such feature to standard release?

During my morning reading, I happened upon this verse from Ecclesiastes:

1 To every thing there is a season, and a time to every purpose under the heaven:...

2 a time to plant, and a time to pluck up that which is planted....

It made me think of the ongoing debate around open-source business models (illustrated well in a recent post by Savio and perhaps more so in the comments section to that post), kicked up by MySQL's recent decision to offer closed extensions to its core (100 percent open source) database, but one that has been simmering for a long time. MySQL is essentially saying, "We've spent a decade planting. We'd like to reap a little of what we've sown now."

MySQL is doing this right. It has focused on adoption first, and has committed to keeping the source of that adoption open source. But in its next phase, perhaps it demonstrates an ideal open source-based business model. Or rather, a phased approach to growing an open-source business...?

...

Here are a few ways I found Views to be useful for me.


Data Cleansing

If I have a column that needs to be cleaned or tested in some way. For example:
Select mycolumn, if(mycolumn is null, TRUE, FALSE) as mycolumnisnull from mytableThis example is not that amazing, but it lets you place conditions for the data. Then instead of re-writing these conditions in your application layer, you can re-use the View from the database.
Another example:
Select email, regex ['some amazing email verification/cleaning regex line'] as cleaned_emails from emailsHere again, we have some condition that placed in the database. We can now call the emails table and tell it to give us only the cleaned emails according to the defined conditions.


Decision Making

You can place some business logic in your Views. I wrote about it in my blog before.
This time I will just use an example:

Select business_loan, if (business_loan > 30000, 'Allow Discount', 'No Discount') as discount from loansSo here, you have an embedded rule that says if the loan is over 30,000, then give a discount. Otherwise, no discount.
There are advantages and disadvantages to using business rules in your database. One advantage is the visual way of verifying that your code is working. You can see next to your data if your code works or if you need to make changes.


Displaying parts of many-column tables

Very simply, if you know you only need a few columns from a table with many columns, you can just specify it in a View. It just keeps things tidier.

Create View contact_details
Select col11 as firstname, col52 as lastname, col37 phonenumber from 100columntableI have used this (a lot) for finding tables to extract data from when I did some ETL-data warehouse work. This is an example:

Create View src_thetableiwaslookingfor
Select * from database1.tablethatiwantThis helped me a lot in the past and also helped when I worked with other developers on the same project. We just all used the same interfaces (Views).


Fashioning your data

You can use some basic text manipulation on your data to help you display it better.
For example:

Select concat(title,'. ',firstname, ' ', lastname) as fullname from contact_details.Here, we just added the title, first name and last name into one string (example, Mr. John Smith).
If you really want to go crazy, you can do this

Create View HTML_export
Select concat('[tr] [td] ',firstname,'[/td] [td] ',lastname,'[/td] [/tr] ') as list from contact_details
So, if in the future you want to get a quick list of all the contact names in HTML format, you can just use the View.


Conclusion
Views are very useful :)
Especially if you dislike long-winded languages when you can do it in 1 line of SQL.

I plan on writing a more detailed article soon about this topic

I have thought about putting up forums on my blog for LONG time and even thought about writing my own. But after playing with phpBB3 for a while on my other site (http://totalplaystation.com/forum), I have decided to use it instead. Let’s just face it; I don’t have the time or resources to write [...]

Altering a big table sucks, and to make it worse you have no idea what’s happening or how long it will take. I’d like a progress bar, or some status output, or something that gives me the feeling like my server didn’t die.

I am speaking at the Italian Free Software Conference, an event with the aim of considering the state of the art of Free Software in Italy.

The conference is held in Trento, Italy, sponsored by public institutions, the local university, and private industries.
My session is about MySQL as open database, which is a philosophical walkthrough of how MySQL has become successful, why Sun has bought it, what is in store for the future.

Readers of this blog care deeply, madly, passionately about open source. But if this blog's traffic data is any indication, readers of this blog care even more about Apple, Google, and Microsoft. In fact, most of the planet, as measured by Google Trends, cares more about what Apple is doing on a given day than what business model MySQL has adopted:

Search volume for Open Source, Google, Microsoft, and Apple

(Credit: Google Trends)

On this blog, MySQL and Ubuntu make an appearance in the top-25 most read stories, but Microsoft, Apple, and Google dominate the most-read stories, despite constituting a relatively small number of my total posts.

I note this data only to remind everyone, myself included, to take ourselves a little less seriously. It's not that open source isn't critically important, because it is: It is the heart of computing going forward. But our petty controversies are just that: Petty. The stakes are pretty small, given that open source is being woven into the fabric of software's future, even within these giant software vendors, regardless of our squabbles.

...

Dear LazyWeb,

Today is "green" day, and for "green" day I want to turn on and off some computers by remote. This means I want to be able to telnet/ssh into a power supply to bring them up and down.

Cheap, I want cheap. Web interface is "ok" assuming it works with links (aka text based browser).

No cards... something I plugin. I want a very simple setup.

I've got test servers I rarely use and I want them off most of the time (aka... MySQL, Memcached, Asterisk, Gearman, Drizzle... all the rest).

Thanks!
-Brian

Last week I learned that Sun has put its 3 database groups (Java DB, MySQL, PostgreSQL) under Marten Mickos. First off, who knew Sun had such a broad database portfolio???? Second, smart move putting them all under Marten. In speaking with Marten's Java DB team I gave them a small nugget of advice that has served us incredibly well with WebSphere Application Server Community Edition (WAS CE). Simply put win with the strengths of the family, not individual products. I've written about customers wanting choice and flexibility and the challenges of trying to position any product, OSS or not, as... READ MORE

LewisC's An Expert's Guide To Oracle Technology

Do you know which open source feature is the most important? Do you know which open source database rocks and which one sucks? Is MySQL better than Postgres? Is Ingres worth considering? How does Firebird compare? Have you used, or have you considered using, an open source database?

Take a survey. It's only 15 questions so it takes just a few minutes.

I'll post a link where you can get the results once they have been compiled and prepared.

BTW, this isn't my survey. I'm just passing on the link.

LewisC

What's in a name? That which we call a rose by any other name would smell as sweet.
Good old William had his reasons to say so, but then either he was not concerned about misspellings or he didn't care. (A recent book about the Bard actually points out that all Shakespeare's known signatures are differently spelled, and none of them is spelled Shakespeare!)
The problem with my name is that, for the majority of non-Italians, it does not sound familiar, and consequently it gets misspelled. As a frequent traveler, I have seen every sort of mischief done to my first name.
I don't really understand why, but most English speakers write "GUIseppe" instead of "GIUseppe." Sometimes I was called "Giuseppa" (which is a female name) or "Giuseppi" (which is a non existing plural of my name), or "Jooseppai" (which is how I pronounce it) and several variations with additional or missing vowels.
Let me set the record straight, for anybody who cares.
The right spelling is "G-I-U-SEPPE". To remember the right order, you may use what I call the selfish rule: First I, then U. Got it? :)

Now, let me use this fact to compare the behavior of two companies. When I was hired by MySQL, I braced myself in wait of the misspelling, which did not come! All my official documents, personnel listings, email accounts, company badge, were produced without any mistake. I was impressed. Apparently, MySQL HR people are very attentive to these details. When I was hired by Sun, instead, well, let me say that it was not the same quality. MySQL was an international company, while Sun seems more an US company with international branches. They are learning, though.

I wrote the draft for this post long ago, but I left it aside. Now, since it seems that I am not the only one with this problem, I state my point.