From dbe03fb0bce93ae750db6c0cd346e87bc82201ab Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 27 Jan 2001 15:33:31 -0700 Subject: fixed up lock counting code - Monty's suggestions updated manual about table lock counter fixed coredump in DROP DATABASE with long bogus name by non-root user fixed bug in handling STOP immediately after ROTATE added test case for buffer overrun on DROP DATABASE by non-root user added test case for the STOP bug in replication Docs/manual.texi: updates for Table_locks_immediate and Table_locks_waited include/thr_lock.h: fixes suggested by Monty to lock counts mysql-test/r/status.result: clean up of result for changes in the test case mysql-test/t/status.test: make it produce the same results if the had been some activity on the server already mysys/thr_lock.c: fixes suggested by Monty sql/mysqld.cc: fixes suggested by Monty sql/slave.cc: fixed bug in processing STOP event after rotate sql/sql_base.cc: fixes suggested by Monty sql/sql_parse.cc: fixed coredump in drop database with long name by a non-root user sql/sql_repl.cc: better status message --- Docs/manual.texi | 127 +++++++++++++++++++++---------------- include/thr_lock.h | 3 +- mysql-test/r/rpl000018.result | 5 ++ mysql-test/r/status.result | 4 +- mysql-test/std_data/master-bin.001 | Bin 0 -> 113 bytes mysql-test/t/overflow.test | 4 ++ mysql-test/t/rpl000018-master.opt | 1 + mysql-test/t/rpl000018-master.sh | 3 + mysql-test/t/rpl000018-slave.opt | 1 + mysql-test/t/rpl000018.test | 19 ++++++ mysql-test/t/status.test | 4 +- mysys/thr_lock.c | 2 +- sql/mysqld.cc | 1 - sql/slave.cc | 9 ++- sql/sql_base.cc | 5 +- sql/sql_parse.cc | 7 ++ sql/sql_repl.cc | 2 +- 17 files changed, 129 insertions(+), 68 deletions(-) create mode 100644 mysql-test/r/rpl000018.result create mode 100644 mysql-test/std_data/master-bin.001 create mode 100644 mysql-test/t/overflow.test create mode 100644 mysql-test/t/rpl000018-master.opt create mode 100755 mysql-test/t/rpl000018-master.sh create mode 100644 mysql-test/t/rpl000018-slave.opt create mode 100644 mysql-test/t/rpl000018.test diff --git a/Docs/manual.texi b/Docs/manual.texi index df903a22191..7d873447928 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -20285,59 +20285,64 @@ The following columns are returned: below, though the format and numbers probably differ: @example -+--------------------------+------------+ -| Variable_name | Value | -+--------------------------+------------+ -| Aborted_clients | 0 | -| Aborted_connects | 0 | -| Bytes_received | 142160923 | -| Bytes_sent | 1161910370 | -| Connections | 30022 | -| Created_tmp_disk_tables | 0 | -| Created_tmp_tables | 8988 | -| Delayed_insert_threads | 0 | -| Delayed_writes | 0 | -| Delayed_errors | 0 | -| Flush_commands | 1 | -| Handler_delete | 462604 | -| Handler_read_first | 95882 | -| Handler_read_key | 27681068 | -| Handler_read_next | 265008218 | -| Handler_read_prev | 3022500 | -| Handler_read_rnd | 36900998 | -| Handler_read_rnd_next | 252097176 | -| Handler_update | 16945404 | -| Handler_write | 66826676 | -| Key_blocks_used | 14955 | -| Key_read_requests | 90131960 | -| Key_reads | 163268 | -| Key_write_requests | 7573912 | -| Key_writes | 3780151 | -| Max_used_connections | 0 | -| Not_flushed_key_blocks | 0 | -| Not_flushed_delayed_rows | 0 | -| Open_tables | 0 | -| Open_files | 0 | -| Open_streams | 0 | -| Opened_tables | 44598 | -| Questions | 1866024 | -| Select_full_join | 0 | -| Select_full_range_join | 0 | -| Select_range | 68187 | -| Select_range_check | 0 | -| Select_scan | 31440 | -| Slave_running | OFF | -| Slow_launch_threads | 0 | -| Slow_queries | 0 | -| Sort_range | 0 | -| Sort_rows | 36650500 | -| Sort_scan | 5298 | -| Threads_cached | 0 | -| Threads_connected | 1 | -| Threads_created | 30022 | -| Threads_running | 1 | -| Uptime | 39613 | -+--------------------------+------------+ ++--------------------------+--------+ +| Variable_name | Value | ++--------------------------+--------+ +| Aborted_clients | 0 | +| Aborted_connects | 0 | +| Bytes_received | 629539 | +| Bytes_sent | 736394 | +| Connections | 62 | +| Created_tmp_disk_tables | 0 | +| Created_tmp_tables | 0 | +| Created_tmp_files | 0 | +| Delayed_insert_threads | 0 | +| Delayed_writes | 0 | +| Delayed_errors | 0 | +| Flush_commands | 1 | +| Handler_delete | 0 | +| Handler_read_first | 1 | +| Handler_read_key | 9201 | +| Handler_read_next | 0 | +| Handler_read_prev | 0 | +| Handler_read_rnd | 0 | +| Handler_read_rnd_next | 45 | +| Handler_update | 5998 | +| Handler_write | 0 | +| Key_blocks_used | 407 | +| Key_read_requests | 27683 | +| Key_reads | 407 | +| Key_write_requests | 0 | +| Key_writes | 0 | +| Max_used_connections | 60 | +| Not_flushed_key_blocks | 0 | +| Not_flushed_delayed_rows | 0 | +| Open_tables | 60 | +| Open_files | 66 | +| Open_streams | 0 | +| Opened_tables | 66 | +| Questions | 9308 | +| Select_full_join | 0 | +| Select_full_range_join | 0 | +| Select_range | 0 | +| Select_range_check | 0 | +| Select_scan | 0 | +| Slave_running | OFF | +| Slave_open_temp_tables | 0 | +| Slow_launch_threads | 0 | +| Slow_queries | 0 | +| Sort_merge_passes | 0 | +| Sort_range | 0 | +| Sort_rows | 0 | +| Sort_scan | 0 | +| Table_locks_immediate | 3183 | +| Table_locks_waited | 6030 | +| Threads_cached | 30 | +| Threads_created | 61 | +| Threads_connected | 31 | +| Threads_running | 31 | +| Uptime | 135 | ++--------------------------+--------+ @end example @cindex variables, status @@ -20400,6 +20405,12 @@ open by the slave thread @item @code{Sort_range} @tab Number of sorts that where done with ranges. @item @code{Sort_rows} @tab Number of sorted rows. @item @code{Sort_scan} @tab Number of sorts that where done by scanning the table. +@item @code{Table_locks_immediate} @tab Number of times a table lock was +acquired immediately. Available after 3.23.33. +@item @code{Table_locks_waited} @tab Number of times a table lock could not +be acquired immediately and a wait was needed. If this is high, and you +have performance problems, you should first optimize your queries, and then +either split your table(s) or use replication. Available after 3.23.33. @item @code{Threads_cached} @tab Number of threads in the thread cache. @item @code{Threads_connected} @tab Number of currently open connections. @item @code{Threads_created} @tab Number of threads created to handle connections. @@ -40814,6 +40825,9 @@ not yet 100 % confident in this code. @appendixsubsec Changes in release 3.23.33 @itemize bullet @item +Added @code{Table_locks_immediate} and @code{Table_locks_waited} status +variables +@item Fixed bug in replication that broke slave server start with existing @code{master.info}. This fixes a bug introduced in 3.23.32. @item @@ -46638,10 +46652,15 @@ for @code{BDB} tables. @xref{Internal locking}. With @code{MyISAM} tables one can freely mix @code{INSERT} and @code{SELECT} without locks (@code{Versioning}). +Starting in version 3.23.33, you can analyze the table lock contention +on your system by checkining @code{Table_locks_waited} and +@code{Table_locks_immediate} environemt variables. + Some database users claim that @strong{MySQL} cannot support near the number of concurrent users because it lacks row-level locking. This is a may be true for some specific applications, but is' not generally -true. As always this depends totally on what the application does and what is the access/update pattern of the data. +true. As always this depends totally on what the application does and what +is the access/update pattern of the data. Pros for row locking: diff --git a/include/thr_lock.h b/include/thr_lock.h index 569a09d7aa5..ffcad4a4d11 100644 --- a/include/thr_lock.h +++ b/include/thr_lock.h @@ -27,7 +27,8 @@ extern "C" { #include struct st_thr_lock; - +extern ulong locks_immediate,locks_waited ; + enum thr_lock_type { TL_IGNORE=-1, TL_UNLOCK, /* UNLOCK ANY LOCK */ TL_READ, /* Read lock */ diff --git a/mysql-test/r/rpl000018.result b/mysql-test/r/rpl000018.result new file mode 100644 index 00000000000..7c89c2ef2b2 --- /dev/null +++ b/mysql-test/r/rpl000018.result @@ -0,0 +1,5 @@ +Log_name +master-bin.001 +master-bin.002 +n +3351 diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result index 661009b6cda..8f3ad3e0d59 100644 --- a/mysql-test/r/status.result +++ b/mysql-test/r/status.result @@ -1,6 +1,6 @@ Variable_name Value -Table_locks_immediate 5 +Table_locks_immediate 0 Table_locks_waited 0 Variable_name Value -Table_locks_immediate 8 +Table_locks_immediate 3 Table_locks_waited 1 diff --git a/mysql-test/std_data/master-bin.001 b/mysql-test/std_data/master-bin.001 new file mode 100644 index 00000000000..fa30d8e5302 Binary files /dev/null and b/mysql-test/std_data/master-bin.001 differ diff --git a/mysql-test/t/overflow.test b/mysql-test/t/overflow.test new file mode 100644 index 00000000000..73554200dce --- /dev/null +++ b/mysql-test/t/overflow.test @@ -0,0 +1,4 @@ +connect (con1,localhost,boo,,test,0,mysql-master.sock); +connection con1; +-- error 1064; +drop database AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA; diff --git a/mysql-test/t/rpl000018-master.opt b/mysql-test/t/rpl000018-master.opt new file mode 100644 index 00000000000..ad2c6a647b5 --- /dev/null +++ b/mysql-test/t/rpl000018-master.opt @@ -0,0 +1 @@ +-O max_binlog_size=4096 diff --git a/mysql-test/t/rpl000018-master.sh b/mysql-test/t/rpl000018-master.sh new file mode 100755 index 00000000000..71f0f12d0c5 --- /dev/null +++ b/mysql-test/t/rpl000018-master.sh @@ -0,0 +1,3 @@ +rm -f $MYSQL_TEST_DIR/var/lib/master-bin.* +cp $MYSQL_TEST_DIR/std_data/master-bin.001 $MYSQL_TEST_DIR/var/lib/ +echo ./master-bin.001 > $MYSQL_TEST_DIR/var/lib/master-bin.index diff --git a/mysql-test/t/rpl000018-slave.opt b/mysql-test/t/rpl000018-slave.opt new file mode 100644 index 00000000000..e854f0d0bb2 --- /dev/null +++ b/mysql-test/t/rpl000018-slave.opt @@ -0,0 +1 @@ +--skip-slave-start --abort-slave-event-count=1 diff --git a/mysql-test/t/rpl000018.test b/mysql-test/t/rpl000018.test new file mode 100644 index 00000000000..44236323d1d --- /dev/null +++ b/mysql-test/t/rpl000018.test @@ -0,0 +1,19 @@ +connect (master,localhost,root,,test,0,mysql-master.sock); +connect (slave,localhost,root,,test,0,mysql-slave.sock); +connection slave; +reset slave; +slave start; +connection master; +show master logs; +drop table if exists t1; +create table t1(n int); +insert into t1 values (3351); +save_master_pos; +connection slave; +sync_with_master; +select * from t1; +connection master; +drop table t1; +save_master_pos; +connection slave; +sync_with_master; diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 2f65f87f29f..7c825469f09 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -1,7 +1,7 @@ connect (con1,localhost,root,,test,0,mysql-master.sock); connect (con2,localhost,root,,test,0,mysql-master.sock); -#don't polute binlog +flush status; show status like 'Table_lock%'; connection con1; SET SQL_LOG_BIN=0; @@ -20,4 +20,4 @@ unlock tables; connection con1; reap; show status like 'Table_lock%'; - +drop table t1; diff --git a/mysys/thr_lock.c b/mysys/thr_lock.c index 6f5f8afac3c..348c7bae74f 100644 --- a/mysys/thr_lock.c +++ b/mysys/thr_lock.c @@ -387,7 +387,7 @@ static my_bool wait_for_lock(struct st_lock_list *wait, THR_LOCK_DATA *data, else { result=0; - ++locks_waited; + statistic_increment(locks_waited, &THR_LOCK_lock); if (data->lock->get_status) (*data->lock->get_status)(data->status_param); check_locks(data->lock,"got wait_for_lock",0); diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 36fc5a49148..465a74c2ac5 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -260,7 +260,6 @@ ulong query_id=1L,long_query_count,long_query_time,aborted_threads, delayed_queue_size,delayed_insert_threads,delayed_insert_writes, delayed_rows_in_use,delayed_insert_errors,flush_time, thread_created; ulong filesort_rows, filesort_range_count, filesort_scan_count; -extern "C" ulong locks_immediate, locks_waited; ulong filesort_merge_passes; ulong select_range_check_count, select_range_count, select_scan_count; ulong select_full_range_join_count,select_full_join_count; diff --git a/sql/slave.cc b/sql/slave.cc index 22d76c54ecd..5a110b6921b 100644 --- a/sql/slave.cc +++ b/sql/slave.cc @@ -1055,9 +1055,12 @@ static int exec_event(THD* thd, NET* net, MASTER_INFO* mi, int event_len) break; case STOP_EVENT: - close_temporary_tables(thd); - mi->inc_pos(event_len); - flush_master_info(mi); + if(mi->pos > 4) // stop event should be ignored after rotate event + { + close_temporary_tables(thd); + mi->inc_pos(event_len); + flush_master_info(mi); + } delete ev; break; case ROTATE_EVENT: diff --git a/sql/sql_base.cc b/sql/sql_base.cc index ed7a8027079..d69bc905c31 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -32,7 +32,6 @@ TABLE *unused_tables; /* Used by mysql_test */ HASH open_cache; /* Used by mysql_test */ -extern "C" ulong locks_waited, locks_immediate; static int open_unireg_entry(THD *thd,TABLE *entry,const char *db, const char *name, const char *alias, bool locked); @@ -1167,8 +1166,8 @@ bool wait_for_tables(THD *thd) { /* Now we can open all tables without any interference */ thd->proc_info="Reopen tables"; - if(!(result=reopen_tables(thd,0,0))) - ++locks_waited; + result=reopen_tables(thd,0,0); + } pthread_mutex_unlock(&LOCK_open); thd->proc_info=0; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 495b00217ef..2926f59547f 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -1886,6 +1886,13 @@ check_access(THD *thd,uint want_access,const char *db, uint *save_priv, if (db == any_db) return FALSE; // Allow select on anything + + if (strlen(db) > NAME_LEN || check_db_name(db)) + { + net_printf(&thd->net,ER_WRONG_DB_NAME, db); + return TRUE; + } + if (db && (!thd->db || strcmp(db,thd->db))) db_access=acl_get(thd->host, thd->ip, (char*) &thd->remote.sin_addr, thd->priv_user, db); /* purecov: inspected */ diff --git a/sql/sql_repl.cc b/sql/sql_repl.cc index 4f0112bac26..0598d906aa0 100644 --- a/sql/sql_repl.cc +++ b/sql/sql_repl.cc @@ -392,7 +392,7 @@ sweepstakes if you report the bug"; thd->mysys_var->current_mutex = log_lock; thd->mysys_var->current_cond = &COND_binlog_update; const char* proc_info = thd->proc_info; - thd->proc_info = "Waiting for update"; + thd->proc_info = "Slave connection: waiting for binlog update"; pthread_mutex_unlock(&thd->mysys_var->mutex); bool read_packet = 0, fatal_error = 0; -- cgit v1.2.1