Ubuntu MySQL Upgrade Issue

I recently had to assist a friend who is hosting sites on an Ubuntu VPS; he found himself unable to connect to local MySQL instance as root.
Obviously, my first suggestion was to simply reset the password using the --skip-grant-tables trick that's well documented elsewhere online. However, attempting to stop the MySQL instance using the command /etc/init.d/mysql stop was not successful. So, after backing up the /var/lib/mysql directory, I began to research the issue a bit further:

1. Unable to use the init.d script to stop MySQL, I manually stopped the process: killall mysqld


2. Started the MySQL daemon, ignoring the permissions/grant tables: /usr/bin/mysqld_safe --skip-grant-tables &


3. Reset the root user's password: UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE User='root';


4. Ran mysqlcheck to look for any corrupted tables (especially in the "mysql" database containing the accounts and permissions) and noticed that errors were being thrown in relation to the "information_schema" database: mysqlcheck --all-databases


5. Attempted a repair of the "information_schema" database: mysqlcheck --repair information_schema


6. Ran mysqlcheck again against the "information_schema" database; errors were still produced by the same tables as before.


7. Restarted mysqld and noticed errors being output in relation to the "debian-sys-maint" user.

This particular error was what helped to identify the root cause of the issue. The server had recently been updated using the apt-get tool and this had included an update to MySQL. Trawling the web unearthed a bug filed on the Ubuntu project bug tracker that suggested that the post-upgrade scripts had not been executed. There is a dedicated MySQL account that exists on a Debian-based system specifically for this (and other DB maintenance) tasks: debian-sys-maint
The credentials for this user can be found in the file /etc/mysql/debian.cnf and should already be present in the MySQL database itself; allowing the system to perform maintenance tasks. Realising this user must be missing from the system, I set about recreating the user:

1. Logged into MySQL as root and, using the credentials specified in /etc/mysql/debian.cnf, created the "debian-sys-maint" user: GRANT ALL ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '';


2. Now restarting MySQL generated different error messages: Cannot proceed because system tables used by Event Scheduler were found damaged at server start


3. Knowing that the MySQL package installed on the system had been upgraded recently (and judging by the number of people in similar situations), I forced the execution of the mysql upgrade scripts: mysql_upgrade -u root -p --verbose --force

After these steps were carried out, restarting the MySQL instance occurred without any errors being produced as well as it being possible to authenticate to the system as "root".

Microsoft SQL Server 2005 DB Restore Issue

After a database backup that was taken from a Microsoft SQL Server 2000 instance was restored to a SQL Server 2005 instance, I experienced an issue where the name of the user that owned the database previously is prepended to all the table names. Check out the image below of the Object Explorer to see what I mean, it's a restored Confluence database; as you can see, "nconfluenceuser" is part of the table names. Usually, this would be "dbo" (DataBase Owner).



This caused an problem for the Confluence instance that attempted to use the database, because none of the queries performed against the database worked - they all failed with an "table does not exist" error.

To get around this issue, I performed the following steps:

1. Created a new database in the Object Explorer.


2. Right-clicked the DB, selected "Tasks", then "Import Data..." - this presented me with the Import and Export wizard.


3. As I was presented with the wizard's welcome screen, I had to hit "Next" to progress to the "Choose a Data Source" screen. Here you select the data source, server and database to import from. As my restored database was on the local machine, the "Data Source" and "Server Name" fields already had appropriate values; all I had to do was select the database from the drop-down list marked "Database".


4. Hitting "Next" took me to the "Destination" screen, which was already filled out correctly, because of how I kicked-off the wizard (via the destination database's context menu).


5. For the next step in the Wizard, I chose the default selection of "Copy data from one or more tables or views" before clicking "Next".


6. Now for the important bit; the "Select Source Tables and Views" step. Here I had to click the "Select All" button, then edit the "Destination" fields for all the tables; removing all the text prior to the table name. After moving down to the next table, the wizard ensured that the edited destination was correctly formatted. See the below pic for an example:
   
   These were the only modification I made in this step, I didn't change any of the other options present


7. I clicked "Next" a couple more times, which allowed me to review the pending transactions, before I clicked "Finish" to finally perform the import; this could take some time, depending on the size of the DB to import and the speed of the machine.


8. After it had finished and I closed the wizard, I was able to see that the newly created DB no longer had the "nconfluence" username prepended to it, but "dbo"; see the below picture.
   


9. My final step was to create a user for Confluence to bind to the database as, granting the account "dbo" privileges over the imported DB.