← Back to branch summary

~mathiaz/ubuntu/lucid/mysql-dfsg-5.1/zap-bug-552053

« back to all changes in this revision

Viewing changes to mysql-test/r/information_schema.result

  • Committer: Bazaar Package Importer
  • Author(s): Mathias Gug
  • Date: 2009-06-25 12:55:45 UTC
  • mfrom: (1.1.2 upstream) (0.1.3 experimental)
  • Revision ID: james.westby@ubuntu.com-20090625125545-m8ogs96zzsri74xe
Tags: 5.1.34-1ubuntu1
https://launchpad.net/bugs/319843
https://launchpad.net/bugs/259477
https://launchpad.net/bugs/310211
https://launchpad.net/bugs/201799
https://launchpad.net/bugs/316974
http://bugs.debian.org/314785
http://bugs.debian.org/324834
http://bugs.debian.org/435165
http://bugs.debian.org/444216
https://launchpad.net/bugs/344816
https://launchpad.net/bugs/323755
* Merge from debian experimental (and 5.0 from main), remaining changes:
  - debian/mysql-server-5.1.config:
    + ask for MySQL root password at priority high instead of medium so
      that the password prompt is seen on a default install. (LP: #319843)
    + don't ask for root password when upgrading from a 5.0 install.
  - debian/control:
    + Make libmysqlclient16-dev a transitional package depending on
      libmysqlclient-dev.
    + Make libmysqlclient-dev conflict with libmysqlclient15-dev.
    + Don't build mysql-server, mysql-client, mysql-common and
      libmysqlclient15-dev binary packages since they're still provided
      by mysql-dfsg-5.0.
    + Make mysql-{client,server}-5.1 packages conflict and
      replace mysql-{client,server}-5.0, but not provide
      mysql-{client,server}.
    + Depend on a specific version of mysql-common rather than the src
      version of mysql-dfsg-5.1 since mysql-common is currently part of
      mysql-dfsg-5.0.
    + Lower mailx from a Recommends to a Suggests to avoid pulling in
      a full MTA on all installs of mysql-server. (LP: #259477)
  - debian/rules:
    + added -fno-strict-aliasing to CFLAGS to get around mysql testsuite
      build failures.
    + install mysql-test and sql-bench to /usr/share/mysql/ rather than
      /usr/.
  - debian/additions/debian-start.inc.sh: support ANSI mode (LP: #310211)
  - Add AppArmor profile:
    - debian/apparmor-profile: apparmor profile.
    - debian/rules, debian/mysql-server-5.0.files: install apparmor profile.
    - debian/mysql-server-5.0.dirs: add etc/apparmor.d/force-complain
    - debian/mysql-server-5.0.postrm: remove symlink in force-complain/ on
      purge.
    - debian/mysql-server-5.1.README.Debian: add apparmor documentation.
    - debian/additions/my.cnf: Add warning about apparmor. (LP: #201799)
    - debian/mysql-server-5.1.postinst: reload apparmor profiles.
  - debian/additions/my.cnf: remove language option. Error message files are
    located in a different directory in MySQL 5.0. Setting the language
    option to use /usr/share/mysql/english breaks 5.0. Both 5.0 and 5.1
    use a default value that works. (LP: #316974)
  - debian/mysql-server-5.1.mysql.init:
    + Clearly indicate that we do not support running multiple instances
      of mysqld by duplicating the init script.
      (closes: #314785, #324834, #435165, #444216)
    + Properly parameterize all existing references to the mysql config
      file (/etc/mysql/my.cnf).
  - debian/mysql-server-5.0.postinst: Clear out the second password
    when setting up mysql. (LP: #344816)
  - mysql-server-core-5.1 package for files needed by Akonadi:
    + debian/control: create mysql-server-core-5.1 package.
    + debian/mysql-server-core-5.1.files, debian/mysql-server-5.1.files:
      move core mysqld files to mysql-server-core-5.1 package.
  - Don't package sql-bench and mysql-test file.
* Dropped changes:
  - debian/patches/92_ssl_test_cert.dpatch: certificate expiration in
    test suite (LP: #323755). Included upstream.
* Dropped from 5.0:
  - apparmor profile:
    - debian/control: Recommends apparmor >= 2.1+1075-0ubuntu6. All version
      of apparmor-profile (>hardy) are higher than this version.
    - debian/mysql-server-5.0.preinst: create symlink for force-complain/
      on pre-feisty upgrades, upgrades where apparmor-profiles profile is
      unchanged (ie non-enforcing) and upgrades where the profile
      doesn't exist. Support for pre-hardy upgrades is no longer needed.
* debian/mysql-server-5.1.postinst: fix debian-sys-maint user creation.

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/r/information_schema.result
9
9
create user mysqltest_3;
10
10
select * from information_schema.SCHEMATA where schema_name > 'm';
11
11
CATALOG_NAME    SCHEMA_NAME     DEFAULT_CHARACTER_SET_NAME      DEFAULT_COLLATION_NAME  SQL_PATH
 
12
NULL    mtr     latin1  latin1_swedish_ci       NULL
12
13
NULL    mysql   latin1  latin1_swedish_ci       NULL
13
14
NULL    test    latin1  latin1_swedish_ci       NULL
14
15
select schema_name from information_schema.schemata;
15
16
schema_name
16
17
information_schema
 
18
mtr
17
19
mysql
18
20
test
19
21
show databases like 't%';
22
24
show databases;
23
25
Database
24
26
information_schema
 
27
mtr
25
28
mysql
26
29
test
27
30
show databases where `database` = 't%';
33
36
create table mysqltest.t4(a int);
34
37
create table t5 (id int auto_increment primary key);
35
38
insert into t5 values (10);
36
 
create view v1 (c) as select table_name from information_schema.TABLES where table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status';
 
39
create view v1 (c) as
 
40
SELECT table_name FROM information_schema.TABLES
 
41
WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND
 
42
table_name<>'ndb_binlog_index' AND
 
43
table_name<>'ndb_apply_status';
37
44
select * from v1;
38
45
c
39
46
CHARACTER_SETS
92
99
t3
93
100
t5
94
101
v1
95
 
select c,table_name from v1 
 
102
select c,table_name from v1
96
103
inner join information_schema.TABLES v2 on (v1.c=v2.table_name)
97
104
where v1.c like "t%";
98
105
c       table_name
111
118
t2      t2
112
119
t3      t3
113
120
t5      t5
114
 
select c,table_name from v1 
 
121
select c,table_name from v1
115
122
left join information_schema.TABLES v2 on (v1.c=v2.table_name)
116
123
where v1.c like "t%";
117
124
c       table_name
190
197
create view mysqltest.v1 (c) as select a from mysqltest.t1;
191
198
grant select (a) on mysqltest.t1 to mysqltest_2@localhost;
192
199
grant select on mysqltest.v1 to mysqltest_3;
193
 
select table_name, column_name, privileges from information_schema.columns 
 
200
select table_name, column_name, privileges from information_schema.columns
194
201
where table_schema = 'mysqltest' and table_name = 't1';
195
202
table_name      column_name     privileges
196
203
t1      a       select
268
275
select * from t1;
269
276
select * from t2;
270
277
end|
271
 
select parameter_style, sql_data_access, dtd_identifier 
272
 
from information_schema.routines;
 
278
select parameter_style, sql_data_access, dtd_identifier
 
279
from information_schema.routines where routine_schema='test';
273
280
parameter_style sql_data_access dtd_identifier
274
281
SQL     CONTAINS SQL    NULL
275
282
SQL     CONTAINS SQL    int(11)
276
 
show procedure status;
 
283
show procedure status where db='test';
277
284
Db      Name    Type    Definer Modified        Created Security_type   Comment character_set_client    collation_connection    Database Collation
278
285
test    sel2    PROCEDURE       root@localhost  #       #       DEFINER         latin1  latin1_swedish_ci       latin1_swedish_ci
279
 
show function status;
 
286
show function status where db='test';
280
287
Db      Name    Type    Definer Modified        Created Security_type   Comment character_set_client    collation_connection    Database Collation
281
288
test    sub1    FUNCTION        root@localhost  #       #       DEFINER         latin1  latin1_swedish_ci       latin1_swedish_ci
282
289
select a.ROUTINE_NAME from information_schema.ROUTINES a,
283
290
information_schema.SCHEMATA b where
284
 
a.ROUTINE_SCHEMA = b.SCHEMA_NAME;
 
291
a.ROUTINE_SCHEMA = b.SCHEMA_NAME AND b.SCHEMA_NAME='test';
285
292
ROUTINE_NAME
286
293
sel2
287
294
sub1
292
299
1       SIMPLE  #       ALL     NULL    NULL    NULL    NULL    NULL    
293
300
1       SIMPLE  #       ALL     NULL    NULL    NULL    NULL    NULL    Using where; Using join buffer
294
301
select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a,
295
 
mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) order by 1;
 
302
mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) AND a.ROUTINE_SCHEMA='test' order by 1;
296
303
ROUTINE_NAME    name
297
304
sel2    sel2
298
305
sub1    sub1
299
 
select count(*) from information_schema.ROUTINES;
 
306
select count(*) from information_schema.ROUTINES where routine_schema='test';
300
307
count(*)
301
308
2
302
 
create view v1 as select routine_schema, routine_name from information_schema.routines 
 
309
create view v1 as select routine_schema, routine_name from information_schema.routines where routine_schema='test'
303
310
order by routine_schema, routine_name;
304
311
select * from v1;
305
312
routine_schema  routine_name
351
358
select * from v0;
352
359
c
353
360
information_schema
 
361
mtr
354
362
mysql
355
363
test
356
364
explain select * from v0;
552
560
create table t1(a NUMERIC(5,3), b NUMERIC(5,1), c float(5,2),
553
561
d NUMERIC(6,4), e float, f DECIMAL(6,3), g int(11), h DOUBLE(10,3),
554
562
i DOUBLE);
555
 
select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, 
 
563
select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH,
556
564
CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
557
565
from information_schema.columns where table_name= 't1';
558
566
COLUMN_NAME     COLUMN_TYPE     CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION       NUMERIC_SCALE
613
621
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
614
622
drop view vo;
615
623
select TABLE_NAME,TABLE_TYPE,ENGINE
616
 
from information_schema.tables 
 
624
from information_schema.tables
617
625
where table_schema='information_schema' limit 2;
618
626
TABLE_NAME      TABLE_TYPE      ENGINE
619
627
CHARACTER_SETS  SYSTEM VIEW     MEMORY
718
726
column_type
719
727
varchar(32)
720
728
varchar(32)
721
 
select TABLE_ROWS from information_schema.tables where 
 
729
select TABLE_ROWS from information_schema.tables where
722
730
table_schema="information_schema" and table_name="COLUMNS";
723
731
TABLE_ROWS
724
732
NULL
752
760
drop view a2, a1;
753
761
drop table t_crashme;
754
762
select table_schema,table_name, column_name from
755
 
information_schema.columns 
 
763
information_schema.columns
756
764
where data_type = 'longtext';
757
765
table_schema    table_name      column_name
758
766
information_schema      COLUMNS COLUMN_DEFAULT
796
804
event   starts  datetime
797
805
event   ends    datetime
798
806
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A
799
 
WHERE NOT EXISTS 
 
807
WHERE NOT EXISTS
800
808
(SELECT * FROM INFORMATION_SCHEMA.COLUMNS B
801
809
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
802
810
AND A.TABLE_NAME = B.TABLE_NAME);
825
833
x_double_precision      NULL    NULL
826
834
drop table t1;
827
835
grant select on test.* to mysqltest_4@localhost;
828
 
SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS 
 
836
SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS
829
837
where COLUMN_NAME='TABLE_NAME';
830
838
TABLE_NAME      COLUMN_NAME     PRIVILEGES
831
839
COLUMNS TABLE_NAME      select
842
850
delete from mysql.user where user='mysqltest_4';
843
851
delete from mysql.db where user='mysqltest_4';
844
852
flush privileges;
845
 
SELECT table_schema, count(*) FROM information_schema.TABLES where table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status' GROUP BY TABLE_SCHEMA;
 
853
SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status' GROUP BY TABLE_SCHEMA;
846
854
table_schema    count(*)
847
855
information_schema      28
848
856
mysql   22
882
890
set @fired:= "Yes";
883
891
end if;
884
892
end     AFTER   NULL            root@localhost  latin1  latin1_swedish_ci       latin1_swedish_ci
885
 
select * from information_schema.triggers;
 
893
select * from information_schema.triggers where trigger_schema in ('mysql', 'information_schema', 'test', 'mysqltest');
886
894
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
887
895
NULL    test    trg1    INSERT  NULL    test    t1      0       NULL    begin
888
896
if new.j > 10 then
1071
1079
SELECT 'foo' FROM DUAL;
1072
1080
END |
1073
1081
ERROR 42000: Unknown database 'information_schema'
1074
 
select  ROUTINE_NAME from routines;
 
1082
select ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema';
1075
1083
ROUTINE_NAME
1076
1084
grant all on information_schema.* to 'user1'@'localhost';
1077
1085
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
1153
1161
INSERT INTO `proc` VALUES ('test','','PROCEDURE','','SQL','CONTAINS_SQL',
1154
1162
'NO','DEFINER','','','BEGIN\r\n  \r\nEND','root@%','2006-03-02 18:40:03',
1155
1163
'2006-03-02 18:40:03','','','utf8','utf8_general_ci','utf8_general_ci','n/a');
1156
 
select routine_name from information_schema.routines;
 
1164
select routine_name from information_schema.routines where ROUTINE_SCHEMA='test';
1157
1165
routine_name
1158
1166
 
1159
1167
delete from proc where name='';
1187
1195
CREATE USER mysql_bug20230@localhost;
1188
1196
GRANT EXECUTE ON PROCEDURE p1 TO mysql_bug20230@localhost;
1189
1197
GRANT EXECUTE ON FUNCTION f1 TO mysql_bug20230@localhost;
1190
 
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES;
 
1198
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test';
1191
1199
ROUTINE_NAME    ROUTINE_DEFINITION
1192
1200
f1      RETURN @a + 1
1193
1201
p1      SET @a= 1
1199
1207
Function        sql_mode        Create Function character_set_client    collation_connection    Database Collation
1200
1208
f1              CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
1201
1209
RETURN @a + 1   latin1  latin1_swedish_ci       latin1_swedish_ci
1202
 
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES;
 
1210
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test';
1203
1211
ROUTINE_NAME    ROUTINE_DEFINITION
1204
1212
f1      NULL
1205
1213
p1      NULL
1302
1310
TRIGGERS        TRIGGER_SCHEMA
1303
1311
USER_PRIVILEGES GRANTEE
1304
1312
VIEWS   TABLE_SCHEMA
1305
 
SELECT MAX(table_name) FROM information_schema.tables;
 
1313
SELECT MAX(table_name) FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test');
1306
1314
MAX(table_name)
1307
1315
VIEWS
1308
1316
SELECT table_name from information_schema.tables
1309
1317
WHERE table_name=(SELECT MAX(table_name)
1310
 
FROM information_schema.tables);
 
1318
FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test'));
1311
1319
table_name
1312
1320
VIEWS
1313
1321
DROP TABLE IF EXISTS bug23037;
1626
1634
Db      Name    Definer Time zone       Type    Execute at      Interval value  Interval field  Starts  Ends    Status  Originator      character_set_client    collation_connection    Database Collation
1627
1635
use test;
1628
1636
#
1629
 
# Bug#34166: Server crash in SHOW OPEN TABLES and prelocking
 
1637
# Bug#34166 Server crash in SHOW OPEN TABLES and prelocking
1630
1638
#
1631
1639
drop table if exists t1;
1632
1640
drop function if exists f1;
1712
1720
CREATE_OPTIONS
1713
1721
KEY_BLOCK_SIZE=1
1714
1722
DROP TABLE t1;
 
1723
SET TIMESTAMP=@@TIMESTAMP + 10000000;
 
1724
SELECT 'OK' AS TEST_RESULT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time < 0;
 
1725
TEST_RESULT
 
1726
OK
 
1727
SET TIMESTAMP=DEFAULT;
1715
1728
End of 5.1 tests.