mrjgreen / db-sync Goto Github PK

View Code? Open in Web Editor NEW

287.0 36.0 88.0 2.23 MB

PHP library with command line tool for efficiently syncing tables between remote MySQL databases

License: MIT License

PHP 100.00%

mysql mysql-backup mysql-replication database sync-data

db-sync's People

Contributors

Stargazers

Watchers

Forkers

toby78 adrianaulbach sdpper qunqiang codeinearts blump chundany mgrennan ahmadsajid1989 jbeigh aydancoskun itmyprofession kelixlabs rosbergrodrigues nztim miatoro yiqun ashrafdev jamsheer rwdxll dotnetcarpenter victor775 asubit mzdani bogiesoft silveringsea websid romanrafaj hmphu php-proj kabsila adeguntoro yesmt09 saywebsolutions rsandic jadb sudogetbeer sayajin101 michalcharvat mikepsinn sam80180 suqingbin0315 xinshao001 csopheak valentinceaprazaru jclaveau dovidezra luohao518 deivid11 ringkubd agungasmara shadow243 ffg0 elviskirui gozan yangshii yudianguo aloha1003 undissss dickoobraz alkanna aronpc grandpiano jiehoes apachesep rcadecaro nezarjhons aidotno scibel carlosleonam peter-id pottertech masarinetwork hummans sergemastaki pencoo mattslowik cmc569 vjccruz dinotd mario1121 vuthaihoc mirkz84 abbeymaniak prist

db-sync's Issues

RuntimeException table does not have a primary key

My Table has a primary key, but when I run The DbSyn, it returns an error table does not have a primary key what can I do.
Thanks in advance

How to use --where= in command line

Im trying the syntax:

db-sync -e --user root --password PASSWORD origin.server.com destiny.server.com:3306 database.table --target.user=remoteuser --target.password='REMOTEPASSWORD' --target.table=database.table --where=date='2017-06-18'

In my table I have a columm date with this data, but the --where cant find.

[info] Hash calculation:

CONCAT(COALESCE(LOWER(CONV(BIT_XOR(CAST(CONV(SUBSTR(MD5(CONCAT_WS('#', id, cliente, date, hour, ethport, ethalias, direction, bytes)),17,16),16,10) AS UNSIGNED)), 10, 16)), 0),COALESCE(LOWER(CONV(BIT_XOR(CAST(CONV(SUBSTR(MD5(CONCAT_WS('#', id, cliente, date, hour, ethport, ethalias, direction, bytes)),1,16),16,10) AS UNSIGNED)), 10, 16)), 0))
[info] Written '0' rows, checked '1024' rows for tables 'table' => 'database.table'
[notice] {"checked":1024,"transferred":0,"affected":0}

Thanks in advance.

Rafael.

Duplicate entry error and question about block-size

Hi,

First, thanks for your great job!

Question:
What's the block-size used for?

And now, I don't understand why I get this:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '_site_transient_timeout_poptags_40cd750bba9870f18aada2478b24840a' for key 'option_name'
host: mysql
username: root
charset: utf8
collation: utf8_general_ci
driver: mysql
options.3: 2
options.19: 2
options.20:
SQL: insert into mydb.wp_options (option_id, option_name, option_value, autoload) values (172, _site_transient_timeout_poptags_40cd750bba9870f18aada2478b24840a, 1448585953, yes), (173, _site_transient_poptags_40cd750bba9870f18aada2478b24840a, a:100:{s:6:"widget";a:3:{s:4:"name";s:6:"widget";s:4:"slug";s:6:"widget";s:5:"count";s:4:"5581";}s:4:"post";a:3:{s:4:"name";s:4:"Post";s:4:"slug";s:4:"post";s:5:"count";s:4:"3497";}s:6:"plugin";a:3:{s:4:"name";s:6:"plugin";s:4:"slug";s:6:"plugin";s:5:"count";s:4:"3449";}s:5:"admin";a:3:{s:4:"name";s:5:"admin";s:4:"slug";s:5:"admin";s:5:"count";s:4:"2960";}s:5:"posts";a:3:{s:4:"name";s:5:"posts";s:4:"slug";s:5:"posts";s:5:"count";s:4:"2696";}s:9:"shortcode";a:3:{s:4:"name";s:9:"shortcode";s:4:"slug";s:9:"shortcode";s:5:"count";s:4:"2149";}s:7:"sidebar";a:3:{s:4:"name";s:7:"sidebar";s:4:"slug";s:7:"sidebar";s:5:"count";s:4:"2142";}s:6:"google";a:3:{s:4:"name";s:6:"google";s:4:"slug";s:6:"google";s:5:"count"
#0 phar:///usr/bin/db-sync/vendor/mrjgreen/database/src/Connection.php(360): Database\Exception\ExceptionHandler->handle('insert into my...', Array, Object(PDOException)) #1 phar:///usr/bin/db-sync/vendor/mrjgreen/database/src/Connection.php(326): Database\Connection->execute('insert into my...', Array, false)
#2 phar:///usr/bin/db-sync/vendor/mrjgreen/database/src/Connection.php(244): Database\Connection->run('insert into my...', Array) #3 phar:///usr/bin/db-sync/vendor/mrjgreen/database/src/Query/Builder.php(1662): Database\Connection->query('insert into my...', Array)
#4 phar:///usr/bin/db-sync/vendor/mrjgreen/database/src/Query/Builder.php(1674): Database\Query\Builder->insertUpdate(Array, Array)
#5 phar:///usr/bin/db-sync/src/Table.php(209): Database\Query\Builder->insertOnDuplicateKeyUpdate(Array, Array)
#6 phar:///usr/bin/db-sync/src/DbSync.php(165): DbSync\Table->insert(Array, Array)
#7 phar:///usr/bin/db-sync/src/DbSync.php(115): DbSync\DbSync->copy(Object(DbSync\Table), Object(DbSync\Table), Array, Array, Array)
#8 phar:///usr/bin/db-sync/src/DbSync.php(118): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 8, Array)
#9 phar:///usr/bin/db-sync/src/DbSync.php(118): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 16, Array)
#10 phar:///usr/bin/db-sync/src/DbSync.php(118): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 32, Array)
#11 phar:///usr/bin/db-sync/src/DbSync.php(118): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 64, Array)
#12 phar:///usr/bin/db-sync/src/DbSync.php(118): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 128, Array)
#13 phar:///usr/bin/db-sync/src/DbSync.php(118): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 256, Array)
#14 phar:///usr/bin/db-sync/src/DbSync.php(118): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 512, Array)
#15 phar:///usr/bin/db-sync/src/DbSync.php(79): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 1024)
#16 phar:///usr/bin/db-sync/src/Command/SyncCommand.php(178): DbSync\DbSync->sync(Object(DbSync\Table), Object(DbSync\Table), Object(DbSync\ColumnConfiguration), Object(DbSync\ColumnConfiguration))
#17 phar:///usr/bin/db-sync/src/Command/SyncCommand.php(110): DbSync\Command\SyncCommand->fire()
#18 phar:///usr/bin/db-sync/vendor/symfony/console/Command/Command.php(259): DbSync\Command\SyncCommand->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#19 phar:///usr/bin/db-sync/bin/sync(75): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#20 phar:///usr/bin/db-sync/bin/sync(91): DbSync\Command\App->run(Object(DbSync\Command\SyncCommand))
#21 /usr/bin/db-sync(12): require('phar:///usr/bin...')
#22 {main}
#0 phar:///usr/bin/db-sync/vendor/mrjgreen/database/src/Connection.php(360): Database\Exception\ExceptionHandler->handle('insert into my...', Array, Object(PDOException)) #1 phar:///usr/bin/db-sync/vendor/mrjgreen/database/src/Connection.php(326): Database\Connection->execute('insert into my...', Array, false)
#2 phar:///usr/bin/db-sync/vendor/mrjgreen/database/src/Connection.php(244): Database\Connection->run('insert into my...', Array) #3 phar:///usr/bin/db-sync/vendor/mrjgreen/database/src/Query/Builder.php(1662): Database\Connection->query('insert into my...', Array)
#4 phar:///usr/bin/db-sync/vendor/mrjgreen/database/src/Query/Builder.php(1674): Database\Query\Builder->insertUpdate(Array, Array)
#5 phar:///usr/bin/db-sync/src/Table.php(209): Database\Query\Builder->insertOnDuplicateKeyUpdate(Array, Array)
#6 phar:///usr/bin/db-sync/src/DbSync.php(165): DbSync\Table->insert(Array, Array)
#7 phar:///usr/bin/db-sync/src/DbSync.php(115): DbSync\DbSync->copy(Object(DbSync\Table), Object(DbSync\Table), Array, Array, Array)
#8 phar:///usr/bin/db-sync/src/DbSync.php(118): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 8, Array)
#9 phar:///usr/bin/db-sync/src/DbSync.php(118): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 16, Array)
#10 phar:///usr/bin/db-sync/src/DbSync.php(118): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 32, Array)
#11 phar:///usr/bin/db-sync/src/DbSync.php(118): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 64, Array)
#12 phar:///usr/bin/db-sync/src/DbSync.php(118): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 128, Array)
#13 phar:///usr/bin/db-sync/src/DbSync.php(118): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 256, Array)
#14 phar:///usr/bin/db-sync/src/DbSync.php(118): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 512, Array)
#15 phar:///usr/bin/db-sync/src/DbSync.php(79): DbSync\DbSync->doComparison(Object(DbSync\Table), Object(DbSync\Table), Array, 'CONCAT(COALESCE...', 1024)
#16 phar:///usr/bin/db-sync/src/Command/SyncCommand.php(178): DbSync\DbSync->sync(Object(DbSync\Table), Object(DbSync\Table), Object(DbSync\ColumnConfiguration), Object(DbSync\ColumnConfiguration))
#17 phar:///usr/bin/db-sync/src/Command/SyncCommand.php(110): DbSync\Command\SyncCommand->fire()
#18 phar:///usr/bin/db-sync/vendor/symfony/console/Command/Command.php(259): DbSync\Command\SyncCommand->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#19 phar:///usr/bin/db-sync/bin/sync(75): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#20 phar:///usr/bin/db-sync/bin/sync(91): DbSync\Command\App->run(Object(DbSync\Command\SyncCommand))
#21 /usr/bin/db-sync(12): require('phar:///usr/bin...')
#22 {main}

Best regards,
Ben

from source column X to target column Y

I have seen there is an option to change the target table, is there a way to change the target column?
So for example I have might have app.users with -c username , and i would like to it to copy that to app2.users -c uid.

') < ()) t limit 1' added into the query

Hello,
I really want db-sync to work with my databases but I can't run the command.
For some reason, the scrip is adding some strange characters into query and generating this error:

This is the full query created by the script:
select CONCAT(COALESCE(LOWER(CONV(BIT_XOR(CAST(CONV(SUBSTR(MD5(CONCAT_WS('#',ID, asin, categories, VERSION)),17,16),16,10) AS UNSIGNED)), 10, 16)), 0),COALESCE(LOWER(CONV(BIT_XOR(CAST(CONV(SUBSTR(MD5(CONCAT_WS('#', ID, asin, categories, VERSION)),1,16),16,10) AS UNSIGNED)), 10, 16)), 0)) from (select ID, asin, categories, VERSIONfromlibrospa_amazon.Tasin where () < ()) t limit 1

How can I fix it? Why it is not working?

Thanks! Great work!

Correct db-sync code for PHP version (correcting DbSync\Transfer error)

Hello,
The original code is giving error while loading DbSync\Transfer. The reason is that the file is here:
DbSync\Transfer\Transfer.
The correct code (for at least Windows installation with composer):

require 'C:\Users\Administrator\vendor\autoload.php';

use DbSync\DbSync;
use DbSync\Transfer\Transfer;
use DbSync\Hash\ShaHash;
use DbSync\Table;
use DbSync\ColumnConfiguration;

$blockSize = 1024;
$transferSize = 128;
$sourceDb = "dbname";
$sourceHost = "sourcehost";
$sourceUser = "user";
$sourcePassword = "pw";
$targetDb = "dbname";
$targetHost = "targethost";
$targetUser = "name";
$targetPassword = "user";

$shah = new ShaHash();
$trf = new Transfer($shah, $blockSize, $transferSize);
$sync = new DbSync($trf);

// $sync->setLogger(new YourPsrLogger());

$sync->dryRun(false);
$sync->delete(true);

$factory = new \Database\Connectors\ConnectionFactory();

$sourceConnection = $factory->make(array(
'driver' => 'mysql',
'host' => $sourceHost,
'username' => $sourceUser,
'password' => $sourcePassword,
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

// Don't connect until we execute our first query
'lazy'      => true,

// Set PDO attributes after connection
'options' => array(
    PDO::MYSQL_ATTR_LOCAL_INFILE    => true,
    PDO::ATTR_EMULATE_PREPARES      => true,
)

));
$targetConnection = $factory->make(array(
'driver' => 'mysql',
'host' => $targetHost,
'username' => $targetUser,
'password' => $targetPassword,
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

// Don't connect until we execute our first query
'lazy'      => true,

// Set PDO attributes after connection
'options' => array(
    PDO::MYSQL_ATTR_LOCAL_INFILE    => true,
    PDO::ATTR_EMULATE_PREPARES      => true,
)

));

// if you only want specific columns
// $columnConfig = new ColumnConfiguration($syncColumns, $ignoreColumns);

// optionally apply a where clause - this can be useful when sync-ing large tables, where
// you can make use of a column to rule out large portions of the data
// that you know haven't changed, such as columns with "on update CURRENT_TIMESTAMP" etc..
// $sourceTable->setWhereClause(new WhereClause("column_name = ?", ['value']));
// $targetTable->setWhereClause(new WhereClause("column_name > ?", ['value']));

$sourceTab = "tablename";
$sourceTable = new Table($sourceConnection, $sourceDb, $sourceTab);
$targetTable = new Table($targetConnection, $targetDb, $sourceTab);
$sync->sync($sourceTable, $targetTable ); //, $columnConfig);
echo "$sourceTab syncronised
";

// Tested and working!

SQL Error with v3.2.0

With MySQL 5.7

[notice] Dry run only. No data will be written to target.
[info] Hash calculation:

CONCAT(COALESCE(LOWER(CONV(BIT_XOR(CAST(CONV(SUBSTR(MD5(CONCAT_WS('#', `id`, `date`, `time`, `ip`, `urlrequested`, `agent`, `referrer`, `search`, `os`, `browser`, `searchengine`, `spider`, `feed`, `user`, `timestamp`, `language`, `country`, `realpost`, `post_title`)),17,16),16,10) AS UNSIGNED)), 10, 16)), 0),COALESCE(LOWER(CONV(BIT_XOR(CAST(CONV(SUBSTR(MD5(CONCAT_WS('#', `id`, `date`, `time`, `ip`, `urlrequested`, `agent`, `referrer`, `search`, `os`, `browser`, `searchengine`, `spider`, `feed`, `user`, `timestamp`, `language`, `country`, `realpost`, `post_title`)),1,16),16,10) AS UNSIGNED)), 10, 16)), 0))
SQLSTATE[42000]: Syntax error or access violation: 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 ') < ()) t limit 1' at line 1

Column not found: 1054 Unknown column 'fieldname' in 'field list'

Hello Friends,

When i try to sync to target, it says:

Column not found: 1054 Unknown column 'fieldname' in 'field list'

How to first create the column and then sync ? Or do it both is that possible ?

Thanks

Feature request: ordering field

Hi!

I have several huge tables with primitive versioning. Actual data are in the end of table, so I prefer to sync from end of table to beginning but there are no such option.

I found orderBy($primaryKey) line but not sure it's correct thing to change this line without changing something #else.

Sync multiple tables

Is there a way to sync multiple tables? The options doesn't seem to provide a way to do that.

The "target.pass" option does not exist.

I'm trying to sync a remote db with my local db and are getting The "target.pass" option does not exist.

I have installed db-sync via composer are using version v3.2.0 on Linux.

My sync command looks like this:

./vendor/bin/sync -v --user USERNAME --password PASSWORD 1.2.3.4 127.0.0.1 dbname.bs_users

My local and remote db share the same credentials. I've tried to be more explicit and declare target.password but I get the same error message.

How can I debug this?

OUTPUT:

Reading ini file 'dbsync.ini'
The "target.pass" option does not exist.
#0 /var/www/trustpet.dk/sql_dumps/sync/vendor/mrjgreen/db-sync/src/Command/SyncCommand.php(116): Symfony\Component\Console\Input\Input->setOption('target.pass', 'cassandra457')
#1 /var/www/trustpet.dk/sql_dumps/sync/vendor/symfony/console/Command/Command.php(265): DbSync\Command\SyncCommand->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#2 /var/www/trustpet.dk/sql_dumps/sync/vendor/mrjgreen/db-sync/bin/sync(76): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#3 /var/www/trustpet.dk/sql_dumps/sync/vendor/mrjgreen/db-sync/bin/sync(92): DbSync\Command\App->run(Object(DbSync\Command\SyncCommand))
#4 {main}

No primary keys table sync issue!

Thanks for the code. It's a nice tool to sync data fo MySQL, but for the table that there is no primary key, can't sync data.May it possible to manully define multi-columns as a 'primary key' to locate checksum caculation to sync no primary key's table.

Base table or view not found: 1146 Table 'TestDatabase;.wp_bp_activity'

I am trying to sync WordPress database once successful, I will be using this script with my custom tables.

Following is my code

setLogger(new Psr\Log\NullLogger()); $sync->dryRun(true); $sync->delete(false); $factory = new Database\Connectors\ConnectionFactory(); $sourceConnection = $factory->make(array( 'driver' => 'mysql', 'host' => 'localhost', 'username' => 'root', 'password' => 'password', 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'database' => 'TestDatabase', 'prefix' => '', 'strict' => false, )); $sourceDb = 'TestDatabase;'; $sourceTable = 'wp_bp_activity'; $targetConnection = $factory->make(array( 'driver' => 'mysql', 'host' => 'localhost', 'username' => 'root', 'password' => 'password', 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', 'strict' => false, )); $targetDb = "TestDatabase"; $targetTable = "XXwp_bp_activity"; $sourceTable = new Table($sourceConnection, $sourceDb, $sourceTable); $targetTable = new Table($targetConnection, $targetDb, $targetTable); // if you only want specific columns //$columnConfig = new ColumnConfiguration($syncColumns, $ignoreColumns); // optionally apply a where clause - this can be useful when sync-ing large tables, where // you can make use of a column to rule out large portions of the data // that you know haven't changed, such as columns with "on update CURRENT_TIMESTAMP" etc.. //$sourceTable->setWhereClause(new WhereClause("column_name = ?", ['value'])); //$targetTable->setWhereClause(new WhereClause("column_name > ?", ['value'])); $sync->sync($sourceTable, $targetTable, $columnConfig); ?>

I tried with and without 'database' => 'TestDatabase' and 'strict' => false or true

but I get following error.

Fatal error: Uncaught exception 'Database\Exception\QueryException' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'TestDatabase;.wp_bp_activity' doesn't exist driver: mysql host: localhost username: root charset: utf8 collation: utf8_unicode_ci database: TestDatabase prefix: strict: SQL: SHOW INDEX FROM TestDatabase;.wp_bp_activity WHERE key_name = 'PRIMARY'' in /var/www/testing.com/dbsync/src/Exception/ExceptionHandler.php:49 Stack trace: #0 /var/www/testing.com/dbsync/src/Connection.php(360): Database\Exception\ExceptionHandler->handle('SHOW INDEX FROM...', Array, Object(PDOException)) #1 /var/www/testing.com/dbsync/src/Connection.php(326): Database\Connection->execute('SHOW INDEX FROM...', Array, true) #2 /var/www/testing.com/dbsync/src/Connection.php(234): Database\Connection->run('SHOW INDEX FROM...', Array, true) #3 /var/www/testing.com/dbsync/src/Table.php(100): Database\Connection->fetchAll('SHOW INDEX FROM. in /var/www/testing.com/dbsync/src/Exception/ExceptionHandler.php on line 49

Different column destination? is it posible?

I am trying to sync to different databases, with different tables and different column names. I s it possible to reference different columns to sync?

could we sync tables with same host but different port for different db

if so, what is the syntax to do that? thanks. -- Shenghua

running as cronjob

Thanks for a great tool. I had 2 questions related to running db-sync as a cronjob via cli:

Is there a way to suppress the output to certain log levels, so that only warnings or errors are printed?
I noticed that when an error occurs, the exit code is still 0. Is it possible to get a non-zero exit code when there is an error?

Using PHP Non-CommandLine - Sync All tables

Hi
I am using php page and have added code from the sample.

I am able to sync single table.
I would like to sync all the tables.
Instead of specifying tables names in my php code would it be possible to sync all the tables.

Could you please share a sample code for the same.

Laravel 5.6 and Symfony Console

I'm using Laravel 5.6.*. It's in my composer.json as: "laravel/framework": "~5.6.0". As a result, it requires "symfony/console": "~4.0" in my composer.lock file.

When I try to install: composer require joegreen0991/db-sync --dev

I get this error:

Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - Installation request for joegreen0991/db-sync ^3.3 -> satisfiable by joegreen0991/db-sync[v3.3.0].
    - Conclusion: remove symfony/console v4.0.6
    - Conclusion: don't install symfony/console v4.0.6
    - joegreen0991/db-sync v3.3.0 requires symfony/console ^3.2 -> satisfiable by symfony/console[v3.2.0, v3.2.1, v3.2.10, v3.2.11, v3.2.12, v3.2.13, v3.2.14, v3.2.2, v3.2.3, v3.2.4, v3.2.5, v3.2.6, v3.2.7, v3.2.8, v3.2.9, v3.3.0, v3.3.1, v3.3.10, v3.3.11, v3.3.12, v3.3.13, v3.3.14, v3.3.15, v3.3.16, v3.3.2, v3.3.3, v3.3.4, v3.3.5, v3.3.6, v3.3.7, v3.3.8, v3.3.9, v3.4.0, v3.4.1, v3.4.2, v3.4.3, v3.4.4, v3.4.5, v3.4.6].
    - Can only install one of: symfony/console[v3.2.0, v4.0.6].
    - Can only install one of: symfony/console[v3.2.1, v4.0.6].
    - Can only install one of: symfony/console[v3.2.10, v4.0.6].
    - Can only install one of: symfony/console[v3.2.11, v4.0.6].
    - Can only install one of: symfony/console[v3.2.12, v4.0.6].
    - Can only install one of: symfony/console[v3.2.13, v4.0.6].
    - Can only install one of: symfony/console[v3.2.14, v4.0.6].
    - Can only install one of: symfony/console[v3.2.2, v4.0.6].
    - Can only install one of: symfony/console[v3.2.3, v4.0.6].
    - Can only install one of: symfony/console[v3.2.4, v4.0.6].
    - Can only install one of: symfony/console[v3.2.5, v4.0.6].
    - Can only install one of: symfony/console[v3.2.6, v4.0.6].
    - Can only install one of: symfony/console[v3.2.7, v4.0.6].
    - Can only install one of: symfony/console[v3.2.8, v4.0.6].
    - Can only install one of: symfony/console[v3.2.9, v4.0.6].
    - Can only install one of: symfony/console[v3.3.0, v4.0.6].
    - Can only install one of: symfony/console[v3.3.1, v4.0.6].
    - Can only install one of: symfony/console[v3.3.10, v4.0.6].
    - Can only install one of: symfony/console[v3.3.11, v4.0.6].
    - Can only install one of: symfony/console[v3.3.12, v4.0.6].
    - Can only install one of: symfony/console[v3.3.13, v4.0.6].
    - Can only install one of: symfony/console[v3.3.14, v4.0.6].
    - Can only install one of: symfony/console[v3.3.15, v4.0.6].
    - Can only install one of: symfony/console[v3.3.16, v4.0.6].
    - Can only install one of: symfony/console[v3.3.2, v4.0.6].
    - Can only install one of: symfony/console[v3.3.3, v4.0.6].
    - Can only install one of: symfony/console[v3.3.4, v4.0.6].
    - Can only install one of: symfony/console[v3.3.5, v4.0.6].
    - Can only install one of: symfony/console[v3.3.6, v4.0.6].
    - Can only install one of: symfony/console[v3.3.7, v4.0.6].
    - Can only install one of: symfony/console[v3.3.8, v4.0.6].
    - Can only install one of: symfony/console[v3.3.9, v4.0.6].
    - Can only install one of: symfony/console[v3.4.0, v4.0.6].
    - Can only install one of: symfony/console[v3.4.1, v4.0.6].
    - Can only install one of: symfony/console[v3.4.2, v4.0.6].
    - Can only install one of: symfony/console[v3.4.3, v4.0.6].
    - Can only install one of: symfony/console[v3.4.4, v4.0.6].
    - Can only install one of: symfony/console[v3.4.5, v4.0.6].
    - Can only install one of: symfony/console[v3.4.6, v4.0.6].
    - Installation request for symfony/console (locked at v4.0.6) -> satisfiable by symfony/console[v4.0.6].


Installation failed, reverting ./composer.json to its original content.

It appears that @vamsweth was able to work around the issue somehow as noted in #32, but the solution is not clear to me.

Delete from source does not sync to Destination

Hi,

I am able to get updated and newly added records to the destination database.

Any record deleted in the source table is not synced to the destination.
the destination database still shows the record which is deleted in the source table.

Is there any settings to delete the records which no longer exists in the source table.
Re-Sync entire table will be a time-consuming process as we will not know when a record is deleted in the source database.

symfony/console issue with Laravel 5.2.*

Hello,

Can you provide the installation procedure to use this library in laravel?

Tried to install using composer but failed due to symfony/console version issue.

[Feature] Flag to change hash algoritm

Currently the default hash algorithm for checking similarity seems to be MD5. Since MD5 and SHA1 has known collision flaws, I'm a bit worried about using MD5 as a diff hash strategy.

It would be nice if I could specify another hash algorithm in the CLI. Perhaps SHA-224 could be offered? CRC-32 also appears to be a poor choice for comparison.

Hi,

Thannks for the code,

i am new to php. and developing one phonegap application . so any idea how can i send table to this code from my application?

and is this worth to send total table to server ?

Specify options in Php?

Is it possible to specify options in the php example such as "-C updated_at" which uses only the "updated_at" column for hash calculation?

Thank you!

could not find driver

I have a problem that is most likely just a stupid user error, but it would be nice to have some help with this.

Mysql is working ok

sudo ./db-sync 127.0.0.1 xxx.xxx.xxx.xxx sync_testi.sync1
Connection to 'mysql:host=127.0.0.1;port=3306' failed: could not find driver
#0 phar:///home/pi/db-sync/db-sync/vendor/mrjgreen/database/src/Connectors/MySqlConnector.php(21): Database\Connectors\Connector->createConnection('mysql:host=127....', Array, Array)
#1 phar:///home/pi/db-sync/db-sync/vendor/mrjgreen/database/src/Connectors/ConnectionFactory.php(119): Database\Connectors\MySqlConnector->connect(Array)
#2 phar:///home/pi/db-sync/db-sync/vendor/mrjgreen/database/src/Connectors/ConnectionFactory.php(97): Database\Connectors\ConnectionFactory->createSingleConnection(Array, false)
#3 phar:///home/pi/db-sync/db-sync/vendor/mrjgreen/database/src/Connectors/ConnectionFactory.php(77): Database\Connectors\ConnectionFactory->makeConnection(Array, false)
#4 phar:///home/pi/db-sync/db-sync/src/Command/SyncCommand.php(220): Database\Connectors\ConnectionFactory->make(Array)
#5 phar:///home/pi/db-sync/db-sync/src/Command/SyncCommand.php(148): DbSync\Command\SyncCommand->createConnection('127.0.0.1', 'root', NULL, 'utf8')
#6 phar:///home/pi/db-sync/db-sync/src/Command/SyncCommand.php(126): DbSync\Command\SyncCommand->fire()
#7 phar:///home/pi/db-sync/db-sync/vendor/symfony/console/Command/Command.php(262): DbSync\Command\SyncCommand->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#8 phar:///home/pi/db-sync/db-sync/bin/sync(75): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#9 phar:///home/pi/db-sync/db-sync/bin/sync(91): DbSync\Command\App->run(Object(DbSync\Command\SyncCommand))
#10 /home/pi/db-sync/db-sync(12): require('phar:///home/pi...')
#11 {main}

db-sync3 error

Hi! I get an error when calling the commands.

Command:

c:\server\OpenServer\domains\mysite.com>php console\tools\db-sync3.phar --user root --pass 123 127.0.0.1 --target.user root 127.0.0.1 table_source.service --target.table
table_destination.service

Error:
Fatal error: Call to undefined function DbSync\Command\posix_getpwuid() in phar://C:/server/OpenServer/domains/mysite.com/console/tools/db-sync3.phar/src/Command/SyncCommand.php on line 35

Syntax error or access violation: 1064

Hi,

I'm trying to sync two remote Db's programatically and am getting the following error:

SQLSTATE[42000]: Syntax error or access violation: 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 ') < ()) t limit 1' at line 1 host: dev.workspace driver: mysql username: root charset: utf8 collation: utf8_general_ci SQL: select CONCAT(COALESCE(LOWER(CONV(BIT_XOR(CAST(CONV(SUBSTR(MD5(CONCAT_WS('#', id, parent_id, name, code, description)),17,16),16,10) AS UNSIGNED)), 10, 16)), 0),COALESCE(LOWER(CONV(BIT_XOR(CAST(CONV(SUBSTR(MD5(CONCAT_WS('#', id, parent_id, name, code, description)),1,16),16,10) AS UNSIGNED)), 10, 16)), 0)) from (select id, parent_id, name, code, descriptionfromcashbuild_store.levels where () < ()) t limit 1

The issue seems to be caused by the where clause at the end of the statement, I managed to track it down to getWhereEnd in the Table class, but have not been able to resolve it.

This is the script running the sync:

`<?php

require 'vendor/autoload.php';
use DbSync\DbSync;
use DbSync\Transfer\Transfer;
use DbSync\Table;
use DbSync\WhereClause;
use DbSync\Hash\ShaHash;
use DbSync\ColumnConfiguration;

class Sync {
public function test() {

   $sync = new DbSync(new Transfer(new ShaHash(), 1024, 8));

    $sync->dryRun(false);

    $sync->delete(false); //This should get set to true for our use case

    $sourceCred = array(
        'host'      => '',
        'driver'    => 'mysql',
        'username'  => '',
        'password'  => '',
        'charset'   => 'utf8',
        'collation' => 'utf8_general_ci',
    );

    $sourceConnection = (new \Database\Connectors\ConnectionFactory())->make($sourceCred); //This is important

    $targetCred = array(
        'host'      => '',
        'driver'    => 'mysql',
        'username'  => 'root',
        'password'  => '',
        'charset'   => 'utf8',
        'collation' => 'utf8_general_ci',
    );

    $targetConnection = (new \Database\Connectors\ConnectionFactory())->make($targetCred);

    $sourceDb = 'cashbuild_live';
    $sourceTable = 'levels';

    $targetDb = 'cashbuild_store';
    $targetTable = 'levels';

    $sourceTable = new Table($sourceConnection, $sourceDb, $sourceTable);
    $targetTable = new Table($targetConnection, $targetDb, $targetTable);
    return $sync->sync($sourceTable, $targetTable); //Returns an object with the number of changes made

}

}
`

Any help would be appreciated.
Regards

Support different database names

Please add support for different database names for sync source and target. I need to sync 2 databases on one host with different name, seems this is not possible at now.

Question: what are the plans for future development?

I came across this nice package and intent to use it for a prototype/software package that is still in development. I need to sync two installations of the same software package, where one system can be used 'offline' as a 'slave' system (no edits/deletes) but needs to remain up to date with the 'master' once in a while.

This package can do this; how is the future looking for the development? The last update was 11 days ago, but before that a few months of no activity? Is this still an active developed project?

Sincerely
Edwin

port option

to sync remote database is often done via SSH tunneling - then MySQL can be reached at a different port. The port option is missing?

Is it compatible with MS Windows?

Can I use DB-Sync on Windows, what steps needed to port it to Windows? The binary .phar seems to target Linux only?

ini file aborts script

I am having a problem using an ini file for the connection criteria
both in using a default 'dbsync.ini' and the same result using the '--config-file my-cfg.ini' option
I have verified a working command line, however when I move that config data into a *.ini file the script seems to abort right after the message;
Reading ini file 'my-cfg.ini.ini'
--verbose displays nothing additional.
though
-v does trigger a display of
ea-php-cli Copyright 2017 cPanel, Inc.

oddly ... if I leave the --execute off the command, I do get;
[notice] Dry run only. No data will be written to target.

when using without an ini file and --verbose the next line is;
[info] Hash calculation:
with the ini file neither that or anything else is displayed following the reading of the ini and Dry run notice.

*** EDIT ***
not sure if this should be a separate post ... but IS related to the the reading of the ini file.
the password string is rejected if includes certain characters. double quotes and carats for sure ... possibly others? though that same password string works fine via command line without ini.

Loss of significance in cast to integer?

db-sync/src/Hash/HashAbstract.php

Line 7 in d484fce

    
           return "COALESCE(LOWER(CONV(BIT_XOR(CAST(" . $hash . " AS UNSIGNED)), 10, 16)), 0)";

It would appear that the cast to integer looses significance of the hash value. For example with an MD5 hash selected:

select md5('#'), cast(md5('#') as unsigned);
+----------------------------------+----------------------------+
| md5('#') | cast(md5('#') as unsigned) |
+----------------------------------+----------------------------+
| 01abfc750a0c942167651c40d088531d | 1 |
+----------------------------------+----------------------------+

The MD5 hash would appear to be truncated starting at the first character that is not 0-9.

How to use as PHP page

Is there a complete example anywhere of how to use this on a PHP web page?

php, php-mysql packages

It seems that "php" and "php-mysql" must to be install before executing db-sync.
I recommend that installation instruction is written on the README.

Composer: Uncaught Error: Class 'DBSync\DbSync' not found

Hi, I'm completely new to composer, so I do not know if this is an issue with how db-sync is configured or how I use composer. I suspect it's the latter, but anyhow it could be an idea to include the answer in the README for other composer newbies.

When I do new DBSync() I get a fatal error. Here is a quick test of what I'm doing:

<?php
require __DIR__ . '/vendor/autoload.php';

use DbSync\Hash\ShaHash;
use DBSync\DbSync;

new ShaHash();
new DBSync(); // this fails with Class 'DBSync\DbSync' not found in /.../sync/sync.php:8
?>

Note that the new ShaHash(); line seems to work just fine.

Table sync sql error duplicate entries

I keep getting this problem for tables with UNIQUE KEY:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '[email protected]' for key 'CUSTOMER_ENTITY_EMAIL_WEBSITE_ID'

this table has "entity_id" as the primary key and "email + website_id" as UNIQUE KEY.

I checked nothing changed for that particular record, so each time it hit the problem, i have to open the sql console, delete the row and re-run the db-sync script.

Please help. Thanks

-Gunardi Wu

Please add missing test db SQL

It's pretty hard to hack and run the tests with db-sync, when I don't have the test data, making it difficult to contribute.

Could you please commit a SQL dump with the dbsynctest tables?

I'm trying to fix an issue with a horrible db I have inherit where a lot of DATE fields has 0000-00-00 values, and I get the following error: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '0000-00-00' for column 'hascancelleddate' at row 1. I would like to fix it and create a PR but still know that the tests will pass.

If nothing else then I want to make sure that the error message is not cut off - I can not see the entire failing SQL statement. I've just begun to poke around the source but line 80 in sync is my starting point.

Array offset error occurred when reading username and password

What should I do? I just ran like:

db-sync --user $DB_USER --password $DB_PASS $DEV_IP $LIVE_IP $table

But, got an error:

#0 phar:///usr/local/bin/db-sync/src/Command/SyncCommand.php(128): Symfony\Component\Debug\ErrorHandler->handleError(8, 'Undefined offse...', 'phar:///usr/loc...', 128, Array)
#1 phar:///usr/local/bin/db-sync/src/Command/SyncCommand.php(102): DbSync\Command\SyncCommand->fire()
#2 phar:///usr/local/bin/db-sync/vendor/symfony/console/Command/Command.php(259): DbSync\Command\SyncCommand->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#3 phar:///usr/local/bin/db-sync/bin/sync(67): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#4 phar:///usr/local/bin/db-sync/bin/sync(82): DbSync\Command\App->run(Object(DbSync\Command\SyncCommand))
#5 /usr/local/bin/db-sync(12): require('phar:///usr/loc...')
#6 {main}

Error if using a column that is not id as primary key

I have found an issue, that if I set a different column as the primary key, it says that the MySQL syntax is incorrect.

[info] Hash calculation:

CONCAT(COALESCE(LOWER(CONV(BIT_XOR(CAST(CONV(SUBSTR(MD5(CONCAT_WS('#', `username`, `groupname`, `priority`)),17,16),16,10) AS UNSIGNED)), 10, 16)), 0),COALESCE(LOWER(CONV(BIT_XOR(CAST(CONV(SUBSTR(MD5(CONCAT_WS('#', `username`, `groupname`, `priority`)),1,16),16,10) AS UNSIGNED)), 10, 16)), 0))

Question: The table ... does not have a primary key

Hi @mrjgreen,

I'm really happy with your tool. It works really great by now.
The only problem I experience is related to tables without a primary key.

There I get the following error:
The table ... does not have a primary key

Is it necessary to have a primary key in case of comparing the tables?
Or is there a possibility to sync without.
It would also be ok if I could tell the tool to simply overwrite all the rows in target_db.table by source_db.table without comparing, if there is no primary key.

Thank you!

Impossible to use due to the namespaces (non-commandline)

I find this tool impossible to use for non-commandline.

I simply wanted to test on localhost before using in production.

I downloaded the archive, extracted it to 'test' directory in root folder. I copied and pasted the sample code under the "Use library within project (non-commandline)" section of Readme.md

I got error: "Fatal error: Class 'DbSync\DbSync' not found in /media/deo/windowsbase/xampp/htdocs/iuo/portal/toolz/mysync/test.php on line 9"

No amount of tweaking made it work, including use include()/required(), appending backlash to beginning of namespace use directive, etc. (although my experience of using PHP namespaces is that you have to include() or require() source namespace files before using them, but does not work here)

I simply want to test this tool, now the first attempt at test is not even working, or is it simply impossible to use it as a normal PHP class script without any installations/extension loading?

The names of the columns

Hello! There is a problem with the column names that contain reserved words. I get an error when I sync table contains a column called "return"

Dependency issue with symfony/console

I would like to use this package but I'm running into dependency issues with symfony/console.
db-sync is using ^2.7 and my project is up to date with the latest version (^3.1).

Any chance you're upgrading soon?

Do not sync table that does not have a primary key

Don't ask me why but sometimes I've got some tables that do not have a primary key ;-)

Why not to sync such tables?

The table db.table does not have a primary key
#0 phar:///usr/bin/db-sync/src/Command/SyncCommand.php(178): DbSync\DbSync->sync(Object(DbSync\Table), Object(DbSync\Table), Object(DbSync\ColumnConfiguration), Object(DbSync\ColumnConfiguration))
#1 phar:///usr/bin/db-sync/src/Command/SyncCommand.php(110): DbSync\Command\SyncCommand->fire()
#2 phar:///usr/bin/db-sync/vendor/symfony/console/Command/Command.php(259): DbSync\Command\SyncCommand->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#3 phar:///usr/bin/db-sync/bin/sync(75): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#4 phar:///usr/bin/db-sync/bin/sync(91): DbSync\Command\App->run(Object(DbSync\Command\SyncCommand))
#5 /usr/bin/db-sync(12): require('phar:///usr/bin...')
#6 {main}
#0 phar:///usr/bin/db-sync/src/Command/SyncCommand.php(178): DbSync\DbSync->sync(Object(DbSync\Table), Object(DbSync\Table), Object(DbSync\ColumnConfiguration), Object(DbSync\ColumnConfiguration))
#1 phar:///usr/bin/db-sync/src/Command/SyncCommand.php(110): DbSync\Command\SyncCommand->fire()
#2 phar:///usr/bin/db-sync/vendor/symfony/console/Command/Command.php(259): DbSync\Command\SyncCommand->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#3 phar:///usr/bin/db-sync/bin/sync(75): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#4 phar:///usr/bin/db-sync/bin/sync(91): DbSync\Command\App->run(Object(DbSync\Command\SyncCommand))
#5 /usr/bin/db-sync(12): require('phar:///usr/bin...')
#6 {main}