Converting MyISAM tables to InnoDB on MediaWiki

Converting MyISAM tables to InnoDB is generally safe and recommended for most use cases, as InnoDB offers better reliability, performance, and features like transactions and row-level locking. 

However, there are some potential risks and considerations you should be aware of before proceeding. 

Here's a detailed breakdown:

---

Potential Risks and Considerations

1. Full-Text Search Limitations

• MyISAM: Supports full-text indexing and searching natively.

• InnoDB: Full-text search support was added in MySQL 5.6+ and MariaDB 10.0+, but it may behave differently or have performance implications compared to MyISAM.

• Action: If your MediaWiki installation relies heavily on full-text search (e.g., for searching article content), test the performance after conversion.

---

2. Table-Level Locking vs. Row-Level Locking

• MyISAM: Uses table-level locking, which can cause contention during heavy write operations.

• InnoDB: Uses row-level locking, which is more efficient for concurrent writes but may require more system resources (e.g., CPU and RAM).

• Action: Ensure your server has sufficient resources to handle InnoDB's row-level locking.

---

3. Storage Requirements

• MyISAM: Typically uses less disk space than InnoDB.

• InnoDB: May use more disk space due to its support for transactions, foreign keys, and other features.

• Action: Ensure you have enough disk space before converting large tables.

---

4. Foreign Key Constraints

• MyISAM: Does not support foreign keys.

• InnoDB: Enforces foreign key constraints, which can cause errors if your schema relies on MyISAM's lack of enforcement.

• Action: Review your database schema for any implicit dependencies that might break when foreign keys are enforced.

---

5. Performance Differences

• MyISAM: Can be faster for read-heavy workloads with minimal writes.

• InnoDB: Generally performs better for mixed read/write workloads and high concurrency.

• Action: Test the performance of your MediaWiki installation after conversion to ensure it meets your expectations.

---

6. Downtime During Conversion

• Converting large tables can take time and may require downtime, especially if the table is heavily used.

• Action: Schedule the conversion during a maintenance window or when traffic is low.

---

7. Backup and Rollback

• If something goes wrong during the conversion, you’ll need a backup to restore the original state.

• Action: Always back up your database before converting tables.

---

Steps to Safely Convert MyISAM Tables to InnoDB

1. Backup the Database

Before making any changes, back up your entire database:

bash

Copy

sudo mysqldump -h localhost -u user_name -p db_name > db_name_backup.sql

---

2. Identify MyISAM Tables

Run the following SQL query to list all MyISAM tables in your database:

sql

Copy

SELECT TABLE_NAME 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'db_name
' AND ENGINE = 'MyISAM';

---

3. Test Conversion on a Single Table

Before converting all tables, test the conversion on a single, less critical table to ensure there are no issues:

sql

Copy

ALTER TABLE table_name ENGINE=InnoDB;

Replace table_name with the name of the table.

---

4. Monitor Performance and Functionality

After converting a table:

• Check for errors in the MariaDB logs.

• Test the functionality of your MediaWiki installation (e.g., editing pages, searching, etc.).

• Monitor server performance (CPU, RAM, disk I/O).

---

5. Convert All MyISAM Tables

If the test conversion is successful, convert all MyISAM tables to InnoDB. You can generate the conversion commands dynamically:

sql

Copy

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ENGINE=InnoDB;') 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'db_name' AND ENGINE = 'MyISAM';

Copy and execute the generated commands in the MariaDB shell.

---

6. Verify the Conversion

After converting all tables, verify that they are now using the InnoDB engine:

sql

Copy

SELECT TABLE_NAME, ENGINE 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'db_name';

---

7. Optimize Tables (Optional)

After conversion, you may want to optimize the tables to reclaim unused space and improve performance:

sql

Copy

OPTIMIZE TABLE table_name;

---

What to Do If Something Goes Wrong

If you encounter issues after converting tables:

1. Restore the database from the backup:

   bash

   Copy

   sudo mysql -h localhost -u user_name -p db_name < db_name
   _backup.sql

2. Check MariaDB logs, review schema dependencies, etc.

3. Unfortunately I won't be able to help, so restore your backup

4. Seek help from the  MediaWiki support community  or a database administrator.