As a follow up to the series of posts I've been making, I wanted to post what I ended up with.  Thanks to everyone who posted comments, your help was extremely useful. To recap, I have a working pair of events to add and remove partitions to this table:

create table log (
    logged datetime not null,
    id int not null auto_increment,
    text varchar(256),
    PRIMARY KEY ( logged, id )
)
PARTITION BY RANGE( TO_DAYS( logged ) ) (
    PARTITION p20080206 VALUES LESS THAN (733444),
    PARTITION p20080207 VALUES LESS THAN (733445),
    PARTITION p20080208 VALUES LESS THAN (733446)

read more

Overview
While everyone tries to improve speed and performance in MySQL, other databases have realized that adding features that cut down on development time and improving time-to-market is what some people are looking for. MySQL is well known for great performance and it might be time to discover other parts of it that will speed up your over-all development process.

This article tries to explain how you can save time and effort on the development process by moving some of that development to the database. It does this by recommending you apply business rules to the database.

Additional Notes:

This example was taken from an actual database. Some of the table designs from that database were not ideally optimized and normalized. Please refer to the theory of the example and not the exact technical detail.

What is a Business Rule?

"Business rules represent …

Since almost a year now, we host a weekly training session for our engineers on Thursday (14:00 UTC winter time), coined the "MySQL University". While it's primary purpose is to share and distribute knowledge about a wide variety of topics relevant to our own developers, many of the sessions are of general interest for developers on other projects as well.

Therefore we hold this sessions in the public and everybody is welcome to attend! You can listen to the presentation via an OGG Audio stream, questions can be posted via IRC on the #mysql-university channel on freenode.net. The audio file and IRC log will be saved, so you can also listen to past university sessions at a later point in time again.

=================================== ERROR ====================================
File holyfoot/hf@mysql.com/deer.(none)|mysql-test/r/bdb_notembedded.result|20061113160642|60022|276fa5181da9a588
is marked as gone in this repository and therefor cannot accept updates.
The fact that you are getting updates indicates that the file is not gone
in the other repository and could be restored in this repository.
if you want to "un-gone" the file(s) using the s.file from a remote
repository, try "bk repair ”
takepatch: other patches left in PENDING
==============================================================================
2.85M uncompressed to 16.4M, 5.77X expansion
Pull failed: takepatch exited 1.

stewart@willster:~/MySQL/5.1/ndb$ cp ../../5.0/ndb/mysql-test/r/SCCS/s.bdb_notembedded.result mysql-test/r/SCCS/

A world of fail. Of course, the “bk repair” suggested does not fix the problem. This kind of problem should just not be possible. grr…

Hi,

I don't get out here too often. If you have a question, or comment, please feel free to post it here, but also send it directly to me @ jmiller@mysql.com. You will get a faster response.

Best wishes,
/Jeb

Recently I helped someone who accidentally ran ALTER TABLE my_table AUTO_INCREMENT=1234; on an InnoDB table, not realizing that this causes the *entire* table to be rebuilt[1]!

They were concerned that if they killed the ALTER process, InnoDB might have to ROLLBACK internally.. and it's possible that a ROLLBACK in InnoDB take up to about 30 times longer than applying the statement took.

So what's the answer? Killing SELECT and ALTER TABLE commands seem to be free of cost. Killing UPDATE, INSERT and DELETE statements is about as much fun as being stabbed in the eye:

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+-------------------------------------------+
| Id | User | Host      | db   | Command | Time | State | Info                                      | …

I recently received a review copy of the book "Xen Virtualization" by Prabhakar Chaganti (Packt Publishing) and finished reading it a few days ago.

The subtitle "A fast and practical guide" is a matching description - I managed to read the ~130 pages over the course of a week. The book is by no means an exhaustive reference manual, but it gives the reader a good overview about Xen and assists with performing the first steps and getting started.

Continue reading "Book review: "Xen Virtualization" by Prabhakar Chaganti (Packt Publishing)"

This thing is catching on … today’s question comes courtesy of Lasse Christiansen who is apparently residing in Japan (Lasse, correct me if I’m wrong).

Suppose you issue the following list of commands:

CREATE TABLE test (
  id varchar(1) NOT NULL,
  PRIMARY KEY  (id)
);
INSERT INTO test VALUES ('0'), ('1'), ('2'), ('a'), ('b');

The INSERT succeeds — nothing surprising here, the table now contains the values ‘0′, ‘1′, ‘2′, ‘a’ and ‘b’

Now you do:

DELETE FROM test WHERE id=0;

…and your client informs you that 3 rows were deleted.

Explain why.

(more…)

As a rule I always execute the following commands on a fresh database installation. Then for each user that is granted privileges, of course they are given a password and the host permissions are locked down as much as possible for their needs, alternately table and column privs as well. I’m not going to get into the parts the manual covers, but rather mention a couple of things on my mind.

First the initial commands:


mysql> use mysql
mysql> delete from user where User='';
mysql> delete from db where User='';
mysql> update user set Password=password('password_here') where User=’root’;
mysql> flush privileges;


However, one thing I’ve noticed is that when you hand over a server to someone that doesn’t necessarily follow your same understanding or regard to user privilege security, bad things can happen. Such as users created without a password. …

A