Sugar Email crashing MySQL

[psdavec]

OS: Windows Server 2003 for Small Business
Sugar Version: 5.0.0 (Build 3095)
Web Server: Apache 2.0
PHP: 5.2.5
MySQL: 5.0.45-community-nt

I was having issues with upgrading patches on 4.5.1 so did a fresh install of 5.0 and copied over the 4.5.1 db, cache and custom folders. I then had to manually move some data around to get email addresses etc to be seen. Now when I try to do anything with Email it will either crash MySQL, or if it does get somewhere I get an error like:

Query Failed:UPDATE outbound_email SET id = 'c18a0ca3-aa81-ced8-e1f6-477408a11acc', name = 'system', type = 'system', user_id = '1', mail_sendtype = '', mail_smtpserver = '', mail_smtpport = '', mail_smtpuser = '', mail_smtppass = '', mail_smtpauth_req = '', mail_smtpssl = '' WHERE id = 'c18a0ca3-aa81-ced8-e1f6-477408a11acc'::MySQL error 1366: Incorrect integer value: '' for column 'mail_smtpport' at row 1

I cannot send or receive any email from Sugar. I have been through each and every one of the 'repair' items. I also manually changed the outbound_email table to contain the correct settings (which do not appear in the above error). I'm assuming that the manual move of 4.5.1. to 5.0 does not work properly so can someone please let me know what else I need to do? Or if there's a script I can run to complete the process.
(BTW: the manual move was suggested by Sugar as was the "how")

[andydreisch]

Hi psdavec, this thread may be helpful.

Andy

[psdavec]

Hi Andy,
I was hopeful but no good I'm afraid, I still get the problem. One thing I noticed is that when I go to the email tab it takes a long time and crashes MySQL. I ran the MySQL log and got this:

080102 11:45:30080102 11:45:30 [ERROR] Cannot find table sugarce/address_book_list_items from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
See http://dev.mysql.com/doc/refman/5.0/...eshooting.html
how you can resolve the problem.

I checked the database using the MySQL tools and found that in the MySQL Query Browser I see both address_book_list and address_book_list_items (both say "Could Not Fetch Columns) but in the MySQL Administrator I do not see these tables in the Suagr DB.
I attempted to create the tables from an older schema but when it said that the table already existed, even though it doesn't. I checked the file system and I do have the FRM files for address_book, address_book_lists and address_book_list_items but there is no MYI or MYD files.

PLEASE HELP!!! We are without the ability to send emails and notifications

[psdavec]

Ok, I now believe this was due to the way the data was migrated from 4.5.1. to 5.0. I did a fresh install of 5.0 in a separate DB and all tables are InnoDB where as half of the table in my 5.0 version are MyISAM. So my question is how do i migrate the data correctly. There have been table changes so I need some sort of script to do this.
Can I just run the 4.5.1 to 5.0 patch on the 5.0. install?
Do you have the scripts to upgrade the DB from the 4.5.1 format to the 5.0 format?
Is there a way to convert MyISAM tables to InnoDB?

[psdavec]

Thanks for all the replies chaps
Anyway. I got this working by going through the database repair in MySQL Administrator. There were about 20 tables that required me to run the repair on them. Once this was done i could get into the email.