@Aurelijus- If you're dealing with foreign key errors you may have to clean the constraints up manually in MySQL- It's actually pretty easy to corrupt the foreign key constraints with web2py's db definitions, and cleanup is a bitch if you don't know exactly what to do. See my post on this, and follow the link within: https://groups.google.com/d/msg/web2py/ztQLASakiYg/UtT8Yi3zH1sJ
On Wednesday, August 8, 2012 8:22:18 AM UTC-4, Aurelijus Useckas wrote: > > Thanx Yarin, I've fixed this one. There are no more reserved words in the > table or field definitions. Nevertheless it returns an error 1005 / 150, > which refer to: "table creation failed because a foreign key constraint > was not correctly formed". > If I get rid of any refering fields it works fine. Somehow the mysql > doesn't recognise/understand the refering definitions. > > On Wed, Aug 8, 2012 at 4:08 AM, Yarin <[email protected] <javascript:>>wrote: > >> Massimo- steps to reproduce (using rocket/mysql/OSX): >> >> 1) In db.py pointing to MySQL: >> >> db.define_table('bad', >> Field('field_a', 'string'), >> Field('long', 'string') >> ) >> >> 2) Make a request. >> >> The app will error out and SQL syntax error will be ticketed. >> >> 3) Make another request. >> >> App requests now hang. Other apps are semi-hung (it's strange- i could >> open example app, but not others). App admin still works (?). The web2py >> process cannot be terminated and must be killed. >> >> (I brush up against this whenever I do something MySQL doesn't like- >> altering two tables at once in db.py, etc...) >> >> On Tuesday, August 7, 2012 5:42:30 PM UTC-4, Massimo Di Pierro wrote: >> >>> can you reproduce. If this is a web2py problem, it needs to be fixed. >>> >>> On Tuesday, 7 August 2012 10:41:27 UTC-5, Yarin wrote: >>> >>>> Think it actually does return a non-specific SQL syntax error that gets >>>> ticketed by web2py- but web2py never goes to an error screen and the >>>> browser just hangs- if i recall.. >>>> >>>> On Tuesday, August 7, 2012 11:30:37 AM UTC-4, Massimo Di Pierro wrote: >>>> >>>>> Damn MySQL. It should not hang, it should return a operationalerror. >>>>> >>>>> >>>>> >>>>> On Tuesday, 7 August 2012 10:03:44 UTC-5, Yarin wrote: >>>>> >>>>>> Change the name of in your Field('long') field in maps table - it's >>>>>> a reserved keyword in MySQL and will cause it to hang. >>>>>> >>>>>> On Tuesday, August 7, 2012 10:57:07 AM UTC-4, Aurelijus Useckas wrote: >>>>>> >>>>>>> Here it is: >>>>>>> >>>>>>> db.define_table('tag', >>>>>>> Field('tag', notnull=True, unique=True), >>>>>>> format='%(tag)s') >>>>>>> >>>>>>> db.define_table('city', >>>>>>> Field('name', notnull=True, unique=True), >>>>>>> format='%(name)s') >>>>>>> >>>>>>> db.define_table('maps', >>>>>>> Field('name'), >>>>>>> Field('last_name'), >>>>>>> Field('long'), >>>>>>> Field('lat')) >>>>>>> >>>>>>> >>>>>>> db.define_table('asoc', >>>>>>> Field('name', label='Pavadinimas', unique=True), >>>>>>> Field('City', 'list:reference city', label='Miestas'), >>>>>>> Field('address', label='Adresas'), >>>>>>> Field('www'), >>>>>>> Field('tel'), >>>>>>> Field('fax'), >>>>>>> Field('email'), >>>>>>> Field('President', label='Asociacijos prezidentas'), >>>>>>> Field('CEO', label='Asociacijos generalinis'), >>>>>>> Field('CEO_tel', label='Generalinio tel.'), >>>>>>> Field('CEO_email', label='Generalinio email'), >>>>>>> Field('CEO_mob', label='Generalinio mobilus'), >>>>>>> Field('Pres_tel', label='Prezidento tel.'), >>>>>>> Field('Pres_email', label='Prezidento email'), >>>>>>> Field('Pres_mob', label='Prezidento mobilus'), >>>>>>> Field('submited_at', 'datetime', default=request.now, >>>>>>> writable=False, readable=False), >>>>>>> Field('updated_at', 'datetime', default=request.now, >>>>>>> update=request.now, writable=False, readable=False), >>>>>>> Field('submited_by', db.auth_user, default=auth.user_id, >>>>>>> writable=False, readable=False), >>>>>>> Field('updated_by', db.auth_user, update=auth.user_id, >>>>>>> writable=False, readable=False), >>>>>>> format='%(name)s') >>>>>>> >>>>>>> db.asoc.email.requires=IS_**EMPTY_OR(IS_EMAIL()) >>>>>>> db.asoc.CEO_email.requires=IS_**EMPTY_OR(IS_EMAIL()) >>>>>>> db.asoc.Pres_email.requires=**IS_EMPTY_OR(IS_EMAIL()) >>>>>>> db.asoc.name.requires=IS_NOT_**EMPTY() >>>>>>> >>>>>>> db.define_table('countries', >>>>>>> Field('Country', notnull=True, unique=True), >>>>>>> Field('latitude', readable=True, writable=False), >>>>>>> Field('longitude', readable=True, writable=False), >>>>>>> format = '%(Country)s' >>>>>>> ) >>>>>>> >>>>>>> def horizontal_checkboxes(f,v): >>>>>>> horizontal_widget = SQLFORM.widgets.checkboxes.** >>>>>>> widget(f,v,cols=4) >>>>>>> return locals() >>>>>>> >>>>>>> db.define_table('company', >>>>>>> Field('title', label='Pavadinimas', unique=True), >>>>>>> Field('CEO', label='Generalinis dir.'), >>>>>>> Field('code', 'integer', label='Įmonės kodas',unique=True, >>>>>>> default=None), >>>>>>> Field('revenue', 'integer', label='Metinė apyvarta (mln.)'), >>>>>>> Field('workers', 'integer', label='Darbuotojų skaičius'), >>>>>>> # Field('logo', 'upload', label='Įmonės logo'), >>>>>>> Field('produce_tag', 'list:reference tag', label='Produkcija', >>>>>>> default=None), >>>>>>> Field('produce', label='Produkcija (smulkiau)'), >>>>>>> Field('Email', default=None, notnull=False), >>>>>>> Field('Tel'), >>>>>>> Field('Fax'), >>>>>>> Field('www'), >>>>>>> Field('City', 'list:reference city', label='Miestas'), >>>>>>> Field('address', label='Adresas'), >>>>>>> Field('extra_contacts', 'boolean', label='Pridėti kontakt. >>>>>>> duomenų'), >>>>>>> Field('extra_contacts_2', 'boolean', label='Pridėti kontakt. >>>>>>> duomenų'), >>>>>>> Field('name_1', label='Vardas'), >>>>>>> Field('pareigos_1', label='Pareigos'), >>>>>>> Field('tel_1', label='Papildomas tel.'), >>>>>>> Field('email_1', label='Papildomas el. paštas'), >>>>>>> Field('name_2', label='Vardas'), >>>>>>> Field('pareigos_2', label='Pareigos'), >>>>>>> Field('tel_2', label='Papildomas tel.'), >>>>>>> Field('email_2', label='Papildomas el. paštas'), >>>>>>> Field('Association', db.asoc, label='Priklauso asociacijai'), >>>>>>> Field('other', 'text', label='Papildoma info'), >>>>>>> Field('submited_at', 'datetime', default=request.now, >>>>>>> writable=False, readable=False), >>>>>>> Field('submited_by', db.auth_user, default=auth.user_id, >>>>>>> writable=False, readable=False), >>>>>>> Field('updated_at', 'datetime', update=request.now, >>>>>>> writable=False, readable=False), >>>>>>> Field('updated_by', db.auth_user, update=auth.user_id, >>>>>>> writable=False, readable=False), >>>>>>> Field('exports_wants_to', 'list:reference countries', >>>>>>> label='Domina šalys'), >>>>>>> Field('exports_to', 'list:reference countries', >>>>>>> label='Eksportuoja į', required=False), >>>>>>> Field('imports_from', 'list:reference countries', >>>>>>> label='Importuoja iš', required=False), >>>>>>> format = '%(title)s') >>>>>>> >>>>>>> from plugin_multiselect_widget import ( >>>>>>> hmultiselect_widget, vmultiselect_widget, >>>>>>> rhmultiselect_widget, rvmultiselect_widget, >>>>>>> ) >>>>>>> >>>>>>> db.company.exports_to.widget = hmultiselect_widget >>>>>>> db.company.imports_from.widget = hmultiselect_widget >>>>>>> db.company.exports_wants_to.**widget = hmultiselect_widget >>>>>>> >>>>>>> db.company.produce_tag.widget = lambda field,value: \ >>>>>>> SQLFORM.widgets.checkboxes.**widget(field,value,cols=6) >>>>>>> >>>>>>> db.company.Email.requires=IS_**EMPTY_OR(IS_EMAIL(error_**message='El. >>>>>>> paštas!')) >>>>>>> db.company.email_1.requires=**IS_EMPTY_OR(IS_EMAIL()) >>>>>>> db.company.email_2.requires=**IS_EMPTY_OR(IS_EMAIL()) >>>>>>> db.company.code.requires=IS_**EMPTY_OR(IS_NOT_IN_DB(db, >>>>>>> db.company.code)) >>>>>>> db.company.exports_to.**requires=IS_EMPTY_OR(IS_IN_DB(**db, >>>>>>> db.countries.Country)) >>>>>>> db.company.exports_to.**requires=IS_IN_DB(db,'countrie**s.id<http://countries.id> >>>>>>> ',db.countries._format,**multiple=True) >>>>>>> db.company.exports_wants_to.**requires=IS_IN_DB(db,'countrie**s.id<http://countries.id> >>>>>>> ',db.countries._format,**multiple=True) >>>>>>> db.company.produce_tag.**requires=IS_IN_DB(db,'tag.id',** >>>>>>> db.tag._format,multiple=True) >>>>>>> >>>>>>> db.company.Association.**requires=IS_IN_DB(db,'asoc.id'**,db.asoc._format, >>>>>>> >>>>>>> zero='-----Pasirinkti-----') >>>>>>> >>>>>>> db.define_table('comment', >>>>>>> Field('body','text',label='**Your comment'), >>>>>>> Field('company', db.company, 'list: reference company', >>>>>>> readable=False, writable=False), >>>>>>> Field('posted_on','datetime',**default=request.now), >>>>>>> Field('posted_by', db.auth_user, 'list: reference auth_user', >>>>>>> default=auth.user_id)) >>>>>>> >>>>>>> db.comment.posted_on.writable=**db.comment.posted_on.readable=** >>>>>>> False >>>>>>> db.comment.posted_by.writable=**db.comment.posted_by.readable=** >>>>>>> False >>>>>>> db.comment.company.default = request.args(0) >>>>>>> >>>>>>> >>>>>>> >>>>>>> On Tuesday, August 7, 2012 5:36:49 PM UTC+3, Massimo Di Pierro wrote: >>>>>>> >>>>>>>> Can you please post the entire mode? My guess is an issue with >>>>>>>> capitalization perhaps combined with corruption of table files. >>>>>>>> >>>>>>>> On Tuesday, 7 August 2012 06:00:06 UTC-5, Aurelijus Useckas wrote: >>>>>>>> >>>>>>>>> I've tried nearly any possible syntax, but with no success. Any >>>>>>>>> line with the reference to other table causes an error 1005 / 150: >>>>>>>>> "table creation failed because a foreign key constraint was not >>>>>>>>> correctly formed" >>>>>>>>> Wasted a lot of time on it with no avail, guess will be forced to >>>>>>>>> go back to ol' SQLite :) >>>>>>>>> >>>>>>>>> On Wednesday, August 1, 2012 8:03:05 PM UTC+3, Massimo Di Pierro >>>>>>>>> wrote: >>>>>>>>> >>>>>>>>>> Try >>>>>>>>>> >>>>>>>>>> Field('City', 'list:reference City', label='Miestas'), >>>>>>>>>> >>>>>>>>>> On Wednesday, 1 August 2012 11:05:02 UTC-5, Aurelijus Useckas >>>>>>>>>> wrote: >>>>>>>>>> >>>>>>>>>>> tried it, still the same error :( thnx anyway >>>>>>>>>>> >>>>>>>>>>> On Wednesday, August 1, 2012 6:31:52 PM UTC+3, Massimo Di Pierro >>>>>>>>>>> wrote: >>>>>>>>>>> >>>>>>>>>>>> Yes. this is wrong: >>>>>>>>>>>> >>>>>>>>>>>> Field('City', db.city, 'list:reference city', label='Miestas'), >>>>>>>>>>>> >>>>>>>>>>>> should be >>>>>>>>>>>> >>>>>>>>>>>> Field('City', 'list:reference city', label='Miestas'), >>>>>>>>>>>> >>>>>>>>>>>> On Wednesday, 1 August 2012 09:15:07 UTC-5, Aurelijus Useckas >>>>>>>>>>>> wrote: >>>>>>>>>>>> >>>>>>>>>>>>> Thank you Massimo, but the 1005/150 mysql problem seems to be >>>>>>>>>>>>> smth wring with foreign key (merging of tables). Are there any >>>>>>>>>>>>> flaws in my >>>>>>>>>>>>> DAL syntax? >>>>>>>>>>>>> >>>>>>>>>>>>> This line seems to be the trigger: >>>>>>>>>>>>> Field('City', db.city, 'list:reference city', label='Miestas'), >>>>>>>>>>>>> >>>>>>>>>>>>> it references: >>>>>>>>>>>>> >>>>>>>>>>>>> db.define_table('city', >>>>>>>>>>>>> Field('name', notnull=True, unique=True), >>>>>>>>>>>>> format='%(name)s') >>>>>>>>>>>>> >>>>>>>>>>>>> any ideas what's bothering mysql? >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> On Wednesday, August 1, 2012 4:56:03 PM UTC+3, Massimo Di >>>>>>>>>>>>> Pierro wrote: >>>>>>>>>>>>> >>>>>>>>>>>>>> db = DAL('mysql://....', check_**reserved=['common','mysql']) >>>>>>>>>>>>>> >>>>>>>>>>>>>> Anyway, I am not sure your problem is that you are using a >>>>>>>>>>>>>> reserved keyword. Perhaps the table exists already. I am also >>>>>>>>>>>>>> surprised you >>>>>>>>>>>>>> are getting a pymysql InternalError and not an OperationalError. >>>>>>>>>>>>>> >>>>>>>>>>>>>> On Wednesday, 1 August 2012 07:45:01 UTC-5, Aurelijus Useckas >>>>>>>>>>>>>> wrote: >>>>>>>>>>>>>> >>>>>>>>>>>>>>> I've changed the long into longitude and it seems to be >>>>>>>>>>>>>>> solved but I still get errors: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> p2 >>>>>>>>>>>>>>> S'<class \'gluon.contrib.pymysql.err.**InternalError\'> >>>>>>>>>>>>>>> (1005, u"Can\'t create table \'./lpkdb/asoc.frm\' (errno: >>>>>>>>>>>>>>> 150)")' >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Now it says it cannot create next table, which is: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> db.define_table('asoc', >>>>>>>>>>>>>>> Field('name', label='Pavadinimas', unique=True), >>>>>>>>>>>>>>> Field('City', db.city, 'list:reference city', >>>>>>>>>>>>>>> label='Miestas'), >>>>>>>>>>>>>>> Field('address', label='Adresas'), >>>>>>>>>>>>>>> Field('www'), >>>>>>>>>>>>>>> Field('tel'), >>>>>>>>>>>>>>> Field('fax'), >>>>>>>>>>>>>>> Field('email'), >>>>>>>>>>>>>>> Field('President', label='Asociacijos prezidentas'), >>>>>>>>>>>>>>> Field('CEO', label='Asociacijos generalinis'), >>>>>>>>>>>>>>> Field('CEO_tel', label='Generalinio tel.'), >>>>>>>>>>>>>>> Field('CEO_email', label='Generalinio email'), >>>>>>>>>>>>>>> Field('CEO_mob', label='Generalinio mobilus'), >>>>>>>>>>>>>>> Field('Pres_tel', label='Prezidento tel.'), >>>>>>>>>>>>>>> Field('Pres_email', label='Prezidento email'), >>>>>>>>>>>>>>> Field('Pres_mob', label='Prezidento mobilus'), >>>>>>>>>>>>>>> Field('submited_at', 'datetime', default=request.now, >>>>>>>>>>>>>>> writable=False, readable=False), >>>>>>>>>>>>>>> Field('updated_at', 'datetime', default=request.now, >>>>>>>>>>>>>>> update=request.now, writable=False, readable=False), >>>>>>>>>>>>>>> Field('submited_by', db.auth_user, default=auth.user_id, >>>>>>>>>>>>>>> writable=False, readable=False), >>>>>>>>>>>>>>> Field('updated_by', db.auth_user, update=auth.user_id, >>>>>>>>>>>>>>> writable=False, readable=False), >>>>>>>>>>>>>>> format='%(name)s') >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Are there some guidlines on how to construct DAL field names >>>>>>>>>>>>>>> in order to avoid any conflict while migrating to mysql? >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> On Wednesday, August 1, 2012 3:36:51 PM UTC+3, tomasz >>>>>>>>>>>>>>> bandura wrote: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Is it 'long' a mysql's reserved word? >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Regards >>>>>>>>>>>>>>>> Tomasz >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> 2012/8/1 Aurelijus Useckas <[email protected]> >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> this is the trigger i guess: >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> db.define_table('maps', >>>>>>>>>>>>>>>>> Field('name'), >>>>>>>>>>>>>>>>> Field('last_name'), >>>>>>>>>>>>>>>>> Field('long'), >>>>>>>>>>>>>>>>> Field('lat'), >>>>>>>>>>>>>>>>> format='%(name)s') >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> On Wednesday, August 1, 2012 3:30:40 PM UTC+3, Aurelijus >>>>>>>>>>>>>>>>> Useckas wrote: >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> Hi, >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> I've built the app on SQLite and now want to migrate to >>>>>>>>>>>>>>>>>> the server based mysql. BUT web2py hangs in the middle of >>>>>>>>>>>>>>>>>> creating, >>>>>>>>>>>>>>>>>> basicaly just after the auth tables. When I restart the >>>>>>>>>>>>>>>>>> apache I can see >>>>>>>>>>>>>>>>>> auth tables as well as a few from my app. >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> this is the err ticket that I get: >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> (dp1 >>>>>>>>>>>>>>>>>> S'output' >>>>>>>>>>>>>>>>>> p2 >>>>>>>>>>>>>>>>>> S'<class \'gluon.contrib.pymysql.err.**Pr**ogrammingError\'> >>>>>>>>>>>>>>>>>> (1064, u"You have an error in your SQL syntax; check the >>>>>>>>>>>>>>>>>> manual that >>>>>>>>>>>>>>>>>> corresponds to your MySQL server version for the right >>>>>>>>>>>>>>>>>> syntax to use near >>>>>>>>>>>>>>>>>> \'long VARCHAR(255),\\n lat VARCHAR(255),\\n PRIMARY >>>>>>>>>>>>>>>>>> KEY(id)\\n) >>>>>>>>>>>>>>>>>> ENGINE=InnoDB CHA\' at line 5")' >>>>>>>>>>>>>>>>>> p3 >>>>>>>>>>>>>>>>>> sS'layer' >>>>>>>>>>>>>>>>>> p4 >>>>>>>>>>>>>>>>>> S'/opt/web-apps/web2py/**applica**tions/init/models/db_** >>>>>>>>>>>>>>>>>> comp.py' >>>>>>>>>>>>>>>>>> p5 >>>>>>>>>>>>>>>>>> sS'code' >>>>>>>>>>>>>>>>>> p6 >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> thnx >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> -- >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> -- >> >> >> >> > > --
