Difficulty with mysql migration

[richard...@verizon.net]

I'm migrating an existing web2py app from sqlite to mysql. The sqlite
version runs fine. The mysql version chokes on the date and datetime
fields:

Here's the table:
db.define_table('x',
db.Field('name','string',length=25,requires=[IS_NOT_EMPTY
(),IS_NOT_IN_DB(db,'x.name')]),
db.Field('f1','datetime',requires=IS_DATETIME()),
db.Field('f2','datetime',requires=IS_DATETIME()),
db.Field('f3','string',requires=IS_IN_SET(range(1,10))),
db.Field('f4','date',requires=IS_NULL_OR(IS_DATE())),
)

Here's the traceback message:
Traceback (most recent call last):
File "gluon/restricted.py", line 107, in restricted
File "C:/web2py/applications/SPiNup/models/1_tables.py", line 57, in
<module>
File "gluon/sql.py", line 993, in define_table
File "gluon/sql.py", line 1314, in _create
File "gluon/sql.py", line 731, in <lambda>
File "MySQLdb\cursors.pyc", line 166, in execute
File "MySQLdb\connections.pyc", line 35, in defaulterrorhandler
ProgrammingError: (1064, "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 'repeat VARCHAR(32),\n\trstop DATE,\n\tPRIMARY KEY
(id)\n) ENGINE=InnoDB CHARACTER SET ' at line 6")

Line 57 in 1_tables.py is the db.Field('f4','date'... ) field. If I
comment out the f4 date field, I get the same problem on the f2 field,
which is a datetime.

Anybody have an idea what the problem is? Could my installation be bad?

[mdipierro]

Can you post your sql.log file? It is in the applicaitons/yourapp/
databases folder.

Massimo

On Jun 20, 10:35 pm, "richard_gor...@verizon.net"

[richard...@verizon.net]

timestamp: 2009-06-21T00:28:27.156000
CREATE TABLE x(
id INT AUTO_INCREMENT NOT NULL,
f1 VARCHAR(25),
f2 DATETIME,
f3 DATETIME,
f4 VARCHAR(32),
f5 DATE,
PRIMARY KEY(id)
) ENGINE=InnoDB CHARACTER SET utf8;

[mdipierro]

Can you run this from the mysql shell and see what you get?

CREATE TABLE x(
id INT AUTO_INCREMENT NOT NULL,
f1 VARCHAR(25),
f2 DATETIME,
f3 DATETIME,
f4 VARCHAR(32),
f5 DATE,
PRIMARY KEY(id)
) ENGINE=InnoDB CHARACTER SET utf8;

I do not think this is not a web2py issue.

massimo

On Jun 20, 11:30 pm, "richard_gor...@verizon.net"

[weheh]

I ran it again and got the same error message:

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version fo rht eright
syntax to use near 'x(id INT AUTO_INCREMENT NOT NULL, f1 VARCHAR(25),
f2 DA' at line 1

[mdipierro]

This confirms it is not a web2py issue since the code web2py generates
is correct and yet mysql does not take it.

could it be f1,f2,f3, etc are reserved keyworks in mysql?

mysql has lots of little undocumented exceptions. That is why I always
suggest postgresql.

Massimo

[weheh]

Bingo. 85% certain looks like I'm bitten by the keyword problem again.
Thanks, Massimo.

[weheh]

Now 100% certain about causing reserved keyword conflict with mysql.
Thanks again for the reminder.

I successfully migrated the data to mysql from sqlite, but did it 1
table at a time. Is there a convenient way to move all the data from
all tables at once? I figured tar and detar just brings back the
sqlite db, so I didn't try it.

[carlo]

maybe I am not fully up to date with the latest web2py releases so
chech this out:

db structure: if you have an existing mysql db with the same structure
as the sqlite db, it is just a matter of changing the connect string
in the model

db data: I think web2py does not care for migrating data from one db
to another (and it should not do), I think you have to do some
scripting or use some external mysql utility to convert data.

carlo

[mdipierro]

yes you can migrate an entire database from the shell with one
command:

1) have a model that connects to the sqliite db
2) python web2py -S yourapp -M
3) in the shell type

db.export_to_csv_file(open('somefile.csv','wb'))

4) quit, edit the model to connect to the other db instead
5) python web2py -S yourapp -M
6) in the shell type

db.import_from_csv_file(open('somefile.csv','rb'))

7) done. Id's we'll be different but references will be preserved and
not be broken.

Massimo

[weheh]

Extra special cool. I will have to try this.

[weheh]

Massimo, I tried your migration suggestion but got an error message on
reading the 'somefile.csv' file:

Traceback (most recent call last):

File "<console>", line 1, in <module>
File "gluon/sql.py", line 997, in import_from_csv_file
ValueError: Mixing iteration and read methods would lose data

[mdipierro]

You must have an old version of web2py. That error cannot come form
line 997.

Massimo

[weheh]

I downloaded the latest version. Now the error message is:

Traceback (most recent call last):
File "<console>", line 1, in <module>

File "gluon/sql.py", line 1034, in import_from_csv_file

[mdipierro]

I will look into this asap.

Massimo

[Richard]

very useful!

[weheh]

Massimo, not a huge rush for me since I already migrated my data one
table at a time using csv. But still an extremely important capability
for those with large DBs that need to migrate to a new db engine.

[mdipierro]

which python version are you running?

[mdipierro]

I think I fixed this in trunk but it may be python version dependent.
Please give it a try and let us know.

On Jun 22, 1:15 am, weheh <richard_gor...@verizon.net> wrote:

[weheh]

1.64.3

[Richard]

> db.import_from_csv_file(open('somefile.csv','rb'))

don't forget you need a db.commit() after importing.

On Jun 22, 2:02 am, mdipierro <mdipie...@cs.depaul.edu> wrote: