diff options
| -rw-r--r-- | Docs/manual.texi | 158 | ||||
| -rw-r--r-- | mysql-test/r/delayed.result | 5 | ||||
| -rw-r--r-- | mysql-test/t/delayed.test | 14 | ||||
| -rw-r--r-- | scripts/mysqldumpslow.sh | 162 | ||||
| -rw-r--r-- | sql/field_conv.cc | 3 | ||||
| -rw-r--r-- | sql/sql_insert.cc | 20 |
6 files changed, 249 insertions, 113 deletions
diff --git a/Docs/manual.texi b/Docs/manual.texi index 4f3946b4741..00fca7457a8 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -469,6 +469,11 @@ Functions for Use in @code{SELECT} and @code{WHERE} Clauses * Silent column changes:: Silent column changes +@code{INSERT} Syntax + +* INSERT SELECT:: +* INSERT DELAYED:: + @code{SHOW} Syntax (Get Information About Tables, Columns,...) * SHOW DATABASE INFO:: @@ -510,19 +515,19 @@ BDB or Berkeley_db Tables * BDB TODO:: * BDB errors:: -GEMINI tables +GEMINI Tables -* GEMINI overview:: -* GEMINI start:: -* GEMINI features:: -* GEMINI TODO:: +* GEMINI overview:: +* GEMINI start:: +* GEMINI features:: +* GEMINI TODO:: INNOBASE Tables * INNOBASE overview:: -* INNOBASE start:: -* Using INNOBASE tables:: -* INNOBASE restrictions:: +* INNOBASE start:: INNOBASE startup options +* Using INNOBASE tables:: Using INNOBASE tables +* INNOBASE restrictions:: Some restrictions on @code{INNOBASE} tables: MySQL Tutorial @@ -590,7 +595,7 @@ Replication in MySQL * Replication Options:: Replication Options in my.cnf * Replication SQL:: SQL Commands related to replication * Replication FAQ:: Frequently Asked Questions about replication -* Troubleshooting Replication:: Troubleshooting Replication +* Troubleshooting Replication:: Troubleshooting Replication. Troubleshooting Replication. Troubleshooting Replication Getting Maximum Performance from MySQL @@ -6154,7 +6159,9 @@ source tree: @itemize @item -Download @strong{BitKeeper} from @uref{http://www.bitmover.com/cgi-bin/download.cgi}. +Download @strong{BitKeeper} from +@uref{http://www.bitmover.com/cgi-bin/download.cgi}. You will need +@strong{Bitkeeper} 2.0 or newer to access our repository. @item Follow the instructions to install it. @item @@ -10810,7 +10817,7 @@ other contexts, however. @strong{MySQL} doesn't yet support the Oracle SQL extension: @code{SELECT ... INTO TABLE ...}. @strong{MySQL} supports instead the ANSI SQL syntax @code{INSERT INTO ... SELECT ...}, which is basically -the same thing. @xref{INSERT}. +the same thing. @xref{INSERT SELECT}. @example INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE @@ -10902,7 +10909,7 @@ still allowed to happen. The new inserted records will not be seen by any of the clients that have a @code{READ} lock until they release their read locks. With @code{INSERT DELAYED} you can queue inserts into a local queue, until the locks are released, without having the client wait for the insert -to complete. +to complete. @xref{INSERT DELAYED}. ``Atomic,'' in the sense that we mean it, is nothing magical. It only means that you can be sure that while each specific update is running, no other @@ -19635,12 +19642,13 @@ mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15); @item If you specify the keyword @code{LOW_PRIORITY}, execution of the -@code{INSERT} is delayed until no other clients are reading from the table. -In this case the client has to wait until the insert statement is completed, -which may take a long time if the table is in heavy use. This is in -contrast to @code{INSERT DELAYED}, which lets the client continue at once. -Note that @code{LOW_PRIORITY} should normally not be used with @code{MyISAM} -tables as this disables concurrent inserts.@xref{MyISAM}. +@code{INSERT} is delayed until no other clients are reading from the +table. In this case the client has to wait until the insert statement +is completed, which may take a long time if the table is in heavy +use. This is in contrast to @code{INSERT DELAYED}, which lets the client +continue at once. @xref{INSERT DELAYED}. Note that @code{LOW_PRIORITY} +should normally not be used with @code{MyISAM} tables as this disables +concurrent inserts. @xref{MyISAM}. @item If you specify the keyword @code{IGNORE} in an @code{INSERT} with many value @@ -19662,32 +19670,6 @@ with the @code{mysql_insert_id} function. @xref{mysql_insert_id, , @code{mysql_insert_id()}}. @end itemize -With @code{INSERT ... SELECT} statement you can quickly insert many rows -into a table from one or many tables. - -@example -INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE -tblTemp1.fldOrder_ID > 100; -@end example - -The following conditions hold for an @code{INSERT ... SELECT} statement: - -@itemize @minus -@item -The query cannot contain an @code{ORDER BY} clause. - -@item -The target table of the @code{INSERT} statement cannot appear in the -@code{FROM} clause of the @code{SELECT} part of the query because it's -forbidden in ANSI SQL to @code{SELECT} from the same table into which you are -inserting. (The problem is that the @code{SELECT} possibly would -find records that were inserted earlier during the same run. When using -sub-select clauses, the situation could easily be very confusing!) - -@item -@code{AUTO_INCREMENT} columns work as usual. -@end itemize - @findex mysql_info() If you use @code{INSERT ... SELECT} or an @code{INSERT ... VALUES} statement with multiple value lists, you can use the C API function @@ -19728,17 +19710,76 @@ Inserting a value into a date or time column that is illegal for the column type. The column is set to the appropriate zero value for the type. @end itemize +@findex REPLACE ... SELECT +@findex INSERT ... SELECT +@menu +* INSERT SELECT:: +* INSERT DELAYED:: +@end menu + +@node INSERT SELECT, INSERT DELAYED, INSERT, INSERT +@subsection INSERT ... SELECT Syntax + +@example +INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT ... +@end example + +With @code{INSERT ... SELECT} statement you can quickly insert many rows +into a table from one or many tables. + +@example +INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE +tblTemp1.fldOrder_ID > 100; +@end example + +The following conditions hold for an @code{INSERT ... SELECT} statement: + +@itemize @minus +@item +The query cannot contain an @code{ORDER BY} clause. + +@item +The target table of the @code{INSERT} statement cannot appear in the +@code{FROM} clause of the @code{SELECT} part of the query because it's +forbidden in ANSI SQL to @code{SELECT} from the same table into which you are +inserting. (The problem is that the @code{SELECT} possibly would +find records that were inserted earlier during the same run. When using +sub-select clauses, the situation could easily be very confusing!) + +@item +@code{AUTO_INCREMENT} columns work as usual. + +@item +You can use the C API function @code{mysql_info()} to get information about +the query. @xref{INSERT}. +@end itemize + +You can of course also use @code{REPLACE} instead of @code{INSERT} to +overwrite old rows. + @findex INSERT DELAYED @findex DELAYED +@cindex INSERT DELAYED +@node INSERT DELAYED, , INSERT SELECT, INSERT +@subsection @code{INSERT DELAYED} syntax -The @code{DELAYED} option -for the -@code{INSERT} statement is a @strong{MySQL}-specific option that is very -useful if you have clients that can't wait for the @code{INSERT} to complete. -This is a common problem when you use @strong{MySQL} for logging and you also -periodically run @code{SELECT} statements that take a long time to complete. -@code{DELAYED} was introduced in @strong{MySQL} Version 3.22.15. It is a -@strong{MySQL} extension to ANSI SQL92. +@example +INSERT DELAYED ... +@end example + +The @code{DELAYED} option for the @code{INSERT} statement is a +@strong{MySQL}-specific option that is very useful if you have clients +that can't wait for the @code{INSERT} to complete. This is a common +problem when you use @strong{MySQL} for logging and you also +periodically run @code{SELECT} and @code{UPDATE} statements that take a +long time to complete. @code{DELAYED} was introduced in @strong{MySQL} +Version 3.22.15. It is a @strong{MySQL} extension to ANSI SQL92. + +@code{INSERT DELAYED} only works with @code{ISAM} and @code{MyISAM} +tables. Note that as @code{MyISAM} tables supports concurrent +@code{SELECT} and @code{INSERT}, if there is no empty blocks in the data +file, you very seldom need to use @code{INSERT DELAYED} with +@code{MyISAM}. When you use @code{INSERT DELAYED}, the client will get an OK at once and the row will be inserted when the table is not in use by any other thread. @@ -23533,10 +23574,10 @@ not trivial). @section GEMINI Tables @menu -* GEMINI overview:: -* GEMINI start:: -* GEMINI features:: -* GEMINI TODO:: +* GEMINI overview:: +* GEMINI start:: +* GEMINI features:: +* GEMINI TODO:: @end menu @node GEMINI overview, GEMINI start, GEMINI, GEMINI @@ -26572,7 +26613,7 @@ tables}. * Replication Options:: Replication Options in my.cnf * Replication SQL:: SQL Commands related to replication * Replication FAQ:: Frequently Asked Questions about replication -* Troubleshooting Replication:: Troubleshooting Replication +* Troubleshooting Replication:: Troubleshooting Replication. Troubleshooting Replication. Troubleshooting Replication @end menu @node Replication Intro, Replication Implementation, Replication, Replication @@ -42009,6 +42050,9 @@ to the @strong{MySQL} source distribution. @item Updated the documentation about @code{GEMINI} tables. @item +Fixed thread-hang-bug in @code{INSERT DELAYED} when inserting +@code{NULL} into an @code{AUTO_INCREMENT} column. +@item @code{REPLACE} will not replace a row that conflicts with an @code{auto_increment} generated key. @item diff --git a/mysql-test/r/delayed.result b/mysql-test/r/delayed.result index c3281ce5330..d1914341a8d 100644 --- a/mysql-test/r/delayed.result +++ b/mysql-test/r/delayed.result @@ -3,3 +3,8 @@ a tmsp 5 19711006010203 6 19711006010203 8 19711006010203 +a b +1 b +2 c +3 d +4 e diff --git a/mysql-test/t/delayed.test b/mysql-test/t/delayed.test index 15a85a09b8d..4bc1afa7612 100644 --- a/mysql-test/t/delayed.test +++ b/mysql-test/t/delayed.test @@ -15,3 +15,17 @@ insert into t1 set a = 8,tmsp=19711006010203; select * from t1 where tmsp=0; select * from t1 where tmsp=19711006010203; drop table t1; + +# +# Test bug when inserting NULL into an auto_increment field with +# INSERT DELAYED +# + +create table t1 (a int not null auto_increment primary key, b char(10)); +insert delayed into t1 values (1,"b"); +insert delayed into t1 values (null,"c"); +insert delayed into t1 values (3,"d"),(null,"e"); +--error 1136 +insert delayed into t1 values (3,"this will give an","error"); +select * from t1; +drop table t1; diff --git a/scripts/mysqldumpslow.sh b/scripts/mysqldumpslow.sh index b0e63dd73c1..02b5d5cd0cd 100644 --- a/scripts/mysqldumpslow.sh +++ b/scripts/mysqldumpslow.sh @@ -1,6 +1,9 @@ #!@PERL@ # mysqldumpslow - parse and summarize the MySQL slow query log +# Original version by Tim Bunce, sometime in 2000. +# Further changes by Tim Bunce, 8th March 2001. + use strict; use Getopt::Long; @@ -8,69 +11,128 @@ use Getopt::Long; # at, al, and ar are the corresponding averages my %opt = ( - s => 'at', - h => '*', + s => 'at', + h => '*', ); GetOptions(\%opt, - 'v+', # verbose - 'd+', # debug - 's=s', # what to sort by (t, at, l, al, r, ar etc) - 'a!', # don't abstract all numbers to N and strings to 'S' - 'g=s', # grep: only consider stmts that include this string - 'h=s', # hostname of db server (can be wildcard) + 'v+', # verbose + 'd+', # debug + 's=s', # what to sort by (t, at, l, al, r, ar etc) + 'r!', # reverse the sort order (largest last instead of first) + 't=i', # just show the top n queries + 'a!', # don't abstract all numbers to N and strings to 'S' + 'n=i', # abstract numbers with at least n digits within names + 'g=s', # grep: only consider stmts that include this string + 'h=s', # hostname of db server for *-slow.log filename (can be wildcard) + 'i=s', # name of server instance (if using mysql.server startup script) + 'l!', # don't subtract lock time from total time ) or die "Bad option"; -my %stmt; - -my $datadir = "/var/lib/mysql"; # XXX should fetch dynamically -@ARGV = <$datadir/$opt{h}-slow.log>; - -$/ = "\n#"; # read entire statements using paragraph mode -while (<>) { - print "[$_]\n" if $opt{v}; - s/^#// unless %stmt; - - s/\s*Time: (\d+) Lock_time: (\d+) Rows_sent: (\d+).*\n//; - my ($t, $l, $r) = ($1, $2, $3); - s/^use \w+;\n//; # not consistently added - s/^SET timestamp=\d+;\n//; - - s/^[ ]*\n//mg; # delete blank lines - s/^[ ]*/ /mg; # normalize leading whitespace - s/\s*;\s*(#\s*)?$//; # remove traing semicolon(+newline-hash) - - next if $opt{g} and !m/$opt{g}/i; - - unless ($opt{a}) { - s/\b\d+\b/N/g; - s/\b0x[0-9A-Fa-f]+\b/N/g; - s/'.*?'/'S'/g; - s/".*?"/"S"/g; - } +unless (@ARGV) { + my $defaults = `my_print_defaults mysqld`; + my $basedir = ($defaults =~ m/--basedir=(.*)/)[0] + or die "Can't determine basedir from 'my_print_defaults mysqld' output: $defaults"; + warn "basedir=$basedir\n" if $opt{v}; + + my $datadir = ($defaults =~ m/--datadir=(.*)/)[0]; + if (!$datadir or $opt{i}) { + # determine the datadir from the instances section of /etc/my.cnf, if any + my $instances = `my_print_defaults instances`; + die "Can't determine datadir from 'my_print_defaults mysqld' output: $defaults" + unless $instances; + my @instances = ($instances =~ m/^--(\w+)-/mg); + die "No -i 'instance_name' specified to select among known instances: @instances.\n" + unless $opt{i}; + die "Instance '$opt{i}' is unknown (known instances: @instances)\n" + unless grep { $_ eq $opt{i} } @instances; + $datadir = ($instances =~ m/--$opt{i}-datadir=(.*)/)[0] + or die "Can't determine --$opt{i}-datadir from 'my_print_defaults instances' output: $instances"; + warn "datadir=$datadir\n" if $opt{v}; + } + + @ARGV = <$datadir/$opt{h}-slow.log>; + die "Can't find '$datadir/$opt{h}-slow.log'\n" unless @ARGV; +} - $stmt{$_}->{c} += 1; - $stmt{$_}->{t} += $t; - $stmt{$_}->{l} += $l; - $stmt{$_}->{r} += $r; +warn "\nReading mysql slow query log from @ARGV\n"; - warn "[$_]" if $opt{d}; +my @pending; +my %stmt; +$/ = ";\n#"; # read entire statements using paragraph mode +while ( defined($_ = shift @pending) or defined($_ = <>) ) { + warn "[[$_]]\n" if $opt{d}; # show raw paragraph being read + + my @chunks = split /^\/.*Version.*started with[\000-\377]*?Time.*Id.*Command.*Argument.*\n/m; + if (@chunks > 1) { + unshift @pending, map { length($_) ? $_ : () } @chunks; + warn "<<".join(">>\n<<",@chunks).">>" if $opt{d}; + next; + } + + s/^#? Time: \d{6}\s+\d+:\d+:\d+.*\n//; + my ($user,$host) = s/^#? User\@Host:\s+(\S+)\s+\@\s+(\S+).*\n// ? ($1,$2) : ('',''); + + s/^# Time: (\d+) Lock_time: (\d+) Rows_sent: (\d+).*\n//; + my ($t, $l, $r) = ($1, $2, $3); + $t -= $l unless $opt{l}; + + # remove fluff that mysqld writes to log when it (re)starts: + s!^/.*Version.*started with:.*\n!!mg; + s!^Tcp port: \d+ Unix socket: \S+\n!!mg; + s!^Time.*Id.*Command.*Argument.*\n!!mg; + + s/^use \w+;\n//; # not consistently added + s/^SET timestamp=\d+;\n//; + + s/^[ ]*\n//mg; # delete blank lines + s/^[ ]*/ /mg; # normalize leading whitespace + s/\s*;\s*(#\s*)?$//; # remove trailing semicolon(+newline-hash) + + next if $opt{g} and !m/$opt{g}/io; + + unless ($opt{a}) { + s/\b\d+\b/N/g; + s/\b0x[0-9A-Fa-f]+\b/N/g; + s/'.*?'/'S'/g; + s/".*?"/"S"/g; + # -n=8: turn log_20001231 into log_NNNNNNNN + s/([a-z_]+)(\d{$opt{n},})/$1.('N' x length($2))/ieg if $opt{n}; + # abbreviate massive "in (...)" statements and similar + s!(([NS],){100,})!sprintf("$2,{repeated %d times}",length($1)/2)!eg; + } + + my $s = $stmt{$_} ||= { users=>{}, hosts=>{} }; + $s->{c} += 1; + $s->{t} += $t; + $s->{l} += $l; + $s->{r} += $r; + $s->{users}->{$user}++ if $user; + $s->{hosts}->{$host}++ if $host; + + warn "{{$_}}\n\n" if $opt{d}; # show processed statement string } foreach (keys %stmt) { - my $v = $stmt{$_} || die; - my ($c, $t, $l, $r) = @{ $v }{qw(c t l r)}; - $v->{at} = $t / $c; - $v->{al} = $l / $c; - $v->{ar} = $r / $c; + my $v = $stmt{$_} || die; + my ($c, $t, $l, $r) = @{ $v }{qw(c t l r)}; + $v->{at} = $t / $c; + $v->{al} = $l / $c; + $v->{ar} = $r / $c; } -my @sorted = sort { $stmt{$a}->{$opt{s}} <=> $stmt{$b}->{$opt{s}} } keys %stmt; +my @sorted = sort { $stmt{$b}->{$opt{s}} <=> $stmt{$a}->{$opt{s}} } keys %stmt; +@sorted = @sorted[0 .. $opt{t}-1] if $opt{t}; +@sorted = reverse @sorted if $opt{r}; foreach (@sorted) { - my $v = $stmt{$_} || die; - my ($c, $t,$at, $l,$al, $r,$ar) = @{ $v }{qw(c t at l al r ar)}; - printf "Count: %d Time: %.2f (%d) Lock_time: %.2f (%d) Rows_sent: %.1f (%d) \n%s\n\n", - $c, $at,$t, $al,$l, $ar,$r, $_; + my $v = $stmt{$_} || die; + my ($c, $t,$at, $l,$al, $r,$ar) = @{ $v }{qw(c t at l al r ar)}; + my @users = keys %{$v->{users}}; + my $user = (@users==1) ? $users[0] : sprintf "%dusers",scalar @users; + my @hosts = keys %{$v->{hosts}}; + my $host = (@hosts==1) ? $hosts[0] : sprintf "%dhosts",scalar @hosts; + printf "Count: %d Time=%.2fs (%ds) Lock=%.2fs (%ds) Rows=%.1f (%d), $user\@$host\n%s\n\n", + $c, $at,$t, $al,$l, $ar,$r, $_; } diff --git a/sql/field_conv.cc b/sql/field_conv.cc index aae9f18e6a0..606edd84c74 100644 --- a/sql/field_conv.cc +++ b/sql/field_conv.cc @@ -133,7 +133,8 @@ set_field_to_null(Field *field) return 0; } if (!current_thd->no_errors) - my_printf_error(ER_BAD_NULL_ERROR,ER(ER_BAD_NULL_ERROR),MYF(0),field->field_name); + my_printf_error(ER_BAD_NULL_ERROR,ER(ER_BAD_NULL_ERROR),MYF(0), + field->field_name); return 1; } return 0; diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 21827d17573..8e8f46b2e1f 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -247,9 +247,12 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, List<Item> &fields, } if (lock_type == TL_WRITE_DELAYED) { - id=0; // No auto_increment id - info.copied=values_list.elements; - end_delayed_insert(thd); + if (!error) + { + id=0; // No auto_increment id + info.copied=values_list.elements; + end_delayed_insert(thd); + } } else { @@ -486,7 +489,7 @@ public: table(0),tables_in_use(0),stacked_inserts(0), status(0), dead(0), group_count(0) { - thd.user=thd.host=(char*) ""; + thd.user=0; thd.host=(char*) localhost; thd.current_tablenr=0; thd.version=refresh_version; thd.command=COM_DELAYED_INSERT; @@ -676,7 +679,7 @@ static TABLE *delayed_get_table(THD *thd,TABLE_LIST *table_list) TABLE *delayed_insert::get_local_table(THD* client_thd) { my_ptrdiff_t adjust_ptrs; - Field **field,**org_field; + Field **field,**org_field, *found_next_number_field; TABLE *copy; /* First request insert thread to get a lock */ @@ -719,11 +722,14 @@ TABLE *delayed_insert::get_local_table(THD* client_thd) adjust_ptrs=PTR_BYTE_DIFF(copy->record[0],table->record[0]); + found_next_number_field=table->found_next_number_field; for (org_field=table->field ; *org_field ; org_field++,field++) { if (!(*field= (*org_field)->new_field(copy))) return 0; (*field)->move_field(adjust_ptrs); // Point at copy->record[0] + if (*org_field == found_next_number_field) + (*field)->table->found_next_number_field= *field; } *field=0; @@ -806,14 +812,17 @@ static int write_delayed(THD *thd,TABLE *table,enum_duplicates duplic, static void end_delayed_insert(THD *thd) { + DBUG_ENTER("end_delayed_insert"); delayed_insert *di=thd->di; pthread_mutex_lock(&di->mutex); + DBUG_PRINT("info",("tables in use: %d",di->tables_in_use)); if (!--di->tables_in_use || di->thd.killed) { // Unlock table di->status=1; pthread_cond_signal(&di->cond); } pthread_mutex_unlock(&di->mutex); + DBUG_VOID_RETURN; } @@ -951,6 +960,7 @@ static pthread_handler_decl(handle_delayed_insert,arg) pthread_mutex_unlock(&di->thd.mysys_var->mutex); di->thd.proc_info=0; + DBUG_PRINT("info",("Waiting for someone to insert rows")); for ( ; ;) { int error; |