From time to time I install MySQL from the development tree, to see what's new, and in the latest one I found a pleasant surprise:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.12-beta

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql [localhost] {datacharmer} ((none)) > show variables like "%log";
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| back_log                       | 50    |
| general_log                    | OFF   |
| innodb_locks_unsafe_for_binlog | OFF   |
| log                            | OFF   |
| slow_query_log                 | OFF   |
| sync_binlog                    | 0     |
+--------------------------------+-------+
6 rows in set (0.00 sec)

mysql [localhost] {datacharmer} …

Hello,

In the event you missed the announcement on one of the MySQL distribution lists from Stephan...

As part of the effort of making MySQL Cluster a more mainstream product, it's been on our agenda for a very long time to rewrite the NDB API documentation so that it becomes usable by mere mortals. It took nearly three months to do this, because the new documentation is almost 300 pages long! The MySQL NDB API Guide covers two low-level APIs for writing applications that work with MySQL's NDB Cluster storage engine:

- The NDB API is an object-oriented application programming interface for MySQL Cluster. The API provides ACID-compliant transactions, table and row scans that are similar in many ways to SQL cursors, and event handling. It also features object-oriented error-handling facilities.

- The other API is the MySQL Cluster Management API (MGM API), a programming interface intended to …

One of the most common usage of partitioning is where one wants to partition
by date. One might want to have one partition per year or per month or per
week or per day. This blog entry shows how to handle this requirement using
MySQL 5.1.

The most common method to partition in this case is by range.
Partitioning in 5.1 uses a function on one or more fields of the table. In 5.1
there are some requirements on these fields if unique indexes or primary keys
also exist in the table. The reason is that 5.1 doesn't have support for global
indexes. Development of support for this have however started so should be in
some future release of MySQL.

In 5.1 functions have to return an integer. There are two functions that has
special support in the MySQL server. These are TO_DAYS() and YEAR(). Both
of these functions take a DATE or DATETIME argument and return …

I noticed this piece from Johan Andersson on Writing NDBAPI programs?connecting to MySQL Cluster last week, which shows you how to use the NDBAPI?the programming interface to the MySQL Cluster system. By coincidence, we enabled the NDBAPI documentation today. It consists of two elements:

read more

Update: MySQL 4 and 5 behave differently.
In MySQL5 there is a steady activity on some handlers, probably caused by the status queries themselves. This does not happen in 4.1. I have issued a support call with MySQL and will see what to do about it.

Update 2: See the follow-up post for more information on how to work around this.

MySQL Administrator is one of the graphical tools MySQL provide to manage their database servers. Apart from other things like server daemon control and a log file viewer this tool includes visual controls to display the load of the database server.

Even though the out-of-the-box configuration already contains some useful diagrams, I added some …

As I wrote earlier in Using the MySQL Test Suite I found an issue with using the current MySQL Sakila Sample Database as a test with mysqltest.

I was running an older version of 5.1.7 beta so I figured the best course of action was to upgrade to 5.1.11 beta.

Well the problem still exists, and I found that the cause was due to the syntax of the DELIMITER command. Assuming the creation of the schema tables, here is an example of what I found.

Running in an interactive mysql session the following works

DROP TRIGGER ins_film;
DELIMITER ;;
CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
    INSERT INTO film_text (film_id, title, description)
        VALUES (new.film_id, new.title, new.description);
  END;;
DELIMITER ;

On a side note, why does DROP TRIGGER not …

Paul has released Version 0.95 of his PBXT MySQL Transactional Storage Engine.

Here is what I did to get it operational under CentOS 4.3.

su -
useradd pbxt
su - pbxt
wget http://www.primebase.com/xt/download/mysql-4.1.16-pbxt-0.9.5.tar.gz
tar xvfz mysql-4.1.16-pbxt-0.9.5.tar.gz
cd mysql-4.1.16-pbxt-0.9.5
./configure --with-debug=full --with-pbxt-storage-engine --without-query-cache --with-mysqld-user=pbxt  --prefix=/home/pbxt/mysql
make
make install
scripts/mysql_install_db
cd /home/pbxt/mysql
./bin/mysqld_safe --user=pbxt --basedir=/home/pbxt/mysql &

Now, lets test and confirm PBXT is operational.

bin/mysql -uroot mysql
Reading table information for completion of table and column names
You can turn off this …

As Sun continues to debate just how they will open-source Java and deal with things like forking and perceived loss of control they would be well served to take a look at how successful open source projects are managed and developed. In the majority of cases there is a company or organization that acts as the Shepard of the project and manages the development roadmap. Case in point: the MySQL database is managed by MySQL AB, Apache is managed by the ASF, and Spring is guided by Interface 21. There are exceptions where the community takes over either and self-governs (like PHP) or creates fissures that result into multiple iterations of the base code (like Linux) which likely …

I've written a lot recently about MySQL index and table structure, primary keys, surrogate keys, and related optimizations. In this article I'll explain how MySQL's index structures enable an extremely important query optimization, and how that differs between storage engines. I'll also show you how to know and predict when the optimization is triggered, how to design tables and queries so it'll be used, and how to avoid defeating it with poor practices. Plus, I'll peek a bit into InnoDB internals to show you what's going on behind the scenes.