Row size too large MySQL error when adding multiple link fields to single content type

My best guess is something is wrong with how your database is set up but, beyond that, no clue. Moving this over to the CCK queue as this is a content copy issue and maybe they have an idea why the import isn't working for you.

Michelle

subscribed

Same issue here.

EDIT: Importing "uprofile.basic-stats.export" worked fine, it was the full file that makes it blow up.

The field it's complaining about isn't in the basic import. It's one of the three favorite site links, which are link fields, so the problem could be there as well.

Michelle

same problem here. First one "uprofile.basic-stats" went fine, as already said.
"uprofile.contact-links" and "uprofile.full" get same error, as stated.

user warning: 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 query: ALTER TABLE content_type_uprofile ADD `field_favorite3_url` VARCHAR(2048) DEFAULT NULL in [mysite]\includes\database.mysql-common.inc on line 298.

If I now try to delete the new content type there's also an error:

user warning: Can't DROP 'field_favorite3_url'; check that column/key exists query: ALTER TABLE content_type_uprofile DROP field_favorite3_url in [mysite]\includes\database.mysql-common.inc on line 322.

by the way: The Page manager "user_view" shows a few errors in it's content, that the type is missing.

thanks in advance

Hi Michelle,

Another puzzling issue. As I mentioned previously, I imported "uprofile.basic-stats.export" with no problem. I then tried importing "uprofile.contact-links.export" and received the error. I deleted the uprofile type, got the "Can't DROP" error as in #5, imported "uprofile.basic-stats.export" again and then edited "uprofile.contact-links.export" to remove field_favorite3 and it worked fine.

Since the import code for field_favorite1, 2 & 3 look the same, it is a bit curious.

A temporary work-around seems to be to remove the field_favorite3 entry from the file and import just the 2 fields.

Rachel

This has been working fine and I haven't changed the export in some time so all I can think of is something changed in either CCK or Link.

I plan to rework the exports, soon, anyway, so this may be a moot point once I do that. We'll see. Without knowing what's wrong, I have no idea if the changes I have planned will have the side effect of fixing this.

Michelle

The new CCK came out 2010-Jun-16 and the new Link on 2010-Jun-14. Something might have changed in one of those. I don't suppose you've tried an import since those came out?

I don't think its worth a lot of time to troubleshoot since you are going to be reworking the exports.

I can post my edited "uprofile.contact-links.export" if you'd like and if someone confirms it works for them we can move on.

Rachel

It seems as if the Link module always gives a command to create varchar(2048), which is unnecessarily long. Summing all of these link fields up leads to more than the max. capacity of a row. Now how to fix this ? Don't worry, only 3 Steps to fix this ...

1. go on and add the uprofile_full.txt. Please ignore the error msg, if it tells you:
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 query: ALTER TABLE content_type_uprofile ADD `field_favorite3_url` VARCHAR(2048)
don't panic, this will be fixed in a moment ... and you won't have to be short a field!

2. log in to MySQL and choose to enter SQL Commands to enter:

ALTER TABLE `content_type_uprofile` CHANGE `field_homepage_url` `field_homepage_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `field_blog_url` `field_blog_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `field_facebook_url` `field_facebook_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `field_twitter_url` `field_twitter_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `field_friendfeed_url` `field_friendfeed_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `field_linkedin_url` `field_linkedin_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `field_myspace_url` `field_myspace_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `field_favorite1_url` `field_favorite1_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8__general_ci NULL DEFAULT NULL, CHANGE `field_favorite1_ur2` `field_favorite1_ur2` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8__general_ci NULL DEFAULT NULL, ALTER TABLE content_type_uprofile ADD `field_favorite3_url` VARCHAR(1024) DEFAULT NULL

3. Now go back to your page and see that this problem is fixed, even if you enter lots and lots of new fields!

Stay true and contribute ;-)

@emo4u.eu: I appreciate your enthusiasm but that isn't a patch and isn't something most people are going to want to do. This needs to be fixed at the source, which appears to be the Link module.

I'm not labeling this as a bug, yet, because I don't know what's going on. This export has been working fine for the last year or so and I have no clue why it would suddenly be acting up.

Moving this to the Link module queue to see if there's any suggestions there.

Michelle

I'm rebuilding my development site and am in the process of installing APK on it. I just imported the full content type export with no issues. I'm guessing it has to do with specific database setups? Not really my area of expertise, I'm afraid.

Michelle

Hmm... Now that's interesting. Since I'm reworking the uprofile node type, I deleted and then re-imported and the second time around I got that error. Really don't know what to make of this. I'd love it if the Link maintainer(s) could chime in here, especially with whether the field really needs to be that big.

Michelle

The link maintainer is kind of enjoying the holiday off.

The field is that big because people said 256 was too small.

Holiday off? What's that? ;)

Well, the APK maintainer is taking the holiday to get an RC out so I'm going to let the export stand as is and hopefully we'll get it sorted before the final release.

Have a good holiday. :)

Michelle

Edit in link.module the constant LINK_URL_MAX_LENGTH (line 40) to the desired size. I'm using 256 but you could also use another value that is smaller then 2048.

The script won't work. Corrections:

ALTER TABLE `content_type_uprofile` CHANGE `field_homepage_url` `field_homepage_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `field_blog_url` `field_blog_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `field_facebook_url` `field_facebook_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `field_twitter_url` `field_twitter_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `field_friendfeed_url` `field_friendfeed_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `field_linkedin_url` `field_linkedin_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `field_myspace_url` `field_myspace_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `field_favorite1_url` `field_favorite1_url` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `field_favorite2_url` `field_favorite1_ur2` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL; ALTER TABLE content_type_uprofile ADD `field_favorite3_url` VARCHAR(1024) DEFAULT NULL

- collation __ instead of _ in some places
- field_favorite2 names were wrong.
- separation of the two alters with ; not ,

The above worked for me.
Though I agree with Michelle a better, code-wise alternative should be preferred.

#15 fixed it for me – thanks!

Indeed #15 worked for me too: for complex content types, a length of 2048 may be too high. Unfortunately it seems this has to be hardcoded, and if it was raised to 2048 because 256 was too short for some then I see no easy solution, unless there is a value in between that can be OK for everybody.

Should anyone prefer #15 in the form of a (trivial) patch against the latest 6.x-2.x branch, it is attached.

#15 worked for me too. I had to update all the existing fields in my DB, and re-save several of my fields to completely resolve my occurrence.

This problem is not limited to the import of content - I just ran into it while adding link fields to a new content type (title adjusted accordingly). My error is as below:

User warning: 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 query: ALTER TABLE content_type_school ADD `field_school_edu_feat_url` VARCHAR(2048) DEFAULT NULL in _db_query() (line 148 of /var/www/drupal/includes/database.mysql.inc).

I can't see any good reason to make these url fields so large to begin with, so #15 will help there, but I also don't understand why these aren't text fields. Text fields aren't added up across rows. If we were to use text fields we wouldn't run into this problem at all.

Hopefully this can help someone...
It seems the issue for me was that the LINK_URL_MAX_LENGTH was set to 2048 (which seemed to be too large) and I changed it to 256 and the error is no longer appearing.

define('LINK_URL_MAX_LENGTH', 256);

#15 worked for me! I had 4 fields on my content type that were CCK fields of type "Link". I updated all of them at the database level to be VARCHAR (1024) and it's working now!

One of my clients has this problem on his Drupal 6 site. I am going to write a patch.

I can see two possible ways.

1) Change the maxlength to 255, keeping varchar as column type
2) Change the column type to text

Right now I am leaning towards 2).

1) Seems to be easier, but also not a "solution" like in "will never fail again". Just think about a big enough content type with hundreds of link fields in one node - it will definitely fail if you use more than ~257 link fields instead of after about ~30 fields right now. Maybe that scenario is not so real, but hey, you never know.

2) Could have a little performance impact but should not be a problem with caching in place. Bit may be a bit more complicated.

If anyone has more insights I am happy to discuss and learn.

How to reproduce this error?

• Clean D6 install
• DL and activate Content + Link
• Create new content type and add Link fields
• With the 10th Link field, I got the error message:
  • user warning: 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 query: ALTER TABLE content_type_links ADD `field_link_10_url` VARCHAR(2048) DEFAULT NULL in /includes/database.mysql-common.inc on line 307.
  • The field_link_10 only has attribute and title column in the database, it is missing the field_link_10_url column.

• Which leads to the following inherited error upon saving a node:

  user warning: Unknown column 'field_link_10_url' in 'field list' query: INSERT INTO content_type_links (vid, nid, field_link_1_url, field_link_1_title, field_link_1_attributes, field_link_10_url, field_link_10_title, field_link_10_attributes, field_link_2_url, field_link_2_title, field_link_2_attributes, field_link_3_url, field_link_3_title, field_link_3_attributes, field_link_4_url, field_link_4_title, field_link_4_attributes, field_link_5_url, field_link_5_title, field_link_5_attributes, field_link_6_url, field_link_6_title, field_link_6_attributes, field_link_7_url, field_link_7_title, field_link_7_attributes, field_link_8_url, field_link_8_title, field_link_8_attributes, field_link_9_url, field_link_9_title, field_link_9_attributes) VALUES (1, 1, NULL, NULL, NULL, 'http://cjs-design.de', 'Test', 'a:0:{}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) in /sites/all/modules/cck/content.module on line 1213.

This patch (for Drupal 6 Link Module) changes the url field type from varchar(2048) to text. This patch should be enough if you ran into this problem to solve it, but it will leave already created link fields alone. So they will still be of type varchar(2048) which could lead to future problems.

I am about to write a second patch. That will add a hook_update_n function to update all link url columns to text.

Okay, this patch does both:

1) Change the module to create new link fields with a text column for url field.
2) Resave all link fields in an update function. By resaving they change to text type also.

This patch is not tested in detail, so please do not use it on production sites without prior testing! It could break your site.

Me working on this patch was partly paid by http://www.pkv-wiki.com - Thanks

Since last week we use this patch on our live site. No problems so far. But please, someone else should test this also.

FYI, We shouldn't change the length of the field to 255, that also presents issues. (See #376818: Really long URL gets cut off)

I like the approach of changing to text, however this patch still isn't working for me. It looks like there was a constant added at some point 'LINK_URL_MAX_LENGTH' that also needs to be updated. Attaching a new patch for review.

edit: looks like the link also contains some whitespace cleanup. sorry bout that!

• @jenlamptom: Your change is good and makes the form accept longer urls. I only had the "Row size too large" issue so I did not check any form fields.
• I wondered why 2048 is the limit but reading this it makes sense: https://stackoverflow.com/questions/417142/what-is-the-maximum-length-of... (Updated in 09/15).
• I have no Idea why the patch failed. Can testbot not work with database changes?

Thank you all for your efforts, but I'm sorry to say that the D6 version is no longer supported.