Database: I have tables of type InnoDB and MyISAM...

[MEVi]

Hello,

In the database I have tables of type InnoDB and MyISAM it's good or not ?

[CoffeeCake]

I'd recommend considering migrating everything to InnoDB, assuming you are running 4.4 or higher. There is a greater risk of data loss with MyISAM and MySQL as well as new IPS installs now default to InnoDB.

[MEVi]

Is there a procedure for making this change? (except making a backup of the database).

[CoffeeCake]

Here's the MySQL documentation: https://dev.mysql.com/doc/refman/8.0/en/converting-tables-to-innodb.html

Backup everything, and select DYNAMIC row format.

[MEVi]

DYNAMIC row format, not working with my host

#!/bin/bash

DATABASE=XXX

ROW_FORMAT=DYNAMIC
#ROW_FORMAT=COMPRESSED

TABLES=$(echo SHOW TABLES | mysql -s $DATABASE)

for TABLE in $TABLES ; do
    echo "ALTER TABLE $TABLE ROW_FORMAT=$ROW_FORMAT;"
    echo "ALTER TABLE $TABLE ROW_FORMAT=$ROW_FORMAT" | mysql $DATABASE
done

 

[CoffeeCake]

You'll need to work with them to sort that out, I'm afraid. You may run into issues if the row format is not dynamic.

[MEVi]

He replied that this would have to be changed for all clients, which is unthinkable.

[Nathan Explosion]

6 hours ago, MEVi said:

He replied that this would have to be changed for all clients, which is unthinkable.

Incorrect - Dynamic row format is a per-database feature; he probably thinks you have asked for the default setting for new tables on the server to be set to Dynamic.

Read this topic:

 

[MEVi]

@Nathan Explosion,

I've read your topic and I confess I don't understand it at all. I need to convert from MyISAM to InnoDB.

Edited January 27, 2021 by MEVi

[MEVi]

@Paul E.,

Quote

How to convert MyISAM to InnoDB?

Here is how we convert to InnoDB using phpMyAdmin and MySQL CLI. First, let’s see how we do it using phpMyAdmin.

• First, we login to phpMyAdmin.
• Then, check the type column and see the storage engines.
• Later, click on the MyISAM table and click the Operations tab and change the storage engine.

Alternatively, we run the below command to change the storage engine of a particular table to InnoDB.

ALTER TABLE table_name ENGINE=InnoDB;

• https://bobcares.com/blog/convert-myisam-to-innodb/

[CoffeeCake]

Bob is giving some poor advice. Don't perform schema changing SQL operations via phpMyAdmin. phpMyAdmin can be a useful tool to do some basic row level inquiries/modifications if needed and basic exploration. You should get into the habit of using a proper client on the database server itself for significant write and change commands. Changing the underlying engine of a table is a significant change. Connecting via terminal and using the mysql command would be a far better approach.

Command line documentation:
https://dev.mysql.com/doc/refman/8.0/en/mysql.html

If your host doesn't give you the option to make the row format dynamic (you may be on an older version of MySQL, and/or your database may be using the original Antelope file format and not Barracuda, the newer InnoDB file format required for dynamic row format), and you're not having issues, then you should be okay for now. It's a recommendation but not a requirement.

The steps required to do all this are going to require reading up on the documentation and better understanding your specific hosting environment. Take a look at what version of MySQL you're running, the schema properties of your database, and how the database engine is configured.

[MEVi]

Hello @Paul E.,

almost all tables in the database are InnoDB except for core_referral_banners, core_follow, +10.

ALTER TABLE core_referral_banners ENGINE=InnoDB
ALTER TABLE core_follow ENGINE=InnoDB
... +10

 

Edited January 29, 2021 by MEVi