Listener feedback:

MySQL will go public. Would you buy stock if you had the money? Why or why not?
Call the comment line at +1 617-674-2369 (US phone number)

Use Odeo to leave a voice mail through your computer:
http://odeo.com/sendmeamessage/Sheeri

Leave a message at the Technocation forums:
http://tinyurl.com/sc6qw

Send an e-mail to podcast@technocation.org

Episode 8 Show Notes:
This episode’s feature is basic MySQL Security. Not only will we discuss what the basic security is, but we’ll discuss the *why*s, not just the how’s.

Direct play this episode at:
http://tinyurl.com/2r9kom

Subscribe to the podcast by clicking: …

We've all done it but possibly not on this scale. Someone may have forgotten a WHERE clause when a bank customer received 75000 bank statements. At 27 pence per letter, that cost about £20000 (US$40000) just for postage. Unfortunately, it wasn't an isolated incident. In the same fortnight, the UK Government managed to fubar a mail merge to 26000 pensioners. In both cases, quality control seems to be evaded or absent.

ID Integrated Data SA situated in Petit-Lancy,Geneva, is further demonstrating its confidence in open source solutions by signing a training partnership agreement with MySQL AB, the developer of the popular database software.

During my work for Journalizer I created a plugin for TinyMCE. What does it do? Well, it solves a problem, but which one? We have list of TinyMCE-controlled textareas and it is so common that you use the tab-key to jump to the next field that we wanted that as well. Unfortunately, this doesn't work in TinyMCE.

Why is that? When using TinyMCE, you declare a HTML form element as "managed" by TinyMCE. What it does is substituting the actual HTML element with an iframe that can be edited like rich text. I.e. you can make things bold and italic and all that just with a click on button and you immediately see the results — nice. But because the actual textarea gets replaced by the iframe, common "tabbing" through your form elements no longer works because …

Some people believe that the ability to commit or rollback multiple statements as a single unit is a defining characteristic of the concept of transactions. They draw the -false- conclusion that enabling autocommit is the same as disabling transactions. It is easy to demonstrate why this is not true.
Autocommit
With autocommit enabled, every statement is wrapped within its own transaction. Successful execution of a statement is implicitly followed by a COMMIT, and the occurrence of an error aborts the transaction, rolling back any changes.

By default, autocommit is enabled in MySQL. You can check the current setting by executing the following statement:

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

The @@ prefix …

So what, if you are planning a centralized MySQL server and many clients all around the world?
First of all, check the manual
Then install timezone description tables from here
Then run a small example to see how the server and client timezones can interact.
Here is the standard server setup

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)

Now add a table for our experiments

mysql> create table time_table (datetime_col datetime);
Query OK, 0 rows affected (0.11 sec)

Let's add some data in …

This week I helped dealing with performance problems (part MySQL related and part related to LAMP in general) of system which does quite a bit of content delivery, serving file downloads and images - something a lot of web sites need to do these days. There were quite a bit of mistakes in design for this one which I though worth to note, adding some issues seen in other systems.

Note this list applies to static content distribution, dynamic content has some of its own issues which need different treatment.

DNS TTL Settings The system was using DNS based load balancing, using something like img23.domain.com to serve some of the images. I'm not big fan of purely DNS based load balancing and HA but it works if configured well. In this case however the problem was zero TTL set in DNS configuration. This obviously adds latency especially for "aggregate" pages which may require images to be pulled from 10 different image …

I did a presentation at the MySQL miniconf as part of LCA2007 in January. It was entitled "The 10 Do's and Dont's of MySQL Cluster" and presented some of the knowledge I had learnt in setting up NDB clusters in the real world. Many people treat NDB as just a simple storage engine and can be used interchangably with InnoDB or MyISAM engine types. My talk presents some of the issues I found in simple summary form and why certain details are important when implementing a MySQL Cluster.

Most of the points will appear as common sense to seasoned DBA's and data architects, but many people currently in industry seem to get these points wrong or totally forget them. My talk exposes the problem areas, but also highlights some of the good things that were found when implementing a cluster and how it can perform better.

Pro MySQL, by Mike Kruckenberg and Jay Pipes, covers advanced MySQL topics, especially focusing on optimization and internals. I give it a solid 4 stars.

Just a small thought to share. Oracle doesn’t really comply to the rule 11 of Codd’s 12 rules. It does have database links and tables can be grouped in different locations (databases) but it’s not possible to distribute a table transparently across several locations. MySQL Cluster, on the other hand, distributes rows of a table over [...]