MySQL Bugs: #4117: Too many columns while creating a table

Bug #4117	Too many columns while creating a table
Submitted:	12 Jun 2004 23:48	Modified:	8 Jan 2014 13:27
Reporter:	Partha Dutta	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	4.1.2	OS:	Solaris (Solaris 2.8 64bit)
Assigned to:		CPU Architecture:	Any

Description:
I have a schema for a table that contains 1896 columns. This schema for the table  can be created under version 4.0.20, but fails with the error "Too many columns" under version 4.1.2.

How to repeat:
Use the attached SQL to create the table under both versions. It will succeed with 4.0.20, but fail in 4.1.2

Suggested fix:
Why should a DDL break in the new version? This should be tested and fixed ASAP!

schema for table with many columns

Attachment: calcrtz.sql (text/x-sql), 81.82 KiB.

Your DDL doesn't work, because in 4.1 we allocate two bytes to store field's length, instead of one. Thus you go above max length a row metadata can be in an frm file.

This cannot be fixed ASAP, but only when we introduce new, more flexible frm format that does not have arbitrary limits (our current one is 20 years old already).

Unfortunately it cannot happen before 5.0

Hi, 
I'm also running into this limitation.

I'm using MySQL v4.1.12 on linux and 5.0.18-nt

A workaorund is to restructure the tables.
Is there any other way around this limit?

I need to create a table with 22,000 fields ;)
This seems to be impossible (the same error); MySQL server version is 5.1.44

This limitation is still present.

Setting to 'verified' as we are discontinuing the use of 'to be fixed later'.

I tested on 8.0.3 because frm is gone. The statement still fails, but with a very good error message:

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs