civicrm_membership_type producing DB Error

[dudeval]

This occurs after I hit 'Confirm' on a paid membership registration page, instead of redirecting to the Thank you page, I get a 'DB Error: syntax error'. It appears to process the payment and the membership correctly. It looks like the error is around civicrm_membership_type, but I can't figure out what it means.
The Error is pasted here: http://pastebin.com/wQAVkDta 
the BackTrace is pasted here: http://pastebin.com/dkw8tG0s

I re-installed the version I was on 4.5.4, and it gave me the same error. I then upgraded to 4.5.6 to see if somehow there was a bug that had been fixed, and I still get the same error. Not sure where to go from here, I've used up my bag of tricks (which isn't very big admittedly)

[joanne]

I am not a techie, so can't address the error message, but perhaps the following may help you in some way.

Do you only have one membership type?

If yes, can you create another one with a very simple name ( eg TEST) and see if you get the same error.

If no, does it happen for all membership types or only some?

[CiviTeacher.com]

Please post screenshots of the a) Membership tab of your Contribution page configuration and b) the MembershipTypes screen in Administer.

[dudeval]

I am not a techie, so can't address the error message, but perhaps the following may help you in some way.

Do you only have one membership type?

If yes, can you create another one with a very simple name ( eg TEST) and see if you get the same error.

If no, does it happen for all membership types or only some?

I have multiple membership types, and it seems to produce the same error regardless of the one I use. Is there a limit of membership types one can use? I created a number of different types to correspond with the way membership dues are distributed in our accounting. The screen shots are below

[CiviTeacher.com]

There's no limit to the number of membership types that I am aware of, other than the natural limits on human comprehension.   That's quite a list.  I suggest reaching out to www.civicrm.org/providers for paid help.  I don't think you're going to come to a resolution (or at least not a quick one) on the volunteer part-time support forums. 

[dudeval]

As I've been working with this, I deleted the bulk of my membership types to see if it makes a difference and so far I have been able to successfully register for membership with out getting the DB error I was getting before. If this proves to have fixed it, it would seem to indicate that the cause of the problem was having too many membership types. I'll report back after I've tested some more.

[dudeval]

It does appear that the number of membership types is what triggers this error. It seems to be somewhere around 30 that sets it off, although it was inconsistent. Fortunately it means I can avoid the error by reducing the number of membership types- I'll just have to find another way to reflect the accounting side of it.

[joanne]

You might be able to do something if you have one membership type called, say Vajra that lasts for 1 month and then use pricesets to set offer the option of one or 12 terms.

Although, on second thoughts, I think you may need to have one priceset that offered all 12 terms ( ie 12 month) options and another that offerred all the one month options. ( restriction caused by wanting suto renewing perhaps?)

Not sure about that, we don't use auto-renew.

[dudeval]

We basically have four membership levels that break down into a national, local, and online memberships at each of the levels. Since some members like to pay the annual membership all at once and some like to pay in monthly installments, I had to create different membership types for the annual price and for the monthly recurring payments (otherwise the recurring payments would charge the annual membership price every month!) This is what led to about 20 different membership types.  To make this cleaner for the user, I used membership price sets and jQuery to create logic so the user only has to select one of the four membership levels, and depending on which level they select and whether they choose recurring billing or not, the appropriate sub-membership types (which are hidden from the user) are selected. This worked well until I started getting this DB error. There were some extraneous testing membership types sitting around which I've since gotten rid of, but it's a bit disconcerting to be pushing it so close to breaking.

[joanne]

Glad to hear that it is working now.

[dudeval]

I was curious to see whether this was a problem with something I did on my installation, or whether it was a limitation in the code itself. I tested it out on the demo site and was able to recreate the problem (sort of).

On my site the DB error occurred when the user confirmed their membership registration on the front end. However on the demo site, the DB error occurred in the back-end when I created over 20 membership types. Specifically when I tried to edit the membership tab of the contribution page.
I just opened a bug report on the issue here:
https://issues.civicrm.org/jira/browse/CRM-16018

You might ask why anyone would need over 20 membership types...
I mentioned in the post above about our membership break down- the main factor that led to so many types was having to duplicate every membership type to account for whether the member wants to pay once for the annual membership, or break it up into monthly installments. So every membership option actually has a monthly membership type in which auto-renew is required, and an annual membership type where auto-renew is an option.

I thought about doing this in a price set- just selecting the same membership type twice and then changing their price in the price set to reflect the monthly and annual membership prices. Then I'd use jQuery to select the appropriate option depending on whether the user selects recurring payments or not. The problem with this is that monthly membership needs to be auto-renewed, but I want to give them a choice on the annual.

If anyone has any ideas on how to accomplish this differently- in a way that doesn't break the system with too many membership types, I'm all ears.