Table creation error 139 using MySql

[Richard]

I am hosting my app on python anywhere and get sometimes the following error:

<class '_mysql_exceptions.OperationalError'> (1005, "Can't create table 'richdijk$imanops03d.form_iv_9cf75329ccbc' (errno: 139)")

when creating a new table using:

    issue_var_fields= [
                    Field( "d"+str(db[issue_var_table][date_row.id]['iv_date']).replace("-", ""),
                          issue_var.data_type,
                          default=db[issue_var_table][date_row.id]['iv_value'],
                          label = str(db[issue_var_table][date_row.id]['iv_date'])
                         )
                     for date_row in db(db[issue_var_table]).select()
                     ]
    db.define_table('form_%s'%issue_var_table, *issue_var_fields)

What may be the cause of this when the error occur? Sometimes the table is created without an error.

[Niphlod]

ask them, it's an error on mysql side, not web2py's. BTW: creating temporary tables at runtime is the dumbest thing since the invention of ORMs. 

[Leonel Câmara]

You're probably going over the column limit. This seems stupid. What are you trying to achieve?

[Richard]

Leonel and Niphlod,

I want to populate a table that is dynamically created have 2 colmuns and an unknown but limited amount of rows.
Using SQLform as input was kind of a clever solution, I thought. This is apparently not the case. Any suggestion would be welcome.

Richard D

[Leonel Câmara]

I find that creating tables dynamically is almost always not what you want or need. What are you really trying to do feature wise?

[Richard]

I'll explain, the app we are building is a generic management framework which will be used in many different domains because the theory it is based on is domain invariant. Part of the functionality is capturing dynamics through time sequences. These time sequences are stored in dynamically created tables, this works fine. Adding and changing data to these tables is the subject of this 139 error.

In short: how to populate a table with 2 columns having an unknown amount of rows.
In very short: grid functionality.

[Leonel Câmara]

I still don't see the need for dynamic tables. Why do timesequences need to be dynamic tables? Where do you store the table definitions for the dynamically created tables?

It's a huge mess to go at it that way. 

Couldn't this suffice?

db.define_table('timesequence',

    Field('sort_position', 'integer'),

    Field('parent_id', 'reference timesequence'),

    Field('name'),

)

This way you have timesequences which may have any number of children timesequences which are ordered.

[Richard]

I have a table which contains the tablenames and table meta-data. This table is used for defining the table programmatically.
A table per time sequence is needed because I want to have security levels on those table devided in view and edit.

Sorry , but I do not understand your idea for the timesequence table.

[Leonel Câmara]

 table per time sequence is needed because I want to have security levels on those table devided in view and edit.

Why do you need a new table for that? You can create whatever permissions you want, if you need you can create read and update permissions per record.

[Richard]

Leonel,

You are right, the most important message you and Niphlod give me is the bad design of the dynamic tables and the need for migration=True.
Is this conclusion correct?

If so, I'll redesign the functionality towards a table containing the time sequences and the relation to a table with the meta data.

regards,
Richard D

[Niphlod]

sorry for being - probably - overzealous, but you're not trying to create a table with 2 columns with an indefinite number of rows. You're doing the exact opposite: creating a table with an indefinite number of columns. This is confirmed by the fact that the variable in your code is indeed *issue_var_field and the error you're facing (if you care to read mysql manual, you'll find about innodb storage per-row limitations).
At this point, I strongly suggest to reengineer your code to use a fixed number of columns ^_^'

[Richard]

No hard feelings, the coding was very simple and out of the box, performing perfectly until ....
But I agree, not a really production proof design.

I already redesigned the functionality using SQLFORM.factory and using an existing table for the time-sequences.