When I upgraded my main tower to Slack v11.0, I had also upgraded MySQL to v5.0.24a (I don't know what version I was using before this). Soon after the upgrade, I noticed that I couldn't access my local PHPBB and PHPMyAdmin installs.
I was receiving the following error using the MySQL client:
bash-3.1$ mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
The MySQL error logs looked like this:
061227 03:08:12 mysqld started
061227 3:08:12 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=slackbox-bin' to avoid this problem.
061227 3:08:12 InnoDB: Started; log sequence number 0 4066534
061227 3:08:12 [Warning] Found invalid password for user: 'root @% '; Ignoring user
061227 3:08:12 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.24a-log' socket: '/tmp/mysql.sock' port: 0 Source distribution
061227 13:13:11 [Note] /usr/libexec/mysqld: Normal shutdown
Even after restarting the MySQL service, skipping grant tables, and reseting the password, the above still showed. So, I ran the following (after restarting the MySQL service using the init script):
bash-3.1$ mysql_fix_privilege_tables --verbose
This script updates all the mysql privilege tables to be usable by
MySQL 4.0 and above.
This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION, stored procedures, or
more secure passwords in 4.1
You can safely ignore all 'Duplicate column' and 'Unknown column' errors
because these just mean that your tables are already up to date.
This script is safe to run even if your tables are already up to date!
ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'
ERROR 1060 (42S21) at line 28: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 29: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 30: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 41: Duplicate column name 'ssl_type'
ERROR 1146 (42S02) at line 67: Table 'mysql.procs_priv' doesn't exist
ERROR 1146 (42S02) at line 68: Table 'mysql.procs_priv' doesn't exist
ERROR 1146 (42S02) at line 70: Table 'mysql.procs_priv' doesn't exist
ERROR 1146 (42S02) at line 72: Table 'mysql.procs_priv' doesn't exist
ERROR 1054 (42S22) at line 94: Unknown column 'Type' in 'columns_priv'
ERROR 1060 (42S21) at line 100: Duplicate column name 'type'
ERROR 1060 (42S21) at line 110: Duplicate column name 'Show_db_priv'
ERROR 1060 (42S21) at line 127: Duplicate column name 'max_questions'
ERROR 1060 (42S21) at line 137: Duplicate column name 'Create_tmp_table_priv'
ERROR 1060 (42S21) at line 140: Duplicate column name 'Create_tmp_table_priv'
ERROR 1061 (42000) at line 145: Duplicate key name 'Grantor'
ERROR 1054 (42S22) at line 247: Unknown column 'Create_view_priv' in 'where clause'
ERROR 1054 (42S22) at line 277: Unknown column 'Create_routine_priv' in 'where clause'
ERROR 1054 (42S22) at line 313: Unknown column 'Create_user_priv' in 'where clause'
done
bash-3.1$
After that, I was able to access the databases using the root MySQL account:
bash-3.1$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
bash-3.1$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.24a-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> quit
Bye
bash-3.1$
The issue? Apparently, there were three duplicates of the "Grant_priv" column, which I don't think was the REAL issue. Per the script notes, duplicates don't appear to break anything, but looking at where the script notes state, "This script updates all the mysql privilege tables to be usable by MySQL 4.0 and above," I wonder if that's all I needed to do (because I upgraded to a higher version).
I sometimes become so reliant upon PHPMyAdmin that I don't always know how to fix underlying issues. It looks like I'll be delving into MySQL commandline more often, just to understand MySQL at a base level before utilizing front-end tools, or at least reference PHPMyAdmin's SQL query statements a bit more (which is a very cool feature, as the front-end puts what its doing into commandline structure).
I'm betting some of you are wondering why I'm using PHPBB on my LAN. I use it as a note-taking tool. Whatever machine I'm on in my LAN, I can reference important notes or create notes that are in a central location. Even if I'm at work or at a coffee shop, I can tunnel into my LAN and view all my notes. The only thing I have to remember is to back up my databases religiously so I don't lose very important data that will hinder my work at home (and somewhat at my workplace). Yeah, I know that there may be better ways to take notes, but my notes sometimes tend to be lengthy and when I'm troubleshooting, it's easy to create a thread of trial-and-error posts so I can keep track of what I need to do, what I've already done, or what I shouldn't do. There aren't too many tools that can organize and store data like a CMS can. :)
Happy Holidays!!
