Thanks Niphlod,
I've been happily using SQLite for testing and smaller projects, so
switching to MySQL was a new thing (I'd like to use PostgreSQL in the
future). I was expecting it to work by just switching the initialization
line, as it was working fine with SQLite. But I learned new stuff, again.
Ykä
On Sunday, October 13, 2013 9:42:50 PM UTC+3, Niphlod wrote:
>
> mysql has a very known problem of not handling unique keys longer than 255
> chars.
> As specified in the book and as a best practice, ALWAYS specify a length
> for 'string' fields. In the case of mysql, if those fields are unique, the
> maximum length is 255.
>
> On the other issue (creating the referencing table before the referenced
> one): seems impossible to let users digest this fact (seems pretty obvious
> to me), but you CAN'T (in ANY serious backend) specify a relationship
> towards something that doesn't exist.
> SQLite has no referential integrity turned on by default, so it skips the
> issue entirely. Remember, SQLite is a wonderful db but is tiny and has its
> own limitations.
>
>
> On Sunday, October 13, 2013 6:36:16 PM UTC+2, Ykä Marjanen wrote:
>>
>> I got everything working, when I moved the table order so that all tables
>> that are referenced later are at the beginning.
>>
>> Additionally I had to declare the "unique" value after creating the
>> table, so:
>>
>> Not working:
>> db.define_table('tablename',Field('name',unique=True)
>>
>> Working:
>> db.define_table('tablename',Field('name')
>> db.tablename.name.unique = True
>>
>> Is this correct behavior?
>>
>> Ykä
>>
>> 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:[email protected]/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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
