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 <ykess...@gmail.com> 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 <aureliju...@gmail.com>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> 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
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> --
>
>
>
>
--
