Hi,

I have a difficult problem. When I try to create a table I getting an
error message "1117: too many columns". Well, the table have more than
1300 columns, according to

http://dev.mysql.com/doc/refman/5.1/...trictions.html

there is a limit for InnoDB (and probably also MyISAM) tables of 1000
columns. Problem - I can't split the table because in that case I have
to restructure my whole framework. Anybody know's a solution? Probably
another Storage engine?

Thanks,

Matthias

On 6 Aug, 16:16, Matthias Braun <mat_br...@web.de> wrote:
> Hi,
>
> I have a difficult problem. When I try to create a table I getting an
> error message "1117: too many columns". Well, the table have more than
> 1300 columns, according to
>
> http://dev.mysql.com/doc/refman/5.1/...trictions.html
>
> there is a limit for InnoDB (and probably also MyISAM) tables of 1000
> columns. Problem - I can't split the table because in that case I have
> to restructure my whole framework. Anybody know's a solution? Probably
> another Storage engine?
>
> Thanks,
>
> Matthias

You must have a legitimate reason, but you've got to wonder at the
need for 1300 columns.

On Mon, 06 Aug 2007 17:16:07 +0200, Matthias Braun wrote:
> Hi,
>
> I have a difficult problem. When I try to create a table I getting an
> error message "1117: too many columns". Well, the table have more than
> 1300 columns, according to
>
> http://dev.mysql.com/doc/refman/5.1/...trictions.html
>
> there is a limit for InnoDB (and probably also MyISAM) tables of 1000
> columns.

The documentation doesn't mention a limit, and a bit of digging shows
that the number of columns a table has is stored in the MyISAM .myi file
in four bytes. That kind of hints that two wasn't big enough. (The
number of blobs, for contrast, is stored in two bytes.)

> Problem - I can't split the table because in that case I have
> to restructure my whole framework. Anybody know's a solution? Probably
> another Storage engine?

Honestly, if you're getting to the point where you have rows even
numbering in the hundreds, much less thousands, you probably ought to be
rethinking the design anyway, Is this some kind of QUALITY_867 kind of
deal that might be better addressed pivoted around vertically and then
joined in?

--
6. I will not gloat over my enemies' predicament before killing them.
--Peter Anspach's list of things to do as an Evil Overlord

Hi Peter,

could you explain how this pivot and pivoted around vertically works?

All,

the table structure is already normalized. The huge structure is for a
telecommunication switch producing for each 15 minutes exact 1300 data
sets per logical node. So splitting tables means getting rid of
normalization.

Matthias

Peter H. Coffin schrieb:
> On Mon, 06 Aug 2007 17:16:07 +0200, Matthias Braun wrote:
>> Hi,
>>
>> I have a difficult problem. When I try to create a table I getting an
>> error message "1117: too many columns". Well, the table have more than
>> 1300 columns, according to
>>
>> http://dev.mysql.com/doc/refman/5.1/...trictions.html
>>
>> there is a limit for InnoDB (and probably also MyISAM) tables of 1000
>> columns.
>
> The documentation doesn't mention a limit, and a bit of digging shows
> that the number of columns a table has is stored in the MyISAM .myi file
> in four bytes. That kind of hints that two wasn't big enough. (The
> number of blobs, for contrast, is stored in two bytes.)
>
>> Problem - I can't split the table because in that case I have
>> to restructure my whole framework. Anybody know's a solution? Probably
>> another Storage engine?
>
> Honestly, if you're getting to the point where you have rows even
> numbering in the hundreds, much less thousands, you probably ought to be
> rethinking the design anyway, Is this some kind of QUALITY_867 kind of
> deal that might be better addressed pivoted around vertically and then
> joined in?
>

On Mon, 06 Aug 2007 18:52:43 +0200, Matthias Braun <mat_braun@web.de>
wrote:

>All,
>
>the table structure is already normalized. The huge structure is for a
>telecommunication switch producing for each 15 minutes exact 1300 data
>sets per logical node. So splitting tables means getting rid of
>normalization.
>
>Matthias

Instead of:

tblNode
-------
tblNode_Id (unique primary key - ex: 1234)
tblNode_DataSet_0001 (ex: "98.6")
....
tblNode_DataSet_1300

Try:

tblNode
-------
tblNode_Id (unique primary key - ex: 1234)

tblDataSet
----------
tblDataSet_Id (unique primary key - ex: 9876)
tblDataSet_tblNode_Id (ex: 1234) *
tblDataSet_Name (ex: 0001) *
tblDataSet_Value (ex: "98.6")

* 2nd and 3rd columns together make a unique secondary key

--

Dennis

On Mon, 06 Aug 2007 13:44:23 -0400, Dennis wrote:
> On Mon, 06 Aug 2007 18:52:43 +0200, Matthias Braun <mat_braun@web.de>
> wrote:
>
>>All,
>>
>>the table structure is already normalized. The huge structure is for a
>>telecommunication switch producing for each 15 minutes exact 1300 data
>>sets per logical node. So splitting tables means getting rid of
>>normalization.
>>
>>Matthias
>
> Instead of:
>
> tblNode
> -------
> tblNode_Id (unique primary key - ex: 1234)
> tblNode_DataSet_0001 (ex: "98.6")
> ...
> tblNode_DataSet_1300
>
> Try:
>
> tblNode
> -------
> tblNode_Id (unique primary key - ex: 1234)
>
> tblDataSet
> ----------
> tblDataSet_Id (unique primary key - ex: 9876)
> tblDataSet_tblNode_Id (ex: 1234) *
> tblDataSet_Name (ex: 0001) *
> tblDataSet_Value (ex: "98.6")
>
> * 2nd and 3rd columns together make a unique secondary key

Bingo. That's it exactly.

--
6. I will not gloat over my enemies' predicament before killing them.
--Peter Anspach's list of things to do as an Evil Overlord