Migration to another Server and Database Vendor

[travelfreak]

Hi,

i have to migrate my sugarcrm 5.2i installation to another server as well as from mssql to
mysql. If all went good i want to upgrade to 5.5.

For the first step i migrated the database to mysql. then i copied the htdocs\sugarcrm folder.
But i cant login. When i try with admin and correct pw the page just refreshes.
When i try admin with incorrect pw theres a message stating that. The config.php file from
sugar "should" be ok, as before i copied htdocs i did a clean install of sugar with mysql
and backed up that config file.

from the sugar log: [ERROR] MySQL error 1062: Duplicate entry '0' for key 'PRIMARY'

Something wrong with my migration scenario?

cheers,
Marcus

[Angel]

It would seem like it.

The error seems to be indicating that one or more tables has records with the same ID value or it is attempting to created records with the same ID value as that of a record that already exists in database.

It is going to take some visual inspection unless you can narrow it down to a specific table.

[travelfreak]

Hi,

any ideas which tables to like first? I wonder why i have no problems
with my ms sql 2005 Database now, all goes smoothly there. I export/import
the same set of schema and data.

cheers,
Marcus

[Angel]

Not sure.

If it is happening right at logon, chances are the problem is in the tracker or user_preferences table.

[travelfreak]

cool thanks, ill check that tomorrow, i mean later the day

cheers,
Marcus

[Angel]

FYI, I was poking around in the database and noticed that indeed the tracker table uses numeric ID values. The ID column for that table is setup as auto-increment.

You can safely purge that table. That'll be the fastest resolution I think.

[travelfreak]

Hi,

i purged to data of the table, but no success with login.

Error: [620][-none-][ERROR] MySQL error 1146: Table 'sugarcrm.tracker' doesn't exist

then i dropped the table and recreated it from backup with that sql statement:

CREATE TABLE `sugarcrm`.`tracker` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `monitor_id` varchar(36) NOT NULL,
  `user_id` varchar(36) DEFAULT NULL,
  `module_name` varchar(255) DEFAULT NULL,
  `item_id` varchar(36) DEFAULT NULL,
  `item_summary` varchar(255) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `action` varchar(255) DEFAULT NULL,
  `session_id` varchar(36) DEFAULT NULL,
  `visible` tinyint(4) DEFAULT '0',
  `deleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_tracker_iid` (`item_id`),
  KEY `idx_tracker_monitor_id` (`monitor_id`),
  KEY `idx_tracker_userid_itemid_vis` (`user_id`,`item_id`,`visible`),
  KEY `idx_tracker_userid_vis_id` (`user_id`,`visible`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=111914 DEFAULT CHARSET=utf8;

Error in sugarcrm.log is still the same: Error: [620][-none-][ERROR] MySQL error 1146: Table 'sugarcrm.tracker' doesn't exist

But for some reasons all the login tryouts are written to that table. id was incremented with every login with action authenticate and login.

Any other ideas? I think i have to migrate the table tracker with all the other tables again.
For the migration i used ESF Database Convert, as the mysql Migration Toolkit has problems to migrate about 20 Tables for various reasons (datetime ....)
ESF did a better job and migrated all tables "successfully".

thanks a lot,
Marcus

[travelfreak]

anyone idea with that ?
iam stuck

[travelfreak]

what i did was following:

migrated all tables except those with auto increment rows.
- bugs
- campaign_trkrs
- cases
- campaigns
- emailman
- prospects
- tracker

created those tables manually regarding to mysql commands from a previous backup from the
orig. empty database. all those tables are empty with auto increment number to 1.

The former errror: [ERROR] MySQL error 1062: Duplicate entry '0' for key 'PRIMARY' is gone now,
but i still cannot login. The login page just refreshes. No more error messages in sugarcrm.log file.

Any further ideas what i can troubleshoot or which logs to check ?

cheers,
Marcus