| chat.freenode.net #tryton log beginning Thu 07 Nov 2019 12:00:01 AM CET | ||
| -!- cedk(~ced@gentoo/developer/cedk) has joined #tryton | 23:44 | |
| -!- sebste_(~sebste@x4db49055.dyn.telefonica.de) has joined #tryton | 01:35 | |
| -!- yangoon(~mathiasb@202-162-142-46.pool.kielnet.net) has joined #tryton | 03:55 | |
| -!- rpit(~rpit@p200300C88F32F600A22070230F535743.dip0.t-ipconnect.de) has joined #tryton | 07:22 | |
| -!- sebste(~sebste@x4db49055.dyn.telefonica.de) has joined #tryton | 07:49 | |
| -!- springwurm(~Springwur@5.104.149.54) has joined #tryton | 07:56 | |
| -!- cedk(~ced@gentoo/developer/cedk) has joined #tryton | 08:00 | |
| -!- Timitos(~kpreisler@2001:a61:5a4:b101:762b:62ff:fe84:ed7e) has joined #tryton | 08:02 | |
| -!- rpit(~rpit@p4FFB778E.dip0.t-ipconnect.de) has joined #tryton | 09:23 | |
| -!- nicoe(~nicoe@213.211.148.72) has joined #tryton | 09:43 | |
| sebste | Good morning, folks. I am doing a migration from sqlite to postgres and i am taking notes for a writeup which I would like to make available for "the next guy". I am struggling a bit, thoug. | 09:51 |
|---|---|---|
| sebste | Here is what I have done so far: setup pgsql server and created user and database. | 09:52 |
| sebste | Ran trytond-admin on that database to create the tables. | 09:52 |
| sebste | created a schema of that db | 09:52 |
| sebste | droped db and recreated it with the schema to get empty tables. | 09:52 |
| sebste | now I want to run sqlite3pgsql.py from the tryton-tools. | 09:53 |
| sebste | Here is the problem: | 09:53 |
| sebste | psycopg2.ProgrammingError: column "active" is of type boolean but expression is of type integer | 09:54 |
| sebste | LINE 1: ...VALUES (1,NULL,'Administration','tryton-settings',1,'2019-06... | 09:54 |
| sebste | HINT: You will need to rewrite or cast the expression. | 09:54 |
| sebste | So what would be the best wa to tackle this? Can I modify the script to automatically convert the fields? | 09:55 |
| cedk | sebste: this seems to be a solution https://dba.stackexchange.com/questions/46140/have-postgresql-accept-1-and-0-as-true-and-false-for-boolean | 09:58 |
| sebste | cedk: thanks! I'll try that! | 10:01 |
| sebste | cedk: the first suggested method, update pg_cast, does not seem to have an effect. | 10:09 |
| sebste | maybe I could use pgloader instead... | 10:10 |
| sebste | seond thought: I just did "update pg_cast set castcontext='a' where casttarget = 'boolean'::regtype;" as postgres in psql, then reran psqlite2pgsl ist this correct? | 10:12 |
| cedk | sebste: you should maybe use 'i' instead of 'a' | 10:20 |
| cedk | as the query use VALUES instead of assignation | 10:21 |
| cedk | https://www.postgresql.org/docs/current/catalog-pg-cast.html | 10:21 |
| sebste | No, does not work - I am in doubt if I perfom this settings in the rigth place, though | 10:24 |
| cedk | sebste: it must be done on the database | 10:25 |
| sebste | cedk: Thanks that seems to do the trick! Makes sense ;-) | 10:28 |
| sebste | Here comes teh next one: | 10:29 |
| sebste | psycopg2.ProgrammingError: column "dashboard_layout" of relation "res_user" does not exist | 10:29 |
| sebste | LINE 1: ...eset_expire","signature","write_date","write_uid","dashboard... | 10:29 |
| sebste | can I just add the colum by modifying the schema and recreating the database? | 10:29 |
| cedk | sebste: it is probably better to drop the column from the source | 10:30 |
| sebste | cedk: you mean in the sqlite db, right? | 10:30 |
| cedk | sebste: wait this should exist in the target | 10:31 |
| cedk | sebste: I guess you did not activate the same modules on both | 10:31 |
| sebste | hmm shoot! That could be. Unfortunately ther old modules are gone since I downgraded to 4.8 because of an update problem. I had to recreate the modules list manually. | 10:36 |
| sebste | what module would res_user likely be part of? | 10:36 |
| cedk | sebste: do not understand the question | 10:39 |
| sebste | cedk: never mind, I'll try to figure it out. I was wondering if one could tell which module might be the wrong one. | 10:41 |
| cedk | sebste: dashboard | 10:41 |
| sebste | thanks! | 10:43 |
| sebste | cedk: if I uninstall module dashboard and run trytond-admin, will it get rid of the related db entries / tables | 11:21 |
| sebste | or the other way around. How do I cleanly uninstall a module in tryton? | 11:25 |
| cedk | sebste: clean uninstall is not supported | 11:32 |
| sebste | cedk: Your note on migration from 4.2 to4.4: https://discuss.tryton.org/t/migration-from-5-2-to-5-4/1547 does that also apply to sqlite? | 12:00 |
| -!- nicoe(~nicoe@213.211.148.72) has joined #tryton | 12:02 | |
| sebste | OK, so back to where it all started: I got everything back to a runing tryton 5.2 installation and pip upgraded all modules to 5.4.0. Running trytond-admin fails though: https://pastebin.com/YkEUNCxk | 12:33 |
| sebste | Unfortnately I have no clue where to look next | 12:36 |
| cedk | sebste: I do not think it applies on sqlite because it has any of the types | 12:37 |
| cedk | sebste: well sqlite does not seem to support DISTINCT ON syntax | 12:43 |
| sebste | cedk: so that means migration from 5.2 to 5.4 does not work on sqlite? | 12:45 |
| sebste | I was hoping to get everything on 5.4 and migrate to postgres afterwards | 12:46 |
| cedk | I filled https://python-sql.tryton.org/bug64 | 12:47 |
| cedk | sebste: indeed I think nobody ever test migration on SQLite as it is for testing purpose only | 12:47 |
| sebste | cedk: ok, I see. Seems I am locked-in right now. The problem with mooving 4.2 over to postgres is, that I had accidentally installed the dashboard module of 5.4. This seems to be the reason why I cant import my data to postgresql. So I was hoping I could iron things out by updating to 5.4 on sqlite and do the move later, when the tables match. I have accumulated a lot of configuration and data during testing and it would be cool to keep that | 12:56 |
| sebste | data. Do you think it makes sense to wait for a bugfix? I would also help if I knew what to do. | 12:56 |
| pokoli | sebste: probably the best is to migrate from sqlite to postgres first, and them upgrade the tryton series | 12:57 |
| sebste | pokoli: yes, I tried that an was almost successfull. Problem is that I had a mixup with a mudule version, so that my data is not compatible with the new datbase structure. :-( | 12:59 |
| sebste | I f I could get rid of the tables related to dashboard module in my current installation in sqlite, it would probably load just fine into the postgresql db | 13:00 |
| pokoli | sebste: indeed you only need to create on postgresql the columns on common tables | 13:01 |
| pokoli | sebste: and then you can remove it | 13:01 |
| sebste | pokoli: sorry? | 13:02 |
| pokoli | sebste: you should manually create all the columns created on the dashboard module on the postgres database, so the import script will create the data (altought you don't need it) | 13:03 |
| pokoli | sebste: without installing the dashboard module | 13:04 |
| -!- springwurm(~Springwur@5.104.149.54) has joined #tryton | 13:04 | |
| pokoli | sebste: once you have the data migrated to postgresql you can remove the manually created columns | 13:04 |
| sebste | pokoli: well I did use trytond-admin to install the tables in the new datbase, then made a dump with just the schema, droped the db and recreated it from the schema to have empty tables | 13:07 |
| pokoli | sebste: so when you load the data into postgres you get some errors of missing columns, don't you? | 13:08 |
| sebste | exactely | 13:09 |
| pokoli | sebste: so if you create this columns manually (without installing the module) you will be able to migrate the data | 13:09 |
| pokoli | sebste: and once you have it migrated you can remove the columns to get rid of uneeded columns | 13:09 |
| sebste | pokoli: I would think so | 13:09 |
| sebste | problem is I am not sure what colum to put where and how | 13:10 |
| sebste | apparently I suck a databases :-D | 13:11 |
| pokoli | sebste: I think you only need to add a VARCHAR named dasbhoar_layout to res_user table | 13:12 |
| sebste | pokoli: alright, I 'll try that later. | 13:13 |
| sebste | funny thing is, that the column doe snot get created by trytond-admin despite the fact that the module is installed | 13:13 |
| sebste | Thank you guys (both) so much for your help an patience, again, you are awesome! | 13:14 |
| cedk | I pushed a patch for https://python-sql.tryton.org/bug64 | 13:24 |
| cedk | sebste: if the column is not created, it means the module is not activated | 13:25 |
| cedk | sebste: I do not know if you want or not this module but you must have the same set of modules activated on both | 13:25 |
| sebste | cedk: yes, I have | 13:28 |
| cedk | sebste: I do not understand | 13:29 |
| sebste | :x | 13:31 |
| pokoli | cedk: IIUC he has the dasboard module on a sqlite database but does not want it on the postgres database | 13:31 |
| cedk | pokoli: this is not possible to switch without having same module sets | 13:32 |
| sebste | actually i don't mind it being there. it is just, that tratond-admin did not create the columns in res_user despite the fact, that the module is installed | 13:33 |
| sebste | in the new db, that is | 13:33 |
| sebste | I am currently trying to add the missing columns manually | 13:33 |
| cedk | sebste: I suspect you did not activate it | 13:50 |
| cedk | sebste: just relaunch trytond-admin -u dashboard | 13:50 |
| -!- mariomop(~quassel@181.228.29.59) has joined #tryton | 14:15 | |
| sebste | cedk: I think you are right. I forgot I need to run trytond-admin twice. Just creating the new schema. Hope the data goes in aftewards. | 15:07 |
| sebste | OK, so far so good. At least I got a step further. But now when run sqlite2pg.py I get this: | 15:10 |
| sebste | psycopg2.IntegrityError: insert or update on table "res_user" violates foreign key constraint "res_user_menu_fkey" | 15:10 |
| sebste | DETAIL: Key (menu)=(2) is not present in table "ir_action". | 15:10 |
| cedk | sebste: it happens, SQLite does not enforce foreign key | 15:12 |
| cedk | sebste: so you have to clean up the data in the source | 15:12 |
| sebste | cedk: So can I just update the rows of res_user to contain the id of the "menu" entry of ir_action in the field menu? | 15:25 |
| sebste | which would be 2 in my case | 15:26 |
| sebste | 2|1|2019-06-13 17:49:32.558392|0||Menu|ir.action.act_window|menu|2019-06-13 17:49:32.580188|0 | 15:26 |
| sebste | well, that didn't work | 15:32 |
| cedk | sebste: why 2, from the error message 2 does not exist | 15:36 |
| sebste | cedk: but if I do SELECT * FROM ir_action WHERE id = 2; | 15:39 |
| sebste | i get 2|1|2019-06-13 17:49:32.558392|0||Menu|ir.action.act_window|menu|2019-06-13 17:49:32.580188|0 | 15:39 |
| sebste | I am sorry - it is a messy learning curve | 15:40 |
| cedk | sebste: I think you must use a schema without constraint on PG and add the constraint after | 15:52 |
Generated by irclog2html.py 2.17.3 by Marius Gedminas - find it at https://mg.pov.lt/irclog2html/!