DATABASE with open HANDLER"

Remove LOCK_create_db, database name locks, and use metadata locks instead.
This exposes CREATE/DROP/ALTER DATABASE statements to the graph-based
deadlock detector in MDL, and paves the way for a safe, deadlock-free
implementation of RENAME DATABASE.

Database DDL statements will now take exclusive metadata locks on
the database name, while table/view/routine DDL statements take
intention exclusive locks on the database name. This prevents race
conditions between database DDL and table/view/routine DDL.
(e.g. DROP DATABASE with concurrent CREATE/ALTER/DROP TABLE)

By adding database name locks, this patch implements
WL#4450 "DDL locking: CREATE/DROP DATABASE must use database locks" and
WL#4985 "DDL locking: namespace/hierarchical locks".

The patch also changes code to use init_one_table() where appropriate.
The new lock_table_names() function requires TABLE_LIST::db_length to
be set correctly, and this is taken care of by init_one_table().

This patch also adds a simple template to help work with 
the mysys HASH data structure.

Most of the patch was written by Konstantin Osipov.

MTR version 1 cannot start server - bootstrap reports unknown InnoDB engine

Removes --loose-skip-innodb from the list of server startup options.

Bug#52114 and Bug#50788.
The bugs themselves are regressions that are introduced
by an incomplete fix for Bug#36171 and will not be pushed.

Remove mysql_lock_have_duplicate(), since now we always
have TABLE_LIST objects for MyISAMMRG children
in lex->query_tables and keep it till the end of the
statement (sub-statement).


mysql-test/r/merge.result:
  Update results (Bug#54811).
mysql-test/t/merge-big.test:
  Update to new wait state.
mysql-test/t/merge.test:
  Add a test case for Bug#54811.
sql/lock.cc:
  Remove a function that is now unused.
sql/lock.h:
  Remove a function that is now unused.
sql/sql_base.cc:
  Don't try to search for duplicate table among THR_LOCK objects, TABLE_LIST list contains all used tables.

This bug is a consequence of WL#5349, as the
default storage engine was changed.

The fix was to explicitly add an ENGINE
clause to a CREATE TABLE statement, to
ensure that we test case preservement on
MyISAM.

The problem was that if a query accessing a view was blocked due to
conflicting locks on tables in the view definition, it would be possible
for a different connection to alter the view definition before the view
query completed. When the view query later resumed, it used the old view
definition. This meant that if the view query was later repeated inside
the same transaction, the two executions of the query would give different
results, thus breaking repeatable read. (The first query used the old
view definition, the second used the new view definition).

This bug is no longer repeatable with the recent changes to the metadata
locking subsystem (revno: 3040). The view query will no longer back-off
and release the lock on the view definiton. Instead it will wait for
the conflicting lock(s) to go away while keeping the view definition lock.
This means that it is no longer possible for a concurrent connection to
alter the view definition. Instead, any such attempt will be blocked.

In the case from the bug report where the same view query was executed
twice inside the same transaction, any ALTER VIEW from other connections
will now be blocked until the transaction has completed (or aborted).
The view queries will therefore use the same view definition and we will
have repeatable read.

Test case added to innodb_mysql_lock.test.
This patch contains no code changes.

   Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
 
   The server crashed on an attempt to optimize a MERGE table with
   non-existent child table.
 
   mysql_admin_table() relied on the table to be successfully open
   if a table object had been allocated.
 
   Changed code to check return value of the open function before
   calling a handler:: function on it.

mysql-test/r/merge.result:
  Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
      Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
      Updated result file.
mysql-test/t/merge.test:
  Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
      Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
      Changed tests to respect changed TEMPORARY MERGE locking (unrelated).
      Changed tests to respect changed CREATE TABLE ... LIKE (unrelated).
      Changed tests to respect that no new tables can be created
      under LOCK TABLE (unrelated).
      Added test for Bug#47633.
  Changed error numbers to symbolic names.
  Added test for child locking for ALTER under LOCK TABLE.
  
  Since Bug 36171 is not pushed yet, not the whole patch has been backported.
mysys/my_delete.c:
  Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
      Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
      Fixed error reporting.
      Fixed indentation.
mysys/my_mmap.c:
  Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
      Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
      Added DBUG.
sql/item_func.cc:
  Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
  Added Debug Sync point, required by merge_sync.test.
sql/sql_table.cc:
  Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
      Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
      Do not call handler:: functions if the table was not opened
      successfully.
  Added Debug Sync point, required by merge_sync.test.
storage/myisam/mi_check.c:
  Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
      Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
      Unmap memory before exchanging data files. Needed on Windows.
storage/myisammrg/ha_myisammrg.cc:
  Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
  Added Debug Sync point, required by merge_sync.test.
  
  merge_sync.test will be introduced by a patch for Bug 36171,
  which is not pushed yet.

           DML flow and SAVEPOINT

The problem was that replication could break if a transaction involving
both transactional and non-transactional tables was rolled back to a
savepoint. It broke if a concurrent connection tried to drop a
transactional table which was locked after the savepoint was set.
This DROP TABLE completed when ROLLBACK TO SAVEPOINT was executed as the
lock on the table was dropped by the transaction. When the slave later
tried to apply the binlog, it would fail as the table would already
have been dropped.

The reason for the problem is that transactions involving both
transactional and non-transactional tables are written fully to the
binlog during ROLLBACK TO SAVEPOINT. At the same time, metadata locks
acquired after a savepoint, were released during ROLLBACK TO SAVEPOINT.
This allowed a second connection to drop a table only used between
SAVEPOINT and ROLLBACK TO SAVEPOINT. Which caused the transaction binlog
to refer to a non-existing table when it was written during ROLLBACK
TO SAVEPOINT.

This patch fixes the problem by not releasing metadata locks when
ROLLBACK TO SAVEPOINT is executed if binlogging is enabled.

The assert was triggered if a connection executing TRUNCATE
on a InnoDB table was killed during open_tables.

This bug was fixed in the scope of Bug #45643
"InnoDB does not support replication of TRUNCATE TABLE".

This patch adds test coverage to innodb_mysql_sync.test.

Accidental change in compile-time definitions for FreeBSD


Revert the accidental setting of "HAVE_BROKEN_REALPATH"
on current versions of FreeBSD,

do it for both autotools ("configure.in")
and cmake ("cmake/os/FreeBSD.cmake").

------------------------------------------------------------
revno: 3672
committer: lars-erik.bjork@sun.com
branch nick: 48067-mysql-6.0-codebase-bugfixing
timestamp: Mon 2009-10-26 13:51:43 +0100
message:
  This is a patch for bug#48067
  "A temp table with the same name as an existing table, makes drop
  database fail"
        
  When dropping the database, mysql_rm_known_files() reads the contents
  of the database directory, and creates a TABLE_LIST object, for each
  .frm file encountered. Temporary tables, however, are not associated 
  with any .frm file.
        
  The list of tables to drop are passed to mysql_rm_table_part2().
  This method prefers temporary tables over regular tables, so if
  there is a temporary table with the same name as a regular, the
  temporary is removed, leaving the regular table intact.
  Regular tables are only deleted if there are no temporary tables
  with the same name.
        
  This fix ensures, that for all TABLE_LIST objects that are created
  by mysql_rm_known_files(), 'open_type' is set to 'OT_BASE_ONLY', to
  indicate that this is a regular table. In all cases in
  mysql_rm_table_part2() where we prefer a temporary table to a
  non-temporary table, we chek if 'open_type' equals 'OT_BASE_ONLY'.


mysql-test/r/temp_table.result:
  The expected result of the test.
mysql-test/t/temp_table.test:
  Test based on the bug report.
sql/sql_db.cc:
  For all TABLE_LIST objects that are created by mysql_rm_known_files(),
  'open_type' is set to 'OT_BASE_ONLY', to indicate that these are
  regular tables.
sql/sql_table.cc:
  Check if 'open_type' is set to 'OT_BASE_ONLY, every place a temporary table is
  preferred to a non-temporary table.

collections. Originally, they had only been added to default.push, so
trees named after mysql-[trunk|next-mr] would not skip those tests.

Don't call member functions for a NIL pointer.


mysql-test/r/subselect4.result:
  Add test case.
mysql-test/t/subselect4.test:
  Add test case.
sql/sql_select.cc:
  If the (virtual) member function clone_item() returns NULL,
  there is no substitution to be made, and we don't need to set the collation.
  The test was invoking Item_cache::clone_item()

            use limit efficiently
Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a
            filesort even if not required

Also two bugs reported after QA review (before the commit
of bugs above to public trees, no documentation needed):

Bug #53737: Performance regressions after applying patch
            for bug 36569
Bug #53742: UPDATEs have no effect after applying patch
            for bug 36569


Execution of single-table UPDATE and DELETE statements did not use the 
same optimizer as was used in the compilation of SELECT statements. 
Instead, it had an optimizer of its own that did not take into account 
that you can omit sorting by retrieving rows using an index.

Extra optimization has been added: when applicable, single-table 
UPDATE/DELETE statements use an existing index instead of filesort. A 
corresponding SELECT query would do the former.

Also handling of the DESC ordering expression has been added when
reverse index scan is applicable.

From now on most single table UPDATE and DELETE statements show the 
same disk access patterns as the corresponding SELECT query. We verify 
this by comparing the result of SHOW STATUS LIKE 'Sort%

Currently the get_index_for_order function 
a) checks quick select index (if any) for compatibility with the
   ORDER expression list or
b) chooses the cheapest available compatible index, but only if 
   the index scan is cheaper than filesort.
Second way is implemented by the new test_if_cheaper_ordering
function (extracted part the test_if_skip_sort_order()).



mysql-test/r/log_state.result:
  Updated result for optimized query, bug #36569.
mysql-test/r/single_delete_update.result:
  Test case for bug #30584, bug #36569 and bug #53742.
mysql-test/r/update.result:
  Updated result for optimized query, bug #30584.
  Note:
  "Handler_read_last 1" omitted, see bug 52312:
  lost Handler_read_last status variable.
mysql-test/t/single_delete_update.test:
  Test case for bug #30584, bug #36569 and bug #53742.
sql/opt_range.cc:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
  
  * get_index_for_order() has been rewritten entirely and moved
    to sql_select.cc
  
  New QUICK_RANGE_SELECT::make_reverse method has been added.
sql/opt_range.h:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
  
  * get_index_for_order() has been rewritten entirely and moved
    to sql_select.cc
  
  New functions:
  * QUICK_SELECT_I::make_reverse()
  * SQL_SELECT::set_quick()
sql/records.cc:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
  
  * init_read_record_idx() has been modified to allow reverse index scan
  
  New functions:
  * rr_index_last()
  * rr_index_desc()
sql/records.h:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
  
  init_read_record_idx() has been modified to allow reverse index scan
sql/sql_delete.cc:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
      
  mysql_delete: an optimization has been added to skip
  unnecessary sorting with ORDER BY clause where select
  result ordering is acceptable.
sql/sql_select.cc:
  Bug #30584, bug #36569, bug #53737, bug #53742:
    UPDATE/DELETE ... WHERE ... ORDER BY...  always does a filesort
    even if not required
      
  The const_expression_in_where function has been modified
  to accept both Item and Field pointers.
  
  New functions:
  * get_index_for_order()
  * test_if_cheaper_ordering() has been extracted from
    test_if_skip_sort_order() to share with get_index_for_order()
  * simple_remove_const()
sql/sql_select.h:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
      
  New functions:
  * test_if_cheaper_ordering()
  * simple_remove_const()
  * get_index_for_order()
sql/sql_update.cc:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
      
  mysql_update: an optimization has been added to skip
  unnecessary sorting with ORDER BY clause where a select
  result ordering is acceptable.
sql/table.cc:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
  
  New functions:
  * TABLE::update_const_key_parts()
  * is_simple_order()
sql/table.h:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
  
  New functions:
  * TABLE::update_const_key_parts()
  * is_simple_order()

Fix syntax error (missing space in SET command),  that effectively prevents mysqld from being build with SSL.

Handle this warning in the future as error, this will prevent pushing to main trees.

mysql-test/suite/perfschema/t/no_threads-master.opt:
  Innodb will start multiple threads, which is
  not compatible with --one-thread.   Disable
  innodb to be able to run the test case.

InnoDB came in a new version "simultanously" with the
commit.  This patch is a minor change to the new innodb
regression test suite. 

subsystem. Fix a number of caveates that the previous
implementation suffered from, including unprotected
access to shared data and lax resource accounting
(share->ref_count) that could lead to deadlocks.

The new implementation still suffers from a number
of potential deadlocks in some edge cases, and this is 
still not enabled by default. Especially since performance
testing has shown that it gives only marginable (not even 
exceeding measuring accuracy) improvements.

@todo: 
- Remove calls to close_cached_tables() with REFRESH_FAST,
and have_lock, because they break the MDL cache. 
- rework FLUSH TABLES <list> to not use close_cached_tables()
- make sure that whenever we set TABLE_SHARE::version to
0 we free MDL cache references to it.


sql/mdl.cc:
  We may cache references to TABLE_SHARE objects in 
  MDL_lock objects for tables. Create a separate
  MDL_lock class to represent a table.
sql/mdl.h:
  Adjust the MDL caching API to avoid races.
sql/sql_base.cc:
  Move all caching functionality close together.
  Implement a solution for deadlocks caused by 
  close_cached_tables() when MDL cache is enabled (incomplete).
sql/sql_yacc.yy:
  Adjust FLUSH rule to do the necessary initialization of
  TABLE_LIST elements used in for FLUSH TABLES <list>, and thus
  work OK with flush_mdl_cache() function.

The default storage engine is changed from MyISAM to
InnoDB, in all builds except for the embedded server.

In addition, the following system variables are 
changed:

  * innodb_file_per_table is enabled
  * innodb_strict_mode is enabled
  * innodb_file_format_name_update is changed
    to 'Barracuda'

The test suite is changed so that tests that do not
explicitly include the have_innodb.inc are run with
--default-storage-engine=MyISAM.  This is to ease the
transition, so that most regression tests are run
with the same engine as before.

Some tests are disabled for the embedded server
regression test, as the output of certain statements
will be different that for the regular server
(i.e SELECT @@default_storage_engine).  This is to
ease transition.



mysql-test/mysql-test-run.pl:
  The regression test suite now adds a
  --default-storage-engine=MyISAM for all non-innodb 
  tests.  This behaviour can be controlled by the
  default-myisam switch in mysql-test-run
mysql-test/t/bootstrap-master.opt:
  The bootstrap test can only be run without InnoDB
  as it starts several mysqld instances on the same
  datadir. This is possible with MyISAM, but not
  with InnoDB.
storage/innobase/CMakeLists.txt:
  Build InnoDB per default
storage/innobase/handler/ha_innodb.cc:
  Change default values for system variables
  
    Enable file_per_table
    Enable strict_mode
    Upgrade default file format to Barracuda

line exceeds the limit

Merge the fix into "trunk-bugfixing".