Select * from information_schema.temporary_tables is not working for MYSQL DATABASE

Question

when I am trying to execute

SELECT * FROM information_schema.temporary_tables

in MYSQL,i am getting error: Unknown table temporary_tables in information_schema. Please Help me out in this.

Answer 1

temporary_tables might not be present in information_schema database. check your database

Answer 2

There is no table name 'temporary_tables' in information_schema database.

For getting the list of tables from INFORMATION_SCHEMA database, you can use

SHOW TABLES FROM INFORMATION_SCHEMA;

Answer 3

What we are trying to achieve is:

1. We have to create a table in the destination database which is always GreenPlum.
2. In source database(MYSQL) we are getting a select query from the USER for example: "select * from ABC A join DEF D on A.Col1=D.col1" then we are creating TEMP TABLE(In case of MYSQL) on top of it for example "CREATE TEMPORARY TABLE table101 AS (select * from ABC A join DEF D on A.Col1=D.col1)".
3. Then using this TEMP table we get the required information from INFORMATION_SCHEMA for example "select * from INFORMATION_SCHEMA.COLUMNS where table_name='table101' ".By this we will get the column_name,data_type,character_maximum_length etc information. Using this information we can get "create table Statement" using Javascript .
4. Then we store this Create table statement in a variable & run it in Execute Row script(Step in pentaho data integration tool) which will create the Table in the destination DB.
5. Problem is that in MYSQL,TEMPORARY table does not get stored in INFORMATION_SCHEMA.NOW what to do plzz suggest me something.

NOTE: In short, we are creating a table in the destination DB using the select statement from the source DB. Means structure of the table in Dest DB depends on the select query in Source DB.