• Matthias Leich mleich@mysql.com's avatar
    Fix for · 28f97047
    Matthias Leich mleich@mysql.com authored
       Bug#37167 funcs_1: Many tests fail if the embedded server is used.
       Bug#37164 funcs_1: Some tests fail if an optional character set is missing.
    + some cleanup within the testsuite related to the fixes above
    + some adjustments to open bugs on Mac OS X
    
    Details:
    - Remove the initial loading of data from tests if these data
      are not somewhere retrieved
    - Remove any use of columns with attribute unicode
      (-> UCS2 is no more needed) from tests where unicode
      properties are not checked or somehow required
    - Create a separate branch of the Character maximum length test
      (CML). If UCS2 is available than this test gets applied to
      every available type of string column with attribute unicode
      This prevents any loss of coverage by the points above.
    - Disable the execution of is_tables_ndb which gives wrong
      results because of a bug. Correct the exepected results of
      this test.
    - In case of tests failing when applied to the embedded server
        1) Create a variant of this test for the embedded server
      or
        2) Skip the test in case of embedded server
      depending on purpose and complexity of test.
    - Skip the tests which could suffer from
      Bug 28309 First insert violates unique constraint - was "memory" table empty ?
      Bug 37380 Test funcs_1.is_columns_myisam_embedded fails on OS X
      (both bugs Mac OS X, embedded server, MySQL 5.0 only)
    - Minor improvements like remove typos
    28f97047
is_views_embedded.result 11 KB
SHOW TABLES FROM information_schema LIKE 'VIEWS';
Tables_in_information_schema (VIEWS)
VIEWS
#######################################################################
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
#######################################################################
DROP VIEW      IF EXISTS test.v1;
DROP PROCEDURE IF EXISTS test.p1;
DROP FUNCTION  IF EXISTS test.f1;
CREATE VIEW test.v1 AS     SELECT * FROM information_schema.VIEWS;
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.VIEWS;
CREATE FUNCTION test.f1() returns BIGINT
BEGIN
DECLARE counter BIGINT DEFAULT NULL;
SELECT COUNT(*) INTO counter FROM information_schema.VIEWS;
RETURN counter;
END//
# Attention: The printing of the next result sets is disabled.
SELECT * FROM information_schema.VIEWS;
SELECT * FROM test.v1;
CALL test.p1;
SELECT test.f1();
DROP VIEW test.v1;
DROP PROCEDURE test.p1;
DROP FUNCTION test.f1;
#########################################################################
# Testcase 3.2.13.1: INFORMATION_SCHEMA.VIEWS layout
#########################################################################
DESCRIBE          information_schema.VIEWS;
Field	Type	Null	Key	Default	Extra
TABLE_CATALOG	varchar(512)	YES		NULL	
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			
SHOW CREATE TABLE information_schema.VIEWS;
Table	Create Table
VIEWS	CREATE TEMPORARY TABLE `VIEWS` (
  `TABLE_CATALOG` varchar(512) default NULL,
  `TABLE_SCHEMA` varchar(64) NOT NULL default '',
  `TABLE_NAME` varchar(64) NOT NULL default '',
  `VIEW_DEFINITION` longtext NOT NULL,
  `CHECK_OPTION` varchar(8) NOT NULL default '',
  `IS_UPDATABLE` varchar(3) NOT NULL default '',
  `DEFINER` varchar(77) NOT NULL default '',
  `SECURITY_TYPE` varchar(7) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
SHOW COLUMNS FROM information_schema.VIEWS;
Field	Type	Null	Key	Default	Extra
TABLE_CATALOG	varchar(512)	YES		NULL	
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			
SELECT table_catalog, table_schema, table_name
FROM information_schema.views WHERE table_catalog IS NOT NULL;
table_catalog	table_schema	table_name
################################################################################
# Testcase 3.2.13.2 + 3.2.13.3: INFORMATION_SCHEMA.VIEWS accessible information
################################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
DROP   USER 'testuser2'@'localhost';
CREATE USER 'testuser2'@'localhost';
DROP   USER 'test_no_views'@'localhost';
CREATE USER 'test_no_views'@'localhost';
CREATE TABLE db_datadict.t1(f1 INT, f2 INT, f3 INT)
ENGINE = <engine_type>;
CREATE VIEW db_datadict.v_granted_to_1 AS SELECT * FROM db_datadict.t1;
CREATE VIEW db_datadict.v_granted_glob AS SELECT f2, f3 FROM db_datadict.t1;
GRANT SELECT ON db_datadict.t1 TO 'testuser1'@'localhost';
GRANT SELECT ON db_datadict.v_granted_to_1 TO 'testuser1'@'localhost';
GRANT SHOW VIEW, CREATE VIEW ON db_datadict.* TO 'testuser2'@'localhost';
SELECT * FROM information_schema.views
WHERE table_schema = 'db_datadict' ORDER BY table_name;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE
NULL	db_datadict	v_granted_glob	/* ALGORITHM=UNDEFINED */ select `db_datadict`.`t1`.`f2` AS `f2`,`db_datadict`.`t1`.`f3` AS `f3` from `db_datadict`.`t1`	NONE	YES	root@localhost	DEFINER
NULL	db_datadict	v_granted_to_1	/* ALGORITHM=UNDEFINED */ select `db_datadict`.`t1`.`f1` AS `f1`,`db_datadict`.`t1`.`f2` AS `f2`,`db_datadict`.`t1`.`f3` AS `f3` from `db_datadict`.`t1`	NONE	YES	root@localhost	DEFINER
# Establish connection testuser1 (user=testuser1)
SELECT * FROM information_schema.views
WHERE table_schema = 'db_datadict' ORDER BY table_name;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE
NULL	db_datadict	v_granted_glob		NONE	YES	root@localhost	DEFINER
NULL	db_datadict	v_granted_to_1		NONE	YES	root@localhost	DEFINER
# Establish connection testuser2 (user=testuser2)
SELECT * FROM information_schema.views
WHERE table_schema = 'db_datadict' ORDER BY table_name;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE
NULL	db_datadict	v_granted_glob		NONE	YES	root@localhost	DEFINER
NULL	db_datadict	v_granted_to_1		NONE	YES	root@localhost	DEFINER
# Establish connection test_no_views (user=test_no_views)
SELECT * FROM information_schema.views
WHERE table_schema = 'db_datadict' ORDER BY table_name;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE
NULL	db_datadict	v_granted_glob		NONE	YES	root@localhost	DEFINER
NULL	db_datadict	v_granted_to_1		NONE	YES	root@localhost	DEFINER
# Switch to connection default and close all other connections
DROP USER 'testuser1'@'localhost';
DROP USER 'testuser2'@'localhost';
DROP USER 'test_no_views'@'localhost';
DROP DATABASE db_datadict;
#########################################################################
# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.VIEWS modifications
#########################################################################
DROP TABLE IF EXISTS test.t1_my_table;
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
CREATE TABLE test.t1_table (f1 BIGINT, f2 CHAR(10))
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
ENGINE = <engine_type>;
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
SELECT * FROM information_schema.views
WHERE table_name LIKE 't1_%';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE
CREATE VIEW test.t1_view AS SELECT DISTINCT f1 FROM test.t1_table;
SELECT * FROM information_schema.views
WHERE table_name LIKE 't1_%';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE
NULL	test	t1_view	/* ALGORITHM=UNDEFINED */ select distinct `test`.`t1_table`.`f1` AS `f1` from `test`.`t1_table`	NONE	NO	root@localhost	DEFINER
SELECT table_name,definer FROM information_schema.views
WHERE table_name = 't1_view';
table_name	definer
t1_view	root@localhost
ALTER DEFINER = 'testuser1'@'localhost' VIEW test.t1_view AS
SELECT DISTINCT f1 FROM test.t1_table;
SELECT table_name,definer,security_type FROM information_schema.views
WHERE table_name LIKE 't1_%';
table_name	definer	security_type
t1_view	testuser1@localhost	DEFINER
ALTER DEFINER = 'root'@'localhost' SQL SECURITY INVOKER VIEW test.t1_view AS
SELECT f1 FROM test.t1_table WITH LOCAL CHECK OPTION;
SELECT table_name,definer,security_type FROM information_schema.views
WHERE table_name LIKE 't1_%';
table_name	definer	security_type
t1_view	root@localhost	INVOKER
SELECT table_schema,table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_schema,table_name;
table_schema	table_name
test	t1_view
RENAME TABLE test.t1_view TO db_datadict.t1_view;
ERROR HY000: Changing schema from 'test' to 'db_datadict' is not allowed.
DROP VIEW test.t1_view;
CREATE VIEW db_datadict.t1_view AS SELECT * FROM test.t1_table;
SELECT table_schema,table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_schema,table_name;
table_schema	table_name
db_datadict	t1_view
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
table_name
t1_view
RENAME TABLE db_datadict.t1_view TO db_datadict.t1_viewx;
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
table_name
t1_viewx
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
table_name
t1_viewx
DROP VIEW db_datadict.t1_viewx;
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
table_name
CREATE VIEW db_datadict.t1_view AS SELECT * FROM test.t1_table;
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
table_name
t1_view
DROP TABLE test.t1_table;
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
table_name
t1_view
Warnings:
Warning	1356	View 'db_datadict.t1_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
CREATE TABLE test.t1_table (f1 BIGINT, f2 CHAR(10))
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci COMMENT = 'Initial Comment'
ENGINE = <engine_type>;
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
table_name
t1_view
DROP DATABASE db_datadict;
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
table_name
DROP USER 'testuser1'@'localhost';
DROP TABLE test.t1_table;
########################################################################
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
#           DDL on INFORMATION_SCHEMA table are not supported
########################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
CREATE VIEW db_datadict.v1 AS SELECT 1;
INSERT INTO information_schema.views
SELECT * FROM information_schema.views;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
INSERT INTO information_schema.views(table_schema, table_name)
VALUES ('db2', 'v2');
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
UPDATE information_schema.views SET table_schema = 'test'
WHERE table_name = 't1';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DELETE FROM information_schema.views WHERE table_name = 't1';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
TRUNCATE information_schema.views;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE INDEX my_idx_on_views ON information_schema.views(table_schema);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.views DROP PRIMARY KEY;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.views ADD f1 INT;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE information_schema.views;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.views RENAME db_datadict.views;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.views RENAME information_schema.xviews;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP DATABASE db_datadict;