MySQL Bugs: #29211: Falcon: information_schema has a falcon

Bug #29211	Falcon: information_schema has a falcon_tables view
Submitted:	19 Jun 2007 13:23	Modified:	18 Oct 2008 15:57
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.1-alpha-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
There is a new table in information_schema, named
FALCON_TABLES. I can see it thus:

mysql> select * from information_schema.falcon_tables;
+--------+----------+-------------+
| SCHEMA | TABLE    | TABLESPACE  |
+--------+----------+-------------+
| D12    | T9#P#P1  | FALCON_USER |
....

This is wrong in a few ways.

1. The name SCHEMA does not correspond to the name used
in information_schema.tables, which is SCHEMA_NAME.

2. SCHEMA is a reserved word.

3. TABLE is a reserved word.

4. There is no table named T9#P#P1. I did create a
table named t9 with a partition named p1 (lower case),
but T9#P#P1 isn't even a legal regular identifier.

5. In other DBMSs the tendency is to put the tablespace
name in a 'tables' entry, e.g.
DB2 syscat.tables
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad...
Oracle ALL_ALL_TABLES
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1003.htm#i15...
PostgreSQL pg_tables
http://www.postgresql.org/docs/8.2/static/view-pg-tables.html

6. MySQL already has information_schema.files.

7. No worklog entry mentions information_schema.falcon_tables,
so I conclude that it is not there "by design".

How to repeat:
create database d12;

use d12;

create table t9 (s1 int)
engine=falcon
partition by list (s1)
(partition p1 values in (1), partition p2 values in (2));

select * from information_schema.falcon_tables where `schema`='d12';

Thank you for the bug report. Verified as described on FC 6.0 32-bit.

Peter, the issues raised in this bug seem to be accounted for.  Please verify.

In the latest release, I see that the SCHEMA column has been renamed
SCHEMA_NAME and the TABLE column has been renamed TABLE_NAME. The
other matters are still outstanding.

I agree that information_schema.FILES should be used by falcon instead of information_schema.FALCON_TABLES

Falcon has no way to show tablespaces which don't have any tables associated to them.  Using information_schema.FILES should correct this. 

Otherwise we would require another pair of command:

SHOW ENGINE <engine name> TABLESPACES;
SHOW ENGINE <engine name> LOGFILE GROUPS;

Chris Powers,

I am reassigning this to you from Jim.  The following analysis is provided by Ann.

-----Original Message-----
From: Ann W. Harrison [mailto:ann@mysql.com] 

This bug started with several not very interesting problems,
and continues to present a couple.

One is that in Peter's opinion (not shared here) we should
report the tablespace as part of the tablename - e.g.
FALCON_USER.XYZZY.

A second is that when a table name is decorated to handle
the various types of name problems induced by partitioning,
delimited identifiers on case insensitive file systems,
etc., we report the table name as presented to us, not as
created by the user.

To quote Peter G.

mysql> select * from information_schema.falcon_tables;
+------------+---------------+-------------+
| SCHEMA_NAME| TABLE_NAME    | TABLESPACE  |
+------------+---------------+-------------+
| D12        | T9#P#P1       | FALCON_USER |
....
"There is no table named T9#P#P1. I did create a
table named t9 with a partition named p1 (lower case),
but T9#P#P1 isn't even a legal regular identifier."

I haven't a clue what to do about reporting back tablenames other
that what we were asked to created.

-----Original Message-----
From: Kevin Lewis [mailto:klewis@mysql.com] 

Since the information schema functions return info specifically for the MySQL server, why don't we do some name mangling to turn 'T9#P#P1' into 'T9-Partition-1' whenever we see this kind of name?  I realize that this artificial intelligence will need to be put into StorageHandler::getTablesInfo().  But it could be done... Bad idea?

-----Original Message-----
From: Ann W. Harrison [mailto:ann@mysql.com] 

Is the 'undecorating' actually deterministic?  One of the uses of
decoration is to support delimited table names on file systems that
aren't case sensitive (I think).  Can we tell the difference between
a table 'T9pp1' and T9-Partition-1?  If it were up to me, I'd mark
this as either 'to be fixed later' or 'will not be fixed.'  I suppose
an alternative would be to see how other engines handle it.  This
name swamp is getting deeper and stickier.

-----Original Message-----
From: Jim Starkey [mailto:jstarkey@mysql.com] 

How about something like 'T9#P#P1 (T9-Partition-1)' ?

On the other hand, I think there are more people using partition than 
people who create quoted identifiers like T9#P#P1.  Why don't we 
validate the format of known partition name and do the reverse 
transposition?

Or, more succinctly, sure, why not?

-----Original Message-----
From: Kevin Lewis [mailto:klewis@mysql.com] 

Actually, leaving the original table name and adding to it our non-deterministic, fallible, artificially unintelligently interpreted table name (in parentheses) is a pretty good idea.  We can be wrong without breaking anything.

-----Original Message-----
From: Ann W. Harrison [mailto:ann@mysql.com] 

    The worklog item I was thinking of is 1324 "table name to filename
encoding" and it appears that the encoding doesn't use the same escape
character.

I believe I was misquoted or misunderstood, I don't remember saying something like
"we should report the tablespace as part of the tablename".

Peter, 

   Certainly misquoted, probably misunderstood... Here's the
old format output:

mysql> select * from information_schema.falcon_tables;
+--------+----------+-------------+
| SCHEMA | TABLE    | TABLESPACE  |
+--------+----------+-------------+
| D12    | T9#P#P1  | FALCON_USER |
....

Here's the comment:

5. In other DBMSs the tendency is to put the tablespace
name in a 'tables' entry, e.g.
DB2 syscat.tables

Which I took to mean that you wanted to see something like

mysql> select * from information_schema.falcon_tables;
+-------------+----------------------+----------------+
| SCHEMA_NAME |       TABLE_NAME     | PARTITION_NAME
+-------------+----------------------+----------------+
|    D12      | FALCON_USER.T9       |      P1        |
....

There are perhaps several misunderstandings; let us blame my attempt to be terse.

One: I said "the tendency is to put the tablespace name in a 'tables' entry".
I did not mean in a table_name column. Please look at the references that I
gave for DB2 and PostgreSQL, you'll notice that "TBSPACEID" and "tablespace"
are separate columns. In fact it would look odd (and a tad unrelational) to
put the tablespace name and the table name in the same column.

Two: I said "the tendency is to put the tablespace name in a 'tables' entry".
Again, please look at the references that I gave for DB2 and PostgreSQL.
You'll notice that these pages describe the equivalent of our
INFORMATION_SCHEMA.TABLES. So "a 'tables' entry" referred to this fact:
by precedent, a possibly acceptable place for this information is
INFORMATION_SCHEMA.TABLES.

Three: I said "There is no table named T9#P#P1. I did create a
table named t9 with a partition named p1 (lower case),
but T9#P#P1 isn't even a legal regular identifier." Please don't
interpret this as something to do with mangling table names.
T9#P#P1 is not a table name. T9#P#P1 is a partition name. So I don't
even see what it's doing in a list of tables.

Most important, in my opinion, is:
"
7. No worklog entry mentions information_schema.falcon_tables,
so I conclude that it is not there "by design".
"
What you add to the Falcon storage engine is your business.
But a new information_schema view is "outside Falcon" --
you move into an area that's beyond your specific responsibility.
It's legitimate therefore to point out that this didn't go
through the regular process for adding server features, and
therefore is a bug. I think that about all the Falcon tables,
and any 'fix' would have to start with removal of the offending
object.

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/commits/36729

ChangeSet@1.2656, 2007-10-31 02:06:53-05:00, chris@xeno.mysql.com +2 -0
  Bug#29211 "Falcon: information_schema has a falcon_tables view"
  - Put table and partition in separate columns

Parsed internal table name into separate table and partition columns:

+-------------+----------------+----------------+--------------+
| SCHEMA_NAME |   TABLE_NAME   |   PARTITION    |  TABLESPACE  |
+-------------+----------------+----------------+--------------+
|    D12      |      T9        |      P1        |  FALCON_USER |
...

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/commits/37020

ChangeSet@1.2681, 2007-11-02 16:06:52-05:00, chris@xeno.mysql.com +2 -0
  Bug#29211, "Falcon: information_schema has a falcon_tables view"
  -Added INTERNAL_NAME column to Information_Schema.falcon_tables.

Works as expected now:

[19:17] root@d12>select * from information_schema.falcon_tables where `schema_name`='d12';
+-------------+------------+-----------+-------------+---------------+
| SCHEMA_NAME | TABLE_NAME | PARTITION | TABLESPACE  | INTERNAL_NAME |
+-------------+------------+-----------+-------------+---------------+
| D12         | T9         | P1        | FALCON_USER | T9#P#P1       |
| D12         | T9         | P2        | FALCON_USER | T9#P#P2       |
+-------------+------------+-----------+-------------+---------------+
2 rows in set (0.01 sec)

Pushed into 6.0.4-alpha

1. The "table_name" column has mangled table names.

How to repeat:

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> create table ê (s1 int) engine=falcon;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.falcon_tables;
+-------------+------------+-----------+-------------+---------------+
| SCHEMA_NAME | TABLE_NAME | PARTITION | TABLESPACE  | INTERNAL_NAME |
+-------------+------------+-----------+-------------+---------------+
| M           | @0Q        |           | FALCON_USER | @0Q           |
+-------------+------------+-----------+-------------+---------------+
1 row in set (0.01 sec)

2. Among several problems in the original bug description that were
not addressed, I note this one in particular:

"
7. No worklog entry mentions information_schema.falcon_tables,
so I conclude that it is not there "by design".
"

INFORMATION_SCHEMA is not part of "Falcon", it belongs to MySQL
in general, and changes to it are expected to go through the
regular worklog process.

There may be an issue with reporting temporary tables as well:

mysql> create temporary table test.t1 (f1 integer) engine=falcon;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from information_schema.FALCON_TABLES;
+-------------+------------+-----------+------------------+---------------+
| SCHEMA_NAME | TABLE_NAME | PARTITION | TABLESPACE       | INTERNAL_NAME |
+-------------+------------+-----------+------------------+---------------+
| TMP         |            |           | FALCON_TEMPORARY | #SQL7D29_1_2  |
+-------------+------------+-----------+------------------+---------------+
1 row in set (0.00 sec)

In my humble opinion, SCHEMA_NAME should be "test", TABLE_NAME must be "t1" and, in case PARTITION is also a reserved word, the column should be named "PARTITION_NAME".

It appears that if TABLE_NAME contains characters outside of ASCII, you can not use the "escaped" TABLE_NAME to drop the table. For example:

mysql> create table `###` (f1 integer not null) engine=falcon;
Query OK, 0 rows affected (0.01 sec)

mysql> select * FROM information_schema.FALCON_TABLES;
+-------------+-----------------+-----------+------------------+-----------------+
| SCHEMA_NAME | TABLE_NAME      | PARTITION | TABLESPACE       | INTERNAL_NAME   |
+-------------+-----------------+-----------+------------------+-----------------+
| TEST        | T3              |           | FALCON_TEMPORARY | T3              |
| TEST        | T12             |           | FALCON_TEMPORARY | T12             |
| TEST        | @0023@0023@0023 |           | FALCON_USER      | @0023@0023@0023 |
+-------------+-----------------+-----------+------------------+-----------------+

mysql> drop table `@0023@0023@0023`;
ERROR 1051 (42S02): Unknown table '@0023@0023@0023'

This means that one can not safely iterate over all rows of FALCON_TABLES and perform operations on individual tables. Unless one manually converts @0023@0023@0023 into a ###, there is no way to match that table to , say, information_schema.TABLES, because the @0023@0023@0023 does not appear there, just the ###.

Peter and Philip.  This bug has changed from one thing to another.  Can we close this bug if the original problem is fixed, and open new bugs for any issues which still exist like table name '###' and temporaty table names?  This bug is too confusing to know what all to fix.

This bug has always been the same thing:
"Falcon: information_schema has a falcon_tables view".
The comments mention various effects of that bug,
rather than constitute new bugs.

I believe that Philip and I may differ about
this, but I believe the table exists without
right or warrant.

Triage is based on the need to document I_S.falcon_tables.

Peter,

INFORMATION_SCHEMA.FALCON_TABLES is an add on for Falcon. However, it looks like that the columns in FALCON_TABLES are redundant:

FALCON_TABLES.SCHEMA_NAME --> TABLES.TABLE_SCHEMA
FALCON_TABLES.TABLE_NAME  --> TABLES.TABLE_NAME
FALCON_TABLES.PARTITION   --> PARTITIONS.PARTITION_NAME
FALCON_TABLES.TABLESPACE  --> PARTITIONS.TABLESPACE_NAME
FALCON_TABLES.INTERNAL_NAME, this could be put into TABLES.TABLE_COMMENT

Assigning to Sergey Votjovich.  We have decided to delete INFORMATION_SCHEME.FALCON_TABLES which is what is originally requested in this bug.  See also Bug#34705 and Bug#34706.

>>Vlad wrote;
>>
>> How would you fix these bugs? For example, to have proper table name as
>> defined by user (in all cases, that is even with non-latin characters and
>> after RENAME) we would need  filename_to_tablename() , that is banned from
>> server API.  And possibly we'd also need to fix Falcon uppercasing while
>> we are at it ;) Too much hacking, is not it?

>Ann wrote;
>Too much hacking and too little value, I agree.  Also too much confusion
>about what table to trust, giving that there is a very large overlap
>between TABLES and FALCON_TABLES.  I can think of no case where the
>internal Falcon name is of interest to anything but Falcon.  So, fine,
>lets get rid of FALCON_TABLES and close all three bugs.

See http://lists.mysql.com/commits/50640

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/commits/50765

2761 Sergey Vojtovich	2008-07-31 [merge]
      Merge falcon-6.0 -> falcon-6.0-team

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/commits/50640

2761 Sergey Vojtovich	2008-07-29
      BUG#29211 - Falcon: information_schema has a falcon_tables view
      BUG#34705 - FALCON_TABLES shows wrong TABLE_NAME
      BUG#34706 - FALCON_TABLES shows wrong information on temporary tables
      
      There were several problems relating to INFORMATION_SCHEMA.FALCON_TABLES,
      e.g.:
      - there is no worklog entry for I_S.FALCON_TABLES, so it is not
        there by design
      - MySQL already has I_S.FILES and I_S.TABLES, which provide
        duplicating information
      - it didn't show properly table names with non-ascii characters
      - it didn't show properly table and schema names for temporary
        tables
      
      This patch removes INFORMATION_SCHEMA.FALCON_TABLES and all
      relevant code.
      
      No test case needed for this fix - it is already covered by existing
      tests.

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/commits/51602

2684 He Zhenxing	2008-08-14 [merge]
      Merge 6.0 -> 6.0-rpl-testfixes

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/commits/51603

2684 He Zhenxing	2008-08-14 [merge]
      Merge 6.0 -> 6.0-rpl-testfixes

Was pushed to 6.0.7.

Pushed into 6.0.7-alpha  (revid:svoj@mysql.com-20080729104539-5grheqzat2gs0avq) (version source revid:v.narayanan@sun.com-20080820070709-nx09bk6qx81osd5s) (pib:3)

Documented in the 6.0.7 changelog as follows:

        *IMPORTANT CHANGE* The INFORMATION_SCHEMA.FALCON_TABLES table has been 
        removed.

Updated se-falcon-stats section of 6.0 Manual accordingly.