A couple months ago, I experienced a number of errors at my Drupal site that all pointed to a corrupted database. I believe the problem likely was the caused by a sloppy export/import I performed with the MySQL database while moving the site to a new server.
While my particular database problem was an easy fix, if you really don't know much about databases you may find that you really need some guidance on how to solve problems like these. This article is the process I went through to correct this particular MySQL database problem in Drupal 5 and some general database rules that show why this problem occurred in the first place.
The database problems first showed its ugly head after the majority of new comments submitted to the site were not being saved. Oddly, I also had a similar problem when trying to attach image files into the Drupal site. In my Drupal 5 site, the I found the following excerpts showing a problem related to the comments table in the Drupal MySQL database.
Duplicate entry '62' for key 1 query: INSERT INTO comments (cid, nid, pid, uid, subject, comment, format, hostname, timestamp, status, score, users, thread, name, mail, homepage) VALUES (62, 1169, 2273, 0,..
Now, you may not look at these logs often because you don't understand everything you read, but the logs are really the first and easiest step you should take in solving the problem.
What does the duplicate error tell me?
Drupal has started giving newly saved comments the same value for the primary key as a previously stored comment. In a database table no row in the table can have a primary key (also called a unique key) of the same value as in another row. If what I just said is confusing to you, there is another way to look at it.
Think of a primary key as a seat on plane. When you board a plane you are given a specific seat on that plane that should not be given to anyone else that boards the plane. However, if someone else comes by to claim that same seat you are sitting in, then due to flight regulations a problem just developed because no two people can be assigned the same seat. This is the duplicate error problem you are seeing in your logs.
How do I fix this duplicate error?
In my particular case, the cid field (the primary key) in the comments table has already been used previously by Drupal. Using the database manager provided by my hosting company (phpMyAdmin) to browse the comments table confirmed that this was indeed the problem. On one of the last rows entered in the database table contained a row that was corrupted (the value for the cid was 1 and what was included in the nid was the value that should have been placed in the cid field).
You can and should use the edit functions in your database manager to correct or delete any of the rows in the comments table that appear to be a problem for your database. However, what you will likely find is that even after you've corrected your comments table...new comments still aren't being saved in the database. Why is that? Drupal (at least in versions 4.6, 4.7, and 5.x) utilizes a sequence table to help it identify what the value for the next comment should be.
Yes, I know there is nothing in the error logs that would indicate that it is the sequence table and not the comments table that needs to be fixed. To be honest, before I had this problem I really didn't know what the sequence table in Drupal actually did! References to the sequence table in the Drupal Handbooks are rare, but luckily the information is there. In an almost orphaned page at drupal.org I found the following process to fixing a similar duplicate entry problem for nodes.
- Access your database. Check the affected table. In this case, the error tells us that it is the node table (query: INSERT INTO node). This also happens with other tables, modify these instructions accordingly.
- This is the node table, so the problem is the node id (nid). Each node has a unique id. Look at your table, and find the highest node id (nid). If you have many nodes, it may help to sort your table by nid to find the highest one.
- Go to your sequences table. Change the node id in the sequences table to a number higher than the id you found in step 2.
So in my particular case using the three steps, replace node with comment and we're golden. In the sequences table you'll also find field not just for node or comment, but also files (which fixed my images problem), user, menu, etc.
According to the handbook "99% of the time, the solution is in the sequence table, but this error will come up ANY time you try to insert duplicate data into a column where every value has to be unique." That should make sense to you in what we already learned about primary keys and airplane seats. So hopefully, all this information should help you possibly fix hopefully your first and last issues in the Drupal database.