Home > Cpanel/WHM > ISAM tables are still in use on this system. These tables must be manually upgraded to MyISAM before an upgrade of the MySQL server is possible.

ISAM tables are still in use on this system. These tables must be manually upgraded to MyISAM before an upgrade of the MySQL server is possible.

While updating mysql from v4.0 to v5.1 in Cpanel server(via WHM Mysql updater), I got the following error:

ISAM tables are still in use on this system. These tables must be manually upgraded to MyISAM before an upgrade of the MySQL server is possible.

The solution was to convert all database tables that are using ISAM engine to MyISAM. The following shell script will help you with the conversion.

NOTE: Take database backup before proceeding with the upgrade and execution of script.

#!/bin/bash
echo "show databases;"|mysql|egrep -v '(^Database|^mysql|^Cpanel)' > ~/databaselist.txt

for i in `cat databaselist.txt`
do
     echo "use ${i};show tables;"|mysql|grep -v ^Tables_in_ > tablesin_${i}.txt
        
     for j in `cat tablesin_${i}.txt`
        do
            var=$(echo "use ${i};show table status like '${j}' \G;"|mysql|grep -v ^Tables_in_|grep "Type:"|awk '{print $2}')
            
            if [ ${var} == 'ISAM' ];then
                 echo "use ${i};ALTER TABLE ${j} ENGINE=MyISAM;"|mysql
            fi
        done
done

OR else you can use the following script

#!/bin/bash

#List out all the databases except information_schema and mysql
DBLISTQUERY="use information_schema;select SCHEMA_NAME from SCHEMATA where SCHEMA_NAME <> 'information_schema' and SCHEMA_NAME <> 'mysql';"
echo ${DBLISTQUERY} |mysql|grep -v ^SCHEMA_NAME$ > ~/databaselist.txt


for i in `cat ~/databaselist.txt`
do
		##List out tables in the database whose engine is ISAM
		echo "use ${i};select TABLE_NAME from TABLES where TABLE_SCHEMA='${i}' and ENGINE='ISAM';"|mysql|grep -v ^TABLE_NAME$ > ~/tables.txt

		##Change engine of those ISAM databases to MyISAM
		for j in `cat ~/tables.txt`
		do
				echo "use ${i};ALTER TABLE ${j} ENGINE=MyISAM;"|mysql
		done

done
Advertisements
Categories: Cpanel/WHM
  1. Mp
    July 29, 2011 at 7:39 am

    I got this error when using the script:

    convert_databases.sh: line 12: [: ==: unary operator expected
    convert_databases.sh: line 12: [: ==: unary operator expected
    convert_databases.sh: line 12: [: ==: unary operator expected
    convert_databases.sh: line 12: [: ==: unary operator expected
    convert_databases.sh: line 12: [: ==: unary operator expected
    convert_databases.sh: line 12: [: ==: unary operator expected
    convert_databases.sh: line 12: [: ==: unary operator expected

    • August 1, 2011 at 2:16 pm

      Hi Mp,

      Please check whether your default shell is bash. You can use the following command to find the default shell:
      ——————–
      echo $SHELL
      ——————–

      If it’s not bash , change it to “/bin/bash”

  2. Mp
    August 1, 2011 at 4:14 pm

    Looks like bash is the default shell:

    root@server [/home]# echo $SHELL
    /bin/bash

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: