WL#4030: Deprecate RENAME DATABASE: replace with ALTER DATABASE <name> UPGRADE
Affects: Server-5.1 — Status: Complete — Priority: Low
RENAME DATABASE was originally introduced to support the new object-name-to-file-name encoding, as added by WL#1324. It does not maintain proper object locking and has other issues. This is reflected in the following bug reports: BUG#28360 BUG#17565 BUG#30099 BUG#21741 Also RENAME DATABASE currently has the following problems, not reported as bugs: * leads to deadlocks * leaves inconsistent data in case of a failure * doesn't update triggers that happen to say 'create trigger after insert on dbname.tablename', not just 'create trigger after insert on tablename', Rename fails with an error if there is such a trigger. * doesn't work if there is at least one view in the database, fails with ER_CHANGING_SCHEMA_IS_NOT_ALLOWED. * doesn't update fully qualified object references in bodies of functions, procedures, events * doesn't move temporary tables * doesn't invalidate caches of prepared statements, stored procedures, functions * ignores GLOBAL READ LOCK. Complete support for RENAME DATABASE was not planned in version 5.1. Since 5.1 is approaching its GA status, we can not implement this feature fully or fix the reported bugs. It will also require significant changes in data dictionary implementation and meta-data locking, if to be done properly. Therefore, as is suggested in this worklog, the problematic syntax is phased out and replaced with syntax that still supports the needs of WL#1324 and mysqlcheck client program, but does not allow for usage that leads to bugs. We're removing an incomplete feature from the server. The scenario in which RENAME DATABASE is employed by mysqlcheck is the following: - if there is a database name that contains special characters, such as, e.g., '-' - this database name is converted to the new format, in which these characters are replaced with encoded sequence in ASCII, as per WL#1324 In order to support this task, a new syntax is introduced: ALTER DATABASE <name> UPGRADE DATA DIRECTORY NAME The semantics of this statement is: - if the <name> is not in the encoding of WL#1324, it is converted to the encoding of WL#1324 The current implementation of RENAME DATABASE (See WL#757) will be used.
1) The statement: RENAME DATABASE <old> TO <new> is *removed* from 5.1, instead of being deprecated. Rationale: - The very existence of this statement is the root cause of the issue, keeping it won't solve the concerns about it's implementation - This statement is used internally only, by the upgrade process - This statement was introduced in 5.1 only, there is no need to support it for backward compatibility. 2) A new statement is implemented: ALTER DATABASE <old_50_name> UPGRADE DATA DIRECTORY NAME Rationale: - The ALTER DATABASE syntax is semantically closer to the operation performed - only 1 name is needed. Providing 2 names (old and new) caused the issues reported, since with 2 names the implementation can be leveraged to not only change the database name encoding, but also the database logical name, which is not fully supported. - UPGRADE DATA DIRECTORY NAME has been chosen since it only uses already existing keywords, while describing the operation performed.
Simple syntax change in the parser, reusing the existing implementation for the runtime part. No detail design needed.
Copyright (c) 2000, 2019, Oracle Corporation and/or its affiliates. All rights reserved.