text: ############## mysql-test\t\concurrent_insert_basic.test #######################
  #                                                                              #
  # Variable Name: concurrent_insert                                             #
  # Scope: GLOBAL                                                                #
  # Access Type: Dynamic                                                         #
  # Data Type: Boolean & Numeric                                                 #
  # Default Value: 1                                                             #
  # Valid Values: 0,1 & 2                                                        #
  #                                                                              #
  #                                                                              #
  # Creation Date: 2008-03-07                                                    #
  # Author:  Salman Rawala                                                       #
  #                                                                              #
  # Modified: HHunger 2009-02-23 Inserted a wait condition right after the       #
  #                              "INSERT ..record_6" to wait for the end of      #
  #                              the insert.                                     #
  #           mleich This test needs some inporovements                          #
  #                                                                              #
  # Description: Test Cases of Dynamic System Variable "concurrent_insert"       #
  #              that checks functionality of this variable                      #
  #                                                                              #
  # Reference:                                                                   #
  #    http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html       #
  #                                                                              #
  ################################################################################
  --source include/force_myisam_default.inc
  
  # Skip the test if MyISAM is not available
  --source include/have_myisam.inc
  
  --disable_warnings
  DROP TABLE IF EXISTS t1;
  --enable_warnings
  
  #########################
  #   Creating new table  #
  #########################
  
  --echo ## Creating new table ##
  CREATE TABLE t1
  (
  name VARCHAR(30)
  );
  
  --echo '#--------------------FN_DYNVARS_018_01-------------------------#'
  ####################################################################
  #    Setting initial value of concurrent_insert to 1
  #    concurrent_insert = 1 means Enables concurrent insert
  #    for MyISAM tables that don't have holes
  ####################################################################
  
  SET @start_value= @@global.concurrent_insert;
  
  --echo ## Setting initial value of variable to 1 ##
  SET @@global.concurrent_insert = 1;
  INSERT INTO t1(name) VALUES('Record_1');
  INSERT INTO t1(name) VALUES('Record_2');
  INSERT INTO t1(name) VALUES('Record_3');
  
  --echo ## locking table ##
  LOCK TABLE t1 READ LOCAL;
  
  --echo ## Creating new connection to insert some rows in table ##
  connect (test_con1,localhost,root,,);
  --echo connection test_con1;
  connection test_con1;
  
  --echo ## New records should come at the end of all rows ##
  INSERT INTO t1(name) VALUES('Record_4');
  SELECT * FROM t1;
  
  --echo ## unlocking tables ##
  --echo connection default;
  connection default;
  UNLOCK TABLES;
  
  --echo ## deleting record to create hole in table ##
  DELETE FROM t1 WHERE name ='Record_2';
  
  
  --echo '#--------------------FN_DYNVARS_018_02-------------------------#'
  ####################################################################
  #    Setting initial value of concurrent_insert to 1
  #    concurrent_insert = 1 and trying to insert some values
  #    in MyISAM tables that have holes
  ####################################################################
  # lock table and connect with connection1
  LOCK TABLE t1 READ LOCAL;
  --echo connection test_con1;
  connection test_con1;
  
  # setting value of concurrent_insert to 1
  SET @@global.concurrent_insert=1;
  
  --echo ## send INSERT which should be blocked until unlock of the table ##
  send
  INSERT INTO t1(name) VALUES('Record_7');
  
  --echo connection default;
  connection default;
  # wait until INSERT will be locked (low performance)
  let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
                       WHERE state= "Waiting for table level lock" AND
                             info LIKE "INSERT INTO t1%";
  --source include/wait_condition.inc
  
  --echo ## show processlist info and state ##
  SELECT state,info FROM INFORMATION_SCHEMA.PROCESSLIST
  WHERE state= "Waiting for table level lock" AND info LIKE "INSERT INTO t1%";
  --echo ## table contents befor UNLOCK ##
  SELECT * FROM t1;
  UNLOCK TABLES;
  --echo ## table contens after UNLOCK ##
  SELECT * FROM t1;
  INSERT INTO t1(name) VALUES('Record_6');
  let $wait_condition= SELECT COUNT(*) = 5 FROM t1;
  --source include/wait_condition.inc
  
  --echo connection test_con1;
  connection test_con1;
  # to complete the send above^
  reap;
  SELECT * FROM t1;
  --echo connection default;
  connection default;
  
  --echo '#--------------------FN_DYNVARS_018_03-------------------------#'
  ################################################################################
  #    Setting value of concurrent_insert to 2 to verify values after inserting
  #    it into table with holes
  #    concurrent_insert = 2 means Enables concurrent insert
  #    for MyISAM tables that have holes but inserts values at the end of all rows
  ################################################################################
  
  --echo ## lock table and connect with connection1 ##
  LOCK TABLE t1 READ LOCAL;
  --echo connection test_con1;
  connection test_con1;
  --echo ## setting value of concurrent_insert to 2 ##
  SET @@global.concurrent_insert=2;
  
  --echo ## Inserting record in table, record should go at the end of the table ##
  INSERT INTO t1(name) VALUES('Record_5');
  SELECT * FROM t1;
  SELECT @@concurrent_insert;
  
  --echo connection default;
  connection default;
  
  --echo ## Unlocking table ##
  UNLOCK TABLES;
  
  SELECT * FROM t1;
  
  --echo ## Inserting new row, this should go in the hole ##
  INSERT INTO t1(name) VALUES('Record_6');
  SELECT * FROM t1;
  
  --echo ## connection test_con1 ##
  
  DELETE FROM t1 WHERE name ='Record_3';
  SELECT * FROM t1;
  
  --echo ## Dropping table ##
  DROP TABLE t1;
  
  --echo ## Disconnecting connection ##
  disconnect test_con1;
  
  SET @@global.concurrent_insert= @start_value;
  
  