CiviCRM Community Forums (archive)

Hey Guys, we're doing a pretty massive dbase installation with many "individual custom data fields date fields" however we seem to be bumping into some sort of mySQL limit where each additional field throws an error that is of the type "Sorry. A non-recoverable error has occurred.
DB Error: unknown error" and you can't click to see the details. When clicking view page source, we see things along the lines of "Database Error Code: Too many keys specified; max 64 keys allowed, 1069" it seems this occurs even more when we use the "Save and New" key. Overall, there's probably 30 or so custom data date fields. Weirder still, even tho it throws the error, it actually places the field in the data set visible on the front end at /civicrm/admin/custom/group?reset=1 -- anyone have any clue what's up? at this point we're wondering if there;s some general weirdness with save and new function or if we somehow corrupted the install? Any help greatly appreciated!

You might want to rethink your custom groups and probably create multiple custom groups with fields split across the various custom groups.

lobo

we'll try creating a new custom data field set as you advised here and report the results back ...H

Might want to give more details on what u tried.

How big does the custom group get before it starts failing?

lobo

this helps...thanks Peter...we're going to try breaking them out into separate custom data sets...an added complication is that it isn't letting us delete the fields as we go, deleting simply throws errors of the non-recoverable variety...so it's a bit of a loop. you create the date fields, hit a certain mysql limit, get an error messge pertaining to the keys, go to delete, then get the same error. thanks for the help we're working on it...H

hmmm. Actually, unless we disable entirely the troubled set of existing "members" custom data fields, civi stops functioning, that is doing a routine search, then clicking any civi record will throw the error,  most recently we get things like unknown column and col exceeded limit...so we're not sure how to add or move the fields into a different custom grouping without deleting them as for each field it says its already in the database (even if we delete) and we can't add it -- there's probably an easy way to move custom data fields between groups as Lobo suggests, above, but we're not seeing it. Are we missing something obvious?

Your best bet would be to do into the DB and delete the database entries and values for the offending table.

I suspect once a table is broken, we cant really recover from the broken state

lobo

I suspect once a table is broken, we cant really recover from the broken state

That's right. I have had this problem--where a field create attempt fails. The field record may be created in civicrm_custom_field but the actual field wasn't made in the custom table. In such cases, or similar cases, manual intervention is the only way.

Hardwick, just to mention, had you created new fields in a new Custom Data Group, you could assign to those an existing option list without having to recreate that list.