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,'countries.id
>>>>> ',db.countries._format,multiple=True)
>>>>> db.company.exports_wants_to.requires=IS_IN_DB(db,'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<javascript:>
>>>>>>>>>>>>>> >
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> 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.**ProgrammingError\'>
>>>>>>>>>>>>>>>> (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/**applications/init/models/db_**
>>>>>>>>>>>>>>>> comp.py'
>>>>>>>>>>>>>>>> p5
>>>>>>>>>>>>>>>> sS'code'
>>>>>>>>>>>>>>>> p6
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> thnx
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
--
