Sync-ing dbs

[bagpiper]

Hi all,

I've got a local SugarCRM and another one online. I'm using phpMyAdmin to export the local db data and then use it to import the data to the online db.

So after 1/2 day of scratching my head and searching for answers I find that this procedure doesn't work...I can see the items on the "last viewed" bar. But no entries in the "List" format for a tab (in this case I'm using SugarCRM for bug tracking).

This is the error I get (the application is truncating the error):

Error retrieving Bug list:  Query Failed:(  SELECT  meetings.id , meetings.name , meetings.status  , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) contact_name, '                                                                                                                                                                                                                                                              ' contact_name_owner , '                                                                                                                                                                                                                                                              ' contact_name_mod , meetings.date_start  , jt1.user_name assigned_user_name, meetings.time_start , meetings.assigned_user_id  , 'meetings' panel_name  FROM meetings   AND contacts.deleted=0  AND jt1.deleted=0 where ((meetings.status='Planned')) AND meetings.deleted=0 ) UNION ALL (  SELECT  tasks.id , tasks.name , tasks.status  , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) contact_name, tasks.date_due  as date_start  , jt1.user_name assigned_user_name, tasks.time_due  as time_start , tasks.assigned_user_id  , 'tasks' panel_name  FROM tasks   AND contacts.deleted=0  AND jt1.deleted=0 where ((tasks.status='Not Started' OR tasks.status='In Progress' OR tasks.status='Pending Input')) AND tasks.deleted=0 ) UNION ALL (  SELECT  calls.id , calls.name , calls.status  , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) contact_name, '                                                                                                                                                                                                                                                              ' contact_name_owner , '                                                                                                                                                                                                                                                              ' contact_name_mod , calls.date_start  , jt1.user_name assigned_user_name, calls.time_start , calls.assigned_user_id  , 'calls' panel_name  FROM calls   AND contacts.deleted=0  AND jt1.deleted=0 where ((calls.status='Planned')) AND calls.deleted=0 ) ORDER BY date_start  desc LIMIT 0,10::MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND contacts.deleted=0  AND jt1.deleted=0 where ((meetings.statu

Can you provide with some clues as to what to do ?

Your help is very well appreciated!

[sacramentojoe]

what method are you using to copy / sync the DB's?

phpMyAdmin has a "copy" function that usually works fine.

if all else fails, you can use mysqldump from source and then re-import on the destination DB.

[Angel]

In addition to Joe's comments, what version of MySQL are you using at each end?

[bagpiper]

Thanks,

Local MySQL is 5.0.41, Hosted MySQL is 4.1.20 (in a shared hosting environment).

I tried using the insert, update and replace methods of phpMyAdmin, but I always end up getting an empty list when browsing a tab. I do get however the "last viewed" information. Also tried the My SQL version export to 4.0 in case I was exporting data in version 5 format.

It would be great if I can get step by step instructions for using phpMyAdmin for synching...to summarize:

- I have a local installation of Sugar (the database has data)
- I made a remote installation of Sugar (the database has empty data)
- I need to copy the local database to the remote one

Thanks so much! I'm sure newcomers to SugarCRM will feel a sense of relief.

[bagpiper]

One more update...I'm using mysqldump to regenerate the tables at the remote server and I'm in the same situation...no listing capabilities. Any help is greatly appreciated...

[Angel]

Are you on a Windows based system? If so, take a look at using SQLyog (www.webyog.com). It has built in functionality that makes it very easy to copy tables from one database to another, including copying to remote servers.

[bagpiper]

Thanks Angel,

Yes indeed, I have a Windows system both locally and remote. I have not heard of SQLyog before

My testing now involved creating a new database locally and installing a new instance of Sugar. As it turns out, using the same mysqldump script I used on my previous test for the remote database works as expected.

So now I'm thinking may be there's something in the ftp transfer didn't work right...I see that when i log in to the remote server I get:

Fatal error: main(): Failed opening required 'cache/dashlets/dashlets.php' (include_path='.;c:\php\pear') in d:\inetpub\ecotronics\crm\modules\Home\index.php on line 36

Could this be the source of my headaches? When I click on a Last Viewed item, I get this error at the end of the page:

Error retrieving Bug list: Query Failed SELECT meetings.id , meetings.name , meetings.status , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) contact_name, ' ' contact_name_owner , ' ' contact_name_mod , meetings.date_start , jt1.user_name assigned_user_name, meetings.time_start , meetings.assigned_user_id , 'meetings' panel_name FROM meetings AND contacts.deleted=0 AND jt1.deleted=0 where ((meetings.status='Planned')) AND meetings.deleted=0 ) UNION ALL ( SELECT tasks.id , tasks.name , tasks.status , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) contact_name, tasks.date_due as date_start , jt1.user_name assigned_user_name, tasks.time_due as time_start , tasks.assigned_user_id , 'tasks' panel_name FROM tasks AND contacts.deleted=0 AND jt1.deleted=0 where ((tasks.status='Not Started' OR tasks.status='In Progress' OR tasks.status='Pending Input')) AND tasks.deleted=0 ) UNION ALL ( SELECT calls.id , calls.name , calls.status , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) contact_name, ' ' contact_name_owner , ' ' contact_name_mod , calls.date_start , jt1.user_name assigned_user_name, calls.time_start , calls.assigned_user_id , 'calls' panel_name FROM calls AND contacts.deleted=0 AND jt1.deleted=0 where ((calls.status='Planned')) AND calls.deleted=0 ) ORDER BY date_start desc LIMIT 0,10::MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND contacts.deleted=0 AND jt1.deleted=0 where ((meetings.statu

[bagpiper]

CASE CLOSED

After sweating for the last 2 days...I'm happy to announce I solved the problem.

The issue was that when I transfered the files (used FileZilla) apparently not all of the files got transferred.
The luck was such that the files made the system work OK but it wasn't so.

So thank you all for your help and tips. I'm now a new happy user of SugarCRM.

CASE CLOSED