 |  | Another approval has arrived. I will be co-presenting a tutorial at the MySQL Users Conference 2009. Partitioning in MySQL 5.1. My co-speaker is Sarah Sproehnle, a well known MySQL trainer. The topic is one of the flagship features of MySQL 5.1. |
mysqldump --opt db_name table_name > file.dumpmysql db_name < file.dump 
 | I am glad to host in these pages a post by Philip Stoev, a remarkable QA engineer, creative, resourceful, and a notorious troublemaker. I met Philip by email in 2007, when I was exploring his Perl modules, which I used for one of my most rewarding articles. A few months later, when we met in person during the MySQL Developers Meeting, Philip was hired as a QA engineers, with my warmest recommendations. This post is about the Random Query Generator, which is also the topic of a MySQL University session. Thanks, Philip! |
$tables = {
rows => [65535],
pk => [ 'integer not null auto_increment' ],
partitions => [ 'PARTITION BY KEY (pk) PARTITIONS 2' ],
};
$fields = {
types => [ 'varchar', 'char' ],
indexes => [undef ]
};
$data = {
strings => [ 'english' ]
}
query:
UPDATE _table SET _field_no_pk = _english WHERE `pk` = _smallint_unsigned;
bzr branch lp:~randgen/randgen/main
perl runall.pl \
--basedir=/path/to/mysql-distribution \
--gendata=partitioning.zz \
--grammar=partitioning.yy \
--queries=100000 \
--threads=10 \
--duraton=360
| The approvals are coming. I will be speaking at the MySQL Users Conference 2009. Using the event scheduler. The friendly behind-the-scenes helper. My co-speaker will be none other than Andrey Hristov, the designer and implementer of the Event scheduler, one of the main features of MySQL 5.1. |
 | I talked several times about partitioning usability. In my many tests of partitioning I found myself in need of generating list of partitions for a given range. I made the Partition Helper (see code at the end of this post) to scratch this particular itch, i.e. making partitions reasonably quickly and without thinking too much. |
./partitions_helper --table=blog_posts \
--column=published \
--interval=month \
--start=2007-01-01 \
--end=2009-01-01
ALTER TABLE blog_posts
PARTITION by range (to_date(published))
(
partition p001 VALUES LESS THAN (to_days('2007-01-01'))
, partition p002 VALUES LESS THAN (to_days('2007-02-01'))
, partition p003 VALUES LESS THAN (to_days('2007-03-01'))
, partition p004 VALUES LESS THAN (to_days('2007-04-01'))
, partition p005 VALUES LESS THAN (to_days('2007-05-01'))
, partition p006 VALUES LESS THAN (to_days('2007-06-01'))
, partition p007 VALUES LESS THAN (to_days('2007-07-01'))
, partition p008 VALUES LESS THAN (to_days('2007-08-01'))
, partition p009 VALUES LESS THAN (to_days('2007-09-01'))
, partition p010 VALUES LESS THAN (to_days('2007-10-01'))
, partition p011 VALUES LESS THAN (to_days('2007-11-01'))
, partition p012 VALUES LESS THAN (to_days('2007-12-01'))
, partition p013 VALUES LESS THAN (to_days('2008-01-01'))
, partition p014 VALUES LESS THAN (to_days('2008-02-01'))
, partition p015 VALUES LESS THAN (to_days('2008-03-01'))
, partition p016 VALUES LESS THAN (to_days('2008-04-01'))
, partition p017 VALUES LESS THAN (to_days('2008-05-01'))
, partition p018 VALUES LESS THAN (to_days('2008-06-01'))
, partition p019 VALUES LESS THAN (to_days('2008-07-01'))
, partition p020 VALUES LESS THAN (to_days('2008-08-01'))
, partition p021 VALUES LESS THAN (to_days('2008-09-01'))
, partition p022 VALUES LESS THAN (to_days('2008-10-01'))
, partition p023 VALUES LESS THAN (to_days('2008-11-01'))
, partition p024 VALUES LESS THAN (to_days('2008-12-01'))
, partition p025 VALUES LESS THAN (to_days('2009-01-01'))
);
./partitions_helper --table=blog_posts --column=published --interval=month \
--start=2007-01-01 --partitions=25
./partitions_helper -t blog_posts -c published -i month -s 2007-01-01 -p 25
./partitions_helper --help
#!/usr/bin/perl
# The MySQL Partitions helper
# Copyright (C) 2008, 2009 Giuseppe Maxia
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# This program creates a ALTER TABLE statement to add or reorganize
# date based partitions for MySQL 5.1 or later
#
use strict;
use warnings;
# use diagnostics;
use English qw( ‐no_match_vars ) ;
use Getopt::Long qw(:config no_ignore_case );
use Data::Dumper;
my $VERSION = '1.0.4';
#
# Parse options are fully qualified options with descriptive help,
# parse string for the command line, and sort order for the help
#
my %parse_options = (
table => {
value => '',
parse => 't|table=s',
help => [
'The table being partitioned',
'(no default)'
],
so => 20,
},
column => {
value => '',
parse => 'c|column=s',
help => [
'The partitioning column',
'(no default)',
],
so => 30,
},
interval => {
value => 'month',
parse => 'i|interval=s',
help => [
'The interval between partitions',
'Accepted: "year", "month", "week", "day", or a number',
'(default: month) ',
],
so => 40,
},
partitions => {
value => 0,
parse => 'p|partitions=i',
help => [
'How many partitions to create',
'(default: 0) ',
],
so => 50,
},
first_partition => {
value => 1,
parse => 'first_partition=i',
help => [
'the first partition to create',
'(default: 1) ',
],
so => 60,
},
reorganize => {
value => '',
parse => 'reorganize=s',
help => [
'the partition(s) to reorganize',
'(default: none) '
],
so => 70,
},
start => {
value => '2001-01-01',
parse => 's|start=s',
help => [
'the minimum partitioning value',
'(default: 1 for numbers, 2001-01-01 for dates) '
],
so => 80,
},
end => {
value => '',
parse => 'e|end=s',
help => [
'The maximum partitioning value',
'Used unless --partitions is used',
'(no default) ',
],
so => 90,
},
function => {
value => '',
parse => 'f|function=s',
help => [
'The partitioning function to use in the "range" declaration',
'(default: to_days, unless --list is used) ',
],
so => 100,
},
list => {
value => 0,
parse => 'l|list',
help => [
'Use the COLUMNS feature (versions >= 5.5)',
'(default: no) ',
],
so => 110,
},
maxvalue => {
value => 0,
parse => 'x|maxvalue',
help => [
'Adds MAXVALUE as last partition',
'(default: disabled) ',
],
so => 115,
},
prefix => {
value => 'p',
parse => 'prefix=s',
help => [
'prefix for partition name',
'(default: p) ',
],
so => 120,
},
explain => {
value => 0,
parse => 'explain',
help => [
'show the current option values',
'(default: no) ',
],
so => 130,
},
version => {
value => 0,
parse => 'version',
help => [
'display the version',
'(default: none) ',
],
so => 400,
},
help => {
value => 0,
parse => 'h|help',
help => [
'display the help page',
'(default: no) ',
],
so => 500,
},
);
#
# convert parse options to simple options
#
my %options = map { $_ , $parse_options{$_}{'value'}} keys %parse_options;
#
# get the options from the command line
#
GetOptions (
map { $parse_options{$_}{parse}, \$options{$_} }
grep { $parse_options{$_}{parse}} keys %parse_options
) or get_help();
get_help() if $options{help};
if ($options{version}) {
print credits();
exit 0;
}
# print Dumper(\%options) ; exit;
my %valid_intervals = (
day => 1,
week => 1,
month => 1,
year => 1,
);
#
# start and end dates
#
my ($syear, $smonth, $sday) = ();
my ($eyear, $emonth, $eday) = ();
#
# deals with placeholder features
#
for my $op ( qw(operation) ) {
if ($options{$op}) {
die "option <$op> is not implemented yet\n";
}
}
#
# check that a table and column are given
#
unless ($options{table}) {
die "table name required\n";
}
unless ($options{column} or $options{reorganize} ) {
die "column name required\n";
}
#
# accept only one of --end or --partitions
#
if ($options{end} && $options{partitions}) {
die "too many quantifiers. Use EITHER '--partitions' OR '--end' \n";
}
#
# check that we parsed a valid interval
#
if ( $options{interval} =~ /^\d+$/) {
unless ($options{start} =~ /^\d+$/) {
$options{start} = 1;
}
if ($options{end}) {
unless ($options{end} =~ /^\d+$/) {
die "the end value must be a number\n";
}
if ($options{end} < $options{interval}) {
die "the end value must be bigger than the interval\n";
}
if ($options{end} <= $options{start}) {
die "the end value must be bigger than the start\n";
}
$options{partitions} = int( ($options{end} +1 - $options{start}) / $options{interval});
}
}
else {
unless (exists $valid_intervals{ $options{interval} } ) {
die "invalid interval specified: $options{interval}\n";
}
#
# for year, month, or week the function must be to_days
#
unless ($options{list}) {
$options{function} = 'to_days' unless $options{function};
}
#
# check the start date
#
if ( $options{start} =~ /(\d{4})[\-\.\/](\d+)[\-\.\/](\d+)/) {
($syear, $smonth, $sday) = ($1, $2, $3);
$smonth +=0;
$sday +=0;
#print "start $syear $smonth $sday\n";
}
else {
die "invalid date $options{start}\n";
}
#
# check the end date
#
if ($options{end}) {
if ( $options{end} =~ /(\d{4})[\-\.\/](\d+)[\-\.\/](\d+)/) {
($eyear, $emonth, $eday) = ($1, $2, $3);
$emonth +=0;
$eday +=0;
# print "end $eyear $emonth $eday\n";
}
else {
die "invalid date $options{end}\n";
}
if ($options{interval} eq 'year') {
$options{partitions} = $eyear - $syear +1;
}
elsif ($options{interval} eq 'month') {
my $months = (12 - $smonth)
+ ( ($eyear - $syear -1) * 12 )
+ $emonth + 1;
# print $months,$/;
$options{partitions} = $months;
}
elsif ($options{interval} eq 'week') {
$options{partitions} = weeks_between($syear, $smonth, $sday,
$eyear, $emonth, $eday);
}
elsif ($options{interval} eq 'day') {
$options{partitions} = days_between($syear, $smonth, $sday,
$eyear, $emonth, $eday);
}
}
}
#
# there must be a valid number of partitions
#
unless ($options{partitions} && ($options{partitions} =~ /^\d+$/) ) {
die "number of partitions required. Use EITHER '--partitions' OR '--end'\n";
}
if ($options{partitions} > 1024) {
die "too many partitions ($options{partitions}). Maximum allowed is 1024\n";
}
else {
print "# partitions: $options{partitions}\n";
}
#
# don't accept a function if COLUMS is being used
#
if ( $options{function} && $options{list} ) {
die "you must specify either list or function\n";
}
if ($options{explain}) {
show_options();
}
# print Dumper(\%options) ; exit;
# -----------------------------------------
# start building the ALTER TABLE statement
# -----------------------------------------
print "ALTER TABLE $options{table} \n";
if ($options{reorganize} ) {
print "REORGANIZE PARTITIONS $options{reorganize} INTO \n";
$options{prefix} = 'pr';
}
else {
print "PARTITION by range " ;
if ($options{function}) {
print "($options{function}(";
}
elsif ($options{list}) {
print "columns("
}
else {
print "("
}
print "$options{column}";
if ($options{function}) {
print ")";
}
print ")\n";
}
print "(\n";
make_partitions( $options{interval});
print ");\n";
# -----------------------------------------
# functions
# -----------------------------------------
sub make_partitions {
my ($interval) = @_;
my $partitions_done=0;
my $p_year = $syear;
my $p_month = $smonth;
my $p_day = $sday;
my $func_start = 'to_days('; #$options{function};
my $func_end = ")";
if ($options{list}) {
$func_start = "";
$func_end = "";
}
for my $part ($options{first_partition} ..
$options{first_partition} + $options{partitions} -1 ) {
if ($partitions_done) {
print ", ";
}
else {
print " "
}
$partitions_done++;
if ($interval =~ /^\d+$/) {
printf "partition %s%03d VALUES LESS THAN (%d)\n",
$options{prefix},
$partitions_done,
($options{start} + (($partitions_done - 1) * $interval)) + $interval;
}
else {
printf "partition %s%03d VALUES LESS THAN (%s'%4d-%02d-%02d'%s)\n",
$options{prefix},
$partitions_done,
$func_start,
$p_year,
$p_month,
$p_day,
$func_end;
if ($interval eq 'day') {
($p_year,$p_month,$p_day) = next_day($p_year, $p_month, $p_day);
}
elsif ($interval eq 'week') {
($p_year,$p_month,$p_day) = next_week($p_year, $p_month, $p_day);
}
elsif ($interval eq 'month') {
($p_year,$p_month) = next_month($p_year, $p_month);
}
elsif ($interval eq 'year') {
($p_year,$p_month) = next_year($p_year, $p_month);
}
else {
die "unsupported interval\n";
}
}
}
if ($options{'maxvalue'}) {
printf ", partition %s%03d VALUES LESS THAN (MAXVALUE)\n",
$options{prefix},
++$partitions_done;
}
}
sub next_year {
my ($y, $m) = @_;
$y++;
return ($y, $m);
}
sub next_week {
my ($y, $m, $d) = @_;
for my $i (1 .. 7) {
($y, $m, $d) = next_day($y, $m, $d);
}
return ($y, $m, $d);
}
sub next_day {
my ($y, $m, $d) = @_;
$d++;
$m += 0;
my $last_day = days_in_month($y, $m);
if ($d > $last_day) {
$d = 1;
$m++;
}
if ($m > 12) {
$m = 1;
$y++;
}
return ($y, $m, $d);
}
sub is_leap_year {
my ($y) = @_;
if (($y % 400) == 0) {
return 1;
}
elsif (($y % 100) == 0) {
return 0;
}
elsif (($y % 4) == 0) {
return 1
}
return 0
}
sub days_in_month {
my ($y, $m) = @_;
$m = $m +0;
my %last_day = (
1 => 31,
2 => 28,
3 => 31,
4 => 30,
5 => 31,
6 => 30,
7 => 31,
8 => 31,
9 => 30,
10=> 31,
11=> 30,
12=> 31,
);
if (($m ==2) and (is_leap_year($y))) {
$last_day{2} = 29;
}
return $last_day{$m};
}
sub days_to_year_end {
my ($y, $m, $d) = @_;
my $days = days_in_month($y,$m) - $d +1 ;
for my $month ( $m +1 .. 12 ) {
$days += days_in_month($y, $month);
}
return $days;
}
sub months_between {
my ($syear, $smonth,
$eyear, $emonth) = @_;
my $months = (12 - $smonth)
+ ( ($eyear - $syear -1) * 12 )
+ $emonth + 1;
return $months;
}
sub days_between {
my ($syear, $smonth, $sday,
$eyear, $emonth, $eday) = @_;
# print "start $syear, $smonth, $sday\n end $eyear, $emonth, $eday\n";
my $days =0;
if ( sprintf ("%4d%2d%2d", $eyear, $emonth, $eday)
lt
sprintf("%4d%2d%2d", $syear, $smonth, $sday) )
{
die "start interval > end interval\n";
}
while ( ($syear < $eyear)
or ( ($syear == $eyear) and ($smonth < $emonth) )
or ( ($syear == $eyear) and ($smonth == $emonth) and ($sday < $eday) )
) {
if ($syear < $eyear) {
$days += days_to_year_end($syear, $smonth, $sday);
$syear++;
$smonth=1;
$sday=1;
}
elsif ($smonth < $emonth) {
$days += days_in_month($syear, $smonth) - $sday;
($syear, $smonth) = next_month($syear, $smonth);
$sday =1;
}
elsif ($sday < $eday) {
$days += $eday - $sday +1;
$sday = $eday;
}
}
return $days;
}
sub weeks_between {
my ($syear, $smonth, $sday,
$eyear, $emonth, $eday) = @_;
my $days = days_between ($syear, $smonth, $sday,
$eyear, $emonth, $eday);
# print $days, "\n"; exit;
return int ($days / 7) +1;
}
sub next_month {
my ($y, $m) = @_;
if ($m == 12) {
$m = 1;
$y++;
}
else {
$m++;
}
return ($y, $m);
}
sub get_help {
my ($msg) = @_;
if ($msg) {
warn "[***] $msg\n\n";
}
my $HELP_MSG = q{};
for my $op (
sort { $parse_options{$a}{so} <=> $parse_options{$b}{so} }
grep { $parse_options{$_}{parse}} keys %parse_options ) {
my $param = $parse_options{$op}{parse};
my $param_str = q{ };
my ($short, $long ) = $param =~ / (?: (\w) \| )? (\S+) /x;
if ($short) {
$param_str .= q{-} . $short . q{ };
}
$long =~ s/ = s \@? / = name/x;
$long =~ s/ = i / = number/x;
$param_str .= q{--} . $long;
$param_str .= (q{ } x (40 - length($param_str)) );
my $text_items = $parse_options{$op}{help};
for my $titem (@{$text_items}) {
$HELP_MSG .= $param_str . $titem . "\n";
$param_str = q{ } x 40;
}
if (@{$text_items} > 1) {
$HELP_MSG .= "\n";
}
# $HELP_MSG .= "\n";
}
print credits(),
"syntax: $PROGRAM_NAME [options] \n",
$HELP_MSG;
exit( $msg ? 1 : 0 );
}
sub credits {
my $CREDITS =
qq( The Partition Helper, version $VERSION\n)
. qq( This program creates a ALTER TABLE statement to add or reorganize\n )
. qq( partitions for MySQL 5.1 or later\n)
. qq( (C) 2008-2010 Giuseppe Maxia\n);
return $CREDITS;
}
sub show_options {
printf "# %-20s %-20s %s\n", 'options', 'default', 'value';
printf "# %-20s %-20s %s\n", '-' x 20, '-' x 20, '-' x 20;
for my $op ( sort { $parse_options{$a}{so} <=> $parse_options{$b}{so} }
keys %parse_options) {
my $v = $options{$op};
my $d = $parse_options{$op}{value};
printf "# %-20s %-20s %s\n",
$op,
'(' . (defined $d ? $d : '') . ')',
defined $v ? $v : '' ;
}
print '# ', '-' x 62, "\n";
}
 | When I filed Bug#39197 replication breaks with large load with InnoDB, flush logs, and slave stop/start, I genuinely thought that it was a serious problem. I was a bit puzzled, to tell the truth, because the scenario that I was using seemed common enough for this bug to be found already. |
SET storage_engine=InnoDB to create the tables necessary for the test. That looked good enough to me. The script was indeed creating InnoDB tables on the master. The trouble was that the "SET" command is not replicated. Thus the slaves were using the default storage engine (i.e. MyISAM). That reduces the scenario to a case were I was splitting a transaction and trying to apply it to a non transactional table. Not a bug, as Andrei Elkin and Sveta Smirnova correctly found out, but not an easy error to spot either. | Lenz has just published an interview to Adam Donnison, Senior Web Developer in the MySQL web team. Under his watch, the site at mysql.com has been powered by MySQL 5.1 for one and half years. In production! Talk about eating your own dog food! In addition to the database server, Adam has spearheaded the test of Enterprise tools and the Query Analyzer. Excellent example, Adam! |
 | I will meet the MySQL community in Paris on November 18, 2008 at 19:30. I will speak about MySQL Proxy Wizardry, and discuss with the community about any subject that will come up. The place: 1 rue royale, 227 Bureaux de la Colline 92210 Saint-Cloud Bâtiment D 9ème étage Tel : 01 78 15 24 00 |
Just arrived to Charlottesville, VA, with Dups. We drove from Baltimore, MD, where we had an enjoyable MySQL meetup yesterday. The room was small, but packed to capacity with open source enthusiasts. Now waiting for the "opening ceremony" at 6pm, when we will fine tune tomorrow's schedule. The list of attendees is impressive. More than 100 people are registered. It seems to be a very interesting gathering. |  |
 | Three unrelated facts reminded me of a popular article about MySQL replication that I wrote long time ago. |
 | The Baltimore MySQL Meetup is scheduled for November 13th at 6pm The place: Lotame Solutions, Inc. 6085 Marshalee Drive Suite 210 Elkridge, MD 21075 |
 | Presenting a topic to a new audience is a two ways learning experience. The attendees learn what the presenter came prepared to say, and in presence of an attentive audience, the presenter can learn something on the spot. During today's Boston MySQL meetup, I got two interesting ideas aboou how to improve MySQL Sandbox. |
| The New York MySQL Meetup is scheduled for November 11th at 6pm The place: Suspenders Restaurant 111 Broadway at Thames St New York, NY 10006 212-732-5005 |
 | The Boston MySQL meetup is held on November 10th at 7pm. The place is MIT Building E51 4 Amherst St Room 372 Cambridge, MA 02142 857 205 9786 |
 | Now that Dups is the Community Relations Manager for North America, we'll start working together in the most active way. Next week, we'll be busy at meetups in New York and Baltimore, and then it will be full immersion in Charlottesville for the Open SQL Camp. |
  | What follows is the text of my "Swedish" blog. Look mom, I am blogging in Swedish! I am starting a blog in Swedish. Now, why should I do such a thing? I usually blog in English, and I don't even bother blogging in my native Italian. Most importantly, I do not speak Swedish at all. So, why bother? My friend and colleague Kaj Arnö had the gall of starting a blog in Italian, a fact that surprised me and made me envious at the same time. |
CREATE TABLE t1 ( d DATE )
PARTITION by range (to_days(d))
(
partition p001 VALUES LESS THAN (to_days('2001-01-01'))
, partition p002 VALUES LESS THAN (to_days('2001-02-01'))
, partition p003 VALUES LESS THAN (to_days('2001-03-01'))
);show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (to_days(d)) (PARTITION p001 VALUES LESS THAN (730851) ENGINE = MyISAM, PARTITION p002 VALUES LESS THAN (730882) ENGINE = MyISAM, PARTITION p003 VALUES LESS THAN (730910) ENGINE = MyISAM) */
select
PARTITION_NAME,
PARTITION_DESCRIPTION
from
information_schema.PARTITIONS
where
table_schema ='test' and table_name='t1';
+----------------+-----------------------+
| PARTITION_NAME | PARTITION_DESCRIPTION |
+----------------+-----------------------+
| p001 | 730851 |
| p002 | 730882 |
| p003 | 730910 |
+----------------+-----------------------+
select
PARTITION_NAME ,
from_days(PARTITION_DESCRIPTION) AS original_value
from
information_schema.PARTITIONS
where
table_schema ='test' and table_name='t1';
+----------------+----------------+
| PARTITION_NAME | original_value |
+----------------+----------------+
| p001 | 2001-01-01 |
| p002 | 2001-02-01 |
| p003 | 2001-03-01 |
+----------------+----------------+
select from_days(723180);
+-------------------+
| from_days(723180) |
+-------------------+
| 1980-01-01 |
+-------------------+
CREATE TABLE t1 ( d DATE )
PARTITION by range (to_days(d))
(
partition p001 VALUES LESS THAN (to_days('2001-01-01'))
, partition p002 VALUES LESS THAN (to_days('2001-02-01'))
, partition p003 VALUES LESS THAN (to_days('2001-03-01'))
);
show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (to_days(d)) (PARTITION p001 VALUES LESS THAN (730851) ENGINE = MyISAM, PARTITION p002 VALUES LESS THAN (730882) ENGINE = MyISAM, PARTITION p003 VALUES LESS THAN (730910) ENGINE = MyISAM) */
CREATE TABLE t1 ( d DATE )
PARTITION by range column_list(d)
(
partition p001 VALUES LESS THAN (column_list('2001-01-01'))
, partition p002 VALUES LESS THAN (column_list('2001-02-01'))
, partition p003 VALUES LESS THAN (column_list('2001-03-01'))
);
SHOW CREATE Table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE COLUMN_LIST(d)
(PARTITION p001 VALUES LESS THAN ( COLUMN_LIST('2001-01-01')) ENGINE = MyISAM,
PARTITION p002 VALUES LESS THAN ( COLUMN_LIST('2001-02-01')) ENGINE = MyISAM,
PARTITION p003 VALUES LESS THAN ( COLUMN_LIST('2001-03-01')) ENGINE = MyISAM) */
  | As announced by Stefan Hinz, our tireless documentation leader, we are using a new system for MySQL University. With DimDim, we can have more fluid and interactive presentations. Unlike the previous homegrown system, you don't need to download the slides, which will be shown to you by the presenter. There is no sound delay, and attendees can interact with the presentation by (integrated) chat and by graphical tools. |
 | MySQL 5.1.29 is available. This is the last RC. Yes. You heard me right. The long wait is almost over. The next release will be GA. Sharpen your tools, and get ready to use partitions, row-based replication, and the event scheduler in production without that uneasy sense of guilt that you feel when using non-GA software. |
 | A relay slave is a slave that writes to its binary log the stream of commands received from the master, thus enabling other slaves to replicate. This is often used to relieve the master from heavy traffic created by many slaves asking for binlog events. |
SET GLOBAL binlog_format=STATEMENT.SELECT @@binlog_format to make sure that all servers have the same setup. | Long time ago, I saw a blog post by Jorge Bernal, with a simple implementation of SIGNAL for MySQL stored procedures. If you have ever tried to write MySQL stored procedures, you know how dearly missed is this feature. I discussed this feature internally, and everyone told me "don't bother, we're going to implement SIGNAL in MySQL 6.1". And indeed, the full implementation for SIGNAL and RESIGNAL is in the roadmap. |
delimiter //
drop procedure if exists less_than_10 //
create procedure less_than_10(i int )
deterministic
begin
if ( i < 10)
then
set @comment = concat('a valid number was entered (', i,')');
else
set @comment = concat('number too high (', i,')') ;
SIGNAL ;
end if;
select concat('everything fine [',@comment,']') as comment;
end//
drop procedure if exists trapping //
create procedure trapping ()
deterministic
begin
declare mystatus varchar(20) default 'ok';
declare continue handler for SQLSTATE '38503'
set mystatus = 'ERROR';
call less_than_10(7);
select mystatus, @comment;
call less_than_10(17);
select mystatus, @comment;
select 'and this is after the error';
end //
delimiter ;
call less_than_10(2);
+--------------------------------------------------+
| comment |
+--------------------------------------------------+
| everything fine [a valid number was entered (2)] |
+--------------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
select @comment;
+--------------------------------+
| @comment |
+--------------------------------+
| a valid number was entered (2) |
+--------------------------------+
1 row in set (0.00 sec)
call less_than_10(12);
ERROR 1340 (38503): Exception generated from user-defined function/procedure
select @comment;
+----------------------+
| @comment |
+----------------------+
| number too high (12) |
+----------------------+
1 row in set (0.00 sec)
call trapping();
+--------------------------------------------------+
| comment |
+--------------------------------------------------+
| everything fine [a valid number was entered (7)] |
+--------------------------------------------------+
1 row in set (0.00 sec)
+----------+--------------------------------+
| mystatus | @comment |
+----------+--------------------------------+
| ok | a valid number was entered (7) |
+----------+--------------------------------+
1 row in set (0.00 sec)
+----------+----------------------+
| mystatus | @comment |
+----------+----------------------+
| ERROR | number too high (17) |
+----------+----------------------+
1 row in set (0.00 sec)
+-----------------------------+
| and this is after the error |
+-----------------------------+
| and this is after the error |
+-----------------------------+
1 row in set (0.00 sec)
| The Open SQL Camp will take place in Charlottesville, VA, USA, on November 14, 15, and 16. Attendees are requested to register in the event's Wiki, and if you are interested in presenting something, there is a mailing list to discuss your intended topics. |
 | Two of the most common tasks for database administrators are cleaning the process list from unresponsive queries and remove idle connections that are filling the connection pool. Both tasks are related to poor usage of the database. In a perfect world, users would only run queries designed, tested, and benchmarked by the DBA or the project manager, and the application servers would never allocate more connections than planned. |
Notice that you can disable an idle connection by setting the variable interactive_timeout, but this method allows you to be more precise. For example you can state that only idle connections to a given database should be killed, or only the ones from a given user.drop procedure if exists purge_slow_queries;
drop procedure if exists purge_idle_connections;
drop event if exists auto_purge_slow_queries;
drop event if exists auto_purge_idle_connections;
delimiter //
create procedure purge_idle_connections()
deterministic
begin
declare done boolean default false;
declare max_time int default coalesce(@max_kill_time, 200);
declare pid bigint;
declare c cursor for
SELECT id
FROM information_schema.processlist
WHERE command in ('Sleep')-- add more conditions hereAND time > max_time;
declare continue handler for not found
set done = true;
open c;
set @q_kill = 'KILL ?';
prepare q_kill from @q_kill;
PURGELOOP: loop
fetch c into pid;
if done then
leave PURGELOOP;
end if;
set @pid = pid;
execute q_kill using @pid;
end loop;
deallocate prepare q_kill;
end//
create procedure purge_slow_queries()
deterministic
begin
declare done boolean default false;
declare max_time int default coalesce(@max_kill_time, 200);
declare pid bigint;
declare c cursor for
SELECT id
FROM information_schema.processlist
WHERE state in ('executing')-- add more conditions hereAND time > max_time;
declare continue handler for not found
set done = true;
open c;
set @q_kill = 'KILL ?';
prepare q_kill from @q_kill;
PURGELOOP: loop
fetch c into pid;
if done then
leave PURGELOOP;
end if;
set @pid = pid;
execute q_kill using @pid;
end loop;
deallocate prepare q_kill;
end//
delimiter ;
create event auto_purge_idle_connections
on schedule every 10 second
do call purge_idle_connections();
create event auto_purge_slow_queries
on schedule every 10 second
do call purge_slow_queries();
 | Followers of my blogs and talks know that I am partial to the Federated engine. Not much as a normal way of storing data, but more as an auxiliary device to create innovative schemes. A few years ago I wrote an article, Federated: the missing manual where I listed most of the gotchas that you can meet when using the Federated engine. This article alone would be enough to discourage you from using Federated in production. If you attempt to use a Federated table like a normal one, your server will suffer, and possibly crash. |
 | The Large Synoptic Survey Telescope is making news once more. Its projected data inflow of 30 Terabyte per night has caught to the imagination of slashdot readers. Why is this interesting? Because it was not news to me. |
INSERT IGNORE INTO trickyTable (id) VALUES (1);
Query OK, 0 rows affected (0.00 sec)
SELECT * FROM trickyTable;
Empty set (0.00 sec)
create table trickytable (id int)
partition by list (id) (partition p1 values in (2));
#or
create table trickytable (id int)
partition by range (id) (partition p1 values less than (1));
INSERT INTO trickytable (id) VALUES (1);
ERROR 1526 (HY000): Table has no partition for value 1
create table special_products (
part_no int not null,
category_id int not null,
primary key (part_no, category_id)
) engine = InnoDB
partition by list (category_id)
(
partition p1 values in (10, 25, 40, 53)
);
insert into special_products values (1, 53), (2, 25);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
insert into special_products values (1, 23);
ERROR 1526 (HY000): Table has no partition for value 23
alter table special_products
reorganize partition p1 into
(
partition p1 values in (10, 23, 25, 40, 53)
);
alter table special_products
reorganize partition p1 into
(
partition p1 values in (10, 23, 25),
partition p2 values in (40, 53)
);
 | In addition to participating to the reviewing committee for the Users Conference 2009, I have also ambitions of being there as a speaker. Thus I am thinking about the topics I could talk about. Being the theme of the conference "innovation everywhere", I will not propose any of my previous talks (as I hope everyone else will, since I will strike all the carbon copy proposals). |
create table t1 (
id int not null primary key,
c char(10)
) engine=revision comment="InnoDB:DOUBLE";
show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t1_revision |
+----------------+
desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| c | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
desc t1_revision;
+--------------------+---------------------+------+-----+
| Field | Type | Null | Key |
+--------------------+---------------------+------+-----+
| id | int(11) | NO | PRI |
| c | char(10) | YES | |
| revision_id | int(10) unsigned | NO | PRI |
| revision_timestamp | timestamp | NO | |
| revision_deleted | tinyint(3) unsigned | NO | |
+--------------------+---------------------+------+-----+
insert into t1 (id,c) values (1, 'aaa'), (2, 'bbb');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
insert into t1 (id, c) values (3, 'ccc'), (4, 'ddd');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
select * from t1;
+----+------+
| id | c |
+----+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | ddd |
+----+------+
4 rows in set (0.00 sec)
select * from t1_revision;
Empty set (0.00 sec)
update t1 set c ='changed' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
select * from t1;
+----+---------+
| id | c |
+----+---------+
| 1 | aaa |
| 2 | bbb |
| 3 | changed |
| 4 | ddd |
+----+---------+
4 rows in set (0.00 sec)
show variables like '%revision%';
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| revision_select_mode | current |
+----------------------+---------+
1 row in set (0.00 sec)
select * from t1_revision;
+----+------+-------------+---------------------+------------------+
| id | c | revision_id | revision_timestamp | revision_deleted |
+----+------+-------------+---------------------+------------------+
| 3 | ccc | 1 | 2008-09-30 05:45:49 | 0 |
+----+------+-------------+---------------------+------------------+
1 row in set (0.00 sec)
delete from t1 where id = 2;
Query OK, 1 row affected (0.01 sec)
select * from t1_revision;
+----+------+-------------+---------------------+------------------+
| id | c | revision_id | revision_timestamp | revision_deleted |
+----+------+-------------+---------------------+------------------+
| 3 | ccc | 1 | 2008-09-30 05:45:49 | 0 |
+----+------+-------------+---------------------+------------------+
1 row in set (0.00 sec)
set revision_select_mode = 'deleted';
Query OK, 0 rows affected (0.01 sec)
select * from t1_revision;
+----+------+-------------+---------------------+------------------+
| id | c | revision_id | revision_timestamp | revision_deleted |
+----+------+-------------+---------------------+------------------+
| 2 | bbb | 1 | 2008-09-30 05:47:14 | 1 |
+----+------+-------------+---------------------+------------------+
1 row in set (0.00 sec)
select * from t1;
+----+---------+
| id | c |
+----+---------+
| 1 | aaa |
| 3 | changed |
| 4 | ddd |
+----+---------+
3 rows in set (0.00 sec)
 | MySQL 5.1 introduces row based replication, a way of replicating data that fixes many inconsistencies of the statement based replication, the standard method used by MySQL so far. |
 | Have you ever used subqueries with MySQL? It's an addition introduced in version 4.1, and since then neglected. The performance of subqueries in MySQL 4.1 and 5.x is really a sad story. For example, using the Employees test database, you may try this query: |
+-------+------------+------------+
| title | from_date | to_date |
+-------+------------+------------+
| Staff | 1990-01-22 | 1996-11-09 |
+-------+------------+------------+
1 row in set (7.88 sec)
select title, from_date, to_date
from titles
where emp_no in (
select emp_no
from employees
where first_name = 'Mary'
and last_name = 'Sluis');
+-------+------------+------------+
| title | from_date | to_date |
+-------+------------+------------+
| Staff | 1990-01-22 | 1996-11-09 |
+-------+------------+------------+
1 row in set (0.13 sec)
explain extended select title, from_date, to_date
from titles
where emp_no in (
select emp_no from employees
where first_name = 'Mary'
and last_name = 'Sluis')\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: employees
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 300024
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: titles
type: ref
possible_keys: PRIMARY,emp_no
key: emp_no
key_len: 4
ref: employees.employees.emp_no
rows: 1
filtered: 100.00
Extra:
2 rows in set, 1 warning (0.00 sec)
show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `employees`.`titles`.`title` AS
`title`,`employees`.`titles`.`from_date` AS `from_date`,
`employees`.`titles`.`to_date` AS `to_date`
from `employees`.`employees`
join `employees`.`titles`
where ((`employees`.`titles`.`emp_no` = `employees`.`employees`.`emp_no`)
and (`employees`.`employees`.`last_name` = 'Sluis')
and (`employees`.`employees`.`first_name` = 'Mary'))
select `employees`.`titles`.`title` AS `title`,
`employees`.`titles`.`from_date` AS `from_date`,
`employees`.`titles`.`to_date` AS `to_date`
from `employees`.`titles`
where (`employees`.`titles`.`emp_no`,
(((`employees`.`titles`.`emp_no`)
in employees on PRIMARY
where ((`employees`.`employees`.`last_name` = 'Sluis')
and (`employees`.`employees`.`first_name` = 'Mary')
and ((`employees`.`titles`.`emp_no`) = `employees`.`employees`.`emp_no`)))))
#! /bin/sh
path=`dirname $0`
. "$path/SETUP.sh"
extra_flags=" $fast_cflags $max_cflags -g"
extra_configs=" --with-plugins=innobase,archive,blackhole,csv,partition "
extra_configs=" $extra_configs --without-falcon "
extra_configs=" $extra_configs --without-maria-storage-engine "
. "$path/FINISH.sh"
