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: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.