Migration from sqlite to mysql - different field-order

[Jörg Schneider]

I tried to migrate from sqlite database to mysql/mariadb but following the instructions on https://realpython.com/web2py-migrating-from-sqlite-to-mysql/ didn't work for me, because of different database scheme created by dal on mysql database. Unfortunately "fake_migrate=True" didn't work either.

For example:

db.define_table('repo', 

                Field('title', requires=IS_NOT_EMPTY(), label='Liedtitel'),

                Field('composer', default='unbekannt', label='Komponist'),

                Field('lyrics', label='Liedtext'),

                Field('arrangement', label='Liedsatz'),

                Field('biblical', label='Bibelstelle'),

                Field('genre', 'reference repository_genre', default='2'),

                Field('purpose',requires=IS_IN_SET(PURPOSES), default=PURPOSES[0], label='Satz für'),

                Field('pdf', 'upload', uploadfolder=os.path.join(request.folder, 'static/repository'), label='PDF-Datei'),

                Field('transl', type='text', label='Übersetzung'),

                Field('transl_file', 'upload', uploadfolder=os.path.join(request.folder, 'static/repository'), label='Anhang zur Übersetzung'),

                auth.signature,

                format='%(title)s %(composer)s'

               ) 

has following table in sqlite:

1. id
   
2. title
   
3. composer
   
4. lyrics
   
5. arrangement
   
6. genre
   
7. pdf
   
8. transl
   
9. transl_file
   
10. status
    
11. purpose
    
12. biblical
    
13. modified_by
    
14. created_by
    
15. created_on
    

which has other order than created on the mysql table:

1. id
   
2. title
   
3. composer
   
4. lyrics
   
5. arrangement
   
6. biblical
   
7. genre
   
8. purpose
   
9. pdf
   
10. transl
    
11. transl_file
12. created_on
    
13. created_by
    
14. modified_on
    
15. modified_by
    

[Dave S]

How does the different field order affect you?  DAL query results within a row are accessed by field name.  DAL inserts and updates also use field names, no?

/dps

[Massimiliano]

Try to recreate the table in sqlite too,  because doesn't reflect you schema in define_table

there is a "status" field not present in the definition and is "modified_on" ismissing.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/551c1d7c-7a06-4592-b9ba-1bc2f5601438%40googlegroups.com.

--

Massimiliano

[Jörg Schneider]

I want to migrate from existing sqlite db to mariadb. And the import in mariadb fails because of the different field order (wrong type, wrong data)

[Jörg Schneider]

Yes, you are right. And in my understanding of Migrations the "status" field is a remaining row that should should disappear after successful migration. I tried to force migrations but it doesn't seem to happen. :-/

To unsubscribe from this group and stop receiving emails from it, send an email to web...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/551c1d7c-7a06-4592-b9ba-1bc2f5601438%40googlegroups.com.

--

Massimiliano

[villas]

You are in a mess because your sqlite table does not reflect your define_table for 'repo'.

If you include auth.signature,  then you should also have these fields:

                     Field('is_active', 'boolean', default=True),
                     Field('created_on', 'datetime', default=request.now),
                     Field('created_by', db.auth_user, default=auth.user_id),
                     Field('modified_on', 'datetime', update=request.now),
                     Field('modified_by', db.auth_user, update=auth.user_id)

You also have a 'status' field.  BTW 'status' is a reserved word on many DBs,  so it is best avoided.  In any case, this field name has no special meaning in Web2py.

So, what to do?  I suggest...

• Do not use web2py for transferring the data.
• Download some Sqlite manager software from the web, 
• Using that software
• open up your  ./databases/storage.sqlite file and see exactly what fields you've got.
• export the data. 
• You should then be able to import that file into MariaDB.
• To use the new MariaDB table,  correct your web2py table definition to reflect the actual fields.
• Do a fake_migrate in web2py.  fake_migrate=True, migrate=True
• Check the table definition file exists in yourapp/databases folder
  

Good luck!