Wednesday, May 21, 2008

Fixing mint tables after a 32bit to 64bit migration

I just migrated a web site from a regular 32bit processor (Intel(R) Xeon(TM) CPU 2.40GHz) to a 64bit slice (Dual Core AMD Opteron(tm) Processor 265). This made mint v2.14 (or actually the underlying MySQL database) start showing the visitor IPs as mostly 127.255.255.255. A little searching got me to here and here and here. I don't run PhpMyAdmin so here are the raw SQL command for you to run on your console:

alter table mint_visit modify ip_long int(10) unsigned;
alter table mint_visit modify referer_checksum int(10) unsigned;
alter table mint_visit modify domain_checksum int(10) unsigned;
alter table mint_visit modify resource_checksum int(10) unsigned;
alter table mint_visit modify session_checksum int(10) unsigned;


After running these a show columns from mint_visit should return the following:

+--------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| dt | int(10) unsigned | NO | MUL | 0 | |
| referer | varchar(255) | NO | | NULL | |
| referer_checksum | int(10) unsigned | YES | MUL | NULL | |
| domain_checksum | int(10) unsigned | YES | MUL | NULL | |
| referer_is_local | tinyint(1) | NO | MUL | -1 | |
| resource | varchar(255) | NO | | NULL | |
| resource_checksum | int(10) unsigned | YES | MUL | NULL | |
| resource_title | varchar(255) | NO | | NULL | |
| search_terms | varchar(255) | NO | | NULL | |
| img_search_found | tinyint(1) unsigned | NO | MUL | 0 | |
| browser_family | varchar(255) | NO | | NULL | |
| browser_version | varchar(15) | NO | | NULL | |
| platform | varchar(255) | NO | | NULL | |
| resolution | varchar(13) | NO | | NULL | |
| flash_version | tinyint(2) | NO | MUL | NULL | |
| local_search_terms | varchar(255) | NO | | NULL | |
| local_search_found | tinyint(1) unsigned | NO | MUL | 0 | |
| window_width | smallint(5) | NO | MUL | -1 | |
| window_height | smallint(5) | NO | MUL | -1 | |
| ip_long | int(10) unsigned | YES | MUL | NULL | |
| session_checksum | int(10) unsigned | YES | MUL | NULL | |
| visitor_name | varchar(255) | NO | | NULL | |
+--------------------+---------------------+------+-----+---------+----------------+
23 rows in set (0.00 sec)


The sooner you fix your database the less info you'll lose. Took me a couple of hours to realize this was happening...