I checked every line of the table creation, and have found the following
issues:
1) When using a reference to another table ('reference participant'), that
table has to be created before the referenced table. This was not a problem
with sqlite.
2) When using unique=True (e.g. Field('email',unique=True)) it will
complain about "Specified key was too long, max key..."
Am I missing something or is this correct behavior?
On Sunday, October 13, 2013 4:54:30 PM UTC+3, Niphlod wrote:
>
> there is no path2 field in the models you posted. are you sure that this
> is the correct traceback ?
> BTW, try to issue the create statement to the db to see what is the error,
> eventually trimming out the column that are references to external tables.
>
> On Sunday, October 13, 2013 3:00:57 PM UTC+2, Ykä Marjanen wrote:
>>
>> And the resulting ticket:
>>
>> Error ticket for "innopinion_01" Ticket ID
>>
>> 91.155.41.52.2013-10-13.12-54-52.7a8f0165-58f8-4ca8-960a-7fdcdc40d788
>> <class '_mysql_exceptions.OperationalError'> (1005, "Can't create table
>> 'ykamarjanen$dbname.campaign' (errno: 150)") Version web2py™ Version
>> 2.6.4-stable+timestamp.2013.09.22.01.43.37 Traceback
>>
>> 1.
>> 2.
>> 3.
>> 4.
>> 5.
>> 6.
>> 7.
>> 8.
>> 9.
>> 10.
>> 11.
>> 12.
>> 13.
>> 14.
>> 15.
>> 16.
>> 17.
>> 18.
>> 19.
>> 20.
>> 21.
>> 22.
>> 23.
>>
>> Traceback (most recent call last):
>> File "/home/ykamarjanen/web2py/gluon/restricted.py", line 217, in
>> restricted
>> exec ccode in environment
>> File "/home/ykamarjanen/web2py/applications/innopinion_01/models/db.py"
>> <https://ykamarjanen.pythonanywhere.com/admin/edit/innopinion_01/models/db.py>,
>> line 114, in <module>
>> Field('path2','text', filter_in=(lambda x: pickle.dumps(x)),
>> filter_out=(lambda s: s and pickle.loads(s)),default=None, readable=False,
>> writable=False))
>> File "/home/ykamarjanen/web2py/gluon/dal.py", line 7895, in define_table
>> table = self.lazy_define_table(tablename,*fields,**args)
>> File "/home/ykamarjanen/web2py/gluon/dal.py", line 7932, in
>> lazy_define_table
>> polymodel=polymodel)
>> File "/home/ykamarjanen/web2py/gluon/dal.py", line 1002, in create_table
>> self.create_sequence_and_triggers(query,table)
>> File "/home/ykamarjanen/web2py/gluon/dal.py", line 1816, in
>> create_sequence_and_triggers
>> self.execute(query)
>> File "/home/ykamarjanen/web2py/gluon/dal.py", line 1833, in execute
>> return self.log_execute(*a, **b)
>> File "/home/ykamarjanen/web2py/gluon/dal.py", line 1827, in log_execute
>> ret = self.cursor.execute(command, *a[1:], **b)
>> File "/usr/local/lib/python2.7/dist-packages/MySQLdb/cursors.py", line
>> 201, in execute
>> self.errorhandler(self, exc, value)
>> File "/usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py", line
>> 36, in defaulterrorhandler
>> raise errorclass, errorvalue
>> OperationalError: (1005, "Can't create table 'ykamarjanen$dbname.campaign'
>> (errno: 150)")
>>
>>
>>
>> On Sunday, October 13, 2013 3:59:08 PM UTC+3, Ykä Marjanen wrote:
>>>
>>> Niphlod,
>>>
>>> this is the log. The test table, that I created, passes fine, but the
>>> "campaign" table doesn't say "success". Is it about the FOREIGN KEY?
>>>
>>> <<<<< sql.log
>>> timestamp: 2013-10-13T12:23:25.789598
>>> CREATE TABLE tests(
>>> id INTEGER PRIMARY KEY AUTOINCREMENT,
>>> auth_table INTEGER REFERENCES auth_user (id) ON DELETE CASCADE,
>>> path2 TEXT
>>> );
>>> success!
>>> timestamp: 2013-10-13T12:29:03.020292
>>> CREATE TABLE campaign(
>>> id INT AUTO_INCREMENT NOT NULL,
>>> uuid VARCHAR(64),
>>> name VARCHAR(512),
>>> pagename VARCHAR(512),
>>> description LONGTEXT,
>>> created_by_auth INT, INDEX created_by_auth__idx (created_by_auth),
>>> FOREIGN KEY (created_by_auth) REFERENCES auth_user (id) ON DELETE CASCADE,
>>> created_by_participant INT, INDEX created_by_participant__idx
>>> (created_by_participant), FOREIGN KEY (created_by_participant) REFERENCES
>>> participant (id) ON DELETE CASCADE,
>>> cctype VARCHAR(512),
>>> privacy VARCHAR(512),
>>> registration VARCHAR(512),
>>> idea_submit VARCHAR(512),
>>> campaign_logo VARCHAR(512),
>>> campaign_start DATE,
>>> campaign_end DATE,
>>> creation_date DATETIME,
>>> twitter_feed CHAR(1),
>>> weekly_newsletter VARCHAR(512),
>>> weekly_contributor_status CHAR(1),
>>> weekly_contributor_nro INT,
>>> weekly_contributor_prize_description LONGTEXT,
>>> overall_contributor_status CHAR(1),
>>> overall_contributor_nro INT,
>>> overall_contributor_prize_description LONGTEXT,
>>> registered_participant_status CHAR(1),
>>> registered_participant_tasks INT,
>>> registered_participant_description LONGTEXT,
>>> free_prize_description LONGTEXT,
>>> status VARCHAR(512),
>>> nro_of_participants INT,
>>> nro_of_ideas INT,
>>> nro_of_ratings INT,
>>> nro_of_comments INT,
>>> additional_settings LONGTEXT,
>>> credits_settings LONGTEXT,
>>> rating_settings LONGTEXT,
>>> process_settings LONGTEXT,
>>> cpath LONGTEXT,
>>> PRIMARY KEY(id)
>>> ) ENGINE=InnoDB CHARACTER SET utf8;
>>>
>>> Ykä
>>>
>>> On Sunday, October 13, 2013 3:47:36 PM UTC+3, Niphlod wrote:
>>>>
>>>> inspect databases/sql.log, there are the statements used to create the
>>>> table there
>>>>
>>>> On Sunday, October 13, 2013 2:42:03 PM UTC+2, Ykä Marjanen wrote:
>>>>>
>>>>> Hi guys,
>>>>>
>>>>> I thought of using pythonanywhere for the next level of development,
>>>>> so deployed my code there. I had been using sqlite as a test environment,
>>>>> and wanted to move to the mysql database that pythonanywhere provides.
>>>>>
>>>>> I created a new mysql database and then switched the DAL connection to:
>>>>> db =
>>>>> DAL('mysql://ykamarjanen:PASS@mysql.server/ykamarjanen$DBNAME',pool_size=1,check_reserved=['all'])
>>>>>
>>>>> After running the web2py I got an error about the database (1005,
>>>>> can't create table, errno:150). I tried different variations, but could
>>>>> figure out what is wrong with my table.
>>>>>
>>>>> Here's my table, that is not created (it has some reserved names,
>>>>> which I changed):
>>>>>
>>>>> db.define_table('campaign',
>>>>> Field('uuid', length=64, default=lambda:str(uuid4())),
>>>>> Field('name'),
>>>>> Field('pagename'),
>>>>> Field('description','text', requires=IS_LENGTH(minsize=20,
>>>>> maxsize=500)),
>>>>> Field('created_by_auth','reference auth_user',
>>>>> default=auth.user_id, readable=False, writable=False),
>>>>> Field('created_by_participant','reference participant',
>>>>> readable=False, writable=False),
>>>>> Field('campaign_type', readable=False, writable=False),
>>>>>
>>>>> Field('privacy',requires=IS_IN_SET(['open','closed'],zero=T('Choose
>>>>> privacy')),default='open'),
>>>>>
>>>>> Field('registration',requires=IS_IN_SET(['open','invitation'],zero=T('Choose
>>>>>
>>>>> registration')),default='open'),
>>>>>
>>>>> Field('idea_submit',requires=IS_IN_SET(['anonymous','registered'],zero=T('Choose
>>>>>
>>>>> how ideas can be sent')),default='anonymous'),
>>>>> Field('campaign_logo',requires=IS_EMPTY_OR(IS_URL())),
>>>>>
>>>>> Field('campaign_start','date',requires=IS_DATE(),default=request.now),
>>>>> Field('campaign_end','date',requires=IS_DATE()),
>>>>> Field('creation_date','datetime',default=request.now),
>>>>> Field('twitter_feed','boolean',default=False),
>>>>>
>>>>> Field('weekly_newsletter',requires=IS_IN_SET(['Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'],zero=T('Pick
>>>>>
>>>>> a day for newsletter')),default='Monday'),
>>>>> Field('weekly_contributor_status','boolean',default=True),
>>>>>
>>>>> Field('weekly_contributor_nro','integer',requires=IS_IN_SET(range(0,20),zero=T('Select
>>>>>
>>>>> the number of participants included in the raffle'))),
>>>>> Field('weekly_contributor_prize_description','text'),
>>>>> Field('overall_contributor_status','boolean',default=True),
>>>>>
>>>>> Field('overall_contributor_nro','integer',requires=IS_IN_SET(range(0,20),zero=T('Select
>>>>>
>>>>> the number of participants included in the raffle'))),
>>>>> Field('overall_contributor_prize_description','text'),
>>>>> Field('registered_participant_status','boolean',default=True),
>>>>>
>>>>> Field('registered_participant_tasks','integer',requires=IS_IN_SET(range(0,20),zero=T('Select
>>>>>
>>>>> the number of minimum tasks for the prize raffle'))),
>>>>> Field('registered_participant_description','text'),
>>>>> Field('free_prize_description','text'),
>>>>> Field('status',
>>>>> requires=IS_IN_SET(['waiting','running','halted','ended']),
>>>>> readable=False,
>>>>> writable=False), #waiting, running, halted, ended
>>>>> Field('nro_of_participants','integer',default=0, readable=False,
>>>>> writable=False),
>>>>> Field('nro_of_ideas','integer',default=0, readable=False,
>>>>> writable=False),
>>>>> Field('nro_of_ratings','integer',default=0, readable=False,
>>>>> writable=False),
>>>>> Field('nro_of_comments','integer',default=0, readable=False,
>>>>> writable=False),
>>>>> Field('additional_settings','text', filter_in=(lambda x:
>>>>> pickle.dumps(x)), filter_out=(lambda s: s and
>>>>> pickle.loads(s)),default=None, readable=False, writable=False),
>>>>> Field('credits_settings','text', filter_in=(lambda x:
>>>>> pickle.dumps(x)), filter_out=(lambda s: s and
>>>>> pickle.loads(s)),default=None, readable=False, writable=False),
>>>>> Field('rating_settings','text', filter_in=(lambda x:
>>>>> pickle.dumps(x)), filter_out=(lambda s: s and
>>>>> pickle.loads(s)),default=None, readable=False, writable=False),
>>>>> Field('process_settings','text', filter_in=(lambda x:
>>>>> pickle.dumps(x)), filter_out=(lambda s: s and
>>>>> pickle.loads(s)),default=None, readable=False, writable=False),
>>>>> Field('path','text', filter_in=(lambda x: pickle.dumps(x)),
>>>>> filter_out=(lambda s: s and pickle.loads(s)),default=None,
>>>>> readable=False,
>>>>> writable=False))
>>>>>
>>>>> Ykä
>>>>>
>>>>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
