| Bug #13622 | VIEW depending on information_schema.TRIGGERS claims it does not exist | ||
|---|---|---|---|
| Submitted: | 29 Sep 2005 20:23 | Modified: | 20 Nov 2005 4:49 |
| Reporter: | Roland Bouman | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Views | Severity: | S3 (Non-critical) |
| Version: | 5.0.13/BK source | OS: | Linux (Linux Suse, WinXP prof) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[29 Sep 2005 20:27]
Roland Bouman
So sorry! My working (2nd) example should read this:
view
schema_objects (
db
, name
, type
, subtype
, created
, comment
)
as
select trigger_schema
, trigger_name
, _utf8'trigger'
, concat(
action_timing
, _utf8' '
, event_manipulation
, _utf8' '
, _utf8'FOR EACH '
, action_orientation
)
, created
, _utf8''
from information_schema.triggers
union all
select table_schema
, table_name
, table_type
, engine
, create_time
, table_comment
from information_schema.tables
union all
select routine_schema
, routine_name
, _utf8'routine'
, routine_type
, created
, routine_comment
from information_schema.routines
;
[30 Sep 2005 0:00]
MySQL Verification Team
miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.14-rc-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create or replace
-> view
-> schema_objects (
-> db
-> , name
-> , type
-> , subtype
-> , created
-> , comment
-> )
-> as
-> select table_schema
-> , table_name
-> , table_type
-> , engine
-> , create_time
-> , table_comment
-> from information_schema.tables
-> union all
-> select trigger_schema
-> , trigger_name
-> , _utf8'trigger'
-> , concat(
-> action_timing
-> , _utf8' '
-> , event_manipulation
-> , _utf8' '
-> , _utf8'FOR EACH '
-> , action_orientation
-> )
-> , created
-> , _utf8''
-> from information_schema.triggers
-> union all
-> select routine_schema
-> , routine_name
-> , _utf8'routine'
-> , routine_type
-> , created
-> , routine_comment
-> from information_schema.routines
-> ;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> select * from schema_objects;
ERROR 1109 (42S02): Unknown table 'triggers' in field list
mysql> select table_schema
-> , table_name
-> , table_type
-> , engine
-> , create_time
-> , table_comment
-> from information_schema.tables
-> union all
-> select trigger_schema
-> , trigger_name
-> , _utf8'trigger'
-> , concat(
-> action_timing
-> , _utf8' '
-> , event_manipulation
-> , _utf8' '
-> , _utf8'FOR EACH '
-> , action_orientation
-> )
-> , created
-> , _utf8''
-> from information_schema.triggers
-> union all
-> select routine_schema
-> , routine_name
-> , _utf8'routine'
-> , routine_type
-> , created
-> , routine_comment
-> from information_schema.routines\G
*************************** 1. row ***************************
table_schema: information_schema
table_name: CHARACTER_SETS
table_type: SYSTEM VIEW
engine: MEMORY
create_time: NULL
table_comment:
*************************** 2. row ***************************
<cut>
engine: FUNCTION
create_time: 2005-09-29 11:06:14
table_comment:
64 rows in set (0.18 sec)
[30 Sep 2005 0:06]
Roland Bouman
BTW, I'm running MySQL on WinXP Professional
[17 Oct 2005 20:02]
Roland Bouman
I think I have found some more information that could help solve this, or at least pinpoint the problem.
It seems that the CONCAT is the troublemaker, not the information_schema.triggers table:
create or replace
view
schema_objects (
schema_name
, object_type
, object_name
, object_subtype
, created
, comment
)
as
select table_schema
, table_type
, table_name
, engine
, create_time
, table_comment
from information_schema.tables
union all
select routine_schema
, routine_type
, routine_name
, concat(
if(is_deterministic='NO','NOT','')
, _utf8' DETERMINISTIC '
, sql_data_access
, ' SQL SECURITY '
, security_type
)
, created
, routine_comment
from information_schema.routines
;
mysql> select * from schema_objects;
ERROR 1109 (42S02): Unknown table 'routines' in field list
What's more, the error can be prevented from occurring by adding an alias to the CONCAT:
create or replace
view
schema_objects (
schema_name
, object_type
, object_name
, object_subtype
, created
, comment
)
as
select table_schema
, table_type
, table_name
, engine
, create_time
, table_comment
from information_schema.tables
union all
select routine_schema
, routine_type
, routine_name
, concat(
if(is_deterministic='NO','NOT','')
, _utf8' DETERMINISTIC '
, sql_data_access
, ' SQL SECURITY '
, security_type
) as subobject_type
, created
, routine_comment
from information_schema.routines
;
[3 Nov 2005 10:20]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/31868
[11 Nov 2005 17:03]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/32185
[18 Nov 2005 20:20]
Evgeny Potemkin
View .frm parser assumes that query string will take only 1 line, with \n in aliases query stringmay take several lines thus produces bad .frm file. Fixed in 5.0.17, cset 1.1944.67.1
[20 Nov 2005 4:49]
Paul DuBois
Noted in 5.0.17 changelog.
Description: A view created from a UNION ALL query does not see the information_schema.TRIGGERS system view depending upon the order of the union members. When the query depending on the TRIGGERS system view appears as the first operand of the UNION ALL, the view is created, and a SELECT query on the view succeeds. However, when the query depending on the TRIGGERS system view appears as the second operand of the UNION ALL, the SELECT query on the view fails, and returns the following error: ERROR 1109 (42S02): Unknown table 'triggers' in field list It was expected that the result would be the same, regardless of the order of the UNION ALL operands. How to repeat: (I tried to simplify the query, but now luck yet, you'll have to do it with this for now, sorry): use test; create or replace view schema_objects ( db , name , type , subtype , created , comment ) as select table_schema , table_name , table_type , engine , create_time , table_comment from information_schema.tables union all select trigger_schema , trigger_name , _utf8'trigger' , concat( action_timing , _utf8' ' , event_manipulation , _utf8' ' , _utf8'FOR EACH ' , action_orientation ) , created , _utf8'' from information_schema.triggers union all select routine_schema , routine_name , _utf8'routine' , routine_type , created , routine_comment from information_schema.routines ; select * from schema_objects; /* This gives me: ERROR 1109 (42S02): Unknown table 'triggers' in field list However, the equivalent view definition: */ create or replace view schema_objects ( db , name , type , subtype , created , comment ) as select table_schema , table_name , table_type , engine , create_time , table_comment from information_schema.tables union all select trigger_schema , trigger_name , _utf8'trigger' , concat( action_timing , _utf8' ' , event_manipulation , _utf8' ' , _utf8'FOR EACH ' , action_orientation ) , created , _utf8'' from information_schema.triggers union all select routine_schema , routine_name , _utf8'routine' , routine_type , created , routine_comment from information_schema.routines ; select * from schema_objects; /* works fine. What is the matter here? */ Suggested fix: The view should work regardless of the order of the UNION ALL operands.