By zach harkey on
I am having a terrible time backing up/restoring/moving/upgrading my database. I have no problem dumping the database, but when I try to restore it to a new location, it just spins forever and ever and eventually just prints the commands without actually adding any tables.
I am getting the following error messages in phpmyadmin for my database"
Problems with indexes of table `node`
More than one INDEX key was created for column `status`Problems with indexes of table `term_node`
PRIMARY and INDEX keys should not both be set for column `tid
Could this be part of the problem? Has anyone else seen this? Does anyone have any ideas what I should do?
Thanks
-Zach
Comments
I don't know the solution to
I don't know the solution to this, but a work around might be the create the table structures from the standard Drupal installation and then populate the tables using your SQL dump?
hth
-K
--------
Quillem.com
Mysql Indexes
"Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first record and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, then this is at least 100 times faster than reading sequentially. Note that if you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks. ..." excerpted from http://dev.mysql.com/doc/mysql/en/mysql-indexes.html
Basically, indexes in mysql are an easy way for mysql to find something by keeping a record of common value appearances - just like an index in the back of a book. If you have two of the same indexes of a primary key (meaning you have a primary key and an index including your primary key field), mysql will give you this "notice" just meaning there is no need for it. I don't think this will interfere with regular operation; however, if it does, just delete the duplicate indexes (under "table structure" in phpmyadmin).
PRIMARY and INDEX keys should not both be set for column `tid
This means just what i said above; except, you're trying to create a primary key and an index exactly the same. This is a problem!!!!
More than one INDEX key was created for column...Solution?
I have this same problem. Did you ever figure out the solution?
Hmm...
Well, I'm seeing an index 'created_2' which is exactly the same as 'created' - neither of which is actually in the Drupal 4.7 MySQL creation script, and which looked... antiquated.
So I whacked 'em. No problems so far.
Then I noted that in the Drupal database creation, index 'status' is in lower caps but - perhaps because of upgrade code - the index was in all caps ('STATUS'). So I changed that. I've seen Caps issues with tables before, but never saw an Index give trouble because of it... still... better safe than sorry...
BTW, I added a closing tag for italics for everyone on this page. ;-)
KnowProSE.com
OpenDepth.com
I have the same problem here
I have the same problem here:
Problems with indexes of table `node`
PRIMARY and INDEX keys should not both be set for column `nid`
More than one INDEX key was created for column `status`
Problems with indexes of table `term_hierarchy`
PRIMARY and INDEX keys should not both be set for column `tid`
Problems with indexes of table `term_node`
PRIMARY and INDEX keys should not both be set for column `tid`
Problems with indexes of table `view_view`
UNIQUE and INDEX keys should not both be set for column `name`
I cannot find a solution among comments here. Can anybody help?
I have the same problem here
I have the same problem here:
Problems with indexes of table `node`
PRIMARY and INDEX keys should not both be set for column `nid`
More than one INDEX key was created for column `status`
Problems with indexes of table `term_hierarchy`
PRIMARY and INDEX keys should not both be set for column `tid`
Problems with indexes of table `term_node`
PRIMARY and INDEX keys should not both be set for column `tid`
Problems with indexes of table `view_view`
UNIQUE and INDEX keys should not both be set for column `name`
I cannot find a solution among comments here. Can anybody help?
What these messages mean
Sorry if I'm being overly technical...
These aren't errors, just warnings. What the error means, is that redundant indexes exist. The column nid and status are already covered by an index. If you have an index that covers multiple columns, and you perform a query with a criteria which filters on the _first_ column in that index, the DB engine will use that index to look up the result set.
Index Name: PRIMARY
Index Type: PRIMARY
Index Clumns: nid, vid
vs
The indexes in question:
Index Name: nid
Index Type: INDEX
Index Columns: nid
since 'nid' is the first column in the index named PRIMARY (which happens to be the Primary key), if you perform a search for a specific node (nid = 38229 ), it can use the Primary index to perform the search - even though you didn't specify a 'vid' value.
Having an index solely for the column 'nid' is redundant. Unnecessary indexes affect updating/deleting/inserting of rows into the table. Imagine having a phone book where, on each page, you have the range of the first 3 letters of the surnames on the page... then you had another index just below that with the same first 3 letters, plus the 3 letters of the range of first names on the page... the extra listing of the surname range isn't nessesary, and if you wanted to insert a new entry, you'd have to update both indexes every time rather than just the one.
Anyway, you can safely ignore these messages. It'd be a worthwhile project for a group to audit the state of Drupal's indexing. Efficient indexing is the #1 way to improve performance of a DB application. It's also the #1 way to bring a site to its knees if you do it wrong. That's why DBAs get paid so much.
I agree!
See my posting http://drupal.org/node/195745
The Drupal database must be audited and fixed ASAP at the very least to make sure indexes are integers,remove any redundant indexes and make all the booleans INT(1).
These changes would not even involve changing any code but would certainly improve performance.
thank you
skorch, thank you for your clear explanation!
Ongoing problem in Drupal 6
I have these problems on a number of Drupal sites that have been around since Drupal 4.6 days. Can someone explain this as a set of steps that I can follow in Phpmyadmin? Thanks!
Problems with indexes of table `actions_aid`
The following indexes appear to be equal and one of them should be removed: PRIMARY, aid
Problems with indexes of table `node`
The following indexes appear to be equal and one of them should be removed: PRIMARY, nid
The following indexes appear to be equal and one of them should be removed: vid_2, vid
Problems with indexes of table `view_view`
The following indexes appear to be equal and one of them should be removed: name, name_2