0

I had some foreign key issues in my database when I change the table engine "MyISAM" to "InnoDB" but then it works fine.

I found one query to change a single table's engine but I have many tables so what I want to know is there any way to do it all once?

ALTER TABLE tbl_name ENGINE=INNODB;

What else I have tried?

I also tried to update the my.ini file by adding the code below but it doesn't work!

default-storage-engine = InnoDB

I found a query way is more convenient as of my understanding so any suggestion for a change it all table engine at once?

This is the Database server detail:

Server: 13227.3232.3232.3232 via TCP/IP
Server type: MariaDB
Server version: 10.4.10-MariaDB - mariadb.org binary distribution
Protocol version: 10
3

My strong advice is not to try and do this on a running database! MyISAM isn't transactional in any case, so any operation that spans many tables can't be performed in this manner (it can in InnoDB - your reason for wanting to change?). You will have to either dump the db and modify the text file or iterate through the tables.

MySQL is moving firmly away from MyISAM - it's being deprecated. Not even the system tables are MyISAM anymore (MySQL >= 8.xx).

BTW, please always include the version of MySQL that you are using - it can be very important - see below!

Therefore, I propose the following solution:

Step 1:

Take a backup of your database using mysqldump - you should be doing this on a regular basis anyway - here is a reference (there are many!):

mysqldump database_name > database_name.sql

of if you have multiple databases on the same server:

mysqldump -u root -p --databases database_name_a database_name_b ... > databases_a_b.sql

Step 2:

Using the text editor of your choice, go through the file and search for the word ENGINE and replace each occurrence of ENGINE=MyISAM with ENGINE=InnoDB.

If you're on *nix, then something like this will do the trick:

sed -e 's/ENGINE=MyISAM/ENGINE=InnoDB/g' > new_file.sql

Do not do this for every occurrence of MyISAM in the entire MySQL server! If you are using a version of MySQL which still has MyISAM system tables - this is very risky - from here - MySQL 5.7. Will not apply for later versions which have moved the system tables to InnoDB anyway:

Warning Do not convert MySQL system tables in the mysql database from MyISAM to InnoDB tables. This is an unsupported operation. If you do this, MySQL does not restart until you restore the old system tables from a backup or regenerate them by reinitializing the data directory (see Section 2.10.1, “Initializing the Data Directory”).

So, do not do the change for any tables in the mysql, information_schema or performance_schema databases - only in the databases that you've created yourself.

Step 3:

Restore from this backup and you're good to go! You should drop your original database and recreate an empty schema (see reference above) of the same name and then:

mysqld  database_name < new_file.sql

At this point, you may wish to implement FOREIGN KEYS - one of the many good reasons to upgrade to InnoDB! Be careful - MyISAM doesn't have DRI (Declarative Referential Integrity) so there may be orphaned records in your existing system - you might want to run tests to check on this?

Running system alternative:

If you're unable to bring down the database, then a solution like this (iterating through every table in the schema) might be helpful (adapted from here and here):

CREATE PROCEDURE UpdateTables ()
BEGIN
  DECLARE Finished BOOL DEFAULT FALSE;
  DECLARE TableName VARCHAR(64);

  DECLARE TablesCursor CURSOR FOR
    SELECT c1.TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c1
    WHERE c1.TABLE_SCHEMA = 'my_schema';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished = TRUE;

  OPEN TablesCursor;

  MainLoop: LOOP
    FETCH TablesCursor INTO TableName;
    IF Finished THEN
      LEAVE MainLoop;
    END IF;

    SET @queryText = CONCAT('ALTER TABLE ', TableName , ' ENGINE=InnoDB');  -- may or may not need backtics
    PREPARE updateQuery FROM @queryText;
    EXECUTE updateQuery;
    DEALLOCATE PREPARE updateQuery;
  END LOOP;

  CLOSE TablesCursor;
END

Note: this is untested - please use with caution and do your own tests before committing to a definitive solution. I would also advise doing something like this when the system load is as light as possible!

Alternatives to the alternative (again, not tested):

If you're a PHP person, this might be suitable.

Another (interesting) approach is this one by Shlomi Noach - a big hitter in the MySQL world.

| improve this answer | |
2

Since your real goal is to convert MyISAM to InnoDB, there are other issues that may need dealing with.

I cover all the issues in http://mysql.rjweb.org/doc.php/myisam2innodb , including how to run a single SQL to generate the set of ALTERs that you are asking for.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.