Error connecting to Mysql DB !

[Yannick]

Hello mate,
Did someone already had this error message below before ? I'm using
MySQL server but with Sqlite I don't have this error everything seems
to be alright...

Error traceback

Traceback (most recent call last):
File "/opt/web2py/gluon/main.py", line 365, in wsgibase
SQLDB.close_all_instances(SQLDB.rollback)
File "/opt/web2py/gluon/sql.py", line 474, in close_all_instances
action(instance)
File "/opt/web2py/gluon/sql.py", line 833, in rollback
self._connection.rollback()
ProgrammingError: (2014, "Commands out of sync; you can't run this
command now")



Please let me know.. Help needed !
Thanks

[mdipierro]

Which action cases this?

[Yannick]

Hello Massimo,
Basically I'm just trying to access the login page through a link like
this:

http://127.0.0.1:8000/AppName/default/login

Cheers,

[mdipierro]

Please email me your app and I'll take a look

[Yannick]

Hello Massimo,
I installed the latest trunk Version 1.57 from here (http://
code.google.com/p/web2py/source/checkout ) and I'm having the same
problem as before same error message on the same application when
trying to access the login page...
Here is the error:

Error traceback
Traceback (most recent call last):
File "/opt/web2py/gluon/main.py", line 365, in wsgibase
SQLDB.close_all_instances(SQLDB.rollback)

File "/opt/web2py/gluon/sql.py", line 504, in close_all_instances
action(instance)
File "/opt/web2py/gluon/sql.py", line 871, in rollback

self._connection.rollback()
ProgrammingError: (2014, "Commands out of sync; you can't run this
command now")

Do you please have any hint ?

Thanks a lot,
Yannick P.

[mdipierro]

I do not know why mysql refuses to complete this particular migration
that increases the default length of fields (required to avoid further
problems later).

If your data is not crytical, I suggest recreate the database (from
outside web2py) and erase the .table files in your app/databases
folder.

Massimo

[Yannick]

Thanks Massimo for your suggestion... NoThe data is not crytical now
since I still in the development-testing phase. I follow your advise
and it work fine now.
however I wonder how to deal with this kind of situation when I'll go
live with critical data...

Do you please have any suggestion how to avoid this situation in the
future.

Thanks again a lot for your help and advises

Cheers,
Yannick P.

[mdipierro]

there are three types of migrations:
1) adding a column
2) dropping a column
3) modifying a column

web2py in case 3 trying to move your data from the old table to the
new table. This seems to fail in MySQL. I do not why in this
particular case. Some times this fails for logical reasons (the field
contains text and you are changing it to date).

1) and 2) have no problems.

If you really need to do 3) and mysql refuses to do it automatically
then you may want add a new dummy column, copy the data there, drop
the old column, add the column again (altered), move the data back,
drop the dummy column.

I hope this makes sense.

If any MySQL expert has any more insight into this problem let me
know.

Massimo

[mdipierro]

I think I found the source of the problem in case 3 below and I fixed
it in trunk.
Other users should not have your problem any longer.
When you do a migration for your own table, make sure the field has a
default='value' else MySQL does not know what to put in the column
when it re-creates it, before it copies in the old values.

Massimo

[Yannick]

Thanks for your analysis :)

Cheers,
Yannick P.