I just encountered an error message I’ve never seen before. I am attempting to add a field to a MySQL (MariaDB) table in Sequel Pro (a MySQL management tool), and I am seeing the following message
Has anyone ever encountered this before? If so, how did you get past this?
Yes, don’t put so much stuff in one row.
you could check how many CHAR() or VARCHAR() you have with what limits.
You can of course increase the limit on your database server.
Please check the documentation.
how many columns are there in the table ?
This one has 39 fields (was just testing in this one as to not mess something up). The one I am ultimately wanting to add a column to currently has 20 fields
The data in each field is not overly huge. Most are one-worders
I have been using type TEXT for all fields except for the primary key (INT). Should I use a different type? VARCHAR? CHAR? Or something else?
Strange that you say you use TEXT as changing some fields to TEXT is what is recommended to work around the problem:
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to
TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored
inline.
VARCHAR and CHAR take space in the row.
BLOB and TEXT are stored separately.
Maybe you query the list of field types and limits and post it?
Thanks for the info on the different types. Not sure if what I have below is what you meant by “limits”
Run a:
DESCRIBE ZBetaSurvey;
What it returns?
I love these little things you guys teach me 
Here is what DESCRIBE returned. There’s a bunch more after ActivationCode, but they’re all the same (field_name - text, Null: YES, Key: , Default:, Extra:):
id - int(11) unsigned, Null: NO, Key: PRI, Default: , Extra: auto_increment
ActivationCode - text, Null: YES, Key: , Default: , Extra:
The point was to see the full schema.
Ah! I thought it was just to look for something off. Here’s the full
id - int(11) unsigned, Null: NO, Key: PRI, Default: , Extra: auto_increment
ActivationCode - text, Null: YES, Key: , Default: , Extra:
Product - text, Null: YES, Key: , Default: , Extra:
FirstName - text, Null: YES, Key: , Default: , Extra:
LastName - text, Null: YES, Key: , Default: , Extra:
UserType - text, Null: YES, Key: , Default: , Extra:
Phone - text, Null: YES, Key: , Default: , Extra:
Email - text, Null: YES, Key: , Default: , Extra:
Password - text, Null: YES, Key: , Default: , Extra:
RDorDTR - text, Null: YES, Key: , Default: , Extra:
MyExamDate - text, Null: YES, Key: , Default: , Extra:
ColorScheme - text, Null: YES, Key: , Default: , Extra:
MyPlantSeeds - text, Null: YES, Key: , Default: , Extra:
TextSize - text, Null: YES, Key: , Default: , Extra:
PretestTaken - text, Null: YES, Key: , Default: , Extra:
CDRorPercent - text, Null: YES, Key: , Default: , Extra:
PassScore - text, Null: YES, Key: , Default: , Extra:
QuestionTime - text, Null: YES, Key: , Default: , Extra:
ExtraTime - text, Null: YES, Key: , Default: , Extra:
ViewedUpdateVerNo - text, Null: YES, Key: , Default: , Extra:
I don’t see ‘s5Comments’ on your list.
That is the schema for the table I am trying to add to for my project
Here is the DESCRIBE schema for the ZBetaSurvey table:
id - int(11) unsigned, Null: NO, Key: PRI, Default: , Extra: auto_increment
ActivationCode - text, Null: YES, Key: , Default: , Extra:
Name - text, Null: YES, Key: , Default: , Extra:
Email - text, Null: YES, Key: , Default: , Extra:
SurveyComplete - text, Null: YES, Key: , Default: , Extra:
s1Completed - text, Null: YES, Key: , Default: , Extra:
s1Devices - text, Null: YES, Key: , Default: , Extra:
s1DeviceRating - text, Null: YES, Key: , Default: , Extra:
s1NumDays - text, Null: YES, Key: , Default: , Extra:
s1NumHours - text, Null: YES, Key: , Default: , Extra:
s2Completed - text, Null: YES, Key: , Default: , Extra:
s2Activation - text, Null: YES, Key: , Default: , Extra:
s2Login - text, Null: YES, Key: , Default: , Extra:
s2ActLoginImprove - text, Null: YES, Key: , Default: , Extra:
s2LoadTimes - text, Null: YES, Key: , Default: , Extra:
s2SlowPages - text, Null: YES, Key: , Default: , Extra:
s2Locations - text, Null: YES, Key: , Default: , Extra:
s2Crashes - text, Null: YES, Key: , Default: , Extra:
s3Completed - text, Null: YES, Key: , Default: , Extra:
s3Library - text, Null: YES, Key: , Default: , Extra:
s3Exams - text, Null: YES, Key: , Default: , Extra:
s3Vids - text, Null: YES, Key: , Default: , Extra:
s3Match - text, Null: YES, Key: , Default: , Extra:
s3Flash - text, Null: YES, Key: , Default: , Extra:
s3Prof - text, Null: YES, Key: , Default: , Extra:
s3Review - text, Null: YES, Key: , Default: , Extra:
s3OtherFeatures - text, Null: YES, Key: , Default: , Extra:
s4Completed - text, Null: YES, Key: , Default: , Extra:
s4Allinclusive - text, Null: YES, Key: , Default: , Extra:
s4AdditionalSubs - text, Null: YES, Key: , Default: , Extra:
s4Price3 - text, Null: YES, Key: , Default: , Extra:
s4Price12 - text, Null: YES, Key: , Default: , Extra:
s5RateOverall - text, Null: YES, Key: , Default: , Extra:
s5RateUsage - text, Null: YES, Key: , Default: , Extra:
s5WouldBuy - text, Null: YES, Key: , Default: , Extra:
s5WouldRec - text, Null: YES, Key: , Default: , Extra:
s5LikeMost - text, Null: YES, Key: , Default: , Extra:
s5LikeLeast - text, Null: YES, Key: , Default: , Extra:
s5Comments - text, Null: YES, Key: , Default: , Extra:
Both are not allowing me to add a field and showing the “row size too large” message if I do attempt
Sorry, too strange (should not happen). I’m out of guesses.
I have only seen the error when the table has VarChar (200) for example and 41 fields, as this goes above the 8126.
Same. It’s really weird. The total number of columns isn’t the issue. I read MySQL can have up to 4096 columns per table, so I’m well below that. The number of characters in each field is not super high. The most in the Settings table is around 30 characters. I was able to add a column to a smaller table with fewer fields. But then why would I be able to make a table with 39 columns (ZBetaSurvey) after making the Settings table with 20 columns? It is really off. I even restarted the computer but to no avail
IDK if this sheds any light, but in my Sequal Pro manager, I also see schema info for Encoding (set to cp1252 West) and Collation (set to latin1_swedish_ci). No idea what these mean or should be. They were defaulted when creating the tables, so I just let as is
And here is the Table Info tab in Squel Pro:
Not sure if those help any but worth noting
To add to the mystery… I just had a thought. What if I duplicate the table? Could I add a new field to it? The answer, yes! Duplicated the table and all the content within
I think what I will do is add the fields to the duplicated table. When ready to deploy the update, I’ll point to the newly one created with the added fields
It’s very bizarre. I appreciate everyone who chimed in
Well, I tried to replicate the problem here and couldn’t. This simply worked here:
CREATE TABLE ZBetaSurvey (
id INT ( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
ActivationCode TEXT NULL,
`Name` TEXT NULL,-- reserved
Email TEXT NULL,
SurveyComplete TEXT NULL,
s1Completed TEXT NULL,
s1Devices TEXT NULL,
s1DeviceRating TEXT NULL,
s1NumDays TEXT NULL,
s1NumHours TEXT NULL,
s2Completed TEXT NULL,
s2Activation TEXT NULL,
s2Login TEXT NULL,
s2ActLoginImprove TEXT NULL,
s2LoadTimes TEXT NULL,
s2SlowPages TEXT NULL,
s2Locations TEXT NULL,
s2Crashes TEXT NULL,
s3Completed TEXT NULL,
s3Library TEXT NULL,
s3Exams TEXT NULL,
s3Vids TEXT NULL,
s3Match TEXT NULL,
s3Flash TEXT NULL,
s3Prof TEXT NULL,
s3Review TEXT NULL,
s3OtherFeatures TEXT NULL,
s4Completed TEXT NULL,
s4Allinclusive TEXT NULL,
s4AdditionalSubs TEXT NULL,
s4Price3 TEXT NULL,
s4Price12 TEXT NULL,
s5RateOverall TEXT NULL,
s5RateUsage TEXT NULL,
s5WouldBuy TEXT NULL,
s5WouldRec TEXT NULL,
s5LikeMost TEXT NULL,
s5LikeLeast TEXT NULL,
s5Comments TEXT NULL,
PRIMARY KEY ( id )
);
ALTER TABLE `ZBetaSurvey`
ADD COLUMN `acc` int NULL DEFAULT NULL AFTER `s5Comments`;
What I have different here is the charset and collation of the db: utf8 and utf8_unicode_ci
And I used a Percona MySQL Server 5.7.23