information_schema.test

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

#
# Bug#14089 FROM list subquery always fails when information_schema is current database
#
use test;
create table t1(id int);
insert into t1(id) values (1);
select 1 from (select 1 from test.t1) a;
use information_schema;
select 1 from (select 1 from test.t1) a;
use test;
drop table t1;

#
# Bug#14476 `information_schema`.`TABLES`.`TABLE_TYPE` with empty value
#
create table t1 (f1 int(11));
create view v1 as select * from t1;
drop table t1;
select table_type from information_schema.tables
where table_name="v1";
drop view v1;

#
# Bug #14387 SHOW COLUMNS doesn't work on temporary tables
# Bug #15224 SHOW INDEX from temporary table doesn't work
# Bug #12770 DESC cannot display the info. about temporary table
#
create temporary table t1(f1 int, index(f1));
show columns from t1;
describe t1;
show indexes from t1;
drop table t1;

#
# Bug#14271 I_S: columns has no size for (var)binary columns
#
create table t1(f1 binary(32), f2 varbinary(64));
select character_maximum_length, character_octet_length
from information_schema.columns where table_name='t1';
drop table t1;

#
# Bug#15533 crash, information_schema, function, view
#
CREATE TABLE t1 (f1 BIGINT, f2 VARCHAR(20), f3 BIGINT);
INSERT INTO t1 SET f1 = 1, f2 = 'Schoenenbourg', f3 = 1;

CREATE FUNCTION func2() RETURNS BIGINT RETURN 1;

delimiter //;
CREATE FUNCTION func1() RETURNS BIGINT
BEGIN
  RETURN ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS);
END//
delimiter ;//

CREATE VIEW v1 AS SELECT 1 FROM t1
                    WHERE f3 = (SELECT func2 ());
SELECT func1();
DROP TABLE t1;
DROP VIEW v1;
DROP FUNCTION func1;
DROP FUNCTION func2;


#
# Bug#15307 GROUP_CONCAT() with ORDER BY returns empty set on information_schema
#
select column_type, group_concat(table_schema, '.', table_name), count(*) as num
from information_schema.columns where
table_schema='information_schema' and
(column_type = 'varchar(7)' or column_type = 'varchar(20)')
group by column_type order by num;

#
# Bug#19236 bad COLUMNS.CHARACTER_MAXIMUM_LENGHT and CHARACTER_OCTET_LENGTH
#
create table t1(f1 char(1) not null, f2 char(9) not null)
default character set utf8;
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
information_schema.columns where table_schema='test' and table_name = 't1';
drop table t1;

#
# Bug#18177 any access to INFORMATION_SCHEMA.ROUTINES crashes
#
use mysql;
INSERT INTO `proc` VALUES ('test','','PROCEDURE','','SQL','CONTAINS_SQL',
'NO','DEFINER','','','BEGIN\r\n  \r\nEND','root@%','2006-03-02 18:40:03',
'2006-03-02 18:40:03','','');
select routine_name from information_schema.routines;
delete from proc where name='';
use test;

#
# Bug#16681 information_schema shows forbidden VIEW details
#
grant select on test.* to mysqltest_1@localhost;
create table t1 (id int);
create view v1 as select * from t1;
create definer = mysqltest_1@localhost
sql security definer view v2 as select 1;

connect (con16681,localhost,mysqltest_1,,test);
connection con16681;

select * from information_schema.views
where table_name='v1' or table_name='v2';
connection default;
drop view v1, v2;
drop table t1;
drop user mysqltest_1@localhost;

#
# Bug#19599 duplication of information_schema column value in a CONCAT expr with user var
#
set @a:= '.';
create table t1(f1 char(5));
create table t2(f1 char(5));
select concat(@a, table_name), @a, table_name
from information_schema.tables where table_schema = 'test';
drop table t1,t2;


#
# Bug#20230: routine_definition is not null
#
--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;
--enable_warnings

CREATE PROCEDURE p1() SET @a= 1;
CREATE FUNCTION f1() RETURNS INT RETURN @a + 1;
CREATE USER mysql_bug20230@localhost;
GRANT EXECUTE ON PROCEDURE p1 TO mysql_bug20230@localhost;
GRANT EXECUTE ON FUNCTION f1 TO mysql_bug20230@localhost;

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES;
SHOW CREATE PROCEDURE p1;
SHOW CREATE FUNCTION f1;

connect (conn1, localhost, mysql_bug20230,,);

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES;
SHOW CREATE PROCEDURE p1;
SHOW CREATE FUNCTION f1;
CALL p1();
SELECT f1();

disconnect conn1;
connection default;

DROP FUNCTION f1;
DROP PROCEDURE p1;
DROP USER mysql_bug20230@localhost;

#
# Bug#18925: subqueries with MIN/MAX functions on INFORMARTION_SCHEMA 
#

SELECT t.table_name, c1.column_name
  FROM information_schema.tables t
       INNER JOIN
       information_schema.columns c1
       ON t.table_schema = c1.table_schema AND
          t.table_name = c1.table_name
  WHERE t.table_schema = 'information_schema' AND
        c1.ordinal_position =
        ( SELECT COALESCE(MIN(c2.ordinal_position),1)
            FROM information_schema.columns c2
            WHERE c2.table_schema = t.table_schema AND
                  c2.table_name = t.table_name AND
                  c2.column_name LIKE '%SCHEMA%'
        );
SELECT t.table_name, c1.column_name
  FROM information_schema.tables t
       INNER JOIN
       information_schema.columns c1
       ON t.table_schema = c1.table_schema AND
          t.table_name = c1.table_name
  WHERE t.table_schema = 'information_schema' AND
        c1.ordinal_position =
        ( SELECT COALESCE(MIN(c2.ordinal_position),1)
            FROM information_schema.columns c2
            WHERE c2.table_schema = 'information_schema' AND
                  c2.table_name = t.table_name AND
                  c2.column_name LIKE '%SCHEMA%'
        );

#
# Bug#21231: query with a simple non-correlated subquery over
#            INFORMARTION_SCHEMA.TABLES 
#

SELECT MAX(table_name) FROM information_schema.tables;
SELECT table_name from information_schema.tables
  WHERE table_name=(SELECT MAX(table_name)
                      FROM information_schema.tables);

#
# Bug #23037: Bug in field "Default" of query "SHOW COLUMNS FROM table"
#
# Note, MyISAM/InnoDB can't take more that 65532 chars, because the row
# size is limited to 65535 bytes (BLOBs not counted)
#
--disable_warnings
DROP TABLE IF EXISTS bug23037;
DROP FUNCTION IF EXISTS get_value;
--enable_warnings
--disable_query_log
DELIMITER |;
CREATE FUNCTION get_value()
  RETURNS TEXT
  DETERMINISTIC
BEGIN
  DECLARE col1, col2, col3, col4, col6 CHAR(255);
  DECLARE default_val VARCHAR(65532);
  DECLARE done INT DEFAULT 0;
  DECLARE cur1 CURSOR FOR SHOW COLUMNS FROM bug23037;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  OPEN cur1;
  FETCH cur1 INTO col1, col2, col3, col4, default_val, col6;
  CLOSE cur1;
  RETURN default_val;
end|
DELIMITER ;|

let $body=`SELECT REPEAT('A', 65532)`;
eval CREATE TABLE bug23037(fld1 VARCHAR(65532) CHARACTER SET latin1 DEFAULT "$body");
--enable_query_log

SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';

SELECT MD5(get_value());

SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT), COLUMN_DEFAULT=get_value() FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';

DROP TABLE bug23037;
DROP FUNCTION get_value;

#
# Bug#22413: EXPLAIN SELECT FROM view with ORDER BY yield server crash
#
create view v1 as
select table_schema as object_schema,
       table_name   as object_name,
       table_type   as object_type
from information_schema.tables
order by object_schema;
explain select * from v1;
explain select * from (select table_name from information_schema.tables) as a;
drop view v1;

#
# Bug#23299 Some queries against INFORMATION_SCHEMA with subqueries fail
#
create table t1 (f1 int(11));
create table t2 (f1 int(11), f2 int(11));

select table_name from information_schema.tables
where table_schema = 'test' and table_name not in
(select table_name from information_schema.columns
 where table_schema = 'test' and column_name = 'f3');
drop table t1,t2;


#
# Bug#24630  Subselect query crashes mysqld
#
select 1 as f1 from information_schema.tables  where "CHARACTER_SETS"=
(select cast(table_name as char)  from information_schema.tables
 order by table_name limit 1) limit 1;

select t.table_name, group_concat(t.table_schema, '.', t.table_name),
       count(*) as num1
from information_schema.tables t
inner join information_schema.columns c1
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
where t.table_schema = 'information_schema' and
        c1.ordinal_position =
        (select isnull(c2.column_type) -
         isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
         count(*) as num
         from information_schema.columns c2 where
         c2.table_schema='information_schema' and
         (c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
          group by c2.column_type order by num limit 1)
group by t.table_name order by num1, t.table_name;
# Bug#24822: Patch: uptime_since_flush_status
#
--replace_column 2 #
show global status like "Uptime_%";
flush status;
--replace_column 2 #
show global status like "Uptime_%";  # Almost certainly zero


#
# Bug#28266 IS_UPDATABLE field on VIEWS table in I_S database is wrong
#
create table t1(f1 int);
create view v1 as select f1+1 as a from t1;
create table t2 (f1 int, f2 int);                                                         
create view v2 as select f1+1 as a, f2 as b from t2;                                      
select table_name, is_updatable from information_schema.views;
#
# Note: we can perform 'delete' for non updatable view.
#
delete from v1;
drop view v1,v2;
drop table t1,t2;

#
# Bug#25859    ALTER DATABASE works w/o parameters
#
--error ER_PARSE_ERROR
alter database;
--error ER_PARSE_ERROR
alter database test;

#
# Bug#27629 Possible security flaw in INFORMATION_SCHEMA and SHOW statements
#

create database mysqltest;
create table mysqltest.t1(a int, b int, c int);
create trigger mysqltest.t1_ai after insert on mysqltest.t1
  for each row set @a = new.a + new.b + new.c;
grant select(b) on mysqltest.t1 to mysqltest_1@localhost;

select trigger_name from information_schema.triggers
where event_object_table='t1';
show triggers from mysqltest;

connect (con27629,localhost,mysqltest_1,,mysqltest);
show columns from t1;
select column_name from information_schema.columns where table_name='t1';

show triggers;
select trigger_name from information_schema.triggers
where event_object_table='t1';
connection default;
drop user mysqltest_1@localhost;
drop database mysqltest;

#
# Bug#27747 database metadata doesn't return sufficient column default info
#
create table t1 (
  f1 varchar(50),
  f2 varchar(50) not null,
  f3 varchar(50) default '',
  f4 varchar(50) default NULL,
  f5 bigint not null,
  f6 bigint not null default 10,
  f7 datetime not null,
  f8 datetime default '2006-01-01'
);
select column_default from information_schema.columns where table_name= 't1';
show columns from t1;
drop table t1;

--echo End of 5.0 tests.