2

I have like 100 tables in mysql database and all are having storage engine: MyISAM. I want to change their storage engine all at once to innoDB and I DO NOT want to use:

1.PHP (How to convert all tables from MyISAM into InnoDB?

  1. Laravel(Laravel & InnoDB)

  2. One table at a time.(how to change storage engine of database in phpmyadmin?)

  3. SQL query .(How to change table storage engine from MyISAM to InnoDB)

  4. by xampp or similar way. (How to set the default storage engine to InnoDB in XAMPP )

So I want to change : ALL TABLE STORAGE ENGINE AT ONCE and using phpmyadmin interface.

Any help?

Community
  • 1
  • 1
Abhay Maurya
  • 11,819
  • 8
  • 46
  • 64

2 Answers2

8

Will 2 steps suffice?

(1) Run the SELECT against information_schema, as seen in some of those links. But have it output 100 fully-formed ALTER statements.

SELECT CONCAT("ALTER TABLE ", table_name, " ENGINE=InnoDB;
")
    FROM information_schema.tables
    WHERE engine='MyISAM'
      AND table_schema='MyDatabase'
      AND ...;

(2) Copy and paste the 100 lines into phpadmin. Go have a coffee. Or spend the time reading about the gotchas in converting to see if you will have some issues.

Doing them truly in parallel is unlikely to be any faster -- you will be I/O bound. And the small tables will finish fast, leaving the big tables to stretch out the time anyway.

Output sample:

SELECT CONCAT("ALTER TABLE ", table_name, " ENGINE=InnoDB;
")
    FROM information_schema.tables
    WHERE engine='MyISAM'
      AND table_schema='test';

produces something like:

ALTER TABLE 07 ENGINE=InnoDB;\n
ALTER TABLE 597377b ENGINE=InnoDB;\n
ALTER TABLE adjprice ENGINE=InnoDB;\n
ALTER TABLE big ENGINE=InnoDB;\n
ALTER TABLE bigv ENGINE=InnoDB;\n
ALTER TABLE blobid ENGINE=InnoDB;\n
ALTER TABLE charlatin1 ENGINE=InnoDB;\n
ALTER TABLE collorder ENGINE=InnoDB;\n
ALTER TABLE collorder2 ENGINE=InnoDB;\n
ALTER TABLE countries ENGINE=InnoDB;\n
ALTER TABLE d1 ENGINE=InnoDB;\n

Then paste however many rows are generated back into the tool. No need to manually type the names.

If you first convert 100 tables, they will not be included when you run the query again. Only the new 25 will be included.

No, you cannot "automatically" change any new tables to a different engine. However...

By saying this in my.cnf, you can have all new table for which you do not explicitly specify an ENGINE be InnoDB:

[mysqld]
default_storage_engine = InnoDB
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks for your answer and effort but your way is practically same as using Query: (http://stackoverflow.com/questions/7455480/how-to-change-table-storage-engine-from-myisam-to-innodb) ..... if tables number increates from 100 to 125 then i will have to add again 25 query lines...I was searching for some other way to solve my problem irrespective of number of tables. – Abhay Maurya Jan 16 '17 at 11:13
  • My approach is two steps, regardless of the number of tables. I added the `SELECT` to the answer; try step (1) to see what I mean. (After fixing the `AND` as needed.) – Rick James Jan 16 '17 at 17:22
  • I understood your logic from very beginning. I will have to modify your query in step1 if table number increases from 100 to 125 for those 25 new tables( to add their names therefore ALTER stmt for them). What I meant by "**irrespective to number of tables**" is that, I wanted to get some way in **phpmyadmin interface** like to select all tables and then somehow change their storage engine from MyISAM to innoDB at once. Same like we can for one table but to make it for all at once. I hope I cleared my question. – Abhay Maurya Jan 17 '17 at 07:31
  • I added more to my answer. If that is not clear, maybe someone else can explain which of us is having a "failure to communicate". – Rick James Jan 17 '17 at 18:44
  • My Bad, so far your answer is seems the best and only one. Thanks for your help and explanation. +1 and accepted. PS. After running query in step1...click on : options->full texts->Go, in result page because by default it shows partial texts which will result in error later on while running queries in step2. – Abhay Maurya Jan 18 '17 at 08:37
  • Oh, perhaps phpmyadmin builds in a `LIMIT 100`? (I don't use phpmyadmin; I would do using the mysql commandline tool.) – Rick James Jan 18 '17 at 18:26
  • Little detail: add ` and ` around the table names or you might get into trouble. I cannot give an example because ` and ` have special meanings in this comment box :( – Roemer Jul 26 '17 at 19:50
1

I opened a feature request about this at https://github.com/phpmyadmin/phpmyadmin/issues/12893

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43