convert MyISAM to InnoDB

[Xontero]

hi guys

can someone help me to convert MyISAM to InnoDB? I want a fast and secure way

[Orpheus]

How to grab all the tables which are using MyISAM storage engine?

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;')
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE'

Where DBNAME is the database name, then copying all the output queries you've to run in console and hit CTRL+Enter

Done! hope that helps

[Xontero]

Bro I did it before and my forum down.

[Orpheus]

You have to generate the queries first using the above and then run them in MySQL CLI(Console).

I did it for my forum the same way. I used phpMyAdmin console inside the same database where I had most of the database tables in MyISAM

[Xontero]

Thanks bro

I'll try it and inform you.

 

[Orpheus]

20 hours ago, LoveHate said:

Thanks bro

I'll try it and inform you.

 

Sure thing, good luck

[Orpheus]

LoveHate Did it work?

[Xontero]

Orpheus no bro.

after use that my forum is down

[Orpheus]

7 hours ago, LoveHate said:

Orpheus no bro.

after use that my forum is down

Bruh, I used that SQL query recently and it worked just fine. I can help if you want to

[Desti]

Do

ALTER TABLE `table name` ENGINE = INNODB;

for all tables manually. It blocking operations and on large tables can take a long time (forum will stop responding). 

[Xontero]

Desti Я сделал этот метод, но после окончания работы моя ассоциация развалилась

[Desti]

Your DB must be configured for innodb. 

Check 

innodb_buffer_pool_size = (50% of memory)G 
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

[Orpheus]

2 hours ago, Desti said:

Do

ALTER TABLE `table name` ENGINE = INNODB;

for all tables manually. It blocking operations and on large tables can take a long time (forum will stop responding). 

Doing it manually is a headache, I have a big database and the SQL query I used worked just fine without any errors of sorts. Probably he/she's doing something wrong which has resulted in an error.

@LoveHate What's the size of your database?

[Desti]

34 минуты назад, Orpheus сказал:

is a headache

Headache is convert to utf8mb4  Manually..

Изменено 28 марта, 2022 пользователем Desti

[Orpheus]

9 hours ago, Desti said:

Headache is convert to utf8mb4  Manually..

Perhaps!

[Xontero]

13 hours ago, Orpheus said:

@LoveHate What's the size of your database?

10GB

[Orpheus]

2 hours ago, LoveHate said:

10GB

That's probably not the actual size of the database. Run MySQL optimization to reduce the size.

Below is how you should run the Query if you're using phpMyAdmin

[Xontero]

Orpheus bro I'm not familiar with database but I try this way

well in first step I run your query in console section and after that I see the below tables:

Spoiler

Then I selected and copied all the output tables and re-entered them in the console section but I got below error:

Spoiler

 

[Desti]

OMG..

you haven't completely copied the strings )

 

Every string must edned with ENGINE=InnoDB;

not ENGINE=Inn..

 

Изменено 29 марта, 2022 пользователем Desti

[Xontero]

41 minutes ago, Desti said:

ENGINE=Inn

For me, the output is displayed like this.

What is the problem that does not show the continuation of the string completely?

[Desti]

26 минут назад, LoveHate сказал:

the output is displayed like this

You should have saved the query result instead of copying it from the screen.

[Orpheus]

1 hour ago, Desti said:

OMG..

you haven't completely copied the strings

Like I said previously, he might have done something wrong which caused the error.

1 hour ago, LoveHate said:

For me, the output is displayed like this.

What is the problem that does not show the continuation of the string completely?

Follow and fix like this

Then you know what you've to do.

Next step would be; well, In most scenarios you can copy the above result to the notepad and then checking and confirming if all is well and at last run the resulted queries and you'll see a successful operation message.

[Orpheus]

Like this

CTRL + Enter (Windows OS) to execute all the queries you'll generate.

Goodluck

Изменено 29 марта, 2022 пользователем Orpheus

[Orpheus]

@LoveHate Let us know if you get any error or if you're stuck. We'll help as much as we can

Do let us know if you're successful in converting the tables.

[Xontero]

Orpheus I finally do that

One question, after copying the output and running it into the console, I got below message. Is this message successful?

In addition, no database type error was observed in the support section of the admin

 

so so thanks again