ASPECT #1

The first thing that caught my eye was this line

InnoDB: Error: trying to load index PRIMARY for table /

This indicates you have a table using the InnoDB Storage Engine

What is interesting about InnoDB is the way a PRIMARY KEY is stored. It is stored in a structure called the gen_clust_index, or more commonly known as the Clustered Index.

My immediate guess is that a certain PRIMARY KEY entry is too big

Please consider some articles on the good, the bad, and the ugly of using long PRIMARY KEYs:

• Long PRIMARY KEY for Innodb tables
• Optimizing Storage Layout for InnoDB tables
• Mysql int vs varchar as primary key (InnoDB Storage Engine?

then see if the <DB Hidden>.<Table Hidden> needs to be redesigned.

ASPECT #2

In terms of your conjecture concerning a parallel truncate table, that sounds kind of dangerous. Why? InnoDB performs TRUNCATE TABLE as DDL not DML. I have written about this before:

• Jul 09, 2012 : What can cause TRUNCATE TABLE to take a really long time?
• Jan 17, 2012 : Problem with InnoDB "per table" file sizes
• Sep 28, 2011 : How to Recover an InnoDB table whose files were moved around

ASPECT #3

Some tuning suggestions

Please add the following to my.ini

[mysqld]
max_allowed_packet=1G
innodb_fast_shutdown=0

Start mysql

In another session, run tail -f <errorlogfile> and watch InnoDB Crash Recovery.

If mysql is fully started back up and InnoDB crash recovery has completed, try to shut mysql down immediately. You may need to resize your InnoDB Transaction Logs.

Sorry for these wild suggestions, but I am flying blind here.

Please post the following in the question:

• your entire my.cnf
• how much RAM is on board

UPDATE 2012-12-05 12:09 EDT

Please do the following:

STEP 01) Add these changes to my.cnf

[mysqld]
max_allowed_packet=1G
innodb_fast_shutdown=0
innodb_thread_concurrency=0

STEP 02) service mysql restart

to make sure mysql comes up

STEP 03) You need to resize ib_logfile0 and ib_logfile1 (24M might be too small)

service mysql stop
cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak

STEP 04) Add these changes to my.cnf

[mysqld]
innodb_log_file_size=512M
innodb_log_buffer_size=8M

STEP 05) service mysql start

mysqld will recreate ib_logfile0 and ib_logfile1 512M each

Now, try and see what happens....

UPDATE 2012-12-05 12:18 EDT

In the meantime, please read my ServerFault post on the mysql packet and its sizing implication with regard to the innodb_log_file_size and innodb_log_buffer_size as I learned from someone else's ServerFault post.

UPDATE 2012-12-05 14:28 EDT

I edited all references to customer tables out of this question.

The root cause was a damaged page in ibdata1 with data and index pages mixed inside. I helped Andrew migrate data out, recreate ibdata1 with innodb_file_per_table, and Andrew reloaded the data.

Let's look at the way you created the user.

EXAMPLE : I have a database called zipcodes. I will create a user called rolando

mysql> grant all on zipcodes.* to rolando identified by 'blahblah';
Query OK, 0 rows affected (0.04 sec)

mysql> show grants for rolando;
+--------------------------------------------------------------------------------------------------------+
| Grants for rolando@%                                                                                   |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rolando'@'%' IDENTIFIED BY PASSWORD '*446525BB82B5E22BD9E525261D37C494F623C52B' |
| GRANT ALL PRIVILEGES ON `zipcodes`.* TO 'rolando'@'%'                                                  |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

I specified rolando without an IP or netblock. By defualt, '%' is used. That should allow remote IP address to login.

You said you tried to login to your account with

 mysql -u violetkiwi -p

Why did this not work? Simply put, you were not connecting remotely. You created the user violetkiwi@'%'. You should create the user violetkiwi@localhost. Then, it can cleanly connect.

You need to make sure your mysql authentication scheme has been cleaned up

MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints brings out how to cleanup the authentication process:

On Unix, MySQL comes with a mysql_secure_installation script that can perform several helpful security-related operations on your installation. The script has the following capabilities:

• Set a password for the root accounts
• Remove any remotely accessible root accounts.
• Remove the anonymous user accounts. This improves security because it prevents the possibility of anyone connecting to the MySQL server as root from a remote host. The results is that anyone who wants to connect as root must first be able to log in on the server host, which provides an additional barrier against attack.
• Remove the test database (If you remove the anonymous accounts, you might also want to remove the test database to which they have access).

A remotely accessible account is an account whose host is % which allows anyone and his grandmother. To find those accounts, run this query:

SELECT user,host FROM mysql.user WHERE host='%';

You should also be cognizant of how MySQL user authentication occurs.

According to MySQL 5.0 Certification Study Guide pages 486,487, the following describes mysql's authentication algorithm:

There are two stages of client access control:

In the first stage, a client attempts to connect and the server either accepts or rejects the connection. For the attempt to succeed, some entry in the user table must match the host from which the client connects, the username, and the password.

In the second stage (which occurs only if a client has already connected sucessfully), the server checks every query it receives from the client to see whether the client has sufficient privileges to execute it.

The server matches a client against entries in the grant tables based on the host from which the client connects and the user the client provides. However, it's possible for more than one record to match:

Host values in grant tables amy be specified as patterns contains wildcard values. If a grant table contains entris from myhost.example.com, %.example.com, %.com, and %, all of them match a client who connects from myhost.example.com.

Patterns are not allowed for the User values in grant table entries, but a username may be given as an empty string to specify an anonymous user. The empty string matches any username and thus effectively acts as a wildcard.

When the Host and the User values in more than one user table record match a client, the server must decide which one to use. It does this by sorting records with the most specific Host and User column values first, and choosing the matching record that occurs first in the sorted list, Sorting take place as follows:

In the Host Column, literal values such as localhost, 127.0.0.1, and myhost.example.com sort ahead of values such as %.example.com that have pattern characters in them. Pattern values are sorted according to how specific they are. For example, %.example.com is more specific than %.com, which is more specific than %.

In the User column, non-blank usernames sort ahead of blank usernames. That is, non-anonymous users sort ahead of anonymous users.

The server performs this sorting when it starts. It reads the grant tables into memory, sorts them, and uses the in-memory copies for access control.

SUMMARY

If you have any users that must have remote access, change the host of each user that has % into the public IP you will be connecting from.

For example, if the user myuser must connect from 208.21.42.184, replace the host like this

UPDATE mysql.user SET host='208.21.42.184' WHERE user='myuser' AND host='%';
FLUSH PRIVILEGES;

You should also be checking for the presence of any test databases. The presence of test databases. Any database named test or whose first five(5) characters are test_ need to be renamed immediately !!! I wrote an earlier post on how and why.