willbryant / kitchen_sync Goto Github PK

Using a filter file with replace on a primary key column would not work. Users are perhaps not super likely to try this for a real explicit PK, but on tables that have no explicit PK they could easily do it for columns in a unique key that we happen to have chosen as the substitute PK. We'd better prevent this.

I haven't done anything in particular about these, so it's unlikely that they work well.

Unclear if anyone actually uses these (comments/feedback very welcome here).

I'm brand new to ks and am using it sync a drupal database between production and my development environment. Its failing on the following error:

Error in the 'from' worker: Couldn't find a primary or non-nullable unique key on table uc_payment_paypal_ipn

which comes from the contributed drupal7 module, ubercart. I can most likely add a primary key but why doesn't --ignore uc_payment_paypal_ipn work around this problem?

ks --from postgresql://logsreader_single:[email protected]/database_single --to mysql://username:password@localhost/3CX --debug

Kitchen Sync
from command: ks_postgresql from 192.168.99.1 - database_single logsreader_single -
Don't know how to interpret type of cl_participants.billing_rate (numeric). Please check https://github.com/willbryant/kitchen_sync/blob/master/SCHEMA.md.
Kitchen Syncing failed.

I don't see the answer anywhere in the filtering documentation so it may not be possible at this time, but I would like to exclude an entire column for a table that is being synchronized because the column is enumerated with the default of NULL and the only values able to be set as "Yes" or "No" and kitchen-sync fails with the message:
Kitchen Sync Don't know how to interpret type of app.abandoned_equipment (enum('Yes','No')). Please check https://github.com/willbryant/kitchen_sync/blob/master/SCHEMA.md. Kitchen Syncing failed.

[...]
-- Configuring done
CMake Error at test/CMakeLists.txt:2 (add_executable):
  Cannot find source file:

    sql_functions_test.cpp

  Tried extensions .c .C .c++ .cc .cpp .cxx .m .M .mm .h .hh .h++ .hm .hpp
  .hxx .in .txx


CMake Error: CMake can not determine linker language for target: ks_unit_tests
CMake Error: Cannot determine link language for target "ks_unit_tests".
-- Generating done
-- Build files have been written to: /tmp/SBo/slackrepo.iBOtRk/build_kitchen-sync/kitchen_sync-1.16/build
system/kitchen-sync: kitchen-sync.SlackBuild failed (status 1)          20:38:37
Unmounting chroot ...
:-( system/kitchen-sync FAILED )-:

Did a file not get git added?

KS tries to create multiple indexes when syncing from MySQL to PostgreSQL

CREATE INDEX "room_info_id_idx" ON library_config ("room_info_id");
CREATE INDEX "room_info_id_idx" ON notification_info ("room_info_id");
CREATE INDEX "room_info_id_idx" ON position_data ("room_info_id");
CREATE INDEX "room_info_id_idx" ON vital_info ("room_info_id");

Which returns the following error:

ERROR: relation "room_info_id_idx" already exists

With both the old SERIAL-style nextval() default and the new GENERATED .. AS IDENTITY implementations, postgresql uses a separate underlying sequence object and does not automatically increase that sequence's nextval if a higher ID is inserted into the table manually.

Therefore we need to do this explicitly or the next insert the user makes locally will generally fail on postgresql if there were any new (higher ID) rows in the 'from' database.

We can choose whether to set the nextval to the same value as the 'from' database has, or to only reset to the highest ID visible in the 'from' database. And we can choose whether to always reset or only reset if higher than the current value.

(mysql and mariadb both automatically increase the internal auto_increment value if a higher ID is inserted, so we don't have to do anything there, but we could reset there too if we wanted.)

On Mysql 8.0 KS runs fail with:

Incorrect argument type to variable 'net_read_timeout'\nSET SESSION net_read_timeout = GREATEST(@@net_read_timeout, 600)

It would be useful if kitchen sync could start with the records at the bottom of the tables, so that only the most recent records were fetched.

Hi!
I'm trying to copy a DB from a server to my local machine. I get the error:
Couldn't exec ks_mysql: No such file or directory

I installed KS using brew and I didn't install MySQL locally but MariaDB. Unfortunately, I can't install MySQL as well as MariaDB using brew (Error: Cannot install MySQL because conflicting formulae are installed. MySQL, MariaDB, and Percona install the same binaries).

Is there any way I could create (build) ks_mysql using MariaDB?

Thanks!

Followed the installation steps on Ubuntu 15.04 and ran into the following.

Couldn't exec ks_postgresql: No such file or directory
Couldn't exec ks_postgresql: No such file or directory
Finished Kitchen Syncing.

Running PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.9.2-10ubuntu13) 4.9.2, 64-bit

Any idea what I might have missed?

I'm trying to figure out how to use the filter's functionality, and I can't figure out the format of the yaml file that kitchen-sync excepts. An example would be nice here.

I'm trying to run ks on a MySQL database with a table containing an enum field. Enum doesn't seem to be supported at the moment, so I tried to --ignore the table but with no luck:

Error in the 'from' worker: Don't know how to represent mysql type of mytable.mycolumn (enum('val1','val2','val3'))

Either having support for enum columns or letting the --ignore skip the schema check for ignored tables would solve this issue.

When kitchen syncing between a MariaDB version 10.3 database and a 10.0 database we noticed we schema incompatible problem. Using the --alter flag was unable to resolve the issue, so I dug further in. It turns out there has been a change to to how current_timestap() is represented in a default value, and as such kitchen sync detects a schema mismatch. However, MariaDB silently upgrades CURRENT_TIMESTAMP to current_timestamp() making it impossible to have KS agree the schemas are the same.

I'll add some more info on the tables in question here soon.

I'm trying to run ks --via myserver.loc:2233 ... since my source server has ssh running on port 2233. As a result I'm getting:

ssh: Could not resolve hostname myserver.loc:2233: nodename nor servname provided, or not known
Error in the 'to' worker: Connection closed
Kitchen Syncing failed.

Am I doing something wrong or is it not possible to set the ssh port manually?

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:[email protected]:3310/kstest" --to "postgres://postgres:[email protected]: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?

Bug report from the field on the 0.99 RC, getting stuck on a small table repeatedly issuing SELECT queries with LIMIT 0.

ks --from mysql://root:[email protected]/testdb --to mysql://root:[email protected]/mydb
Kitchen Sync
Connection closed
Error in the 'to' worker: Couldn't write to descriptor: Bad file descriptor
Kitchen Syncing failed.

Regression in 0.40:

starting unit_balances
Commands out of sync; you can't run this command now
DELETE FROM unit_balances WHERE (id=31758784)
OR (id=32181697)
OR (id=32623602)
etc.

Hi,

I was using the KS (v. 1.13) to sync from Postgres to MySQL and got the following output:

Kitchen Sync
Couldn't find a primary or non-nullable unique key on table spinstrumentsector
Kitchen Syncing failed.

Is there any possibility I can still make the sync work?

I'm getting the following error when I try and make a connection:

Kitchen Sync
Error in the 'from' worker: ERROR: invalid name syntax

SELECT tablename FROM pg_tables WHERE schemaname = ANY (current_schemas(false)) ORDER BY pg_relation_size(tablename::text) DESC, tablename ASC
Connection closed
Kitchen Syncing failed.

My command is:

ks --from "postgresql://somebody:password@localhost:5432/testdb" --to "mysql://somebody:[email protected]/test_rep" --only "contacts"

Sort of a technicality, but if there's a numeric column and a filter file used with a replace option, this will work normally:

numtest:
  replace:
    val: "10"

But this won't:

numtest:
  replace:
    val: "'10'"

In the second case, the database will tell KS the resultset column has a string type not an integer type, and so it will be serialised as strings - so differently to the current column values found in the 'to' end table, which will always be serialised as integers. As a result, the hashes won't match.

This doesn't really cause any problems as such, but it's inefficient because the hashes will never match and so the whole table will get reloaded.

MariaDB 5.2+, MySQL 5.7+, PostgreSQL 12+ all support generated columns. Currently we will ignore the fact that they are generated and attempt to write to them, which of course won't work.

Hi @willbryant

I'm using ks to sync some tables between 2 postgres databases. Here is the command I use:

ks --from postgresql://havs:123@localhost/welligence_development --to postgresql://havs:123@localhost/welligence_brazil --only countries

Here is the error message I got: Don't know how to interpret type of countries.name (character varying)

According to the supported schema, I understand that the column with type character varying should be supported and I should not encounter this issue. Any idea?

Similar to #31, we should not error out on filter problems that are for tables ignored due to the --ignore or --only option.

According to http://www.postgresql-archive.org/PQunescapebytea-not-reverse-of-PQescapebytea-tp5793863p5797490.html, we shouldn't have to escape the string returned by PQescapeByteaConn using PQescapeStringConn.

But we get errors if we don't, so something is wrong here.

PostgreSQL 10 introduced the new identity column feature to replace serial. We should support it like we do serial.

Need to decide what to do when creating a table - specifically whether to always create identity or to use a flag column to indicate old or new syntax.

Is it possible to run ks in the background keeping databases in sync (near) realtime?

I've packaged both v1.6 and v1.7 and run into the following problem. I have a server with kitchen sync 1.0 installed and am trying to sync (mysql) from it using either of these version with the --via option. This fails with the error:

Kitchen Sync
This program is a part of Kitchen Sync.  Instead of running this program directly, run 'ks'.
Couldn't start Kitchen Sync over SSH to myserver.  Please check that you can SSH to that server yourself, and that Kitchen Sync's binary can be found on that system at ks_mysql.

I have bisected this and fdbdd2c is the commit which broke compatibility. Do you expect a newer client to be able to speak to the server running 1.0? d3f7c09 it not part of 1.0 so, the server cannot answer the do-nothing request. Since fdbdd2c made this now fatal, compatibility is broken. In my case, the server is unfortunately not under my control, so getting a newer ks installed will take some time.

Got a bit confused thinking I was getting authentication errors from the other end, when it was authentication errors locally. Adding [from] or [to] to the logging would be helpful.

via the LLDB debugger on a Mac

* thread #1, queue = 'com.apple.main-thread', stop reason = EXC_BAD_ACCESS (code=1, address=0x0)
    frame #0: 0x00007fff9ed21b52 libsystem_c.dylib`strlen + 18
  * frame #1: 0x00000001000178e5 ks_mysql`std::__1::char_traits<char>::length(__s=0x0000000000000000) at __string:215
    frame #2: 0x0000000100014062 ks_mysql`int endpoint_main<MySQLClient>(int, char**) [inlined] bool std::__1::operator==<char, std::__1::char_traits<char>, std::__1::allocator<char> >(__lhs=0x0000000000000000, __rhs="from") at string:3533
    frame #3: 0x0000000100014056 ks_mysql`int endpoint_main<MySQLClient>(int, char**) [inlined] bool std::__1::operator!=<char, std::__1::char_traits<char>, std::__1::allocator<char> >(__lhs=0x0000000000000000, __rhs="from") at string:3566
    frame #4: 0x000000010001403a ks_mysql`int endpoint_main<MySQLClient>(argc=1, argv=0x00007fff5fbff758) at endpoint.h:17
    frame #5: 0x0000000100013f02 ks_mysql`main(argc=1, argv=0x00007fff5fbff758) at ks_mysql.cpp:642
    frame #6: 0x00007fff9eceb235 libdyld.dylib`start + 1
    frame #7: 0x00007fff9eceb235 libdyld.dylib`start + 1

When synchronising tables that are missing at the receiving end and using the --alter flag to populate the missing table, tables that had no primary key at the source end (but that do have a suitable unique key) will be created with a real primary key at the receiving end.

Unconfirmed, just logging for later. Delete if this is a pain / mess.

It looks like if you create a table, for example: "myTable", ks won't sync it because it correctly creates the sequence but seems to be lowercasing the sequence name somewhere when it tries to tell the column to use it.

I need to learn more about how sequences work to confirm this is an actual issue.

Hi Will,

I am getting the following error:-

ks --from postgresql://logsreader_single:[email protected]/database_single --to mysql://username:password@localhost/3CX --debug

Error in the 'from' worker: ERROR: relation "table1" does not exist

SELECT tablename FROM pg_tables WHERE schemaname = ANY (current_schemas(false)) ORDER BY pg_relation_size(tablename::text) DESC, tablename ASC
Connection closed
Kitchen Syncing failed.

When trying to synchronise two Postgre DBs, I get the following message:

Kitchen Sync
Don't know how to interpret type of movie.genres (text[])
Kitchen Syncing failed.

Column genres in table movie is indeed of array type. Is it currently supported?

Running kitchen_sync on Slackware fails since the blowfish cipher is not supported. I am currently patching before building to work around this, but maybe we can make it configurable at runtime or have the makefile detect this?

aclemons@host ~ $ ssh -Q cipher
3des-cbc
blowfish-cbc
cast128-cbc
arcfour
arcfour128
arcfour256
aes128-cbc
aes192-cbc
aes256-cbc
[email protected]
aes128-ctr
aes192-ctr
aes256-ctr
[email protected]
[email protected]
[email protected]
aclemons@host ~ $ ssh -V
OpenSSH_7.2p2, OpenSSL 1.0.2h  3 May 2016

ALTER TABLE foo DROP `bar`, DROP `qux`, ADD `bar` varchar(255), ADD `qux` int
Error in the 'to' worker: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

Opening this bug report to track this issue. It's a known MySQL / MariaDB bug where you cannot both drop and add the column that is indexed (in this case qux is indexed)

I have the following source MariaDB database:

CREATE TABLE 'test' (
'id' int(10) NOT NULL AUTO_INCREMENT,
'time' tinyint(2) DEFAULT '20'
);

with the following record in it:

SELECT * FROM test;
id | time
1 | 20

When running ks I get the following error:

1553852889.477541 worker 0 starting test
1553852889.477762 worker 0 <- range test
1553852889.545032 -> range test (1) (1)
1553852889.645944 worker 0 <- rows test (NULL) (1)
1553852889.679986 worker 0 -> rows test (NULL) (1)
Error in the 'to' worker: ERROR: column "time" is of type smallint but expression is of type boolean
LINE 2: ...1,false...
^
HINT: You will need to rewrite or cast the expression.

INSERT INTO test VALUES
(1,false...
Error in the 'from' worker: Connection closed
Kitchen Syncing failed.

I'm installing for the first time on Ubuntu Trusty.

When I run ks I get the error:

Couldn't exec ks_postgres: No such file or directory

When I ran make install I noticed that it installs to /usr/local/bin/ks_postgresql. Note the "ql" at the end vs no "ql" in the error.

$ which ks_postgresql
/usr/local/bin/ks_postgresql

$ which ks_postgres
ks_postgres not found

syncing between 2 mariadb servers, single table with --only option, db version from 10.1.11 to 10.2.6

The initial table was a complex one with no primary key, then I tried just syncing a much simplier asset table

CREATE TABLE `assets` (
  `name` varchar(16) NOT NULL,
  `aclass` tinyint(4) NOT NULL,
  `asset` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`name`),
  UNIQUE KEY `aclass` (`aclass`,`asset`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It still failed with the same error.

Error in the 'to' worker: Don't know how to convert MessagePack type 171 to map

Can you explain how to write custom types trasformation rules , in case we sync db from mysql to postgresql database .

i suppose it should be some .yml file , but can you give example how to write it ?

Thank you in advance ,

Konstantin

Currently when running the make step for Kitchen Sync, libyaml not only gets built, its tests get run too. This isn't really relevant, and fails on small nodes with little memory.

Ideally make this fail rather than surprising someone doing something strange one day.

the program currently doesn't accept characters such as @ in password connection string even with proper bash ecapes.

https://github.com/willbryant/kitchen_sync/blob/master/src/db_url.cpp#L78

I have an issue, kitchen sync result with failed response:

Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Connection closed
Connection closed
Connection closed
Connection closed
Kitchen Syncing failed.

Any help how to customize unix_socket path?

@willbryant

As your request in #54, I create this new issue. Here is my command:

ks --from postgresql://havs:123@localhost/welligence_development --to postgresql://havs:123@localhost/welligence_brazil --only countries --debug

Here is the debug log

Kitchen Sync
from command: ks_postgresql from localhost - welligence_development havs 123 -
1551881536.810149 worker 0 starting countries
1551881536.810212 worker 0 <- range countries
1551881536.810747 -> range countries (1) (18)
1551881536.811430 worker 0 <- hash countries (NULL) (9) 1
1551881536.811804 worker 0 -> hash countries (NULL) (9) 1 doesn't match
1551881536.811822 worker 0 countries has 3 range(s) to check and 1 to retrieve, sharing not needed
1551881536.811829 worker 0 <- rows countries (NULL) (1)
1551881536.811839 worker 0 <- hash countries (1) (5) 221
1551881536.812029 worker 0 -> rows countries (NULL) (1)
1551881536.812216 worker 0 <- hash countries (5) (9) 221
1551881536.812404 worker 0 -> hash countries (1) (5) 4 doesn't match
1551881536.812417 worker 0 countries has 1 range(s) to check and 1 to retrieve, sharing not needed
1551881536.812427 worker 0 <- rows countries (1) (5)
1551881536.812441 worker 0 -> hash countries (5) (9) 4 doesn't match
1551881536.812449 worker 0 countries has 1 range(s) to check and 1 to retrieve, sharing not needed
1551881536.812458 worker 0 <- rows countries (5) (9)
1551881536.812620 worker 0 -> rows countries (1) (5)
1551881536.812837 worker 0 <- hash countries (9) (18) 1
1551881536.813173 worker 0 -> rows countries (5) (9)
1551881536.813362 worker 0 -> hash countries (9) (18) 1 doesn't match
1551881536.813376 worker 0 countries has 2 range(s) to check and 1 to retrieve, sharing not needed
1551881536.813386 worker 0 <- rows countries (9) (10)
1551881536.813401 worker 0 <- hash countries (10) (14) 184
1551881536.813624 worker 0 -> rows countries (9) (10)
1551881536.813824 worker 0 <- hash countries (14) (18) 184
1551881536.814024 worker 0 -> hash countries (10) (14) 4 doesn't match
1551881536.814037 worker 0 countries has 0 range(s) to check and 1 to retrieve, sharing not needed
1551881536.814045 worker 0 <- rows countries (10) (14)
1551881536.814057 worker 0 -> hash countries (14) (18) 4 doesn't match
1551881536.814063 worker 0 countries has 0 range(s) to check and 1 to retrieve, sharing not needed
1551881536.814071 worker 0 <- rows countries (14) (18)
1551881536.814236 worker 0 -> rows countries (10) (14)
1551881536.814450 worker 0 -> rows countries (14) (18)
Error in the 'to' worker: ERROR: update or delete on table "countries" violates foreign key constraint "fk_rails_1630089669" on table "facilities"
DETAIL: Key (id)=(4) is still referenced from table "facilities".

DELETE FROM countries WHERE (id=1)
OR (id=2)
OR (id=3)
OR (id=4)
OR (id=5)
OR (id=6)
OR (id=7)
OR (id=8)
OR (id=9)
OR (id=10)
OR (id=11)
OR (id=12)
OR (id=13)
OR (id=14)
OR (id=15)
OR (id=16)
OR (id=1...
Error in the 'from' worker: Connection closed
Kitchen Syncing failed.

Seems to be a problem with the work of the timestamp data type:

DROP TABLE IF EXISTS `ts_bug`;
CREATE TABLE `ts_bug` (
    `ids` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tm` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`ids`)
);
INSERT INTO `ts_bug` (tm) VALUES (CURRENT_TIMESTAMP);

$ ks --from "mysql://denji@localhost/db1" --to "postgresql://denji@localhost/db1"

> Don't know how to represent mysql type of ts_bug.tm (timestamp)

Types formats Conversion: mysql2pgsql/lib/postgres_writer.py

• https://github.com/mihailShumilov/mysql2postgresql (PHP)

• https://github.com/dimitri/pgloader ( Tcl (1.х), Python (2.x), Common Lisp (3.x) )

• https://github.com/maxlapshin/mysql2postgres (Ruby)

• https://github.com/lanyrd/mysql-postgresql-converter (Python)

• http://www.postgresonline.com/journal/archives/29-How-to-convert-a-table-column-to-another-data-type.html

• PostgreSQL complain about timestamp '0000-00-00 00:00:00'

  ALTER TABLE table ALTER COLUMN col TYPE integer USING function(...);
  

mysqlcompat (SQL functions, easing migration)

• https://github.com/pornel/mysqlcompat (SQL/Shell)
• http://www.postgresql.org/ftp/projects/pgFoundry/mysqlcompat/mysqlcompat/ (SQL/Shell)