compare_tables issue after upgrade of database

Worker Joined: May 26, 2006 Posts: 153

I have updated my two sites to RN2.4.01 and on one of them have the following table errors reported by the compare_tables.php utility.

Field keys do not match:
nuke_bbsessions session_id : Base MUL Your table
nuke_bbsessions session_ip : Base MUL Your table

and

nuke_counter type : Base PRI Your table
nuke_counter var : Base PRI Your table

As it doesn't tell me what is in my tables its a bit difficult to work out what I need to change if anything.

The other few errors reported had more information and I could fix them in phpMyadmin.

Posted: Sun May 16, 2010 7:03 pm

Obviously replace nuke with your prefix if it is different.

ALTER TABLE `nuke_bbsessions` ADD KEY `session_ip` (`session_ip`);
ALTER TABLE `nuke_bbsessions` ADD KEY `session_id` (`session_id`);
ALTER TABLE `nuke_counter` ADD PRIMARY KEY (`type`(20),`var`(20));

Posted: Mon May 17, 2010 10:03 am

Quote:

it doesn't tell me what is in my tables

That's because nothing was in your tables. The message indicates that the field was missing -- at least until you apply Palbin's fix.

Posted: Tue May 18, 2010 2:58 pm

I have just checked my tables and they have the correct fields, the only thing I can see that might have triped up the compare tables utility is the following:

All the fields that have been picked up as faults have a setting of "latin_swedish_ci" under the Collation heading, should this be empty.

Also nuke_counter table has 'type' and 'var' as varchar(80)

Posted: Wed May 19, 2010 5:32 am

Just enter the three statements into phpmyadmin and your problems will be solved. It is not that the fields are missing is that they are not assinged as keys/indices.

Posted: Thu May 20, 2010 12:11 am

I have run the SQL up dates suggested above and still have the following errors reported. the nuke_counter update reported that the tables already existed.

You are comparing this data base against the structure of the database represented in fieldlist_240_01.txt
field keys do not match nuke_bbsessions session_id : Base MUL Your table
field keys do not match nuke_counter type : Base PRI Your table
field keys do not match nuke_counter var : Base PRI Your table

This is the actual bbsessions table from phpMyadmin:-

session_id char(32) latin1_swedish_ci No
session_user_id mediumint( Cool

No 0
session_start int(11) No 0
session_time int(11) No 0
session_ip char( Cool

latin1_swedish_ci No 0
session_page int(11) No 0
session_logged_in tinyint(1) No 0
session_admin tinyint(2) No 0

- Details...
Indexes: Documentation
Action Keyname Type Unique Packed Field Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No session_id 16 A
Edit Drop session_user_id BTREE No No session_user_id 0 A
Edit Drop session_id_ip_user_id BTREE No No session_id 16 A
session_ip 16 A
session_user_id 16 A
Edit Drop session_ip BTREE No No session_ip 4 A
Edit Drop session_id BTREE No No session_id 16 A
The indexes PRIMARY and session_id seem to be equal and one of them could possibly be removed.

nuke_counter tables look like this in phpMyadmin and report no index defined:-

Field Type Collation Attributes Null Default Extra Action
type varchar(80) latin1_swedish_ci No
var varchar(80) latin1_swedish_ci No
count int(10) UNSIGNED No 0

Posted: Fri May 21, 2010 3:32 pm

I have run the SQL above and then run the compare_tables.php and still get the following errors:

The database you are using for comparison is: ******_nuke
You are comparing this data base against the structure of the database represented in fieldlist_240_01.txt
field keys do not match nuke_counter type : Base PRI Your table
field keys do not match nuke_counter var : Base PRI Your table

If I check in phpMyadmin I have the following Indexes shown for nuke_counter table:

Indexes: Documentation
Action Keyname Type Unique Packed Field Cardinality Collation Null Comment
Edit Drop type BTREE No No type (20) 3 A
var (20) 21 A

Posted: Fri May 21, 2010 3:57 pm

You could try dropping the key and running the last line of the sql I posted.

Posted: Sat May 22, 2010 2:31 pm

Palbin wrote:

You could try dropping the key and running the last line of the sql I posted.

Have done that and I just get:

#1062 - Duplicate entry 'total-hits' for key 1

Looks like the exact entry I have on my other site and that doesn't generate the error.

If I look at the Modify Indexs I do have the following:

("PRIMARY" must be the name of and only of a primary key!)

As far as I can tell everything is working ok so unless anyone knows what else I can try, I'll probably leave it.

Posted: Thu May 27, 2010 3:05 pm

Palbin wrote:

You could try dropping the key and running the last line of the sql I posted.

Have just approached it from another direction. I have exported out the sql table information from the website that is ok and the same for the problem database table.
Then replaced the problem table with the correct table information and imported the sql back into the problem database after backing up the old nuke_counter table by renaming it. I have just run the compare_table.php and it reports no errors now.