Frustrated - data is in DB but not displaying in Sugar

[davidhperry]

I was running version 4.0.1a and the Upgrade Wizard wasn't working so I did a fresh install of 4.5.1f. i exported the data from the old DB and imported into it into the new DB via phpmyadmin. When I login to Sugar, I don't see any of the data at all (no accounts, leads, contracts, etc). I checked the database tables and everything is there.

Any idea what I can do to make it show up?? Thanks.

[kbrill]

You get no data and no errors? Try turning your logging level up to DEBUG in log4php.properties and see if there are any messages logged in sugarcrm.log.

[davidhperry]

I enabled logging - The "log4php.properties" file now reads:

log4php.appender.A2=LoggerAppenderRollingFile
log4php.appender.A2.MaxFileSize=10MB
log4php.appender.A2.MaxBackupIndex=5
log4php.appender.A2.layout=LoggerLayoutTTCC
log4php.appender.A2.layout.ContextPrinting="true"
log4php.appender.A2.layout.DateFormat="%c"
log4php.appender.A2.File=./sugarcrm.log

log4php.rootLogger=fatal, A2
//log4php.rootLogger=error, A2
//log4php.rootLogger=warn, A2
//log4php.rootLogger=info, A2
log4php.rootLogger=debug, A2

Here is the output from the log file:

Fri Dec 22 21:51:54 2006,265 [1754] DEBUG SugarCRM - --------->  BEGING INCLUDING REQUESTED PAGE: [modules/Opportunities/index.php]  <------------
Fri Dec 22 21:51:54 2006,315 [1754] DEBUG SugarCRM - get_user_array query: SELECT id, first_name, last_name, user_name from users WHERE status='Active' AND is_group=0  ORDER BY user_name ASC
Fri Dec 22 21:51:54 2006,316 [1754] INFO SugarCRM - Query:SELECT id, first_name, last_name, user_name from users WHERE status='Active' AND is_group=0  ORDER BY user_name ASC
Fri Dec 22 21:51:54 2006,317 [1754] INFO SugarCRM - Query Execution Time:0.00090200000000001
Fri Dec 22 21:51:54 2006,333 [1754] DEBUG SugarCRM - Loading Preferences DB admin
Fri Dec 22 21:51:54 2006,333 [1754] INFO SugarCRM - Query:SELECT contents FROM user_preferences WHERE assigned_user_id='1' AND category = 'Opportunities2_OPPORTUNITY' AND deleted = 0
Fri Dec 22 21:51:54 2006,334 [1754] INFO SugarCRM - Query Execution Time:0.00086399999999998
Fri Dec 22 21:51:54 2006,336 [1754] DEBUG SugarCRM - SugarBean.load_relationships, Loading relationship (accounts).
Fri Dec 22 21:51:54 2006,345 [1754] DEBUG SugarCRM - Link Constructor, relationship name: accounts_opportunities
Fri Dec 22 21:51:54 2006,346 [1754] DEBUG SugarCRM - Link Constructor, Table name: 
Fri Dec 22 21:51:54 2006,346 [1754] DEBUG SugarCRM - Link Constructor, Key name: 
Fri Dec 22 21:51:54 2006,362 [1754] DEBUG SugarCRM - Link Constructor, _bean_table_name: opportunities
Fri Dec 22 21:51:54 2006,362 [1754] DEBUG SugarCRM - Link Constructor, _bean_key_name: id
Fri Dec 22 21:51:54 2006,363 [1754] DEBUG SugarCRM - Link Constructor, relationship record found.
Fri Dec 22 21:51:54 2006,363 [1754] DEBUG SugarCRM - SugarBean.load_relationships, Loading relationship (assigned_user_link).
Fri Dec 22 21:51:54 2006,364 [1754] DEBUG SugarCRM - Link Constructor, relationship name: opportunities_assigned_user
Fri Dec 22 21:51:54 2006,364 [1754] DEBUG SugarCRM - Link Constructor, Table name: 
Fri Dec 22 21:51:54 2006,364 [1754] DEBUG SugarCRM - Link Constructor, Key name: 
Fri Dec 22 21:51:54 2006,364 [1754] DEBUG SugarCRM - Link Constructor, _bean_table_name: opportunities
Fri Dec 22 21:51:54 2006,364 [1754] DEBUG SugarCRM - Link Constructor, _bean_key_name: assigned_user_id
Fri Dec 22 21:51:54 2006,365 [1754] DEBUG SugarCRM - Link Constructor, relationship record found.
Fri Dec 22 21:51:54 2006,366 [1754] DEBUG SugarCRM - Limit Query: SELECT  opportunities.id , opportunities.name  , accounts.name account_name, jtl0.account_id account_id, opportunities.sales_stage , opportunities.amount_usdollar , opportunities.date_closed  , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod, opportunities.assigned_user_id  FROM opportunities   LEFT JOIN  accounts_opportunities jtl0 ON opportunities.id=jtl0.opportunity_id  AND jtl0.deleted=0
 LEFT JOIN  accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0
 AND accounts.deleted=0  LEFT JOIN  users jt1 ON
jt1.id= opportunities.assigned_user_id AND jt1.deleted=0
 AND jt1.deleted=0 where opportunities.deleted=0 Start: 0 count: 21
Fri Dec 22 21:51:54 2006,366 [1754] INFO SugarCRM - Query: SELECT  opportunities.id , opportunities.name  , accounts.name account_name, jtl0.account_id account_id, opportunities.sales_stage , opportunities.amount_usdollar , opportunities.date_closed  , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod, opportunities.assigned_user_id  FROM opportunities   LEFT JOIN  accounts_opportunities jtl0 ON opportunities.id=jtl0.opportunity_id  AND jtl0.deleted=0
 LEFT JOIN  accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0
 AND accounts.deleted=0  LEFT JOIN  users jt1 ON
jt1.id= opportunities.assigned_user_id AND jt1.deleted=0
 AND jt1.deleted=0 where opportunities.deleted=0 LIMIT 0,21
Fri Dec 22 21:51:54 2006,368 [1754] INFO SugarCRM - Query Execution Time:0.001383
Fri Dec 22 21:51:54 2006,401 [1754] INFO SugarCRM - Query:SELECT id, name FROM saved_search 
                  WHERE 
                    deleted = '0' AND
                    assigned_user_id = '1' AND 
                    search_module =  'Opportunities'
                  ORDER BY name
Fri Dec 22 21:51:54 2006,402 [1754] INFO SugarCRM - Query Execution Time:0.00092300000000001
Fri Dec 22 21:51:54 2006,406 [1754] DEBUG SugarCRM - --------->  END INCLUDING REQUESTED PAGE: [modules/Opportunities/index.php]  <------------

[kbrill]

Start: 0 count: 21

than shows 21 records returned. but none are displayed? You really have me with this one. No records in any module? What if you add a record?

[chrisky]

I was running version 4.0.1a and the Upgrade Wizard wasn't working so I did a fresh install of 4.5.1f. i exported the data from the old DB and imported into it into the new DB via phpmyadmin. When I login to Sugar, I don't see any of the data at all (no accounts, leads, contracts, etc). I checked the database tables and everything is there.

Any idea what I can do to make it show up?? Thanks.

I know what your issue is,and will do my best to provide you with a solution in this quick post in which I haven't lotsa speare time to go on and on.

First, let me give ya a heads up. Depending on the extent of your customization on the old 401a deployment (ie. custom fields, addon modules, corresponding relationships, etc.), you could have a bit of manual work to do in order to migrate all your data.

And don't forget about associated files as well..
You'll have to move (copy) those files to the new deployment.

You'll want to scrap your new 451 database, and replace it with a standard/stock 451 database from a clean install.
There are a dozen ways you accomplish that, your simplest method would probably be to just edit Sugar's config and change the name of the database to something different (assuming you have a clean 451 db you've previously backed up or can duplicate). Or you can just unlock the installer and rerun Sugar's installer.

You see, it'd take you more time to manually correct and/or identify the issues in which now exist with the db..
It's wiser to just start from the beginning.

As you've found out, you can not just use a sugar database with a different sugarcrm deployment and/or version, it' doesn't work, for a number of reasons. And should some think that it is a smooth idea, don't say I didn't tell ya so..
Even if all goes a-ok and appears to be 'good to go'; it is very likely that problems may crop up in time, specifically related to Sugar's various administrative & maintenance routines.

I can't emphasize this warning enough, Sugar all on its own will drop (delete) tables and ALL therein contained data without any notice, warning, or confirmation! I'd gotten a few doses of that this prior year and didn't find it too comical myself, though no data loss as a result of my databackup policies it was nothing more than an inconvience; an unwelcomed one at that. (This would take place down the road, another day, week, or month, and thus blind siding ya. Most would be pulling their hair out wondering where the heck it all went and why.)

Continuing on, there are some tables, such as the config table and versions table which contain [settings and/or] data in which is specific to that sugar deployment. If you peruse the versions table, you'll get to see a very simple first hand example of these concepts (i.e. the version of that sugarcrm deployment).

Anyways, jumping to what to do..
Start again from a clean and functioning 451 db,
then start with just one or a couple tables, and export your data/records ONLY,
proceed to import those records [from a single table only] into your new 451 database..
(i.e. leads, contacts, etc.)
Check Sugar to verify that the records are there, and accessible via Sugar.
(they'll be their)

Then, what I suggest is that you dujmp the whole 401 database to a text file, of which you'll be manually editing here shortly.. This time around, you'll probably want to dump it as an 'insert' type import and no table dropping, no replacing, and not even any updating.. You can do otherwise later, but for the time and to avoid any potentional extra troubles, you want to try and keep it simple.

Once, you have successfully migrated the records/data from a single table to the new deployment, you can then attack more...
If you dumped the db to a text file, you'll find it much wiser to just edit the text file and remove those tables and/or records in which you do not want imported into the new 451 db.

So simply put, you just want to xfr actual records(data) ONLY, nothing more..
And if you're lucky, you don't have any great amount of customization(s) in which complicate things.. (my fingers are crossed for ya).

I woulldn't be surprised if your 401 database export was configured to drop tables, thus replace the 451 database tables with 401 database tables which is probably the case and a good chunk of your troubles.. Sugar (451) unable to find many of its tables,r columns, and/or whatever else [sugar] is essentially just having a heart attack at the lack thereof.

Wow, I spent the past 33 mins typing this, thus I really hope it helps you..
And Happy New Year btw!

[davidhperry]

Wow - thank you, thank you, thank you! I followed your instructions and everything worked out *perfectly!* I now have the latest version of the system installed and all of my original data has been imported. Now than you mention it, I can see how using the old table structure would have caused problems with newer versions of the code.

Thank you so much for taking the time to spell this out for a noob like me. I know it will help many others as well.

I can't emphasize this warning enough, Sugar all on its own will drop (delete) tables and ALL therein contained data without any notice, warning, or confirmation! I'd gotten a few doses of that this prior year and didn't find it too comical myself, though no data loss as a result of my data backup policies it was nothing more than an inconvenience; an unwelcome one at that. (This would take place down the road, another day, week, or month, and thus blind siding ya. Most would be pulling their hair out wondering where the heck it all went and why.)

Wow - that's scary. Are you saying that Sugar does this on its own or is it more likely to happen with systems that have been modified? Either way, I will be backing up on a monthly basis to try to avoid much data loss.

Thanks again and have a great new year!

[kpit]

SugarCRM does not drop tables except when installing the first time. Upgrades will alter tables but not drop them.

I Think the main point chrisky is making was that backups are essential before doing any changes you think could cause problems like upgrading and installing new modules. Always back up before you make any changes to the program or data.

In general you should back up as often as you can afford to loose data. For example if you can afford to loose a months worth of changes to your data back up monthly. My system backs up dailly because I would hate to do more than one days worth of entry work for my self. Not to mention all the other people in the system.

[dtokeefe]

Regardless of the chance that Sugar might drop a table or column of data, your backup routine should be much more frequent than monthly. Every 3 hours is a good idea, maybe every night if the system is not used much. You should also stored you db backups on a different server, CD/DVD, or offsite via FTP. This protects against physical threats to your server, like fire, floods and theft.

It's also a good idea to verify the integrity of your backups periodically, say weekly. See if you can successfully restore the database from a backup. Nothing is more frustrating than a backup that doesn't work.

Cheers.

David

[davidhperry]

Max and David,

Thanks for the pointers. I think we'll be updating fairly frequently so that we don't have to deal with any data loss. That would especially suck since I spent so much time getting this system up and going. I'd certainly hate to lose all that effort at this point.

Speaking of restoring backups, which directory are the document files stored in? That's the last thing I need to move over from the previous version. I poked around in the /modules directory but I couldn't fine where they are located.

[davidhperry]

Never mind on the last question. I found the documents in /cache/upload