Conversion Databease MyISAM to InnoDB

[bonny3]

Good evening everyone,
I have a problem, I should convert the database from MyISAM to InnoDB, but I can not do so with terminal commands ssh to the problem of password plesk.
http://kb.odin.com/it/116916
I can do it directly from myphpadmyn?
I found this guide,
http://richardcummings.info/convert-myisam-to-innodb-using-phpmyadmin/
it's enough?
if the problem is that I can change only one table at a time. I can do it all together the database?
I ask your help
Thank you

 

[UFHH01]

Hi bonny3,

I have a problem, I should convert the database from MyISAM to InnoDB, but I can not do so with terminal commands ssh to the problem of password plesk.

Could you please try to explain, what the problem is? Is this the error message you get:

Access denied for user 'admin'@'localhost' (using password: YES)

If this is your case, please consider to use the suggestions from:

How to get or reset password for "admin" account in Plesk ( KB - article 346 )​

Did you know, that there is as well a short command to login into your MySQL - server as root ( admin ), when you are logged in as root over ssh, which is "plesk db" ?


To convert a whole database over phpMyAdmin ( as admin = root ), you would use a SQL - query like:

SET@DATABASE_NAME ='NAME_OF_THE_DESIRED_DATABASE';

SELECT CONCAT('ALTER TABLE `', table_name,'` ENGINE=InnoDB;')AS sql_statements
FROM information_schema.tables AS tb
WHERE table_schema =@DATABASE_NAME
AND`ENGINE`='MyISAM'
AND`TABLE_TYPE`='BASE TABLE'
ORDERBY table_name DESC;

... which will result in a list of commands, which you will have to perform as a new SQL - command, in order to convert the database.

 

[bonny3]

Hi bonny3,


Could you please try to explain, what the problem is? Is this the error message you get:

Access denied for user 'admin'@'localhost' (using password: YES)

If this is your case, please consider to use the suggestions from:

How to get or reset password for "admin" account in Plesk ( KB - article 346 )​

Did you know, that there is as well a short command to login into your MySQL - server as root ( admin ), when you are logged in as root over ssh, which is "plesk db" ?


To convert a whole database over phpMyAdmin ( as admin = root ), you would use a SQL - query like:

SET@DATABASE_NAME ='NAME_OF_THE_DESIRED_DATABASE';

SELECT CONCAT('ALTER TABLE `', table_name,'` ENGINE=InnoDB;')AS sql_statements
FROM information_schema.tables AS tb
WHERE table_schema =@DATABASE_NAME
AND`ENGINE`='MyISAM'
AND`TABLE_TYPE`='BASE TABLE'
ORDERBY table_name DESC;

... which will result in a list of commands, which you will have to perform as a new SQL - command, in order to convert the database.

I have a Error:

Errore
Analisi statica:

Sono stati trovati 1 errori durante l'analisi.

1. Parola chiave non riconosciuta. (near "DESC" at position 225)

Query SQL:

SELECT CONCAT('ALTER TABLE `', table_name,'` ENGINE=InnoDB;')AS sql_statements FROM information_schema.tables AS tb WHERE table_schema =@DATABASE_NAME AND`ENGINE`='MyISAM' AND`TABLE_TYPE`='BASE TABLE' ORDERBY table_name DESC LIMIT 0, 25

Messaggio di MySQL:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDERBY table_name DESC LIMIT 0, 25' at line 6
I have a Error

Why?

 

[UFHH01]

Hi bonny3,

please consider to use as standart setting "utf8_general_ci" ( Start page for phpMyAdmin - "General Settings" ) to avoid issues with the above SQL - query, because different collations can cause errors.

If you still experience issues, please consider to modify your my.cnf ( inside "[mysqld]" ) to:

...
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
...

... and restart your mysql - server afterwards.

 

[bonny3]

Hi bonny3,

please consider to use as standart setting "utf8_general_ci" ( Start page for phpMyAdmin - "General Settings" ) to avoid issues with the above SQL - query, because different collations can cause errors.

If you still experience issues, please consider to modify your my.cnf ( inside "[mysqld]" ) to:

...
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
...

... and restart your mysql - server afterwards.

Good evening,
I always have the same problem .
Send a screenshot.
Continuous ada have an error in the string :
ORDERBY table_name DESC;

I hope you can help me

Thank you

 

[UFHH01]

Hi bonny3,

you can change each database specific global collations, by clicking on the database in phpMyAdmin, followed by a click on "Operations". You will now see the standart setting for this database at "Collation", which should equal the database specific settings for each database - table.

... but ... to be honest... I really don't know, why you don't use a ssh - client. Your way is much more time-investing and can ( as you already saw ) cause several issues. Using a command, as for example

mysql -uadmin -p`cat /etc/psa/.psa.shadow` YOUR_DATABASE_NAME -e "show table status where Engine='MyISAM';" | awk 'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}' | mysql -uadmin -p`cat /etc/psa/.psa.shadow` YOUR_DATABASE_NAME

... is far easier to perform and as far that I know, there shouldn't be any issue at all.


If you desire to convert ALL databases ( with a bypass for tables with FULLTEXT indexes ), you could as well use the command:

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' ENGINE=InnoDB;') FROM (SELECT A.db,A.tb,A.tbsize FROM (SELECT table_schema db,table_name tb,(data_length+index_length) tbsize FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A LEFT JOIN (SELECT table_schema db,table_name tb FROM information_schema.statistics WHERE index_type='FULLTEXT') B USING (db,tb) WHERE B.db IS NULL) AA ORDER BY tbsize">/root/ConvertMyISAM_to_InnoDB.sql

... inspect the SQL - file "/root/ConvertMyISAM_to_InnoDB.sql" and if you are satisfied, you would execute the SQL - file with the command:​

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -A </root/ConvertMyISAM_to_InnoDB.sql

 

[bonny3]

Hi bonny3,

you can change each database specific global collations, by clicking on the database in phpMyAdmin, followed by a click on "Operations". You will now see the standart setting for this database at "Collation", which should equal the database specific settings for each database - table.

... but ... to be honest... I really don't know, why you don't use a ssh - client. Your way is much more time-investing and can ( as you already saw ) cause several issues. Using a command, as for example

mysql -uadmin -p`cat /etc/psa/.psa.shadow` YOUR_DATABASE_NAME -e "show table status where Engine='MyISAM';" | awk 'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}' | mysql -uadmin -p`cat /etc/psa/.psa.shadow` YOUR_DATABASE_NAME

... is far easier to perform and as far that I know, there shouldn't be any issue at all.


If you desire to convert ALL databases ( with a bypass for tables with FULLTEXT indexes ), you could as well use the command:

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' ENGINE=InnoDB;') FROM (SELECT A.db,A.tb,A.tbsize FROM (SELECT table_schema db,table_name tb,(data_length+index_length) tbsize FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A LEFT JOIN (SELECT table_schema db,table_name tb FROM information_schema.statistics WHERE index_type='FULLTEXT') B USING (db,tb) WHERE B.db IS NULL) AA ORDER BY tbsize">/root/ConvertMyISAM_to_InnoDB.sql

... inspect the SQL - file "/root/ConvertMyISAM_to_InnoDB.sql" and if you are satisfied, you would execute the SQL - file with the command:​

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -A </root/ConvertMyISAM_to_InnoDB.sql

Thanks for the reply,

I have tried to run the various codes,
The first I produce an error:
ERROR 1214 (HY000) at line 1: The used table type does not support FULLTEXT index es

The second did not result from any

The third is that worked better, but it changed all databases and even the APSC, horde, psa, roundcubemail, sitebuilder5. is a correct thing to do?

If you want to change only a single database without generating the error?

Thank you

Best regards

 

[bonny3]

Hi bonny3,

you can change each database specific global collations, by clicking on the database in phpMyAdmin, followed by a click on "Operations". You will now see the standart setting for this database at "Collation", which should equal the database specific settings for each database - table.

... but ... to be honest... I really don't know, why you don't use a ssh - client. Your way is much more time-investing and can ( as you already saw ) cause several issues. Using a command, as for example

mysql -uadmin -p`cat /etc/psa/.psa.shadow` YOUR_DATABASE_NAME -e "show table status where Engine='MyISAM';" | awk 'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}' | mysql -uadmin -p`cat /etc/psa/.psa.shadow` YOUR_DATABASE_NAME

... is far easier to perform and as far that I know, there shouldn't be any issue at all.


If you desire to convert ALL databases ( with a bypass for tables with FULLTEXT indexes ), you could as well use the command:

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' ENGINE=InnoDB;') FROM (SELECT A.db,A.tb,A.tbsize FROM (SELECT table_schema db,table_name tb,(data_length+index_length) tbsize FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A LEFT JOIN (SELECT table_schema db,table_name tb FROM information_schema.statistics WHERE index_type='FULLTEXT') B USING (db,tb) WHERE B.db IS NULL) AA ORDER BY tbsize">/root/ConvertMyISAM_to_InnoDB.sql

... inspect the SQL - file "/root/ConvertMyISAM_to_InnoDB.sql" and if you are satisfied, you would execute the SQL - file with the command:​

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -A </root/ConvertMyISAM_to_InnoDB.sql

Good evening UFHH01,
Could you help me?
Thank you