Amazon RDS – How to Handle NULL CREATE_TIME in INFORMATION_SCHEMA.TABLES

amazon-rdsinformation-schema

We are running an Amazon Aurora database that emulates MySQL 5.6.10. The CREATE_TIME in SHOW TABLE STATUS and INFORMATION_SCHEMA.TABLES is consistently NULL for all our tables.

Is this a bug, a limitation of Aurora's architecture, or am I doing something wrong? Is this information available anyplace else?

db2 information_schema> SELECT DISTINCT TABLE_SCHEMA 
FROM information_schema.TABLES WHERE CREATE_TIME IS NOT NULL;
+--------------------+
| TABLE_SCHEMA       |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (9.00 sec)

Additional notes: We have several thousand tables across several hundred databases, on several different hosts; many of the tables were created with files-per-table on, and some with it off. We do not have access to the .frm files, and they may or may not exist in Aurora. This has always worked for us in MySQL.

Update: Amazon support told us, vaguely, this is a bug and "it will be corrected in a later release." Still interested in knowing if this is going to be retroactively available, or if there is another place to get this 'cause Amazon is not elaborating.

Best Answer

This was the response from AWS - they agree using the TABLE_COMMENT column is the way forward. I can also confirm we have successfully implemented this solution.

Hello,

Thank you for contacting AWS premium support. My name is Vidyadhar and I am glad to assist you on this case.

From the case description, I understand that you noticed only null values in create_time column in information_schema.tables. You were removing old tables based on the values inside this column in MySQL. Now, you would like to know reason for not showing up creation time in Aurora and alternatives to achieve your requirement.

Just to let you know, there is a known bug in MySQL related to the same where create_time will be null for partitioned columns. This has been fixed in MySQL 5.6.25 version. Please look into below link for reference.

https://bugs.mysql.com/bug.php?id=69990

In aurora this column will be NULL for all tables including partitioned tables. This has been already notified to the service team. Also, there is no workaround to this available in Aurora for now.

It is best option to utilize the table comment session as a work around to note the time of table creation and proceed accordingly with table deletion. You can look into TABLE_COMMENT column in information_schema.tables. Please refer to below example which might be helpful.
MySQL [test]> create table S1(id int) comment='2017-09-21 15:53:51';
Query OK, 0 rows affected (0.02 sec)

MySQL [test]>  show create table S1\G
*************************** 1. row ***************************
       Table: S1 Create Table: CREATE TABLE `S1` (   `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1
COMMENT='2017-09-21 15:53:51' 1 row in set (0.00 sec)

MySQL [test]> select TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT from
information_schema.tables where table_schema='test' and
table_name='S1';
+--------------+------------+---------------------+ 
| TABLE_SCHEMA | TABLE_NAME | TABLE_COMMENT       |
+--------------+------------+---------------------+ 
| test         | S1         | 2017-09-21 15:53:51 |
+--------------+------------+---------------------+ 
1 row in set (0.01 sec)
I truly apologize for the inconvenience caused. Aurora service team is working hard to add features and fixing bugs. As of now, I am unable to provide any ETA but definitely this will be fixed in future versions.

I hope this information is helpful. Please feel free to reach me if you have any further questions or concerns.

Have a great day!

Related Solutions

Purpose of TABLE_CATALOG Column in INFORMATION_SCHEMA.TABLES

That column exists for compatibility with other databases.

Interestingly, here is information_schema.tables in PostgreSQL

postgres=# \d information_schema.tables
                       View "information_schema.tables"
            Column            |               Type                | Modifiers
------------------------------+-----------------------------------+-----------
 table_catalog                | information_schema.sql_identifier |
 table_schema                 | information_schema.sql_identifier |
 table_name                   | information_schema.sql_identifier |
 table_type                   | information_schema.character_data |
 self_referencing_column_name | information_schema.sql_identifier |
 reference_generation         | information_schema.character_data |
 user_defined_type_catalog    | information_schema.sql_identifier |
 user_defined_type_schema     | information_schema.sql_identifier |
 user_defined_type_name       | information_schema.sql_identifier |
 is_insertable_into           | information_schema.character_data |
 is_typed                     | information_schema.character_data |
 commit_action                | information_schema.character_data |
View definition:
 SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name,
        CASE
            WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text
            WHEN c.relkind = 'r'::"char" THEN 'BASE TABLE'::text
            WHEN c.relkind = 'v'::"char" THEN 'VIEW'::text
            ELSE NULL::text
        END::information_schema.character_data AS table_type, NULL::character varying::information_schema.sql_identifier AS self_referencing_column_name, NULL::character varying::information_schema.character_data AS reference_generation, NULL::character varying::information_schema.sql_identifier AS user_defined_type_catalog, NULL::character varying::information_schema.sql_identifier AS user_defined_type_schema, NULL::character varying::information_schema.sql_identifier AS user_defined_type_name,
        CASE
            WHEN c.relkind = 'r'::"char" OR c.relkind = 'v'::"char" AND (EXISTS ( SELECT 1
               FROM pg_rewrite
              WHERE pg_rewrite.ev_class = c.oid AND pg_rewrite.ev_type = '3'::"char" AND pg_rewrite.is_instead)) THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.character_data AS is_insertable_into, 'NO'::character varying::information_schema.character_data AS is_typed,
        CASE
            WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE'::text
            ELSE NULL::text
        END::information_schema.character_data AS commit_action
   FROM pg_namespace nc, pg_class c
  WHERE c.relnamespace = nc.oid AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char"])) AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));

In PostgreSQL, the table_catalog field actually means something. In fact, it is part of the SQL-92 standard. The catalog of a database groups related metadata for a database. See, in PostgreSQL, the information_schema is only for the database you are connected to. In MySQL, this concept is blurred and the information_schema contains all metadata for all databases, especially if you have ALL PRIVILEGES on *.* as with root@localhost.

It makes sense that it is there in MySQL but blank. The information_schema in MySQL will collapse for you if your DB user has ALL PRIVILEGES on db.* instead of *.*. In PostgreSQL, the collapse is automatic when you connect to a database schema. So, the table_catalog column is just there for MySQL to be SQL-92 compatible and nothing more.

Amazon AWS RDS Aurora – Row Size Too Large (VB5)

Well, after researching into this and trying out around 20-30 possibly solutions I could not make it work and as far as I can see the limits are set in Aurora and can NOT be changed. Hence the import fails in every single possible solution.

I killed the Aurora instance and created a MySQL one, imported without any hiccups right away. The import or log size limit is set to a much higher default and doesn't choke on the data. Amazon was quoted to say that in Aurora they will not, so far, ever change the limits on this in Aurora. On the other side in MySQL they made it a editable parameter some time ago with, as I said above, already much higher limit.

TLDR: Aurora will always crap out on you with too large fields no matter what field type you set - use RDS MySQL.

Best Answer

Related Solutions

Purpose of TABLE_CATALOG Column in INFORMATION_SCHEMA.TABLES

Amazon AWS RDS Aurora – Row Size Too Large (VB5)

Related Question