Error - Sorry couldn't insert new item

danthmanAdvanced

206 posts

Suddenly I am receiving this message when I try to add a new item when in fields builder. I have searched the database, dumped the cache, manually deleted the cache and public cache but I cannot find the problem.

What shall I do?

thanks

Dan

Quote · 5 Dec 2014

Nathan PatonUndefined

9604 posts

What's the item you're trying to add, and does another item already exist with the exact same name? I've seen this sometimes happen when a sample block is added without changing its name afterwards.

BoonEx Certified Host: Zarconia.net - Fully Supported Shared and Dedicated for Dolphin

Quote · 5 Dec 2014

danthmanAdvanced

206 posts

I have found that if I delete two fields I can add two more but no more than that. It appears as though there is a limit as to how many fields I can add in Fields Builder>Edit Profile. How can I increase the limit?

Dan

Quote · 5 Dec 2014

danthmanAdvanced

206 posts

Every field is a unique name. I get the message when I drag the NEW_ITEM bolck into the active items area.

Quote · 5 Dec 2014

UnderDog4AllAdvanced

504 posts

Re-iterating what Nathan said:

It sounds like you need to check around on different pages and see if you have dragged a "NEW_ITEM" block, and forgot to change the "NEW_ITEM" to something else.

Quote · 5 Dec 2014

geek_girlPremium

11493 posts

I am also adding that this most likely an existing block with the same name; such as the "NEW_ITEM". You might have a quicker time of finding this by looking in the database; you can sort on the blocks for sys_page_compose and order by name; see if you find a "NEW_ITEM" listed in the database. If so, then you will know which page it is on instead of switching from page to page to page in the Page Builder.

Geeks, making the world a better place

Quote · 5 Dec 2014

deano92964Premium

10772 posts

Dan

Looks like the last two people that replied missed this post i quoted above. And if what he stated there is true, then the cause as mentioned in your answers has been ruled out.

https://www.deanbassett.com

Quote · 5 Dec 2014

danthmanAdvanced

206 posts

If that were the case when I deleted a few fields, then added a new one, I would get the error message but I am able to delete a few fields, then add new ones in without any problem but when I get to the same number of fields as I had before I start getting the error.

I have a large number of fields. It seems as though there is a limit to the number of fields I can add but I don't know how to increase it.

Dan

Quote · 5 Dec 2014

deano92964Premium

10772 posts

The only limit dolphin has is to the max number of the ID which is 65535. But if your reached that limit you would not be able to add any even after deleting a couple.

So my only guess is you have reached the limit on size of the total number of columns that a mysql table can have. If that's the case, there is no way to increase that. It's a mysql server limitation.

Only suggestion i have there if that is indeed the cause is to find a way to reduce the size of the field your creating.

Example. If you have several varchar(255) fields but only need 128 chars then reduce them. Same with other field types. Set them up for the minimum length you can safely get away with.

You may end up having to find a way to store the extra data you need in a separate table without using the profile table.

https://www.deanbassett.com

Quote · 5 Dec 2014

danthmanAdvanced

206 posts

Looking through the database I have found the profile fields I have been adding in the Profiles table. It has 126 rows at the moment compared with another site that has not been modified with 53 rows in the Profiles table. I tried to manually add a new profile field into the table and received this error message:

#1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

I changed the last two entries from varchar(255) to text(64) and then I was able to add a few more but soon reached the limit again. I changed three more in the database and was able to add three more fields but then reached the limit again.

Any ideas?

Dan

Quote · 5 Dec 2014

danthmanAdvanced

206 posts

Thanks Deano, I did not see your post about the limit when I posted my last post. It looks like that is what I will have to do.

Thanks,

Dan

Quote · 5 Dec 2014

danthmanAdvanced

206 posts

I just had a thought, will adding lots of profile fields limit the amount of members the site can have or does it just limit the number of profile fields to the max limit of 65535 characters? In other words. if all the members filled out all the additional profile fields would that limit membership since I am already pushing the limit or is the 65535 limit just for the table structure itself?

Quote · 6 Dec 2014

geek_girlPremium

11493 posts

I find it rather interesting that you would have that many profile fields on your site. Why so many that you have reached the mysql limit. Here is the page that addresses the number of columns in a mysql database table.

D.3.2 The Maximum Number of Columns Per Table

There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors, listed in the following discussion.

Every table has a maximum row size of 65,535 bytes. This maximum applies to all storage engines, but a given engine might have additional constraints that result in a lower effective maximum row size.

The maximum row size constrains the number of columns because the total width of all columns cannot exceed this size. For example, utf8 characters require up to three bytes per character, so for a CHAR(255) CHARACTER SET utf8 column, the server must allocate 255 × 3 = 765 bytes per value. Consequently, a table cannot contain more than 65,535 / 765 = 85 such columns.

Storage for variable-length columns includes length bytes, which are assessed against the row size. For example, a VARCHAR(255) CHARACTER SET utf8 column takes two bytes to store the length of the value, so each value can take up to 767 bytes.

BLOB and TEXT columns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately.

Declaring columns NULL can reduce the maximum number of columns permitted. NULL columns require additional space in the row to record whether their values are NULL.

For MyISAM and ISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte. The maximum row length in bytes can be calculated as follows:
```
row length = 1
             + (sum of column lengths)
             + (number of NULL columns + delete_flag + 7)/8
             + (number of variable-length columns)
```
delete_flag is 1 for tables with static row format. Static tables use a bit in the row record for a flag that indicates whether the row has been deleted. delete_flag is 0 for dynamic tables because the flag is stored in the dynamic row header.

These calculations do not apply for InnoDB tables, for which storage size is no different for NULL columns than for NOT NULL columns.
Each table has an .frm file that contains the table definition. The server uses the following expression to check some of the table information stored in the file against an upper limit of 64KB:
```
if (info_length+(ulong) create_fields.elements*FCOMP+288+
    n_length+int_length+com_length > 65535L || int_count > 255)
```
The portion of the information stored in the .frm file that is checked against the expression cannot grow beyond the 64KB limit, so if the table definition reaches this size, no more columns can be added.

The relevant factors in the expression are:
- info_length is space needed for “screens.” This is related to MySQL's Unireg heritage.
- create_fields.elements is the number of columns.
- FCOMP is 17.
- n_length is the total length of all column names, including one byte per name as a separator.
- int_length is related to the list of values for ENUM and SET columns.
- com_length is the total length of column comments.
Thus, using long column names can reduce the maximum number of columns, as can the inclusion of ENUM or SET columns, or use of column comments.
Individual storage engines might impose additional restrictions that limit table column count. Examples:
- InnoDB permits no more than 1000 columns.
- InnoDB restricts row size to something less than half a database page (approximately 8000 bytes), not including VARBINARY, VARCHAR, BLOB, or TEXT columns.

Geeks, making the world a better place

Quote · 6 Dec 2014

geek_girlPremium

11493 posts

I am guessing you are doing something more with the Profiles table than just standard profile fields. Perhaps you should rethink your procedure.

Geeks, making the world a better place

Quote · 6 Dec 2014

deano92964Premium

10772 posts

You can have a many members as you want. The 65535 limit is just the length of a table row.

But GG is correct. You may need to re-think things. If you have that many profile fields, you must have a enormous join form. I certainly would not want to take the time to fill it out if i was joining. Perhaps your using that table for things that should be moved off into custom modules with their own tables.

https://www.deanbassett.com

Quote · 6 Dec 2014

danthmanAdvanced

206 posts

Yes, the site owner wanted a large form, it is not in the signup, it will appear in the profile info page. It was originally 221 questions, I was able to get him to reduce it to 160 questions but I agree with you, I don't think anyone will take the time to fill it out.

By going into the database I was able to change the varchar(255) to varchar(32) allowing me to add all of the questions.

Thanks to both of you for your help,

Dan

Quote · 8 Dec 2014

Reply ›

Error - Sorry couldn't insert new item

D.3.2 The Maximum Number of Columns Per Table

Blogs

Help

Product

Company