0

Hi I am working with Temporary table and I would like to know the temporary table storage Engine (InnoDB, MyISAM .... )

I am using the following code to find out but it is not showing me the storage Engine.

$engine="SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND `TABLE_NAME`='temporary_table'";
$export = mysql_query($engine, $connection) or die ("Sql error : ".mysql_error());
while ($row = mysql_fetch_array($export, MYSQL_BOTH)) {
    printf ("ENGINE: %s ---", $row[0]);
}

But the same code is working when I try to find the storage engine for Physical tables in my DB?

Any Help is much appreciated.!! Thank you.

Till Helge
  • 9,253
  • 2
  • 40
  • 56
DonOfDen
  • 3,968
  • 11
  • 62
  • 112

2 Answers2

1

Unfortunately:

Currently, the [INFORMATION_SCHEMA.]TABLES table does not list TEMPORARY tables.

I would advise parsing the result of SHOW CREATE TABLE temporary_table;

To extract only the ENGINE of this return value:

$rset = mysql_query('SHOW CREATE TABLE temporary_table;')
$row = mysql_fetch_array($rset, MYSQL_BOTH);
preg_match('/ENGINE\=(?P<engine>\w+)/', $row[1], $matches);
echo $matches['engine'];
RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • Hi Yak! Thanks for your answer.. But I want o know only the Storage Engine. I worked out your Query it gives all the details of the table.. But I want the Storage Engine alone. Is there any other way? – DonOfDen Apr 30 '13 at 09:25
  • Well, with a little bit of regex tinkering, you should be able to extract the desired piece of information. If time allows, I will come back with an example. – RandomSeed Apr 30 '13 at 09:27
  • Hi I am using the below Query to Create the temporary table.: CREATE TEMPORARY TABLE csv_import( id INT, entitiy_id INT, attribute_id INT, value TEXT )ENGINE MyISAM I want to make sure that the table is created in MyISAM. – DonOfDen Apr 30 '13 at 09:27
  • @TomPHP - if you do a statement like that, then the engine will be MyISAM. What's the point of your question? Why do you need to make sure that table is MyISAM if your `create table` **specifies** so? – N.B. Apr 30 '13 at 09:37
  • Actually when The user works on the Application they need to know from which Temporary Storage engine the Data is coming... Because they will change the storage engine for there wish when they work on it.! So after the Temp DB is created they will click on a button to know the Storage Engine.. so in thet action of the button I am checking the Temp Table Engine. Is it Clear..? This is the requirement.. I am trying for the solution. – DonOfDen Apr 30 '13 at 09:41
  • Why my question in down rated? – DonOfDen Apr 30 '13 at 09:42
  • Hi Yak! Can you link me to that example! – DonOfDen Apr 30 '13 at 09:44
  • The user in this is the Admin.. who is working with the DB..! Do you find any stupid things in it! Hope You now understand the Users is not always the one who sits and just see the website even an ADMIN is an user..!! – DonOfDen Apr 30 '13 at 09:45
  • So? What relevant information can anyone extrapolate by knowing that some temporary table (which is gone by the time the user reads the message) is of a certain engine? What difference does it make to the user? Do you even know the differences between engines? Do you understand that if you say that ENGINE = MYISAM that engine is and always will be MYISAM? – N.B. Apr 30 '13 at 09:46
  • Hi N.B. If you can let me know your ideas to find it..! not every thing solved in that way! by just commenting... This solution may help others.. as well as me. – DonOfDen Apr 30 '13 at 09:48
  • No, it can't be helpful to anyone, your question is incredibly pointless but you just can't understand it. – N.B. Apr 30 '13 at 09:49
  • Its a requirement.. which I have to provide the solution for it.. The user need it for there bla bla.. – DonOfDen Apr 30 '13 at 09:50
  • Please, @N.B., if you find the question pointless, you do not need to answer it. I suppose you have voted to close the question, thank you for your contribution. – RandomSeed Apr 30 '13 at 09:51
  • I don't need to but I want to. Thank you for contributing for me to "understand" it. – N.B. Apr 30 '13 at 09:52
  • @N.B. Without knowing the context of the application it is not possible to make such statements. The application could be database management application, in which case working with DB internals is not only valid, but expected. – Kami Apr 30 '13 at 09:53
  • Of course it's possible to make such statements. If you specify that table engine = MYISAM then it's MyISAM, it's not magic unicorn. Period. Now, as for "client's" request, no matter how retarded - that doesn't change the fact that table engine *will* be MyISAM unless specified otherwise (or if the engine statement is left out when by default temp table is MEMORY). Also, db management application performed by a newbie? Don't joke please :) – N.B. Apr 30 '13 at 09:55
  • Hi Kami, Yak and N.B: I tried with the code of Yak.. It is not showing echoing any thing... when I tied to echo what $row[0] echoes.. It echoes "my temporary table name". Am I doing any wrong? – DonOfDen Apr 30 '13 at 09:57
  • @TomPHP Yes I made a mistake in my first version. The desired result is in `$row[1]` (anwser updated) – RandomSeed Apr 30 '13 at 10:00
  • They have helped me a lot and made me to think too... Following is the code which I edited from Yak! $rset = mysql_query('SHOW CREATE TABLE temporary_table_name'); $row = mysql_fetch_array($rset, MYSQL_BOTH); preg_match('/ENGINE\=(?P\w+)/', $row[1], $matches); echo "Matches:".$matches['engine']; OutPUT: Matches:MyISAM Thank you all guys! Hope this will help others! – DonOfDen Apr 30 '13 at 10:05
  • Superb! Yak.. Thank you for your time..! – DonOfDen Apr 30 '13 at 10:06
  • Of course it will say it's MyISAM.. your `CREATE TABLE` specified it so. Why are you running in circle for no reason? – N.B. Apr 30 '13 at 10:08
  • Hi N.B. Even I am able to understand that, Even I too talked the same why but the Admin wanted to know it! Its an requirement.. As a programmer we need to do it.. I suggested your solution but dynamically they will change the Engine name.. So as to confirm it they need this small solution to it.. They will work in Interface... alone.. The interface generate the Queries for the Admin... I can't simply fight to them... Its requirement.! – DonOfDen Apr 30 '13 at 10:14
  • @TomPHP - I'm not arguing what your client wants, I'm just trying to tell you that you're running in circle.. but hey, whatever works for you. – N.B. Apr 30 '13 at 10:21
  • Cheers! N.B. Let me Think for other requirements. :) – DonOfDen Apr 30 '13 at 10:25
0

You will want to search the temporary tables store information_schema.temporary_tables or the global temporary table store information_schema.global_temporary_tables.

Try

`SELECT ENGINE FROM information_schema.temporary_tables WHERE TABLE_SCHEMA='test' AND `TABLE_NAME`='temporary_table'`
Kami
  • 19,134
  • 4
  • 51
  • 63
  • Hi Kami I am getting the following Error: Sql error : Unknown table 'temporary_tables' in information_schema – DonOfDen Apr 30 '13 at 09:19
  • This might apply to another DB engine, but I am afraid I find no reference to a `information_schema.temporary_tables` table in MySQL. Are you referring to the [Percona](http://www.percona.com/doc/percona-server/5.5/diagnostics/misc_info_schema_tables.html?id=percona-server:features:misc_info_schema_tbls) flavour of MySQL instead? – RandomSeed Apr 30 '13 at 09:22
  • Hi I am using the below Query to Create the temporary table.: CREATE TEMPORARY TABLE csv_import( id INT, entitiy_id INT, attribute_id INT, value TEXT )ENGINE MyISAM I want to make sure that the table is created in MyISAM. – DonOfDen Apr 30 '13 at 09:28
  • They have helped me a lot and made me to think too... Following is the code which I edited from Yak! $rset = mysql_query('SHOW CREATE TABLE temporary_table_name'); $row = mysql_fetch_array($rset, MYSQL_BOTH); preg_match('/ENGINE\=(?P\w+)/', $row[1], $matches); echo "Matches:".$matches['engine']; OutPUT: Matches:MyISAM Thank you all guys! Hope this will help others! – DonOfDen Apr 30 '13 at 10:04