Error when making a field

4 weeks 2 days ago - 4 weeks 2 days ago #316395 by matthewdavey
I have 224 fields
87 TABS

If I ad/make a field and save I get
Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
But it makes it but its broken.
and then click Community Builder tools

CB fields data storage database structure differences:
Table #__comprofiler Column cb_fieldsexample does not exist

The CB fields data storage database structure differences can be fixed (adjusted) by clicking here: Click here to fix (adjust) all CB fields data storage database differences listed above (you can also Click here to preview fixing (adjusting) queries in a dry-run), but in all cases you need to backup database first as this adjustment is changing the database structure to match the needed structure for the installed version.
Click here to show details
CB fields data storage database checks done. If all lines above are in green, test completed successfully. Otherwise, please take corrective measures proposed in red.

Then I click repair and

Then it said error
An error has occurred.
1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

Then I go into CBuilder fields and delete that field cb_fieldsexample and then go Community Builder tools again it says

All CB fields data storage database is up to date.
Click here to show details

It just won't let me make or edit a CB user either so I can't update a users subscription? Which is important because my site is live.
I can't don't understand the problem if I could study or pay someone to fix it I would. or if I could understand what it is and how to fix it I would study.
It only seems to be affecting 1 database BUT no others have the amount of fields so I don't know if its a server problem or database problem.

I had a similar problem 5 years 5 months ago #248254
Ok, that one is (finally) fixed for CB 2.0 RC2.
see www.joomlapolis.com/forum/153-professional-member-support/226050-4627-field-wont-save-when-i-click-save?start=30

I just changed
And change around line 312:
<field type="text" label="Text Field">
<data name="" nametype="namesuffix" type="sql:varchar(255)" null="true" default="NULL" />

to:
<field type="text" label="Text Field">
<data name="" nametype="namesuffix" type="sql:text" null="true" default="NULL" />

But it did nothing?

Thanks for persisting with my problem. I'm using php 7.2
4 weeks 2 days ago #316400 by krileon
You have reached the field limit for your database. You need to use the "Check Community Builder User Fields Database with strict column types" tool in CB > Tools. It is a link as part of the description of the "Check Community Builder User Fields Database" tool.

If that is not working review your _comprofiler table and see how many varchar columns you have. You maybe able to change some of them (only change fields prefixed with cb_) to text type to see if that can resolve your issue. The type fix tool is supposed to do this for you, but it may not be able to due to the database limit being hit.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Templates - CBSubs - Hosting - Forge - Incubator - GroupJive
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM EST to 4:00 PM EST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
4 weeks 1 day ago - 4 weeks 1 day ago #316405 by matthewdavey
I have checked "Check Community Builder User Fields Database with strict column types" Everything is ok

All the _comprofiler table fields
CB connect
Facebook
Twitter
Google
LinkedIn
Meetup
Flickr
Vimeo, Spotify are varchar that's about 25. Most cb_ are text and a fair amount mediumtext that I made and about 7 custom cb_customCBplugins that are varchar that were made. A few datetime some tinyint(4) a few tinyint(3) a some json a some int(11)
That makes up the 224 fields
ONLY 224 you said I should be able to have 1000
I need more thats why Im stuck..And I cant edit a CB user or make a new one?

I'm thinking the only way to fix this is to upgrade to your specifications.
Mine the 16k page size doesn't work.
What is the better settings or the settings you use, I need?

What are Hostgator they talking about it could be helpful if you upload the dump that you are attempting to restore and provide us with the path and filename for the dump, or if you can provide us with the query we can investigate further, but we are VERY limited in what we can do when an issue is out of scope.


I have a dedicated WHM server I just need to know the sequence. Can I make a new WHM domain account and configure it properly to suit CBuilder forever? I don't know what is wrong it cost me like $6000.00.
Do I have to configure the whole WHM? I don't know.
I'll pay your paypal email address if you can fix this?

Do I download my database and structure and make a new configuration? and then upload the database? I don't know?
What is the steps to have a configuration that you are using that will continually work with CBuilder

Thanks
4 weeks 1 day ago #316411 by krileon
Increasing the innodb page size limit can help resolve your issue, but requires a full rebuild of the database. You've hit the row limit for your databases current configuration. You should consider reducing the number of fields you need. The 1000 columns limit depends entirely on the type of fields. Each type consumes a different amount of bytes that slowly climbs to your databases limit. The row limit is typically just half of the page size limit so for you that's 8k. This is not something we can fix for you.

You can try changing some varchar columns to text. Your table is also still using the old utf8_general_ci collation. You can try changing that to utf8mb4_unicode_ci to force some types to consume more bytes and force them to be stored separately reducing row size. I don't know what more to tell you as I said before I am not a database expert. You can find various configuration suggestions around the web that might help; examples as follows.

stackoverflow.com/questions/22637733/mysql-error-code-1118-row-size-too-large-8126-changing-some-columns-to-te


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Templates - CBSubs - Hosting - Forge - Incubator - GroupJive
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM EST to 4:00 PM EST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
4 weeks 18 hours ago #316424 by matthewdavey
When I changed Table options Collation to utf8mb4_unicode_ci
I got error see attached. I can't change it.

It seems that when ever I try to change varchar(255) to text it gets same error
Actually ANY CHANGE ANYWHERE GIVES SEE ATTACHED

I tried an older website and added fields and it got to 235 fields before it got the error. Thats 10 more fields than the database I was using.
All my data bases have same error

I believe I have reached the field limit for my database.es
Question is I don't know how to upgrade. I keep getting links sent to me but I'm not as smart as you.
I don't understand how to make the new settings to fix the problem.
Is it an entire server issue that will affect every database? I believe so?
I have a Dedicated WHM does the whole server need upgrading? My 10 year project is and was a waste of time and money. I thought it was great but I can't even edit a users profile?
I need more fields. I might have to wait till CBuilder 3 .
When will you be upgrading to CBuilder 3
You said in a forum post you will be changing the structure to fix the problem?
Thanks
Attachments:
4 weeks 6 hours ago #316428 by krileon
message_last_sent is a datetime column. Do not change it to varchar, text, or anything other than leaving it alone as datetime. You only need to be changing varchar columns to text columns to try and free up space for more columns, but you need to be careful with this. That does not mean you suddenly have more row storage space. Filling text columns (e.g. textarea fields) with large amounts of content will cause you to hit storage limits as well.

I tried an older website and added fields and it got to 235 fields before it got the error. Thats 10 more fields than the database I was using.
All my data bases have same error

As I've already explained it's a database limitation. Any site on the same server is doing to hit the same limit. Depending on the type of fields used that limit may be a few less or a few more fields.

Question is I don't know how to upgrade. I keep getting links sent to me but I'm not as smart as you.
I don't understand how to make the new settings to fix the problem.

If you need a larger limit you will have to change the page size limit and rebuild the database. If you are incapable of doing this you will have to do as your host and I have suggested and hire a database expert.

Is it an entire server issue that will affect every database? I believe so?

Yes, it impacts the entire server. You would be changing the storage behavior for Innodb databases.

I have a Dedicated WHM does the whole server need upgrading? My 10 year project is and was a waste of time and money. I thought it was great but I can't even edit a users profile?

The server does not need upgrading. It needs its current configuration changed as suggested earlier (again the page size limit) and database rebuilt. This is not a simple task.

I need more fields. I might have to wait till CBuilder 3 .

You really should consider setting up your site to not require so many fields. That is a significant amount of information you're asking for your users and I imagine your registration/profile edit being significantly more complicated than it needs to be.

When will you be upgrading to CBuilder 3

Not anytime soon. End of this year at earliest.

You said in a forum post you will be changing the structure to fix the problem?

Yes, we will be completely rewriting how fields are stored. We will be moving them to a separate table.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Templates - CBSubs - Hosting - Forge - Incubator - GroupJive
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM EST to 4:00 PM EST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
Moderators: beatnantkrileon
Time to create page: 0.448 seconds

Facebook Twitter LinkedIn