Create table error when more than 72 fields

Bu

Bullfn33 14 Jun, 2012

Is there a limit to the number of field names you can have in a table in CF4? I need to create a table with 77 fields but after doing some experimenting, I receive the following error when trying to create a table with more than 72 fields. 72 or less will create the table but more will trigger the error. The only difference in the field names I took out to get it to create table are numbers 6-10 on end of them so I don't think it has anything to do with the field names themselves.

Check table query below:

CREATE TABLE IF NOT EXISTS `#__chronoforms_data_xxxxx` ( `cf_id` INT(11) NOT NULL auto_increment, `cf_uid` VARCHAR(255) NOT NULL, `cf_created` DATETIME NOT NULL, `cf_modified` DATETIME NOT NULL, `cf_ipaddress` VARCHAR(255) NOT NULL, `cf_user_id` VARCHAR(255) NOT NULL, `contest` VARCHAR(255) NOT NULL, `week` VARCHAR(255) NOT NULL, `date` VARCHAR(255) NOT NULL, `lock_time` VARCHAR(255) NOT NULL, `username` VARCHAR(255) NOT NULL, `school` VARCHAR(255) NOT NULL, `gm_1_conf` VARCHAR(255) NOT NULL, `gm_1` VARCHAR(255) NOT NULL, `gm_2_conf` VARCHAR(255) NOT NULL, `gm_2` VARCHAR(255) NOT NULL, `gm_3_conf` VARCHAR(255) NOT NULL, `gm_3` VARCHAR(255) NOT NULL, `gm_4_conf` VARCHAR(255) NOT NULL, `gm_4` VARCHAR(255) NOT NULL, `gm_5_conf` VARCHAR(255) NOT NULL, `gm_5` VARCHAR(255) NOT NULL, `gm_6_conf` VARCHAR(255) NOT NULL, `gm_6` VARCHAR(255) NOT NULL, `gm_7_conf` VARCHAR(255) NOT NULL, `gm_7` VARCHAR(255) NOT NULL, `gm_8_conf` VARCHAR(255) NOT NULL, `gm_8` VARCHAR(255) NOT NULL, `gm_9_conf` VARCHAR(255) NOT NULL, `gm_9` VARCHAR(255) NOT NULL, `gm_10_conf` VARCHAR(255) NOT NULL, `gm_10` VARCHAR(255) NOT NULL, `mg` VARCHAR(255) NOT NULL, `away_1` VARCHAR(255) NOT NULL, `away_2` VARCHAR(255) NOT NULL, `away_3` VARCHAR(255) NOT NULL, `away_4` VARCHAR(255) NOT NULL, `away_5` VARCHAR(255) NOT NULL, `away_6` VARCHAR(255) NOT NULL, `away_7` VARCHAR(255) NOT NULL, `away_8` VARCHAR(255) NOT NULL, `away_9` VARCHAR(255) NOT NULL, `away_10` VARCHAR(255) NOT NULL, `home_1` VARCHAR(255) NOT NULL, `home_2` VARCHAR(255) NOT NULL, `home_3` VARCHAR(255) NOT NULL, `home_4` VARCHAR(255) NOT NULL, `home_5` VARCHAR(255) NOT NULL, `home_6` VARCHAR(255) NOT NULL, `home_7` VARCHAR(255) NOT NULL, `home_8` VARCHAR(255) NOT NULL, `home_9` VARCHAR(255) NOT NULL, `home_10` VARCHAR(255) NOT NULL, `rk1_a` VARCHAR(255) NOT NULL, `rk2_a` VARCHAR(255) NOT NULL, `rk3_a` VARCHAR(255) NOT NULL, `rk4_a` VARCHAR(255) NOT NULL, `rk5_a` VARCHAR(255) NOT NULL, `rk6_a` VARCHAR(255) NOT NULL, `rk7_a` VARCHAR(255) NOT NULL, `rk8_a` VARCHAR(255) NOT NULL, `rk9_a` VARCHAR(255) NOT NULL, `rk10_a` VARCHAR(255) NOT NULL, `rk1_h` VARCHAR(255) NOT NULL, `rk2_h` VARCHAR(255) NOT NULL, `rk3_h` VARCHAR(255) NOT NULL, `rk4_h` VARCHAR(255) NOT NULL, `rk5_h` VARCHAR(255) NOT NULL, `rk6_h` VARCHAR(255) NOT NULL, `rk7_h` VARCHAR(255) NOT NULL, `rk8_h` VARCHAR(255) NOT NULL, `rk9_h` VARCHAR(255) NOT NULL, `rk10_h` VARCHAR(255) NOT NULL, `time_1` VARCHAR(255) NOT NULL, `time_2` VARCHAR(255) NOT NULL, `time_3` VARCHAR(255) NOT NULL, `time_4` VARCHAR(255) NOT NULL, `time_5` VARCHAR(255) NOT NULL, `time_6` VARCHAR(255) NOT NULL, `time_7` VARCHAR(255) NOT NULL, `time_8` VARCHAR(255) NOT NULL, `time_9` VARCHAR(255) NOT NULL, `time_10` VARCHAR(255) NOT NULL, `tv_1` VARCHAR(255) NOT NULL, `tv_2` VARCHAR(255) NOT NULL, `tv_3` VARCHAR(255) NOT NULL, `tv_4` VARCHAR(255) NOT NULL, `tv_5` VARCHAR(255) NOT NULL, `tv_6` VARCHAR(255) NOT NULL, `tv_7` VARCHAR(255) NOT NULL, `tv_8` VARCHAR(255) NOT NULL, `tv_9` VARCHAR(255) NOT NULL, `tv_10` VARCHAR(255) NOT NULL, PRIMARY KEY (`cf_id`) );

jj

jjspelman 15 Jun, 2012

I am also having the same problem. I am recreating custom forms that were originally built on a development server, but am getting this error on the live site.

Gr

GreyHead 15 Jun, 2012

Hi both,

Please see this FAQ. Different symptom but most likely the same problem.

Bob

jj

jjspelman 15 Jun, 2012

I just checked and HostGator is not using suhosin.

It worked fine on Rochen (development server) and even when I transferred the site to HostGator initially.

But I made some changes to form element names and am experiencing this problem now.

There are no spaces in the names, I removed the IDs on one test, and still get the red warning box.

Bu

Bullfn33 15 Jun, 2012

I contacted HostGator about this and the guy I chatted with said, "The issue is not that we limit the number of variables, but the maximum post size in PHP." That can't be right can it? Maximum post size on shared servers is set to 64MB which is plenty of size for 90 or so form fields table isn't it?

Gr

GreyHead 16 Jun, 2012

Hi both,

I did a little research and it looks as though PHP 5.3.9 re-introduced max_input_vars as a php.ini file setting with a default of 1000 if it is not explicitly set. Note that the 'create table' uses 8 post variables in each row so the total mounts up quite quickly. Even so 72 fields should only require about 580 vars.

There is also a max_input_time setting but that defaults to -1 i.e. 'off'.

And post_max_size is also a possibility if it is set very low for some reason.

And if modSecurity is installed on the server then the SecFilter settings can also limit the number or total size of vars allowed.

Bob

ju

juliogarcia 29 Jun, 2012

I am also having the same problem. I am recreating custom forms that were originally built on a development server, but am getting this error on the live site.

Having the same problem here too.

I have been trying to find a workaround for 2 days. We are using Hostgator, have spend 4 hours with tech support there and still cant find the solution.

ChronoForms is an excellent program, I wish that we could have a little more help here....

thanks.

Gr

GreyHead 29 Jun, 2012

Hi juliogarcia,

All that I know about this has been gathered into this FAQ. It looks as though there is a particular problem with Hostgator - I can only think that this is a server setting somewhere :-(

Bob

jj

jjspelman 07 Jul, 2012

I had another chat with HostGator and they assured me there was no limit to the number of columns per table. but clearly there is some limit to something, somewhere. I don't know a thing about data bases but I had to reduce the number of variables being written to each table to around 80. Now all the forms are being written to a table through the Chronoforms admin area.

Could it be that this many variables, with the majority set to "varchar 255' that there is too much being pushed to the table? I don't know, I'm reaching. I mean 80 X 255 = 2040. Does that mean anything?

Gr

GreyHead 07 Jul, 2012

Hi JJSpelman,

There are MySQL limits on the maximum size of a row in a table. They are a maximum of 4096 columns (Hostgator notwithstanding) and 65,535 bytes. You aren’t hitting the column limit but you might be hitting the byte one. Because there has to be provision for UTF characters taking 3 bytes each a VARCHAR(255) column can take 767 bytes (including 2 to record the length). And 65,535/767 gives 85 columns.

The answer is to re-adjust the table to downsize the column lengths to match the maximum likely values. Most data will fit in VARCHAR(16), (32) or (64) - you can use any number but powers of 2 are convenient markers. Textareas should usually use a TEXT column or risk being pruned at 255 characters.

I'll go update the FAQ to show this - I'd forgotten the column size limit.

Bob

jj

jjspelman 07 Jul, 2012

Well now that makes a great deal of sense!

I am simply bringing these custom forms into CF and allowing it to create the tables. Not knowing any of the stuff you just mentioned, I just let everything set at the defaults they were given (255). So maybe I can go in and prune a good number of those variables to a more digestible number. I will take your advice and be sure to keep it at a multiple of 2.

Thanks, Bob!

jj

jjspelman 07 Jul, 2012

Well I just went in and changed the varchar from 255 to different values ranging form 12-36 in about 80% of the variables. This worked and allowed me to keep the fields/variables I had removed in order to make it work last night.

A little tedious having to go into about 100 fields and change that value, but it worked. 2 more forms need the same fix.

I have another site in development that is going to have multi-page forms with many variables, a good number of them will be text areas. How can I get around this problem?

Thanks for your help, Bob!!

Bu

Bullfn33 17 Jul, 2012

Sorry for not replying back. Usually I get emails after replies but did not on this thread for some reason. I had contacted HostGator again and someone else told me to change the field types to TEXT or BLOB which worked to save all the fields I needed so I can also confirm GreyHead's solution works for HostGator users.

Gr

GreyHead 18 Jul, 2012

Hi jjspellman and Bullfn33,

@jjspellman: I guess my first reaction is that it might help to redesign the forms or tables if you have so many rows to edit.

If the majority of rows need to be of type TEXT or BLOB then you could edit the default values around line 709 of administrator/components/com_chronoforms/controller.php

			foreach($fields_names as $name){
				if($name != 'cf_id'){
					$defaults[$name] = array('type' => 'TEXT', 'length' => '', 'default' => '', 'key' => '', 'extra' => '', 'enabled' => 1);
				}
			}

If this is overkill the other way then a macro might help. I use Breevy in Windows (TextExpander is similar on a Mac) and you could set up a little macro to tab through a few boxes and reset the values. A lot less tedious than setting each one by hand.

@Bullfn33: Thanks for the BLOB suggestion, I'll add it to the FAQ.

Bob

Forums

Create table error when more than 72 fields