I have a very "wide" MySQL table, with nearly 700 columns. (I inherited this system and will be moving to a key-value store in the future, but that's outside the point of this question.) When trying to add a VARCHAR(500) I was met with this error:
ERROR 1118 (42000): 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
After a little research I decided to turn the eight VARCHAR(2000) columns in the table into TEXT columns. After doing that, and trying to add my new VARCHAR(500) again I was met with the same error.
Now, those VARCHAR(2000) columns probably didn't have 500 characters between them, but they did have more than the ~20 characters I was trying to insert into the new VARCHAR(500) field.
As I understand it, VARCHAR only takes up as much space as it needs. But if a table can only hold 8126 characters then either it should never have been able to allocate eight VARCHAR(2000) since 8 x 2000 = 16,000 which is much more than 8126, or an error would happen when you tried to fill those fields beyond 8126, regardless of what data you put where. So my first question is: is that correct that a table could accept eight VARCHAR(2000) but you would run out of space while filling them?
And my second question is: why didn't moving those VARCHAR(2000) to TEXT clear up space for me to add a new VARCHAR(500)? Especially since I was only putting ~20 characters in that 500 and there were about 100 characters across the 2000s already.
NOTE: I do see in other questions ways to increase the table size, but I'm not trying to do that. This is a question about theory, not looking for a work-around solution.
