Topic: My experience migrating an SME10 test server to SME11

[wdepot]

I thought it might be good to give everyone an idea of what it takes (at least currently) to migrate from SME10 to SME11.

First of course, you need a fresh install of SME11 on the target and make sure it is fully updated. Since I do web programming I also installed Webhosting and PHPmyadmin on the new SME 11 installation.

For my first attempt at transfer I made a backup to a USB disk from the admin console (when you log into the server directly using the admin user) on the SME10 machine. I then used the admin console on the SME11 machine to restore from the USB disk. Everything seemed to work fine until I tried accessing one of the test web sites on the SME11 server and promptly got the following error message (being a test server I have all PHP error messages enabled):

Fatal error: Uncaught mysqli_sql_exception: Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted

The error is tied to a line that reads:

 $check_query = tep_db_query('show function status'); //(tep_db_query is a wrapper for the mysqli_query function)
 
The results of the query are a list of user defined MySQL functions which is checked for the existence of a couple of functions and if they don't exist they are created with a MySQL "create function" query.

After getting the error I checked the proc table in the mysql database using PHPmyadmin and quickly realized that this table is where user defined MySQL functions are stored. On the old SME10 test server I used PHPmyadmin to perform "show create table" on the mysql.proc table and saved the results including that it used MariaDB 5.5.68 (the version shown by PHPmyadmin on SME10). I did a completely new install of SME11 on the new test server and then again used the "show create table proc" command and compared the results for MariaDB 11.4.10 on the new machine to what I got from the old SME10 test server. It quickly became apparent that not only does MariaDB add a new column to the mysql.proc table but the definitions for several of the other columns have also changed. In addition, while this table on the SME10 machine contained only functions that were defined by my own test web sites, the new SME11 version already contains quite a few defined procedures and functions that you don't want to overwrite from an old backup.

A few other failed attempts at transfer finally led me to the Migratehelper contribution which I discovered was already installed on the SME10 machine when I tried to use yum to install it per the instructions. It may have been made part of the of the final update that was made to SME10.

The procedure I followed to finally complete a successful transfer is as follows:

First I used PHPmyadmin on the old SME10 server and selected each of my user created databases and used the Export command. This downloaded an SQL file named with the database name.

Next I modified the the /usr/bin/migratehelper.sh script. Find the exclude files list and be sure to add

Code: [Select]

/home/e-smith/db/mysql to the list to make sure the backup created by the script does not include SQL files that you do not want to overwrite. By the way, I would suggest using nano to edit the script and not vim as suggested in the instructions. I had to do a hard restart on the SME10 server when I tried to do the edit with vim because I could not get it to return to command mode to save and exit.

Once I finally had the script modified to prevent saving MySQL files I ran it and created a new backup on the USB disk as instructed in the documentation.

Since I knew that restoring the backup to the SME11 machine first would set the IP address for it to the same as that of the SME10 machine which would create an Ethernet conflict for the rsync instructions in the Migratehelper documentation, I performed the rsync instruction first. Then I logged in to the admin console on the SME11 machine and restored from the backup created by the modified Migratehelper script.

Next I used PHPmyadmin on the SME11 server to create each of the user databases that were on the old SME10 machine and then Imported the saved SQL files to each one. Make sure you have the correct database selected before you click the Import button as I accidentally imported one into the mysql database and had to redo the install of the SME11 server.

The final step was to use PHPmyadmin to recreate the users for each of my databases with the correct privileges and passwords so my test sites would be able to access them.

I finally had successfully copied the content from my old SME10 machine to the SME11 server and the test sites are working on the new server with minor tweaks needed.

[ReetP]

Please remember this is Beta.

As normal, any issue should be reported in the bug tracker (or chat to us directly on rocket - just ask me for an account) or it may well get missed. That is the place to report problems and get assistance. Not here as it can confuse other users - they may not have the same setup as you.

Backup/restore should work with standard dbs - if it doesn't then bug it.

If you've been hacking about then failures may be expected. But again, bug it so we know.

Also if you have upgraded some dbs to maria 10.x they will not be automatically imported.

Migratehelper has been around a while and is a great tool, but if you can't use vi/vim (the interwebs have help pages you could read) then just use nano. Don't just blindly follow instructions......!

[ReetP]

PS..... Most upgrade issues are due fundamentally to upstream choices.

I still don't understand how Debian/derivatives can manage pretty well seamless upgrades, and RedHat can't. 

[Jean-Philippe Pialasse]

a lot has change between mariadb 5.5 and 11.4.

we handled issues with mysql.user being a read only view and not a table anymore. 

if you want to bug your issue with mysql.proc go but we will need a curated dump to be able to reproduce. 


also have you executed a post-upgrade and reboot to be sure tables were upgraded by mariadb upgrade script?

[gary.futch]

I thought it might be good to give everyone an idea of what it takes (at least currently) to migrate from SME10 to SME11.

First of course, you need a fresh install of SME11 on the target and make sure it is fully updated. Since I do web programming I also installed Webhosting and PHPmyadmin on the new SME 11 installation.

For my first attempt at transfer I made a backup to a USB disk from the admin console (when you log into the server directly using the admin user) on the SME10 machine. I then used the admin console on the SME11 machine to restore from the USB disk. Everything seemed to work fine until I tried accessing one of the test web sites on the SME11 server and promptly got the following error message (being a test server I have all PHP error messages enabled):

Fatal error: Uncaught mysqli_sql_exception: Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted

The error is tied to a line that reads:

 $check_query = tep_db_query('show function status'); //(tep_db_query is a wrapper for the mysqli_query function)
 
The results of the query are a list of user defined MySQL functions which is checked for the existence of a couple of functions and if they don't exist they are created with a MySQL "create function" query.

After getting the error I checked the proc table in the mysql database using PHPmyadmin and quickly realized that this table is where user defined MySQL functions are stored. On the old SME10 test server I used PHPmyadmin to perform "show create table" on the mysql.proc table and saved the results including that it used MariaDB 5.5.68 (the version shown by PHPmyadmin on SME10). I did a completely new install of SME11 on the new test server and then again used the "show create table proc" command and compared the results for MariaDB 11.4.10 on the new machine to what I got from the old SME10 test server. It quickly became apparent that not only does MariaDB add a new column to the mysql.proc table but the definitions for several of the other columns have also changed. In addition, while this table on the SME10 machine contained only functions that were defined by my own test web sites, the new SME11 version already contains quite a few defined procedures and functions that you don't want to overwrite from an old backup.

A few other failed attempts at transfer finally led me to the Migratehelper contribution which I discovered was already installed on the SME10 machine when I tried to use yum to install it per the instructions. It may have been made part of the of the final update that was made to SME10.

The procedure I followed to finally complete a successful transfer is as follows:

First I used PHPmyadmin on the old SME10 server and selected each of my user created databases and used the Export command. This downloaded an SQL file named with the database name.

Next I modified the the /usr/bin/migratehelper.sh script. Find the exclude files list and be sure to add

Code: [Select]

/home/e-smith/db/mysql to the list to make sure the backup created by the script does not include SQL files that you do not want to overwrite. By the way, I would suggest using nano to edit the script and not vim as suggested in the instructions. I had to do a hard restart on the SME10 server when I tried to do the edit with vim because I could not get it to return to command mode to save and exit.

Once I finally had the script modified to prevent saving MySQL files I ran it and created a new backup on the USB disk as instructed in the documentation.

Since I knew that restoring the backup to the SME11 machine first would set the IP address for it to the same as that of the SME10 machine which would create an Ethernet conflict for the rsync instructions in the Migratehelper documentation, I performed the rsync instruction first. Then I logged in to the admin console on the SME11 machine and restored from the backup created by the modified Migratehelper script.

Next I used PHPmyadmin on the SME11 server to create each of the user databases that were on the old SME10 machine and then Imported the saved SQL files to each one. Make sure you have the correct database selected before you click the Import button as I accidentally imported one into the mysql database and had to redo the install of the SME11 server.

The final step was to use PHPmyadmin to recreate the users for each of my databases with the correct privileges and passwords so my test sites would be able to access them.

I finally had successfully copied the content from my old SME10 machine to the SME11 server and the test sites are working on the new server with minor tweaks needed.

This isn’t really corruption, it’s a version mismatch issue with MariaDB system tables. When you restored the full backup from SME10, it also brought over the old mysql.proc table from MariaDB 5.5, but SME11 is running MariaDB 11 where the structure is different. That’s why you hit the “column count is wrong” error. System tables like mysql.proc are not meant to be copied across major versions.

The approach you ended up using is actually the correct one: don’t migrate the mysql system database, only export and import your user databases. Creating fresh databases on the new server and importing SQL dumps avoids all those compatibility issues. Excluding /home/e-smith/db/mysql in migratehelper was the key fix here.

If someone already overwrote system tables and things break, usually the fix is to reinitialize MySQL/MariaDB and reimport data properly. And if things go further and user tables become inaccessible or damaged during failed migrations, tools like Stellar Repair for MySQL can sometimes help recover data before rebuilding.

[Jean-Philippe Pialasse]

I must say the opposite, you need to import your mysql.dump as part of restore process of SME Server.
by default we only run mariadb-upgrade after mysql-init it run after importing, logs should helps debugging that andagain it is expected you migrate your mysql.dump including proc table and migration update the content..

only way to understand would be to have a bug with your logs + snippet of code to show the issue to be bale to reproduce

it is expected that running mariadb-upgrade --upgrade-system-tables should fix that

perhaps it needs a mariadb-upgrade --upgrade-system-tables --force in this case... but again it needs a proper investigation and debugging