#At file:///ext/mysql/bzr/backup/wl4727/ based on revid:ingo.struewing@stripped5vnyom9ui52lau
2800 Rafal Somla 2009-05-04
WL#4727 - Online Backup: Systematic Object Coverage in Tests
Adds test backup_object_coverage which tests basic BACKUP/RESTORE
functionality for a wide range of randomly generated objects.
The test is scheduled to be run on daily basis by MTR2. It can be
also run manually.
Limitation: The following aspects of tables are currently not covered:
- spatial indexes,
- using parser plugins in fulltext indexes,
- table partitions,
- mrege tables,
- federated tables.
They should be addressed by separate WL#4905.
Note: when trying this test, it is good to change $query_count to 1000
to shorten the running time.
@ mysql-test/collections/backup.rqg-daily
Arrange for backup_object_coverage test to be run on daily basis.
@ mysql-test/include/have_rqg.inc
Remove --seed=0 option (which is the default) so that it can be specified
when invoking RQG.
@ mysql-test/suite/backup/t/backup_check_i_s.test
A test which should detect changes in the server which might require extending/updating
backup system and the object coverage test.
@ mysql-test/suite/backup_extra/README
Add explanation of the purpose of backup_extra suite.
@ mysql-test/suite/backup_extra/include/check-objects.sql
SQL queries used to create a "footprint" of all objects being backed-up and restored.
@ mysql-test/suite/backup_extra/include/objects.grammar
A RQG grammar defining what objects will be generated.
@ mysql-test/suite/backup_extra/t/backup_object_coverage.test
The test for object coverage.
added:
mysql-test/collections/backup.rqg-daily
mysql-test/suite/backup/r/backup_check_i_s.result
mysql-test/suite/backup/t/backup_check_i_s.test
mysql-test/suite/backup_extra/README
mysql-test/suite/backup_extra/include/check-objects.sql
mysql-test/suite/backup_extra/include/objects.grammar
mysql-test/suite/backup_extra/t/backup_object_coverage.test
modified:
mysql-test/include/have_rqg.inc
=== added file 'mysql-test/collections/backup.rqg-daily'
--- a/mysql-test/collections/backup.rqg-daily 1970-01-01 00:00:00 +0000
+++ b/mysql-test/collections/backup.rqg-daily 2009-05-04 05:27:25 +0000
@@ -0,0 +1 @@
+perl mysql-test-run.pl --timer --suite=backup_extra backup_object_coverage
=== modified file 'mysql-test/include/have_rqg.inc'
--- a/mysql-test/include/have_rqg.inc 2009-03-31 14:18:21 +0000
+++ b/mysql-test/include/have_rqg.inc 2009-05-04 05:27:25 +0000
@@ -9,6 +9,6 @@ if (`select '$RQG_HOME'=''`)
# setup $RQG variable
-let $RQG= perl $RQG_HOME/gensql.pl --seed=0;
+let $RQG= perl $RQG_HOME/gensql.pl;
--enable_query_log
=== added file 'mysql-test/suite/backup/r/backup_check_i_s.result'
--- a/mysql-test/suite/backup/r/backup_check_i_s.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/r/backup_check_i_s.result 2009-05-04 05:27:25 +0000
@@ -0,0 +1,257 @@
+# This test was created to detect changes in the server which
+# might be relevant to the MySQL backup system.
+#
+# If you see result mismatch for this test, please contact
+# developers of the MySQL backup system, who should decide how
+# to handle it. The test script contains additional information.
+#
+USE information_schema;
+DESCRIBE schemata;
+Field Type Null Key Default Extra
+CATALOG_NAME varchar(512) NO
+SCHEMA_NAME varchar(64) NO
+DEFAULT_CHARACTER_SET_NAME varchar(32) NO
+DEFAULT_COLLATION_NAME varchar(32) NO
+SQL_PATH varchar(512) YES NULL
+DESCRIBE tables;
+Field Type Null Key Default Extra
+TABLE_CATALOG varchar(512) NO
+TABLE_SCHEMA varchar(64) NO
+TABLE_NAME varchar(64) NO
+TABLE_TYPE varchar(64) NO
+ENGINE varchar(64) YES NULL
+VERSION bigint(21) unsigned YES NULL
+ROW_FORMAT varchar(10) YES NULL
+TABLE_ROWS bigint(21) unsigned YES NULL
+AVG_ROW_LENGTH bigint(21) unsigned YES NULL
+DATA_LENGTH bigint(21) unsigned YES NULL
+MAX_DATA_LENGTH bigint(21) unsigned YES NULL
+INDEX_LENGTH bigint(21) unsigned YES NULL
+DATA_FREE bigint(21) unsigned YES NULL
+AUTO_INCREMENT bigint(21) unsigned YES NULL
+CREATE_TIME datetime YES NULL
+UPDATE_TIME datetime YES NULL
+CHECK_TIME datetime YES NULL
+TABLE_COLLATION varchar(32) YES NULL
+CHECKSUM bigint(21) unsigned YES NULL
+CREATE_OPTIONS varchar(255) YES NULL
+TABLE_COMMENT varchar(2048) NO
+TABLESPACE_NAME varchar(64) YES NULL
+DESCRIBE columns;
+Field Type Null Key Default Extra
+TABLE_CATALOG varchar(512) NO
+TABLE_SCHEMA varchar(64) NO
+TABLE_NAME varchar(64) NO
+COLUMN_NAME varchar(64) NO
+ORDINAL_POSITION bigint(21) unsigned NO 0
+COLUMN_DEFAULT longtext YES NULL
+IS_NULLABLE varchar(3) NO
+DATA_TYPE varchar(64) NO
+CHARACTER_MAXIMUM_LENGTH bigint(21) unsigned YES NULL
+CHARACTER_OCTET_LENGTH bigint(21) unsigned YES NULL
+NUMERIC_PRECISION bigint(21) unsigned YES NULL
+NUMERIC_SCALE bigint(21) unsigned YES NULL
+CHARACTER_SET_NAME varchar(32) YES NULL
+COLLATION_NAME varchar(32) YES NULL
+COLUMN_TYPE longtext NO NULL
+COLUMN_KEY varchar(3) NO
+EXTRA varchar(27) NO
+PRIVILEGES varchar(80) NO
+COLUMN_COMMENT varchar(1024) NO
+STORAGE varchar(8) NO
+FORMAT varchar(8) NO
+DESCRIBE views;
+Field Type Null Key Default Extra
+TABLE_CATALOG varchar(512) NO
+TABLE_SCHEMA varchar(64) NO
+TABLE_NAME varchar(64) NO
+VIEW_DEFINITION longtext NO NULL
+CHECK_OPTION varchar(8) NO
+IS_UPDATABLE varchar(3) NO
+DEFINER varchar(77) NO
+SECURITY_TYPE varchar(7) NO
+CHARACTER_SET_CLIENT varchar(32) NO
+COLLATION_CONNECTION varchar(32) NO
+DESCRIBE routines;
+Field Type Null Key Default Extra
+SPECIFIC_NAME varchar(64) NO
+ROUTINE_CATALOG varchar(512) NO
+ROUTINE_SCHEMA varchar(64) NO
+ROUTINE_NAME varchar(64) NO
+ROUTINE_TYPE varchar(9) NO
+DATA_TYPE varchar(64) NO
+CHARACTER_MAXIMUM_LENGTH int(21) YES NULL
+CHARACTER_OCTET_LENGTH int(21) YES NULL
+NUMERIC_PRECISION int(21) YES NULL
+NUMERIC_SCALE int(21) YES NULL
+CHARACTER_SET_NAME varchar(64) YES NULL
+COLLATION_NAME varchar(64) YES NULL
+DTD_IDENTIFIER longtext YES NULL
+ROUTINE_BODY varchar(8) NO
+ROUTINE_DEFINITION longtext YES NULL
+EXTERNAL_NAME varchar(64) YES NULL
+EXTERNAL_LANGUAGE varchar(64) YES NULL
+PARAMETER_STYLE varchar(8) NO
+IS_DETERMINISTIC varchar(3) NO
+SQL_DATA_ACCESS varchar(64) NO
+SQL_PATH varchar(64) YES NULL
+SECURITY_TYPE varchar(7) NO
+CREATED datetime NO 0000-00-00 00:00:00
+LAST_ALTERED datetime NO 0000-00-00 00:00:00
+SQL_MODE varchar(8192) NO
+ROUTINE_COMMENT varchar(64) NO
+DEFINER varchar(77) NO
+CHARACTER_SET_CLIENT varchar(32) NO
+COLLATION_CONNECTION varchar(32) NO
+DATABASE_COLLATION varchar(32) NO
+DESCRIBE events;
+Field Type Null Key Default Extra
+EVENT_CATALOG varchar(64) NO
+EVENT_SCHEMA varchar(64) NO
+EVENT_NAME varchar(64) NO
+DEFINER varchar(77) NO
+TIME_ZONE varchar(64) NO
+EVENT_BODY varchar(8) NO
+EVENT_DEFINITION longtext NO NULL
+EVENT_TYPE varchar(9) NO
+EXECUTE_AT datetime YES NULL
+INTERVAL_VALUE varchar(256) YES NULL
+INTERVAL_FIELD varchar(18) YES NULL
+SQL_MODE varchar(8192) NO
+STARTS datetime YES NULL
+ENDS datetime YES NULL
+STATUS varchar(18) NO
+ON_COMPLETION varchar(12) NO
+CREATED datetime NO 0000-00-00 00:00:00
+LAST_ALTERED datetime NO 0000-00-00 00:00:00
+LAST_EXECUTED datetime YES NULL
+EVENT_COMMENT varchar(64) NO
+ORIGINATOR bigint(10) NO 0
+CHARACTER_SET_CLIENT varchar(32) NO
+COLLATION_CONNECTION varchar(32) NO
+DATABASE_COLLATION varchar(32) NO
+DESCRIBE triggers;
+Field Type Null Key Default Extra
+TRIGGER_CATALOG varchar(512) NO
+TRIGGER_SCHEMA varchar(64) NO
+TRIGGER_NAME varchar(64) NO
+EVENT_MANIPULATION varchar(6) NO
+EVENT_OBJECT_CATALOG varchar(512) NO
+EVENT_OBJECT_SCHEMA varchar(64) NO
+EVENT_OBJECT_TABLE varchar(64) NO
+ACTION_ORDER bigint(4) NO 0
+ACTION_CONDITION longtext YES NULL
+ACTION_STATEMENT longtext NO NULL
+ACTION_ORIENTATION varchar(9) NO
+ACTION_TIMING varchar(6) NO
+ACTION_REFERENCE_OLD_TABLE varchar(64) YES NULL
+ACTION_REFERENCE_NEW_TABLE varchar(64) YES NULL
+ACTION_REFERENCE_OLD_ROW varchar(3) NO
+ACTION_REFERENCE_NEW_ROW varchar(3) NO
+CREATED datetime YES NULL
+SQL_MODE varchar(8192) NO
+DEFINER varchar(77) NO
+CHARACTER_SET_CLIENT varchar(32) NO
+COLLATION_CONNECTION varchar(32) NO
+DATABASE_COLLATION varchar(32) NO
+DESCRIBE schema_privileges;
+Field Type Null Key Default Extra
+GRANTEE varchar(81) NO
+TABLE_CATALOG varchar(512) NO
+TABLE_SCHEMA varchar(64) NO
+PRIVILEGE_TYPE varchar(64) NO
+IS_GRANTABLE varchar(3) NO
+DESCRIBE table_privileges;
+Field Type Null Key Default Extra
+GRANTEE varchar(81) NO
+TABLE_CATALOG varchar(512) NO
+TABLE_SCHEMA varchar(64) NO
+TABLE_NAME varchar(64) NO
+PRIVILEGE_TYPE varchar(64) NO
+IS_GRANTABLE varchar(3) NO
+DESCRIBE column_privileges;
+Field Type Null Key Default Extra
+GRANTEE varchar(81) NO
+TABLE_CATALOG varchar(512) NO
+TABLE_SCHEMA varchar(64) NO
+TABLE_NAME varchar(64) NO
+COLUMN_NAME varchar(64) NO
+PRIVILEGE_TYPE varchar(64) NO
+IS_GRANTABLE varchar(3) NO
+DESCRIBE table_constraints;
+Field Type Null Key Default Extra
+CONSTRAINT_CATALOG varchar(512) NO
+CONSTRAINT_SCHEMA varchar(64) NO
+CONSTRAINT_NAME varchar(64) NO
+TABLE_SCHEMA varchar(64) NO
+TABLE_NAME varchar(64) NO
+CONSTRAINT_TYPE varchar(64) NO
+DESCRIBE key_column_usage;
+Field Type Null Key Default Extra
+CONSTRAINT_CATALOG varchar(512) NO
+CONSTRAINT_SCHEMA varchar(64) NO
+CONSTRAINT_NAME varchar(64) NO
+TABLE_CATALOG varchar(512) NO
+TABLE_SCHEMA varchar(64) NO
+TABLE_NAME varchar(64) NO
+COLUMN_NAME varchar(64) NO
+ORDINAL_POSITION bigint(10) NO 0
+POSITION_IN_UNIQUE_CONSTRAINT bigint(10) YES NULL
+REFERENCED_TABLE_SCHEMA varchar(64) YES NULL
+REFERENCED_TABLE_NAME varchar(64) YES NULL
+REFERENCED_COLUMN_NAME varchar(64) YES NULL
+DESCRIBE referential_constraints;
+Field Type Null Key Default Extra
+CONSTRAINT_CATALOG varchar(512) NO
+CONSTRAINT_SCHEMA varchar(64) NO
+CONSTRAINT_NAME varchar(64) NO
+UNIQUE_CONSTRAINT_CATALOG varchar(512) NO
+UNIQUE_CONSTRAINT_SCHEMA varchar(64) NO
+UNIQUE_CONSTRAINT_NAME varchar(64) YES NULL
+MATCH_OPTION varchar(64) NO
+UPDATE_RULE varchar(64) NO
+DELETE_RULE varchar(64) NO
+TABLE_NAME varchar(64) NO
+REFERENCED_TABLE_NAME varchar(64) NO
+SHOW TABLES;
+Tables_in_information_schema
+CHARACTER_SETS
+COLLATIONS
+COLLATION_CHARACTER_SET_APPLICABILITY
+COLUMNS
+COLUMN_PRIVILEGES
+ENGINES
+EVENTS
+FILES
+GLOBAL_STATUS
+GLOBAL_VARIABLES
+KEY_COLUMN_USAGE
+PARAMETERS
+PARTITIONS
+PLUGINS
+PROCESSLIST
+PROFILING
+REFERENTIAL_CONSTRAINTS
+ROUTINES
+SCHEMATA
+SCHEMA_PRIVILEGES
+SESSION_STATUS
+SESSION_VARIABLES
+STATISTICS
+TABLES
+TABLESPACES
+TABLE_CONSTRAINTS
+TABLE_PRIVILEGES
+TRIGGERS
+USER_PRIVILEGES
+VIEWS
+FALCON_RECORD_CACHE_SUMMARY
+FALCON_SYSTEM_MEMORY_DETAIL
+FALCON_TABLESPACE_IO
+FALCON_SYSTEM_MEMORY_SUMMARY
+FALCON_VERSION
+FALCON_TRANSACTION_SUMMARY
+FALCON_SERIAL_LOG_INFO
+FALCON_SYNCOBJECTS
+FALCON_TRANSACTIONS
+FALCON_RECORD_CACHE_DETAIL
=== added file 'mysql-test/suite/backup/t/backup_check_i_s.test'
--- a/mysql-test/suite/backup/t/backup_check_i_s.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_check_i_s.test 2009-05-04 05:27:25 +0000
@@ -0,0 +1,54 @@
+--echo # This test was created to detect changes in the server which
+--echo # might be relevant to the MySQL backup system.
+--echo #
+--echo # If you see result mismatch for this test, please contact
+--echo # developers of the MySQL backup system, who should decide how
+--echo # to handle it. The test script contains additional information.
+--echo #
+#
+# What needs to be done if a result mismatch for this test was detected
+# =====================================================================
+#
+# Analyse the differences and decide what action should be taken.
+#
+# If new server objects are introduced then:
+# ------------------------------------------
+# 1. check if these new objects should be handled by BACKUP/RESTORE;
+# 2. if yes, extend backup_object_coverage test to cover new types
+# of objects.
+#
+# If new object attributes are introduced, or existing attributes changed:
+# ------------------------------------------------------------------------
+# 3. extend backup_object_coverage test to cover new attributes;
+# 4. if the new attributes are not restored, then si_objects services must
+# be extended to correctly serialize and restore them.
+#
+# Note 1: It is quite possible that result mismatch for this test do not
+# require any changes to the backup system or tests. It might be a result
+# of changing the layout of INFORMATION_SCHEMA without any relevant changes
+# in the server. In that case the new result set should be recorded.
+#
+# Note 2: Extending backup_object_coverage test is done by editing files
+# objects.grammar and check-objects.sql in directory
+# suite/backup_extra/include/.
+#
+
+USE information_schema;
+
+DESCRIBE schemata;
+DESCRIBE tables;
+DESCRIBE columns;
+DESCRIBE views;
+DESCRIBE routines;
+DESCRIBE events;
+DESCRIBE triggers;
+
+DESCRIBE schema_privileges;
+DESCRIBE table_privileges;
+DESCRIBE column_privileges;
+
+DESCRIBE table_constraints;
+DESCRIBE key_column_usage;
+DESCRIBE referential_constraints;
+
+SHOW TABLES;
=== added file 'mysql-test/suite/backup_extra/README'
--- a/mysql-test/suite/backup_extra/README 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_extra/README 2009-05-04 05:27:25 +0000
@@ -0,0 +1,5 @@
+This is a suite for backup tests which are *not* to be run as part of regression
+testing.
+
+Instead, they are run on fixed schedule basis (as defined in collections/ dir)
+or manually.
=== added file 'mysql-test/suite/backup_extra/include/check-objects.sql'
--- a/mysql-test/suite/backup_extra/include/check-objects.sql 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_extra/include/check-objects.sql 2009-05-04 05:27:25 +0000
@@ -0,0 +1,266 @@
+#
+# List various objects existing in the server and their properties.
+#
+# This file is used in backup_object_coverage.test to check if all objects
+# were correctly restored.
+#
+# Note: Some properties are not listed because they are not preserved
+# by backup/restore (BUG#44514/15). When these bugs are fixed, the
+# corresponding properties should be uncommented (assuming that they have
+# to be preserved).
+#
+
+SELECT '# databases' AS '####';
+
+USE information_schema;
+
+SELECT
+ CATALOG_NAME,
+ SCHEMA_NAME,
+ DEFAULT_CHARACTER_SET_NAME,
+ DEFAULT_COLLATION_NAME,
+ SQL_PATH
+FROM schemata WHERE schema_name LIKE 'db%'
+ORDER BY schema_name;
+
+SELECT '# tables' AS '####';
+
+SELECT
+ TABLE_CATALOG,
+ TABLE_SCHEMA,
+ TABLE_NAME,
+ TABLE_TYPE,
+ ENGINE,
+ VERSION,
+ ROW_FORMAT,
+ TABLE_ROWS,
+ AVG_ROW_LENGTH,
+ DATA_LENGTH,
+ MAX_DATA_LENGTH,
+ INDEX_LENGTH,
+ DATA_FREE,
+ AUTO_INCREMENT,
+# CREATE_TIME, # BUG#44514
+# UPDATE_TIME, # BUG#44514
+ CHECK_TIME,
+ TABLE_COLLATION,
+ CHECKSUM,
+ CREATE_OPTIONS,
+ TABLE_COMMENT,
+ TABLESPACE_NAME
+FROM tables WHERE table_schema LIKE 'db%'
+ORDER BY table_schema, table_name;
+
+SELECT '# table columns' AS '####';
+
+SELECT
+ TABLE_CATALOG,
+ TABLE_SCHEMA,
+ TABLE_NAME,
+ COLUMN_NAME,
+ ORDINAL_POSITION,
+ COLUMN_DEFAULT,
+ IS_NULLABLE,
+ DATA_TYPE,
+ CHARACTER_MAXIMUM_LENGTH,
+ CHARACTER_OCTET_LENGTH,
+ NUMERIC_PRECISION,
+ NUMERIC_SCALE,
+ CHARACTER_SET_NAME,
+ COLLATION_NAME,
+ COLUMN_TYPE,
+ COLUMN_KEY,
+ EXTRA,
+ PRIVILEGES,
+ COLUMN_COMMENT,
+ STORAGE,
+ FORMAT
+FROM columns WHERE table_schema LIKE 'db%'
+ORDER BY table_schema, table_name, ordinal_position;
+
+SELECT '# views' AS '####';
+
+SELECT
+ TABLE_CATALOG,
+ TABLE_SCHEMA,
+ TABLE_NAME,
+ VIEW_DEFINITION,
+ CHECK_OPTION,
+ IS_UPDATABLE,
+ DEFINER,
+ SECURITY_TYPE,
+ CHARACTER_SET_CLIENT,
+ COLLATION_CONNECTION
+FROM views WHERE table_schema LIKE 'db%'
+ORDER BY table_schema, table_name;
+
+SELECT '# stored routines' AS '####';
+
+SELECT
+ SPECIFIC_NAME,
+ ROUTINE_CATALOG,
+ ROUTINE_SCHEMA,
+ ROUTINE_NAME,
+ ROUTINE_TYPE,
+ DATA_TYPE,
+# CHARACTER_MAXIMUM_LENGTH, # BUG#44515
+# CHARACTER_OCTET_LENGTH, # BUG#44515
+ NUMERIC_PRECISION,
+ NUMERIC_SCALE,
+ CHARACTER_SET_NAME,
+ COLLATION_NAME,
+# DTD_IDENTIFIER, # BUG#44515
+ ROUTINE_BODY,
+ ROUTINE_DEFINITION,
+ EXTERNAL_NAME,
+ EXTERNAL_LANGUAGE,
+ PARAMETER_STYLE,
+ IS_DETERMINISTIC,
+ SQL_DATA_ACCESS,
+ SQL_PATH,
+ SECURITY_TYPE,
+# CREATED, # BUG#44514
+# LAST_ALTERED, # BUG#44514
+ SQL_MODE,
+ ROUTINE_COMMENT,
+ DEFINER,
+ CHARACTER_SET_CLIENT,
+ COLLATION_CONNECTION
+ DATABASE_COLLATION
+FROM routines WHERE routine_schema LIKE 'db%'
+ORDER BY routine_schema, routine_name;
+
+SELECT '# events' AS '####';
+
+SELECT
+ EVENT_CATALOG,
+ EVENT_SCHEMA,
+ EVENT_NAME,
+ DEFINER,
+ TIME_ZONE,
+ EVENT_BODY,
+ EVENT_DEFINITION,
+ EVENT_TYPE,
+ EXECUTE_AT,
+ INTERVAL_VALUE,
+ INTERVAL_FIELD,
+ SQL_MODE,
+ STARTS,
+ ENDS,
+ STATUS,
+ ON_COMPLETION,
+# CREATED, # BUG#44514
+# LAST_ALTERED, # BUG#44514
+ LAST_EXECUTED,
+ EVENT_COMMENT,
+ ORIGINATOR,
+ CHARACTER_SET_CLIENT,
+ COLLATION_CONNECTION
+ DATABASE_COLLATION
+FROM events WHERE event_schema LIKE 'db%'
+ORDER BY event_schema, event_name;
+
+SELECT '# triggers' AS '###';
+
+SELECT
+ TRIGGER_CATALOG,
+ TRIGGER_SCHEMA,
+ TRIGGER_NAME,
+ EVENT_MANIPULATION,
+ EVENT_OBJECT_CATALOG,
+ EVENT_OBJECT_SCHEMA,
+ EVENT_OBJECT_TABLE,
+ ACTION_ORDER,
+ ACTION_CONDITION,
+ ACTION_STATEMENT,
+ ACTION_ORIENTATION,
+ ACTION_TIMING,
+ ACTION_REFERENCE_OLD_TABLE,
+ ACTION_REFERENCE_NEW_TABLE,
+ ACTION_REFERENCE_OLD_ROW,
+ ACTION_REFERENCE_NEW_ROW,
+ CREATED,
+ SQL_MODE,
+ DEFINER,
+ CHARACTER_SET_CLIENT,
+ COLLATION_CONNECTION
+ DATABASE_COLLATION
+FROM triggers WHERE trigger_schema LIKE 'db%'
+ORDER BY trigger_schema, trigger_name;
+
+
+SELECT '# privileges' AS '####';
+
+SELECT
+ GRANTEE,
+ TABLE_CATALOG,
+ TABLE_SCHEMA,
+ PRIVILEGE_TYPE,
+ IS_GRANTABLE
+FROM schema_privileges WHERE table_schema LIKE 'db%'
+ORDER BY grantee, table_schema, privilege_type;
+
+SELECT
+ GRANTEE,
+ TABLE_CATALOG,
+ TABLE_SCHEMA,
+ TABLE_NAME,
+ PRIVILEGE_TYPE,
+ IS_GRANTABLE
+FROM table_privileges WHERE table_schema LIKE 'db%'
+ORDER BY grantee, table_schema, table_name, privilege_type;
+
+SELECT
+ GRANTEE,
+ TABLE_CATALOG,
+ TABLE_SCHEMA,
+ TABLE_NAME,
+ COLUMN_NAME,
+ PRIVILEGE_TYPE,
+ IS_GRANTABLE
+FROM column_privileges WHERE table_schema LIKE 'db%'
+ORDER BY grantee, table_schema, table_name, column_name, privilege_type;
+
+SELECT '# Keys & Constraints' AS '####';
+
+SELECT
+ CONSTRAINT_CATALOG,
+ CONSTRAINT_SCHEMA,
+ CONSTRAINT_NAME,
+ TABLE_SCHEMA,
+ TABLE_NAME,
+ CONSTRAINT_TYPE
+FROM table_constraints WHERE table_schema LIKE 'db%'
+ORDER BY table_schema, table_name, constraint_name;
+
+SELECT
+ CONSTRAINT_CATALOG,
+ CONSTRAINT_SCHEMA,
+ CONSTRAINT_NAME,
+ TABLE_CATALOG,
+ TABLE_SCHEMA,
+ TABLE_NAME,
+ COLUMN_NAME,
+ ORDINAL_POSITION,
+ POSITION_IN_UNIQUE_CONSTRAINT,
+ REFERENCED_TABLE_SCHEMA,
+ REFERENCED_TABLE_NAME,
+ REFERENCED_COLUMN_NAME
+FROM key_column_usage WHERE table_schema LIKE 'db%'
+ORDER BY table_schema, table_name, column_name, ordinal_position;
+
+SELECT
+ CONSTRAINT_CATALOG,
+ CONSTRAINT_SCHEMA,
+ CONSTRAINT_NAME,
+ UNIQUE_CONSTRAINT_CATALOG,
+ UNIQUE_CONSTRAINT_SCHEMA,
+ UNIQUE_CONSTRAINT_NAME,
+ MATCH_OPTION,
+ UPDATE_RULE,
+ DELETE_RULE,
+ TABLE_NAME,
+ REFERENCED_TABLE_NAME
+FROM referential_constraints WHERE constraint_schema LIKE 'db%'
+ORDER BY unique_constraint_schema, unique_constraint_name;
+
=== added file 'mysql-test/suite/backup_extra/include/objects.grammar'
--- a/mysql-test/suite/backup_extra/include/objects.grammar 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_extra/include/objects.grammar 2009-05-04 05:27:25 +0000
@@ -0,0 +1,684 @@
+############################################################################
+# This is a grammar for the Random Query Generator (RQG). It is used in
+# backup_object_coverage.test to generate statements which create and set
+# properties of objects to be backed-up and restored.
+#
+# The grammar was written based on the syntax of the available CREATE and
+# ALTER statements. It covers only these objects which are handled by the
+# current backup system. That is:
+#
+# - tables,
+# - views,
+# - stored routines,
+# - events,
+# - triggers,
+# - privileges (database level and below).
+#
+# The grammar should be updated when new objects or new object properties
+# are added in the server and/or backup system is extended to handle them.
+# There is a test backup_check_i_s in the backup suite which should fail if
+# server is extended.
+#
+# The statements generated by this grammar require the following objects
+# to exist in the server:
+#
+# - users usr0, ..., usr9.
+# - databases db0, ..., db9.
+# - falcon tablespaces ts0,..,ts3;
+#
+# They also assume that the server contains:
+#
+# - storage engines: MyISAM, Falcon, InnoDB, Memory, Archive, CSV and Blackhole;
+# - character sets: ucs2, utf8, utf16, ascii, latin2, macce, cp1256, greek,
+# hebrew, latin5, latin7, cp866, koi8r, big5, gbk, ujis;
+#
+# Environment variable MYISAM_DATA_DIR should point at a directory where myisam
+# data and index files can be kept.
+#
+# Note: The following aspects of tables are currently not covered by this
+# grammar:
+#
+# - spatial indexes,
+# - using parser plugins in fulltext indexes,
+# - table partitions,
+# - merge tables,
+# - federated tables,
+#
+# Also, procedures, functions, triggers and events have trivial bodies only.
+#
+
+############################################################################
+# Global settings and initialization.
+#
+# Note: Archive storage engine is not included because of BUG#44516 which can
+# lead to test failures. When this bug is fixed, Archive should be added to
+# @storage_engines array.
+#
+query_init:
+ { @storage_engines= (MyISAM, Falcon, InnoDB, Memory, CSV, Blackhole); undef }
+ { @charsets= (ucs2, utf8, utf16, ascii, latin2, macce, cp1256, greek, hebrew, latin5, latin7, cp866, koi8r, big5, gbk, ujis); undef }
+ GRANT SELECT, CREATE, CREATE VIEW, CREATE ROUTINE ON *.* TO usr0
+ ; GRANT SELECT, CREATE, CREATE VIEW, CREATE ROUTINE ON *.* TO usr1
+ ; GRANT SELECT, CREATE, CREATE VIEW, CREATE ROUTINE ON *.* TO usr2
+ ; GRANT SELECT, CREATE, CREATE VIEW, CREATE ROUTINE ON *.* TO usr3
+ ; GRANT SELECT, CREATE, CREATE VIEW, CREATE ROUTINE ON *.* TO usr4
+ ; GRANT SELECT, CREATE, CREATE VIEW, CREATE ROUTINE ON *.* TO usr5
+ ; GRANT SELECT, CREATE, CREATE VIEW, CREATE ROUTINE ON *.* TO usr6
+ ; GRANT SELECT, CREATE, CREATE VIEW, CREATE ROUTINE ON *.* TO usr7
+ ; GRANT SELECT, CREATE, CREATE VIEW, CREATE ROUTINE ON *.* TO usr8
+ ; GRANT SELECT, CREATE, CREATE VIEW, CREATE ROUTINE ON *.* TO usr9
+;
+
+#
+# Allocate new object in the current database (indicated by $db_num) and
+# generate name for it. Objects are described by structures storing object
+# parameters. These structures are collected in corresponding lists: @views,
+# @tables, @procedures etc.
+#
+# Objects are counted by consecutive numbers and their names are prefixed with
+# letter indicating object type. Each database has a separate namespace.
+#
+new_view: { $vi= { db=>$db_num, num=>++$v_count[$db_num] }; push(@views, $vi); $view= "v$vi->{num}" } ;
+new_table: { $ti = { db=>$db_num, num=>++$t_count[$db_num], cols=>0 }; push(@tables, $ti); $table= "t$ti->{num}" } ;
+new_procedure: { $pi= { db=>$db_num, num=>++$p_count[$db_num] }; push(@procedures, $pi); $procedure= "p$pi->{num}" } ;
+new_function: { $fi= { db=>$db_num, num=>++$f_count[$db_num] }; push(@functions, $fi); $function= "f$fi->{num}" } ;
+new_trigger: { $ri= { db=>$db_num, num=>++$r_count[$db_num] }; push(@triggers, $ri); $trigger= "r$ri->{num}" } ;
+new_event: { $ei= { db=>$db_num, num=>++$e_count[$db_num] }; push(@events, $ei); $event= "e$ei->{num}" } ;
+
+#
+# Allocate "object" in a table (described by $ti).
+#
+new_column: { $ci = { t=>$ti, num=>$ti->{cols}++ }; push(@cols, $ci); $col= "c$ci->{num}" } ;
+new_param: { $ci = { t=>$ti, num=>$ti->{cols}++ }; push(@params, $ci); $param= "a$ci->{num}" } ;
+new_index: { $index= "idx".++$idx_num } ;
+
+#
+# Randomly pick a previously generated object.
+#
+table_ref: { $ti= $prng->arrayElement([@tables]); $table= "db$ti->{db}.t$ti->{num}" } ;
+view_ref: { $vi= $prng->arrayElement([@views]); $view= "db$vi->{db}.v$vi->{num}" } ;
+proc_ref: { $pi= $prng->arrayElement([@procedures]); $proc= "db$pi->{db}.p$pi->{num}" } ;
+func_ref: { $fi= $prng->arrayElement([@functions]); $func= "db$fi->{db}.f$fi->{num}" } ;
+event_ref: { $ei= $prng->arrayElement([@events]); $event= "db$ei->{db}.e$ei->{num}" } ;
+routine_ref: func_ref | proc_ref ;
+
+#
+# Pick random global object
+#
+db: { $db_num= $prng->int(0,9); $db= "db$db_num" } ;
+user: { $usr_num= $prng->int(0,9); $user= "usr$usr_num" } ;
+tablespace: { $ts_num= $prng->int(0,3); $ts= "ts$ts_num" } ;
+charset_name: { $charset= $prng->arrayElement([@charsets]) } ;
+collation_name: { "${charset}_bin" } ;
+storage_engine: { $se= $ti->{engine}= $prng->arrayElement([@storage_engines]) } ;
+
+
+#
+# The main rule.
+#
+query: create_object | create_object | create_object
+ | grant_privilege
+ | alter_object | alter_object ;
+
+create_object: USE db ; CREATE dbobj ;
+dbobj: table | view | procedure | function | trigger | event ;
+
+alter_object:
+ alter_database
+ | alter_table
+ | alter_procedure
+ | alter_function
+ | alter_event
+;
+
+alter_database: ALTER DATABASE db character_set ;
+
+
+############################################################################
+# Tables
+#
+# Note: Many properties of the created table will be changed later using
+# ALTER TABLE statements generated from alter_table rule.
+#
+
+table: TABLE new_table ( table_cols )
+ ENGINE= storage_engine
+ opt_data_directory opt_index_directory
+;
+
+# DATA/INDEX DIRECTORY option is valid only for MyISAM engine
+opt_data_directory:
+ | { "DATA DIRECTORY '$dir'" if (($ti->{engine} eq "MyISAM") && ($dir= $ENV{MYISAM_DATA_DIR})) } ;
+opt_index_directory:
+ | { "INDEX DIRECTORY '$dir'" if (($ti->{engine} eq "MyISAM") && ($dir= $ENV{MYISAM_DATA_DIR})) } ;
+
+
+table_cols: one_col | three_col | five_col ;
+
+one_col: column_definition ;
+
+three_col: column_definition,
+ column_definition,
+ column_definition ;
+
+five_col: column_definition,
+ column_definition,
+ column_definition,
+ column_definition,
+ column_definition ;
+
+column_definition: new_column data_type column_option ;
+
+column_option:
+ | opt_auto_inc
+ | NULL
+ | NOT NULL
+ | COMMENT 'A comment' ;
+
+#
+# AUTO_INCREMENT option implicitly creates a primary key on the column, so
+# it can be added only if there is no key defined yet.
+#
+opt_auto_inc:
+ | { if (!$ti->{has_key} && $ci->{auto_inc}) { $ci->{indexed}= $ti->{has_key}=1; "KEY AUTO_INCREMENT" } } ;
+
+#
+# Additional changes using ALTER TABLE
+#
+
+alter_table:
+ ALTER TABLE alter_table_spec
+ | fkey_constraint
+ | fulltext_or_spatial_index
+;
+
+fulltext_or_spatial_index:
+ fulltext_index
+# | spatial_index #TODO
+;
+
+alter_table_spec:
+ primary_key
+ | secondary_key
+ | default_value
+# | partition_options #TODO
+ | set_tablespace
+ | table_ref table_option
+;
+
+table_option:
+ AUTO_INCREMENT = _smallint_unsigned
+ | AVG_ROW_LENGTH = _smallint_unsigned
+ | DEFAULT CHARACTER SET = charset_name opt_table_collation
+ | CHECKSUM = bool
+ | COMMENT 'A comment'
+# | CONNECTION [=] 'connect_string' #TODO
+ | DELAY_KEY_WRITE = bool
+ | INSERT_METHOD = insert_method
+ | KEY_BLOCK_SIZE = _smallint_unsigned
+ | MAX_ROWS = _smallint_unsigned
+ | MIN_ROWS = _tinyint_unsigned
+ | PACK_KEYS = pack_keys
+ | PASSWORD = ' password '
+ | ROW_FORMAT = row_format
+# | UNION [=] (tbl_name[,tbl_name]...) #TODO
+;
+
+opt_table_collation: | , COLLATE = collation_name ;
+insert_method: NO | FIRST | LAST ;
+pack_keys: 0 | 1 | DEFAULT ;
+row_format: DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT ;
+
+bool: 0 | 1 ;
+
+
+#
+# Add a primary or secondary key to a table:
+# - pick table $ti,
+# - pick column $ci of $ti,
+# - add key witdth (1) if it is a blob column,
+# - add key options
+#
+# In the case of a primary key, a table which does not have one must be picked.
+#
+primary_key:
+ { $ti= $prng->arrayElement([grep {!$_->{has_key}} @tables]); $ti->{has_key}=1; "db$ti->{db}.t$ti->{num}" }
+ ADD PRIMARY KEY
+ ( { $ci= $prng->arrayElement([grep {$_->{t}==$ti} @cols]); $ci->{indexed}=1; "c$ci->{num}" }
+ { "(1)" if ($ci->{is_blob}) } opt_asc_desc ) opt_index_option ;
+
+secondary_key: table_ref ADD opt_unique KEY
+ ( { $ci= $prng->arrayElement([grep {$_->{t}==$ti} @cols]); $ci->{indexed}=1; "c$ci->{num}" }
+ { "(1)" if ($ci->{is_blob}) } opt_asc_desc ) opt_index_option ;
+
+opt_unique: | UNIQUE ;
+
+opt_index_option:
+ | KEY BLOCK_SIZE = _smallint_unsigned
+ | USING BTREE # note: BTREE is suported by most engines
+ | COMMENT 'A comment'
+;
+
+opt_asc_desc: | ASC | DESC ;
+
+#
+# Define default value for one of table columns:
+# - pick a column $ci which has a default_value,
+# - read table $ti of the selected column.
+#
+default_value:
+ { $ci= $prng->arrayElement([grep {$_->{default_value}} @cols]); $col="c$ci->{num}"; $ti=$ci->{t}; undef }
+ { $table= "db$ti->{db}.t$ti->{num}" } ALTER COLUMN $col
+ SET DEFAULT { $ci->{default_value} } ;
+
+#
+# Specify tablespace of a Falcon table.
+#
+# Note: when other storage engines start to support tablespaces, this rule would
+# have to be changed.
+#
+set_tablespace:
+ { $ti= $prng->arrayElement([grep {$_->{engine} eq 'Falcon'} @tables]); $tbl="db$ti->{db}.t$ti->{num}" }
+ TABLESPACE tablespace
+;
+
+############################################################################
+# Fkey Constraints
+#
+# The procedure is as follows:
+# - pick a random table $ti (table_ref rule);
+# - pick a random column $fi from the table which is not a blob;
+# - pick a random column $ri which has the stame type as $fi and is indexed;
+# - create foreign key on $fi referring to $ri.
+#
+# Note: if a colums with required properties does not exist then an
+# invalid constraint will be constructed and the CREATE TABLE statement will
+# fail.
+#
+
+fkey_constraint: ALTER TABLE table_ref ADD FOREIGN KEY
+ # select colum from the table but not a blob
+ { $fi= $prng->arrayElement([grep {$_->{t}==$ti && !$_->{is_blob}} @cols]); undef }
+ # read column type and name
+ { $ftype= $fi->{type}; $fcol= "c$fi->{num}" ; undef }
+ # select different column of the same type which is indexed
+ { $ri= $prng->arrayElement([grep {$_ != $fi && ($_->{type} eq $ftype) && $_->{indexed}} @cols]) ; undef }
+ # construct reference to the remote column
+ { $rtable= "db$ri->{t}->{db}.t$ri->{t}->{num}"; $rcol= "c$ri->{num}"; undef }
+ ( $fcol ) REFERENCES $rtable ( $rcol )
+ column_ref_option on_delete_spec on_update_spec
+;
+
+column_ref_option: | MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ;
+on_delete_spec: | ON DELETE reference_option ;
+on_update_spec: | ON UPDATE reference_option ;
+reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION ;
+
+
+############################################################################
+# Fulltext index
+#
+# Such indexes are valid only for MyISAM tables and only on CHAR, VARCHAR
+# and TEXT columns.
+#
+
+fulltext_index: CREATE FULLTEXT INDEX new_index
+ { $ci= $prng->arrayElement([grep { ($_->{t}->{engine} eq 'MyISAM') && ($_->{type} eq 'char' or $_->{type} eq 'text') } @cols]); undef }
+ { $ti=$ci->{t}; $table= "db$ti->{db}.t$ti->{num}"; $col= "c$ci->{num}"; undef }
+ ON $table ( $col opt_asc_desc ) opt_fulltext_index_option
+;
+
+opt_fulltext_index_option: |
+ | KEY_BLOCK_SIZE = _smallint_unsigned
+ | USING BTREE
+# | WITH PARSER parser #TODO
+ | COMMENT 'A comment'
+;
+
+
+############################################################################
+#
+# Views
+#
+
+view:
+ opt_view_alg_spec
+ opt_definer
+ opt_view_security_spec
+ VIEW new_view AS select
+ opt_view_option
+;
+
+opt_view_alg_spec: | | ALGORITHM = view_algorithm ;
+
+opt_definer: | DEFINER= user | DEFINER= CURRENT_USER;
+
+opt_view_security_spec: | sql_security ;
+
+opt_view_option: | | WITH view_check_type CHECK OPTION ;
+
+select: SELECT * FROM table_or_view |
+ # pick random table
+ { $ti= $prng->arrayElement([@tables]); $tbl= "db$ti->{db}.t$ti->{num}"; undef }
+ # and a column of that table
+ { $ci= $prng->arrayElement([grep {$_->{t} == $ti} @cols]); $col= "c$ci->{num}"; undef }
+ SELECT $col FROM $tbl
+;
+
+table_or_view: table_ref | view_ref ;
+
+view_algorithm: UNDEFINED | MERGE | TEMPTABLE ;
+view_check_type: CASCADED | LOCAL ;
+
+sql_security: SQL SECURITY definer_or_invoker ;
+definer_or_invoker: DEFINER | INVOKER ;
+
+
+############################################################################
+#
+# Procedures & functions
+#
+# Note: We use trivial body "SET @foo=1" for all generated procedures.
+#
+
+#
+# Procedure
+#
+
+procedure:
+ opt_definer PROCEDURE new_procedure
+ ( { $ti= $pi; undef } proc_parameters )
+ routine_options
+ SET @foo=1
+;
+
+proc_parameters: | one_p_param | two_p_param | three_p_param ;
+
+one_p_param:
+ proc_parameter ;
+
+two_p_param:
+ proc_parameter ,
+ proc_parameter ;
+
+three_p_param:
+ proc_parameter ,
+ proc_parameter ,
+ proc_parameter ;
+
+proc_parameter: opt_p_param_spec func_parameter ;
+
+opt_p_param_spec: | | IN | OUT | INOUT ;
+
+#
+# Function
+#
+
+function:
+ opt_definer FUNCTION new_function
+ ( { $ti= $fi; undef } func_parameters )
+ return_spec
+ routine_options
+ RETURN $ret_val
+;
+
+func_parameters: | one_f_param | two_f_param | three_f_param ;
+
+one_f_param:
+ func_parameter ;
+
+two_f_param:
+ func_parameter ,
+ func_parameter ;
+
+three_f_param:
+ func_parameter ,
+ func_parameter ,
+ func_parameter ;
+
+func_parameter: new_param data_type ;
+
+#
+# We generate two types of functions: function returning constant value or
+# function returning one of its parameters.
+#
+return_spec:
+ RETURNS data_type { $ret_val= $ci->{default_value} or 1; undef }
+ | { $ci= $prng->arrayElement([grep {$_->{t}==$fi && ($_->{type} ne 'enum') && ($_->{type} ne 'set')} @params]); $ret_val="a$ci->{num}"; undef }
+ RETURNS { $ci->{type} }
+;
+
+
+routine_options: | opt_routine_opt_language opt_routine_opt_determinism ;
+
+opt_routine_opt_language: | LANGUAGE SQL ;
+opt_routine_opt_determinism: | DETERMINISTIC | NOT DETERMINISTIC ;
+
+#
+# Statements altering functions/procedures.
+#
+
+alter_procedure: ALTER PROCEDURE proc_ref alter_routine_spec ;
+alter_function: ALTER FUNCTION func_ref alter_routine_spec ;
+
+alter_routine_spec:
+ CONTAINS SQL | NO SQL
+ | READS SQL DATA | MODIFIES SQL DATA
+ | sql_security
+ | COMMENT 'A comment' ;
+
+
+############################################################################
+#
+# Triggers
+#
+# Note: We generate triggers with trivial bodies. In particular they do not
+# touch any objects in the datbase.
+#
+
+trigger:
+ opt_definer TRIGGER new_trigger trigger_time trigger_event
+ ON { $ti= $prng->arrayElement([grep {$_->{db}==$db_num} @tables]); "t$ti->{num}" }
+ FOR EACH ROW SET @foo=1
+;
+
+trigger_time: BEFORE | AFTER ;
+
+trigger_event: INSERT | UPDATE | DELETE ;
+
+############################################################################
+#
+# Events
+#
+# Note: The DEFINER clause is not included because it is not currently
+# preserved by BACKUP/RESTORE commands (BUG#????).
+#
+
+event:
+ opt_definer
+ EVENT new_event ON SCHEDULE event_schedule
+ DO SET @foo=1
+;
+
+alter_event: ALTER EVENT event_ref alter_event_spec ;
+
+alter_event_spec:
+ ON COMPLETION PRESERVE
+ | ON COMPLETION NOT PRESERVE
+ | ENABLE
+ | DISABLE
+ | DISABLE ON SLAVE
+ | COMMENT 'A comment' ;
+
+event_schedule:
+ AT time_spec
+ | EVERY interval opt_starts ;
+
+time_spec: _timestamp opt_interval ;
+
+opt_starts: STARTS _timestamp opt_interval opt_ends ;
+opt_ends: ENDS _timestamp opt_interval ;
+opt_interval: + INTERVAL interval ;
+
+interval: _digit interval_unit ;
+
+interval_unit:
+ YEAR | QUARTER | MONTH | WEEK | DAY
+ | HOUR | MINUTE | SECOND
+ | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND
+ | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND ;
+
+
+############################################################################
+#
+# Privileges
+#
+
+grant_privilege: db_grant | per_db_grant | column_grant ;
+
+db_grant: GRANT db_privilege ON db.* TO user ;
+per_db_grant: GRANT privilege ON grant_target TO user ;
+column_grant:
+ { $ci= $prng->arrayElement([@cols]); $ti=$ci->{t}; undef }
+ { $col= "c$ci->{num}"; $table= "db$ti->{db}.t$ti->{num}"; undef }
+ GRANT col_privilege ( $col ) ON $table TO user ;
+
+grant_target: table_ref | view_ref ;
+
+#
+# Privileges which can be granted on datbase level.
+#
+db_privilege:
+ privilege
+ | CREATE # Enables use of CREATE TABLE
+ | CREATE ROUTINE # Enables creation of stored routines
+ | CREATE VIEW # Enables use of CREATE VIEW
+ | EVENT # Enables creation of events for the event scheduler
+ | SHOW VIEW # Enables use of SHOW CREATE VIEW
+ | CREATE TEMPORARY TABLES # Enables use of CREATE TEMPORARY TABLE
+ | LOCK TABLES # Enables use of LOCK TABLES on tables for which you have the SELECT privilege
+ | EXECUTE
+ | ALTER ROUTINE
+;
+
+#
+# Privileges which can be granted to individual objects within database.
+#
+# Note: only tables and views can be targets of privileges.
+#
+privilege:
+ col_privilege
+ | ALTER # Enables use of ALTER TABLE
+ | DELETE # Enables use of DELETE
+ | DROP # Enables use of DROP TABLE
+ | INDEX # Enables use of CREATE INDEX and DROP INDEX
+ | TRIGGER # Enables the user to create or drop triggers
+ | GRANT OPTION # Enables privileges to be granted
+;
+
+#
+# Privileges which can be granted to single columns.
+#
+col_privilege:
+ INSERT # Enables use of INSERT
+ | SELECT # Enables use of SELECT
+ | UPDATE # Enables use of UPDATE
+;
+
+#
+# Global privileges - we do not backup them and thus not use them here.
+#
+global_privileges:
+ CREATE USER # Enables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
+ | FILE # Enables use of SELECT ... INTO OUTFILE and LOAD DATA INFILE
+ | PROCESS # Enables the user to see all processes with SHOW PROCESSLIST
+ | REPLICATION CLIENT # Enables the user to ask where slave or master servers are
+ | REPLICATION SLAVE # Needed for replication slaves (to read binary log events from the master)
+ | SHOW DATABASES # SHOW DATABASES shows all databases
+ | SHUTDOWN # Enables use of mysqladmin shutdown
+ | SUPER # Enables use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows you to connect (once) even if max_connections is reached
+ | RELOAD # Enables use of FLUSH
+;
+
+#
+# Other privileges from documentation which are ignored.
+#
+# ALL PRIVILEGES # Grants all privileges at specified access level except GRANT OPTION
+# | USAGE # Synonym for t implemented
+
+
+############################################################################
+#
+# Column types
+#
+# pick type of a column and set $ci flags:
+#
+# $ci->{type} - the type of the column
+# $ci->{default_value} - if defined, can be used for DEFAULT clause
+# $ci->{auto_inc} - column can have AUTO_INCREMENT option
+# $ci->{is_blob} - column is a blob
+#
+
+data_type: { $ci->{default_value}= 0; $ci->{auto_inc}=1; undef } dt_numeric
+ | { $ci->{default_value}= "'a'"; undef } dt_char
+ | dt_datetime
+ | { $ci->{is_blob}=1; undef } dt_blob
+ | dt_set_or_enum ;
+
+dt_numeric: INT opt_num_len opt_num_opts { $ci->{type}= 'int'; undef }
+ | INTEGER opt_num_len opt_num_opts { $ci->{type}= 'int'; undef }
+ | TINYINT opt_num_len opt_num_opts { $ci->{type}= 'tinyint'; undef }
+ | SMALLINT opt_num_len opt_num_opts { $ci->{type}= 'smallint'; undef }
+ | BIGINT opt_num_len opt_num_opts { $ci->{type}= 'bigint'; undef }
+ | REAL opt_num_len_prec opt_num_opts { $ci->{type}= 'real'; $ci->{default_value}= 0.0; undef }
+ | DOUBLE opt_num_len_prec opt_num_opts { $ci->{type}= 'double'; $ci->{default_value}= 0.0; undef }
+ | FLOAT opt_num_len_prec opt_num_opts { $ci->{type}= 'float'; $ci->{default_value}= 0.0; undef }
+ | DECIMAL opt_num_len_opt_prec opt_num_opts { $ci->{type}= 'decimal'; $ci->{auto_inc}=undef }
+ | NUMERIC opt_num_len_opt_prec opt_num_opts { $ci->{type}= 'decimal'; $ci->{auto_inc}=undef } ;
+
+opt_num_len: | ( { $num_len = $prng->int(1,30) } ) ;
+opt_num_len_prec: | ( { $num_len = $prng->int(1,30) },
+ { $num_prec= $prng->int(0,$num_len) } ) ;
+opt_num_len_opt_prec:
+ | ( { $num_len = $prng->int(1,30) }
+ { $num_prec= $prng->int(-1,$num_len) ; $num_prec >= 0 ? ", $num_prec" : undef } ) ;
+
+opt_num_opts: | opt_unsigned | opt_zerofill ;
+opt_unsigned: | UNSIGNED ;
+opt_zerofill: | ZEROFILL ;
+
+dt_char: CHAR opt_charset { $ci->{type}= 'char'; undef }
+ | CHAR ( _tinyint_unsigned ) opt_charset { $ci->{type}= 'char'; undef }
+ | VARCHAR ( _tinyint_unsigned ) opt_charset { $ci->{is_blob}= 1; $ci->{type}= 'char'; undef }
+ | TEXT dt_char_opts { $ci->{type}='text'; $ci->{is_blob}= 1; undef }
+ | TINYTEXT dt_char_opts { $ci->{type}='text'; $ci->{is_blob}= 1; undef }
+ | MEDIUMTEXT dt_char_opts { $ci->{type}='text'; $ci->{is_blob}= 1; undef }
+ | LONGTEXT dt_char_opts { $ci->{type}='text'; $ci->{is_blob}= 1; undef } ;
+
+dt_char_opts:
+ | BINARY
+ | character_set ;
+
+character_set: CHARACTER SET charset_name opt_collation ;
+opt_collation: | COLLATE collation_name ;
+opt_charset: | | character_set ;
+
+dt_datetime: DATE { $ci->{type}='date'; $ci->{default_value}= "'".$prng->date()."'"; undef }
+ | TIME { $ci->{type}='time'; $ci->{default_value}= "'".$prng->time()."'"; undef }
+ | TIMESTAMP { $ci->{type}='timestamp'; $ci->{default_value}= "'".$prng->timestamp()."'"; undef }
+ | DATETIME { $ci->{type}='datetime'; $ci->{default_value}= "'".$prng->datetime()."'"; undef }
+ | YEAR { $ci->{type}='year'; $ci->{default_value}= "'".$prng->year()."'"; undef } ;
+
+dt_blob: BLOB { $ci->{type}='blob'; undef } ;
+ | TINYBLOB { $ci->{type}='tinyblob'; undef }
+ | MEDIUMBLOB { $ci->{type}='mediumblob'; undef }
+ | LONGBLOB { $ci->{type}='longblob'; undef } ;
+
+dt_set_or_enum:
+ ENUM( word_list ) opt_charset { $ci->{type}='enum'; undef } ;
+ | SET( word_list ) opt_charset { $ci->{type}='enum'; undef } ;
+
+word_list: word | word, word_list ;
+
+word: 'w _smallint_unsigned w' ;
=== added file 'mysql-test/suite/backup_extra/t/backup_object_coverage.test'
--- a/mysql-test/suite/backup_extra/t/backup_object_coverage.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_extra/t/backup_object_coverage.test 2009-05-04 05:27:25 +0000
@@ -0,0 +1,161 @@
+# Basic BACKUP/RESTORE test using RQG.
+#
+# 1. generate CREATE statements with RQG and store in objects.sql
+# 2. source objects.sql
+# 3. save footprint of all the objects.
+# 4. backup databases
+# 5. drop databases
+# 6. restore from backup images
+# 7. compare obj footprint with the saved one.
+#
+# This test depends on:
+#
+# - Environment variable RQG_HOME which should point at RQG installation.
+# - Grammar file objects.grammar defining what objects will be generated.
+# - SQL file check-objects.sql which is used to generate footprint of the
+# objects present in the server.
+#
+# It is assumed that files objects.grammar and check-objects.sql reside in
+# suite/backup_extra/include/ directory.
+#
+
+--source include/have_innodb.inc
+--source include/have_falcon.inc
+--source include/have_rqg.inc
+
+let $query_count= 10000; # number of CREATE statements to generate
+
+let $incdir= suite/backup_extra/include;
+let $grammar= $incdir/objects.grammar;
+let $tmpdir= $MYSQLTEST_VARDIR/tmp;
+let $bdir=`select @@backupdir`;
+let MYISAM_DATA_DIR= $tmpdir;
+
+
+--disable_query_log
+--disable_result_log
+
+#################
+#
+# Setup
+#
+
+USE test;
+
+#
+# Create objects required by objects.grammar
+#
+
+CREATE USER usr0;
+CREATE USER usr1;
+CREATE USER usr2;
+CREATE USER usr3;
+CREATE USER usr4;
+CREATE USER usr5;
+CREATE USER usr6;
+CREATE USER usr7;
+CREATE USER usr8;
+CREATE USER usr9;
+
+CREATE DATABASE db0;
+CREATE DATABASE db1;
+CREATE DATABASE db2;
+CREATE DATABASE db3;
+CREATE DATABASE db4;
+CREATE DATABASE db5;
+CREATE DATABASE db6;
+CREATE DATABASE db7;
+CREATE DATABASE db8;
+CREATE DATABASE db9;
+
+CREATE TABLESPACE ts0 ADD DATAFILE 'ts0.data' ENGINE = Falcon;
+CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.data' ENGINE = Falcon;
+CREATE TABLESPACE ts2 ADD DATAFILE 'ts2.data' ENGINE = Falcon;
+CREATE TABLESPACE ts3 ADD DATAFILE 'ts3.data' ENGINE = Falcon;
+
+#################
+--echo #
+--echo # Start of test
+--echo #
+
+--echo # 1. generate CREATE statements with RQG and store in objects.sql
+--exec $RQG --seed=time --grammar=$grammar --queries=$query_count >$tmpdir/objects.sql
+
+--echo # 2. source objects.sql
+#
+# Note: some statements in objects.sql might fail. This is OK - we just
+# ignore it.
+#
+--disable_abort_on_error
+--source $tmpdir/objects.sql
+--enable_abort_on_error
+
+--echo # 3. save footprint of all the objects.
+--exec $MYSQL <$incdir/check-objects.sql >$tmpdir/footprint.pre
+
+--echo # 4. backup databases (this will take some time)
+BACKUP DATABASE db0, db1, db2, db3, db4, db5, db6, db7, db8, db9 TO 'boc.bkp';
+
+--echo # 5. drop databases
+DROP DATABASE db0;
+DROP DATABASE db1;
+DROP DATABASE db2;
+DROP DATABASE db3;
+DROP DATABASE db4;
+DROP DATABASE db5;
+DROP DATABASE db6;
+DROP DATABASE db7;
+DROP DATABASE db8;
+DROP DATABASE db9;
+
+--echo # 6. restore from backup image
+RESTORE FROM 'boc.bkp';
+
+--echo # 7. compare obj footprint with the saved one.
+--exec $MYSQL <$incdir/check-objects.sql >$tmpdir/footprint.post
+--diff_files $tmpdir/footprint.pre $tmpdir/footprint.post
+
+#################
+--echo #
+--echo # Cleanup
+--echo #
+
+--remove_file $bdir/boc.bkp
+--remove_file $tmpdir/objects.sql
+--remove_file $tmpdir/footprint.pre
+--remove_file $tmpdir/footprint.post
+
+#
+# Disable fkey constraint checks which can be violated when objects are
+# dropped.
+#
+SET foreign_key_checks= 0;
+
+DROP USER usr0;
+DROP USER usr1;
+DROP USER usr2;
+DROP USER usr3;
+DROP USER usr4;
+DROP USER usr5;
+DROP USER usr6;
+DROP USER usr7;
+DROP USER usr8;
+DROP USER usr9;
+
+DROP DATABASE db0;
+DROP DATABASE db1;
+DROP DATABASE db2;
+DROP DATABASE db3;
+DROP DATABASE db4;
+DROP DATABASE db5;
+DROP DATABASE db6;
+DROP DATABASE db7;
+DROP DATABASE db8;
+DROP DATABASE db9;
+
+DROP TABLESPACE ts0 ENGINE = Falcon;
+DROP TABLESPACE ts1 ENGINE = Falcon;
+DROP TABLESPACE ts2 ENGINE = Falcon;
+DROP TABLESPACE ts3 ENGINE = Falcon;
+
+--exit
Attachment: [text/bzr-bundle] bzr/rafal.somla@sun.com-20090504052725-643j5uf6wc5o4k6z.bundle
| Thread |
|---|
| • bzr commit into mysql-6.0-backup branch (Rafal.Somla:2800) WL#4727 | Rafal Somla | 4 May |
