MySql

Repairing WordPress MySql Table corruption

We suffered a SAN outage which caused one of our wordpress servers to come back up with a corrupted table.

 

In the wordpress logs this presented as:

[error][client IP] Request exceeded the limit of 10 internal redirects due to probable configuration error. Use 'LimitInternalRecursion' to increase the limit if necessary. Use 'LogLevel debug' to get a backtrace., referer: http://example.com/

Not exactly clear error message.  Digging into the mysqld log was more illustrative of the problem:

[ERROR] /usr/libexec/mysqld: Table './db/wp_options' is marked as crashed and should be repaired

(The database name was changed to protect the innocent).

With the above error, I now understood the problem and how to approach fixing it.

# mysql -u root -p
Enter password:
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> connect db;

Connection id: 2244
Current database: db

mysql> select * from wp_options limit 1;
ERROR 145 (HY000): Table ‘./db/wp_options’ is marked as crashed and should be repaired
mysql> repair table wp_options;
+—————————–+——–+———-+——————————————————-+
| Table | Op | Msg_type | Msg_text |
+—————————–+——–+———-+——————————————————-+
| db.wp_options | repair | info | Wrong block with wrong total length starting at 52540 |
| db.wp_options | repair | warning | Number of rows changed from 314 to 313 |
| db.wp_options | repair | status | OK |
+—————————–+——–+———-+——————————————————-+
3 rows in set (0.05 sec)

mysql> select * from wp_options limit 1;
+———–+————-+————————–+———-+
| option_id | option_name | option_value | autoload |
+———–+————-+————————–+———-+
| 1 | siteurl | https://example.com | yes |
+———–+————-+————————–+———-+
1 row in set (0.01 sec)

mysql> exit
Bye

And that quickly the site was back up and functional, no restart required!

Hope that helps someone or me in the future.

 

 

 

 

MySql, phpMyAdmin user creation and grant errors

Recently I got a report from a user that they could not create a database using phpMyAdmin.  When I logged in and did some testing I began getting errors when I attempted to create a user, a database or grant privileges as root…

The root cause was a misalignment of the grants due to a missed step during a patch cycle (as we haven’t manually upgraded).  Here is more about it and how I fixed the problem:

First doing some investigation…

# mysql -uroot -p
Server version: 5.5.52 MySQL Community Server (GPL) by Remi

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.5.52 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT column_name,ordinal_position FROM information_schema.columns WHERE table_schema='mysql' and table_name='user' and column_name='plugin';
Empty set (0.00 sec)

mysql> SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user';
+--------------+
| column_count |
+--------------+
| 39 |
+--------------+
1 row in set (0.00 sec)

That last query should have the following numbers:

  • If you get 43, MySQL 5.6
  • If you get 42, MySQL 5.5
  • If you get 39, MySQL 5.1
  • If you get 37, MySQL 5.0

As you can see from the above output the numbers are not correct, as we are using version 5.5 and only had 39 column’s returned, instead of the expected 42.  This means that the upgrade wasn’t completed.  We can fix this with the following:

# mysql_upgrade -u root -p --upgrade-system-tables
Looking for 'mysql' as: mysql
The --upgrade-system-tables option was used, databases won't be touched.
Running 'mysql_fix_privilege_tables'...
OK

Rerunning the previous mysql queries we can see the changes:

# mysql -uroot -p
Server version: 5.5.52 MySQL Community Server (GPL) by Remi

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.5.52 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT column_name,ordinal_position FROM information_schema.columns WHERE table_schema='mysql' and table_name='user' and column_name='plugin';
+-------------+------------------+
| column_name | ordinal_position |
+-------------+------------------+
| plugin | 41 |
+-------------+------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user';
+--------------+
| column_count |
+--------------+
| 42 |
+--------------+
1 row in set (0.00 sec)

Now I can go back into the phpMyAdmin interface and everything is working again.  Problem Solved.

Hope this helps, if it does say hello.

My MySQL Cheat Sheet

I know, man.  No, I mean I know I could use ‘man pages’!  Or I could just ‘google it’ but then it isn’t mine.  Since I do not have time for a complete brain-dump this MySql “cheat sheet” will grow over time.  Feel free to add your favorite MySql commands in the comments, if their really useful I’ll add them to the list!

If you don’t know what MySql is…look it up!  And, who are you?!  Seriously…

Create a DB & Assign to a User:

Create a New DB, Create a User and Grant them permissions to the New DB.

mysql> create database someDB_name;
Query OK, 1 row affected (0.13 sec)

mysql> create user 'someUser_name'@'localhost' IDENTIFIED BY 'some_password';
Query OK, 0 rows affected (0.13 sec)

mysql> GRANT ALL PRIVILEGES ON someDB_name.* to someUser_name@localhost;
Query OK, 0 rows affected (0.05 sec)

The above should be pretty self explanatory but for thoroughness sake…  The first line creates an empty database.  At that point only the root or admin user can use this database.  The Second command, creates a user account and assigns it a password.  This user account has NO privileges at this point.  The Third line is the most important.  When you grant permissions you can grant global permissions *.* meaning you can access ALL databases (not a good idea).  OR you can set Database permissions like I did above; database_name.*.  That .* after the database name means you have full privileges to that database only.  OR you can refine the permissions even further and grant permissions to a specific table in the database: database_name.some_table. Hope that clarifies things.  To state it in a more succinct way use this framework:

 GRANT [type of permission] ON [database_name].[table_name] TO ‘[username]’@'localhost’;

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

FLUSH PRIVILEGES;

Your changes will now be in effect.  I always like to test the account out before giving the account to the user.  To test out your new user, log out and log back in as the user:

mysql> quit 
mysql -u [username]-p

Revoke User Access or Delete a whole DB:

If you need to revoke a permission, the structure is almost identical to granting it:

 REVOKE [type of permission] ON [database name].[table name] TO ‘[username]’@‘localhost’;

You delete databases with DROP, you can also use DROP to delete a user altogether:

 DROP USER ‘demo’@‘localhost’;

 Recover Access when you have forgotten the root password:

Not that, that ever happens…

mysqld_safe --skip-grant-tables
mysql --user=root mysql

    update user set Password=PASSWORD('new-password') where user='root';
    flush privileges;
    exit;

That’s it for now.  More to follow…