Disclaimer

This blog is kind of my own personal work Notebook, and the processes below are only guaranteed to work for my specific configurations. So, use this info at your own risk, and please understand I won't be able to provide any help if you break something

Tuesday, January 7, 2025

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.

Saturday, June 1, 2024

Install MediaWiki on a secure, optimized stack with Nginx, MariaDB, and PHP on Ubuntu.

  1. Install Nginx, MariaDB, and PHP:
sudo apt update sudo apt install nginx mariadb-server php php-fpm php-mbstring php-xml php-json php-mysql php-curl php-intl php-gd php-mbstring texlive imagemagick unzip -y
  1. Configure MariaDB:
sudo mysql_secure_installation sudo mysql -u root -p

Inside the MariaDB shell:

CREATE DATABASE wikidb; GRANT ALL PRIVILEGES ON wikidb.* TO 'wikiuser'@'localhost' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; EXIT;
  1. Download and Configure MediaWiki:
cd /var/www/html wget https://releases.wikimedia.org/mediawiki/1.31/mediawiki-1.31.0.tar.gz tar xvf mediawiki-1.31.0.tar.gz mv mediawiki-1.31.0 mediawiki sudo chown -R www-data:www-data /var/www/html/mediawiki
  1. Configure Nginx: Create a new Nginx configuration file:
sudo nano /etc/nginx/conf.d/wiki.conf

Add the following content:

server { listen 80; server_name wiki.example.com; root /var/www/html/mediawiki; index index.php; error_log /var/log/nginx/mediawiki.error; access_log /var/log/nginx/mediawiki.access; location / { try_files $uri $uri/ /index.php; } location ~ \.php$ { fastcgi_pass unix:/run/php/php7.4-fpm.sock; fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name; include fastcgi_params; include snippets/fastcgi-php.conf; } location ~ /\.ht { deny all; } }

Test and restart Nginx:

sudo nginx -t sudo systemctl restart nginx
  1. Secure with Let's Encrypt: Install Certbot:
sudo apt install certbot python3-certbot-nginx -y

Obtain and install the SSL certificate:

sudo certbot --nginx -d wiki.example.com
  1. Finalize MediaWiki Installation: Open your browser and navigate to http://wiki.example.com to complete the MediaWiki setup through the web interface.

Sunday, May 5, 2024

WordPress recovery mode

You can manually generate a recovery link to gain access to your WordPress site by adding: 

/wp-login.php?action=entered_recovery_mode

right after your domain name.  


For example, if your domain name is www.example.com, the 'safe mode' recovery URL would be: 

https://www.example.com/wp-login.php?action=entered_recovery_mode


Friday, December 8, 2023

Using 'screen' to detach your local computer from long-running processes on a remote server

After getting the error message "client_loop: send disconnect: Broken pipe", (which typically indicates a broken connection between the SSH client where you are running the command and the server), I started using screen, which is the perfect solution to manage long-running processes on a remote server that take a long time to complete, like data processing, backups, or maintenance scripts. 

screen or GNU screen is an utility that multiplexes a physical terminal between several processes, typically interactive shells. In my case screen allowed me to start a process remotely from my computer, disconnect from it, and then reconnect later, all without interrupting the process because my computer gets detached from it, so no more  broken connections issues. The whole process takes place uninterrupted at the server until you decide to reconnect and resume the session. Sounds like basic stuff I wish I had known earlier, but it's never too late!

Here's a step-by-step guide on how to use screen for this particular purpose:

Step 1: Connect to the Remote Server

First, use SSH or your preferred method to connect to the remote server where you want to run the process.

bash
ssh user@remote-server.com

Step 2: Install screen (if not already installed)

Make sure screen is installed on the server. You can usually install it using the package manager:

For Ubuntu/Debian systems:

bash
sudo apt-get install screen

For CentOS/RHEL systems:

bash
sudo yum install screen

Step 3: Start a New screen Session

Once connected to the server and screen is installed, start a new screen session:

bash
screen -S session_name

Replace session_name with a name that helps you identify the session later.

Step 4: Run Your Long-Running Process

Within the screen session, start your long-running process. For example:

bash
python long_running_script.py

or

bash
./rebuildall.php

Step 5: Detach from the screen Session

After starting the process, you can detach from the screen session and let the process run in the background. To detach, press:

  • Ctrl + A, then D

This key combination first tells screen you are about to give it a command (Ctrl + A) and then D tells it to detach.

Step 6: Disconnect from the Server

Now, you can safely disconnect from the server, and your process will continue to run in the screen session.

bash
exit

Step 7: Reconnect to the Server and Resume screen Session

Later, when you want to check on your process, reconnect to the server and use the following command to list your screen sessions:

bash
screen -ls

To reattach to your session, use:

bash
screen -r session_name

If you have only one detached session, screen -r will reattach to it directly.

Additional Tips

  • Multiple Sessions: You can have multiple screen sessions running at the same time. Give each one a unique name to identify them easily.
  • Session Commands: Within screen, Ctrl + A is the command prefix for various operations (like creating new windows, navigating between them, etc.).
  • Closing a Session: To close a session, simply exit the shell in the screen session, or you can detach and then type screen -X -S session_name quit.

Using screen in this way ensures that your long-running processes on a remote server are safe from network disruptions and can be easily managed without needing to keep a continuous connection.

There’s lots more to learn about screen at the Screen User’s Manual page.

Sunday, November 26, 2023

Maintenance scripts to run after a MediaWiki migration / update

Migrating or updating a MediaWiki installation is a significant task that requires careful attention to detail to ensure smooth operation and data integrity. 

Here are some of the most important maintenance scripts to run after a MediaWiki migration or update:

  • update.php: This is arguably the most crucial script to run after an update. It updates the MediaWiki database schema to the latest version. Failing to run this script can result in database errors or a non-functioning wiki.
  • rebuildall.php: This script helps in rebuilding various secondary storage structures, including search indexes, recent changes, and more. It's particularly useful if there's been a significant change in the data structure or storage engine. 
    rebuildall.php is the equivalent of running rebuildtextindex.php, followed by rebuildrecentchanges.php, and finally followed by refreshLinks.php.
  • runJobs.php: MediaWiki defers some tasks to be executed later by job queue. After an update or migration, it’s important to make sure that any pending jobs are processed, as they might include tasks essential for the wiki's functionality.
  • refreshLinks.php: This script updates the links tables, which store information about what pages link to each other. It's essential for maintaining the integrity of internal links and backlinks, especially after large-scale content migration.
  • rebuildtextindex.php: If you’re using MediaWiki's built-in search engine, this script will rebuild the full-text search index. This is crucial for ensuring that the search function works correctly post-migration.
  • initSiteStats.php: This script recalculates and updates site statistics, such as the number of articles, files, users, page views, etc. It's important for keeping track of the growth and usage of your wiki.
  • rebuildrecentchanges.php: This script rebuilds the recent changes table. It’s particularly useful if there have been significant changes to the wiki's content or structure.
  • checkImages.php: Use this script to check the consistency of images and image metadata, ensuring that all files are correctly stored and accessible.
  • rebuildFileCache.php: If you're using file caching, this script will help regenerate the cache, which is important for performance, especially on high-traffic wikis.
  • cleanupUsersWithNoId.php: This script cleans up users who don't have an ID in the user table, which might happen during migrations.
  • cleanupSpam.php: If your wiki is public and allows user contributions, this script can help clean up spam pages.
  • updateSearchIndex.php: In case you've made significant changes to the content, running this script will update the search index to reflect the new state of the wiki.