destination schema for psql

[markuman]

Say my destination is postgres and it has got one database (postgres) with several schemas. With a default ks sync, the data would be placed in the public schema of the postgres database`.

# setup test environment
docker network create testdb
docker run --rm -d -p 3310:3306 -e MYSQL_ROOT_PASSWORD=password --network testdb --name db1 mariadb
docker run --rm --name postgres -p 5432:5432 --network=testdb -e POSTGRES_PASSWORD=password -d postgres

# init mariadb source test database + table
mysql -h 127.1 -P 3310 -u root -ppassword -e "create database kstest CHARACTER SET latin1;"
mysql -h 127.1 -P 3310 -u root -ppassword kstest -e "create table data (id integer, primary key (id));"

# sync from mariadb kstest db to postgres 
./ks --from "mysql://root:password@127.0.0.1:3310/kstest" --to "postgres://postgres:password@127.0.0.1:5432/postgres"

# now the data are successful synced to schema `public` of database `postgres` 

Is it possible to select/use/set another destination schema than public when postres is the destination databsae?

[willbryant]

Great issue write-up BTW.

There's no specific feature to do this, but you can pretty easily work around it using a SET option, like this:

./ks --from "mysql://root:password@127.0.0.1:3310/kstest" --to "postgres://postgres:password@127.0.0.1:5433/postgres" --alter --set-to-variables="search_path TO myschema"

This works because postgresql will create new tables in the first schema listed in the search path (you can use eg. TO myschema,public to list more than one).

I believe this will get you in business. But I have to be completely honest and say that I haven't given multiple schemas very much thought, as I haven't seen them in use very much myself - mostly I've seen multiple databases or even clusters. Could you tell me a little more about your use case?

[markuman]

Thx, that works.

But I ended up with Error in the 'to' worker: Don't know how to convert MessagePack type 171 to map. I thought it is refering to #37 -> #42 But checking out the pr/42, build and run it results in the same error.

./ks --verbose --debug --from mysql://usr_replica:password@some_mysql:3306/mein --to postgresql://usr_replica:password@some_postgres:5432/postgres --alter --set-to-variables="search_path TO test_mein_ks"
Kitchen Sync
from command: ./ks_mysql from some_mysql 3306 mein usr_replica password -
./ks_postgresql(+0x82f93) [0x55bc0fdc3f93]
./ks_postgresql(_ZN8UnpackerI12FDReadStreamE15next_map_lengthEv+0x9b) [0x55bc0fded733]
./ks_postgresql(_ZrsI12FDReadStreamEvR8UnpackerIT_ER6Column+0x1d) [0x55bc0fe0c228]
./ks_postgresql(_ZN8UnpackerI12FDReadStreamE4nextI6ColumnEET_v+0x30) [0x55bc0fe0b794]
./ks_postgresql(_ZrsI12FDReadStream6ColumnER8UnpackerIT_ES5_RSt6vectorIT0_SaIS7_EE+0x81) [0x55bc0fe0a380]
./ks_postgresql(_ZrsI12FDReadStreamEvR8UnpackerIT_ER5Table+0xab) [0x55bc0fe074ee]
./ks_postgresql(_ZN8UnpackerI12FDReadStreamE4nextI5TableEET_v+0x30) [0x55bc0fe0560a]
./ks_postgresql(_ZrsI12FDReadStream5TableER8UnpackerIT_ES5_RSt6vectorIT0_SaIS7_EE+0x81) [0x55bc0fe02623]
./ks_postgresql(_ZrsI12FDReadStreamEvR8UnpackerIT_ER8Database+0x74) [0x55bc0fdffa64]
./ks_postgresql(_Z11read_valuesI12FDReadStream8DatabaseJEEvR8UnpackerIT_ERT0_DpRT1_+0x23) [0x55bc0fdfc235]
./ks_postgresql(_Z10read_arrayI12FDReadStreamJ8DatabaseEEvR8UnpackerIT_EDpRT0_+0x13f) [0x55bc0fdf921a]
./ks_postgresql(_Z18read_all_argumentsI12FDReadStreamJ8DatabaseEEvR8UnpackerIT_EDpRT0_+0x26) [0x55bc0fdf78b7]
./ks_postgresql(_Z21read_expected_commandI12FDReadStreamJ8DatabaseEEvR8UnpackerIT_EjDpRT0_+0x156) [0x55bc0fdf5b3a]
./ks_postgresql(_ZN12SyncToWorkerI16PostgreSQLClientE24retrieve_database_schemaEv+0x51) [0x55bc0fdf4d7f]
./ks_postgresql(_ZN12SyncToWorkerI16PostgreSQLClientEclEv+0x31) [0x55bc0fdf4933]
./ks_postgresql(_ZSt13__invoke_implIvR12SyncToWorkerI16PostgreSQLClientEJEET_St14__invoke_otherOT0_DpOT1_+0x20) [0x55bc0fdf48ff]
./ks_postgresql(_ZSt8__invokeIR12SyncToWorkerI16PostgreSQLClientEJEENSt9result_ofIFOT_DpOT0_EE4typeES6_S9_+0x26) [0x55bc0fdf48d5]
./ks_postgresql(_ZNKSt17reference_wrapperI12SyncToWorkerI16PostgreSQLClientEEclIJEEENSt9result_ofIFRS2_DpOT_EE4typeES9_+0x20) [0x55bc0fdf488e]
./ks_postgresql(_ZNSt12_Bind_simpleIFSt17reference_wrapperI12SyncToWorkerI16PostgreSQLClientEEvEE9_M_invokeIJEEEvSt12_Index_tupleIJXspT_EEE+0x28) [0x55bc0fdf486c]
./ks_postgresql(_ZNSt12_Bind_simpleIFSt17reference_wrapperI12SyncToWorkerI16PostgreSQLClientEEvEEclEv+0x1d) [0x55bc0fdf4809]
./ks_postgresql(_ZNSt6thread11_State_implISt12_Bind_simpleIFSt17reference_wrapperI12SyncToWorkerI16PostgreSQLClientEEvEEE6_M_runEv+0x1c) [0x55bc0fdf4732]
/usr/lib/x86_64-linux-gnu/libstdc++.so.6(+0xb9e6f) [0x7f5829c82e6f]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7494) [0x7f5829f52494]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7f58293f7acf]
Error in the 'to' worker: Don't know how to convert MessagePack type 171 to map
Error in the 'from' worker: Connection closed
Kitchen Syncing failed.

But I have to be completely honest and say that I haven't given multiple schemas very much thought, as I haven't seen them in use very much myself - mostly I've seen multiple databases or even clusters. Could you tell me a little more about your use case?

Me neither. That's something I found that way :)

[willbryant]

I guess that means some other type of column. Any chance you could dump the schema, or is it too sensitive to release?

[willbryant]

It's not meant to explode with an internal error like that. I thought I already fixed that, but I missed a test case.

I've fixed that in 1.5, which I've just pushed. Please try again with that - it should tell you the actual problem type - and open a new ticket with the details.

I'll go ahead and close this issue in the meantime since the original problem is solved.