Upgrading Simple Machines Forum (SMF) to Mint 21 and MariaDB 10.6.23

There are versious things to consider, here. A few are:

• I did this in a virtual machine
• Creating databases and users requires a different syntax than older versions of MariaDB
• Deleting a user and re-creating a user on MariaDB is fussier than previous versions.

Notes

1. First, creating a database and user is as follows:

create database smf_okn8hy;
CREATE USER 'k_hjBBgh'@'localhost' IDENTIFIED BY 'w_lNJHU';
GRANT ALL PRIVILEGES ON smf_okn8hy.* TO 'kjnM_iJh'@'localhost';

2. Deleting a database and adding a user threw errors (if the user had pre-existed). The error was: ERROR 1396 (HY000). Fixing it was using a combination of the commands at the bottom of this page which I copied from Google – it was a pain in the ass. Deleting the database worked normally but I could not delete a user and, then, re-add the user without some of the commands noted below. I think it went like the following but I would swear to nothing…I cannot recall with certainty what I had to do:

DROP USER 'username'@'hostname';
FLUSH PRIVILEGES;
CREATE USER IF NOT EXISTS 'your_username'@'your_host' IDENTIFIED BY 'your_password';

3. I thought I would upgrade SMF incrementally, since mine was old, but I had to use the newest release. I downloaded and attempted to upgrade with version 2.0.19 and it did not work. It failed to connect to the database, would not accept my password, etc. Also, I cannot recall for sure but I believe I had to manually edit the admin password using Phpmyadmin and a plain text password. There was a warning ‘thing’ that said this might be required and the password would be converted to a hashed version autmatically. Anyway, I had to use version 2.1.7. Here’s their download page.

4. Make sure to edit your hosts file, particularly in a virtual machine. I was doing this on VMware and VMware will read the hosts file on the host machine for a URL. I had a forum located at www.netkwik.com/smf (which I was testing in a VM) and had big problems until I pointed the virtual machine to itself for netkwik.com.

5. Upgrading is simply the following:

1. Create your database and upload your old .sql file with phpmyadmin.
2. I would not swear to it but I think you have to find the user table and change the password to plain text before running the upgrade.
3. Just copy all of the files in the upgrade package over your old files.
4. Go to http://yourdomain.com/smf/upgrade.php
5. Follow the prompts….that’s it.

From Google – help with MariaDB

• User Already Exists: You are creating a user with the same name and host as an existing account.
• Zombie User Account: A user was deleted using DELETE FROM mysql.user instead of DROP USER. This leaves orphaned entries in other privilege tables, preventing a fresh creation of the same name.
• Cached Privileges: The server has cached old user data and needs to refresh its internal grant tables.
• Host Mismatch: Creating user@'%' when user@'localhost' already exists (or vice versa) can sometimes trigger conflicts depending on the specific operation. 
  Stack Overflow +5

1. Check for Existing Users
   Verify if the user exists and note the exact host they are associated with:
   SELECT User, Host FROM mysql.user WHERE User = 'your_username';
   

   (Note: If the user appears but you cannot drop them normally, they might be in a “zombie” state).

2. Properly Drop and Flush
   If the user exists or you just “deleted” them manually, use the proper command and then refresh the system:
   DROP USER 'your_username'@'your_host';

   FLUSH PRIVILEGES;
   

   If DROP USER fails, you may need to manually clean the table and then flush:

   DELETE FROM mysql.user WHERE User='your_username' AND Host='your_host';
   FLUSH PRIVILEGES;
   

   (Refer to the MySQL DROP USER documentation for syntax details).

3. Use “IF NOT EXISTS”
   To prevent this error in scripts, use the IF NOT EXISTS clause during creation:

   CREATE USER IF NOT EXISTS 'your_username'@'your_host' IDENTIFIED BY 'your_password';
   ```
   
   

4. Restart the Service
   If FLUSH PRIVILEGES does not work, a full restart of the MySQL/MariaDB service often clears the memory cache causing the conflict. 
   Stack Overflow +4