Ugrade report/issues

[lcdweb]

I've been lagging in reporting (and actually testing) the upgrade process. But here's what I've come up with, and one error message I've not been able to figure out yet--

I ran an upgrade on one site last week and had problems in two areas:
1) Missing values (NULL) in the "name" field for a few records in civicrm_custom_field. Manually entering in values corrected the problem.
2) Problems with foreign key values throughout the CiviMail tables. To be more accurate, I think there were two table in which there were problems. But the impacted records were all test records, so rather than try to resolve the data issues, I simply dropped the test record rows (had to cycle back through eight tables in order to get all the related records). If I was to guess at the problem, I would say something's not working quite right with the delete function in CiviMail.

I ran an upgrade on a second site today. Two problems:
1) Same missing value problem for the name field in civicrm_custom_field. I had six records missing the value (string and memo fields). Given that it was a repeated error on a completely separate set of data, it would seem there's something wrong with the custom field creation functions. I haven't checked Jira to see if there is a recorded problem in 1.9 that was fixed in 2.0
2) Second problem is nastier. I get a DB syntax error on step 5 throwing the following error:

Code: [Select]

[nativecode=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 '(
        `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Default MySQL prima' at line 1]
The problem is with MySQL versioning (I'm running 5.0.41 on this test server). Did some digging online, and there were actually a couple CiviCRM posts and Jira issues that showed up, including a recent one impacting 2.0 (http://issues.civicrm.org/jira/browse/CRM-2604, http://issues.civicrm.org/jira/browse/CRM-2818). Neither are identical to the error I received (and obviously mine happened during the upgrade process; these others are with operating functionality). Maybe there are some slight syntax differences within MySQL 5.x that the core team has used?

I'm going to sort through my custom data tables and see if I come up with anything that might be causing this.
-Brian

[lcdweb]

Ok, that second issue in the second data set was also related to missing data -- a few records in civicrm_custom_group were also missing values in the "name" field.

I couldn't find an existing issue in Jira for this. It happened on two separate 1.9 sites, so I don't think it's a fluke. But am curious if anyone else noticed the missing data and had issues with the upgrade process.

[lcdweb]

One more side note --

I see that v2.0 handles custom fields/groups by creating a new table associated with the group, rather than handling it within the four previous existing tables (faster, less query joins). Makes sense. Not a big issue, but those dynamically generated tables don't begin with the civicrm_ prefix as with all other tables. Might be good to change that at some point for the sake of consistency.

-Brian

[Donald Lobo]

hey brian:

1. all new custom tables are created with the prefix: civicrm_value_DOMAINID_xxxxxxxxxxx

2. with regard to missing name, yes we do suspect this is an issue in prior version. Not sure of a good solution, but we figured getting a user to manually add the name and fix their existing db is not a bad option. Kinda hard to go back and figure out when/where this bug was introduced and potential fixes?

3. can u give more details on problems with test records in civimail and FK values?

thanx

lobo

[lcdweb]

hey Lobo,

1) On the test upgrade I did today with beta 5, the tables built were prefixed with: custom_value_1_etc. Check CRM/Upgrade/TwoZero/Form/Step5.php line 81.

2) Fixing it by hand for the purpose of the upgrade process definitely makes sense. I was more concerned that moving forward, whatever caused the data to not be stored properly be fixed in 2.0. Given the restructuring of the custom field storage, it's probably a mute point anyway.

3) Will do. I had planned to rerun the test upgrade on that site again. I'll grab the errors and let you know.

-B

[Donald Lobo]

1. fixed. Would have been nice to have caught and fixed this in our extended 4 alpha / 4 beta releases

http://issues.civicrm.org/jira/browse/CRM-2838

will be part of 2.0.1

lobo