How to find MySQL temporary table storage engine

Asked 30/4, 2013 at 9:4 Answered 30/4, 2013 at 9:21

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.

Cadaver answered 30/4, 2013 at 9:4 Comment(2)

I got the error: E_TOO_MANY_CAPITAL_LETTERS. – Hunsaker 30/4, 2013 at 9:9

but I am getting the following answer for my physical tables "ENGINE: InnoDB " – Cadaver 30/4, 2013 at 9:12

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'];

Draconic answered 30/4, 2013 at 9:21 Comment(24)

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? – Cadaver 30/4, 2013 at 9: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. – Draconic 30/4, 2013 at 9: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. – Cadaver 30/4, 2013 at 9: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? – Illicit 30/4, 2013 at 9: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. – Cadaver 30/4, 2013 at 9:41

Why my question in down rated? – Cadaver 30/4, 2013 at 9:42

Hi Yak! Can you link me to that example! – Cadaver 30/4, 2013 at 9: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..!! – Cadaver 30/4, 2013 at 9: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? – Illicit 30/4, 2013 at 9: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. – Cadaver 30/4, 2013 at 9:48

No, it can't be helpful to anyone, your question is incredibly pointless but you just can't understand it. – Illicit 30/4, 2013 at 9:49

Its a requirement.. which I have to provide the solution for it.. The user need it for there bla bla.. – Cadaver 30/4, 2013 at 9: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. – Draconic 30/4, 2013 at 9:51

I don't need to but I want to. Thank you for contributing for me to "understand" it. – Illicit 30/4, 2013 at 9:52

@Illicit 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. – Birkner 30/4, 2013 at 9: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 :) – Illicit 30/4, 2013 at 9: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? – Cadaver 30/4, 2013 at 9:57

@TomPHP Yes I made a mistake in my first version. The desired result is in $row[1] (anwser updated) – Draconic 30/4, 2013 at 10:0

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<engine>\w+)/', $row[1], $matches); echo "Matches:".$matches['engine']; OutPUT: Matches:MyISAM Thank you all guys! Hope this will help others! – Cadaver 30/4, 2013 at 10:5

Superb! Yak.. Thank you for your time..! – Cadaver 30/4, 2013 at 10:6

Of course it will say it's MyISAM.. your CREATE TABLE specified it so. Why are you running in circle for no reason? – Illicit 30/4, 2013 at 10:8

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.! – Cadaver 30/4, 2013 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. – Illicit 30/4, 2013 at 10:21

Cheers! N.B. Let me Think for other requirements. :) – Cadaver 30/4, 2013 at 10:25

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'`

Birkner answered 30/4, 2013 at 9:15 Comment(4)

Hi Kami I am getting the following Error: Sql error : Unknown table 'temporary_tables' in information_schema – Cadaver 30/4, 2013 at 9: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 flavour of MySQL instead? – Draconic 30/4, 2013 at 9:22

Recommended topics

Hot tags