How to use `connection.initBinlog`

[KrishnaPG]

Typically we do this

var db = mysql.createConnection({
  host     : 'example.org',
  user     : 'bob',
  password : 'secret'
});

db.connect();

So, should db.initBinlog() come after the above two lines or should it be used to replace them?

The reason I am asking is, theinitBinLog() is accepting settings object which is essentially same as the one supplied to createConnection() - right?

[numtel]

Yes, you must place db.initBinlog() afterwards. The settings passed to initBinlog must provide a user that has replication privileges. You can grant these with the following query:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'localhost'

Since the connection from mysql.createConnection is not required to have these privileges, I decided to use this format. The extra number of connections is a limitation that I'm aware of. It would probably be nice to use fewer connections if possible.

[KrishnaPG]

Thank you for the explanation. It is helpful.

[KrishnaPG]

A quick question: Presently I am doing

db.initUpdateTable("users");
db.initUpdateTable("acls");

two times for two tables (since it seems initUpdateTable is table specific).

But it looks like initBinlog is not table specific and scoped at db-level, so it should be enough to call initBinlog() just once for all tables - right?

I am doing as below - looks like it is working - but just want to make sure I am following the right procedure:

var dbConnSettings = {
    host: "xxx.xxx.xxx.xxx",
    port: 12321,
    database: "dbName",
    user: "user-name",
    password: "usre-password"  
};

var db = mysql.createConnection(dbConnSettings);

db.connect();

dbConnSettings["minInterval"] = 1000; //<- reusing the connection settings

db.initBinlog(dbConnSettings);

// --- no need for theses two below ? ---
// db.initUpdateTable("acls");
// db.initUpdateTable("users");

[numtel]

That looks correct for the binlog code.

You should never need to call initUpdateTable more than once. Its argument, tableName, refers to the table that will be created to store update information from the triggers, not the table that you want to watch for changes.

[numtel]

I have just released version 0.1.0 that changes the interface completely in order to make joined queries manageable. Please see the updated readme for more details.

[KrishnaPG]

Having difficult time making the re-activity work with the latest version. Earlier with db.initBinlog I was able to successfully see the results getting updated in the browsers straight from the DB.

But with LiveMysql I am not seeing the reactive update behavior. For testing I am using simple select * query, but couldn't make it work

var dbConnectionSettings = {
    host: "xyz.xyz.xyz.zyx",
    port: 3306,
    database: "authDB",
    user: "dbUser",
    password: "something", 
    minInterval: 1000,
    serverId: 999 
};

var DB = new LiveMysql(dbConnectionSettings, function (err) {
    if (err)
        console.error("Error connecting to DB:");
    else
        console.log("Connected to DB:");
});

Meteor.publish('authUsers', function () {
    return DB.select("select * from users", [{ table: 'users' }]);
});

authUsersColl = new MysqlSubscription('authUsers');

authUsersColl.addEventListener('update', function (index, msg) {
    console.log("updated: ", msg);
});

All the above code is on the server side. The update event listener is getting called initially once for each record while the app starts - but afterwards not getting call for other new records.

I could see the binlog files generated on the server alright with new "INSERT ...." commands inside them.

Any tips on how to troubleshoot this to isolate where the problem could be? Would the Update event listener get triggered when new records added also, or just only when existing records are modified?

[numtel]

That code works for me just fine.

The update event should be emitted on any change: INSERT, UPDATE, or DELETE. It's the changed event for only when rows are modified.

• Is your MySQL server configured to output the binary log in row mode?

  In my.cnf, you need the following line: binlog_format = row

• If so, does the test suite pass on your machine?

[KrishnaPG]

Thank you Ben. The test suite idea seems to be good one - I did not run it till now (not aware of how to invoke that functionality, though I saw in the package code there is some Package.onTest methods).

I am looking into info on how to run the test suites. Meanwhile, if you have any pointers on how to get use test suite please pass on. Thanks much for your time in answering these questions. It is very helpful.

[numtel]

In the last section of the readme, there is a list of commands that you can run to see the test results. This will give a better idea of any specific problems.

First, download this repository using git clone https://github.com/numtel/meteor-mysql.git.

You will need to configure the MySQL connection settings in test/settings/local.json. (Whoops, just noticed that I need to update this file path in the readme :speak_no_evil: ). The database specified should be an empty database with no tables because the tests will create and delete tables as needed.

If you set the recreateDb value to true, the test suite will automatically create the database, allowing you to specify a database name that does not yet exist in local.json.

The command to run Tinytest with the new settings file path is: (Optionally, set the port as well. Results are viewed in the browser.)

meteor test-packages --settings test/settings/local.json --port 3500 ./

[KrishnaPG]

Thank you Ben - this testing looks interesting. I got a combination of results

It says S: Initialization OK followed by fail: assert_equal - expected true - actual "Mismatched lengths" - not, and Insert / Delete OK (69 times) followed by fail — assert_equal - message Row inserted - expected true - actual "Mismatched lengths" - not etc..

Any idea how to interpret this ? In case it helps, this is a Ubuntu 14 LTS VM hosted on Azure.

[KrishnaPG]

It looks like server is fine - I could see binlog dump working on it. The connection info is saying "Master has sent all binlog to slave; waiting for binlog to be updated". Seems some problem my side

[numtel]

Those results mean that there are no binary log row events coming through. This same configuration worked with version 0.0.20?

What version of MySQL server are you running?

Can you post the contents of your my.cnf file?

[KrishnaPG]

$mysql --version
mysql  Ver 14.14 Distrib 5.5.41, for debian-linux-gnu (x86_64) using readline 6.3

The my.cnf file content

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

#
# * Fine Tuning
#
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP

query_cache_limit       = 1M
query_cache_size        = 16M

log_error = /var/log/mysql/error.log

server-id               = 1
binlog_format           = row
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
innodb_flush_log_at_trx_commit=1
sync_binlog=1

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

Does this binlog use any extra ports to be opened, other than the default 3306?

Also what is the entry point in the package that gets called for the biglog updates? Is there some kind of debug/trace switch that we can enable to know whenever its getting called and what parameters it is receiving etc.?

[KrishnaPG]

Ben, the server seems fine - it could be I am doing something wrong in the app with publish/subscribe.

I am verifying with your leaderboard example. It is working great with the same server. So, something wrong on my side. Let me debug it and find out. Will post it once I get something.

[KrishnaPG]

Not sure what is happening - but if I remove the port and minInterval from the connection settings, it looks like the updates are coming. (In the leaderboard example they are were not specified, hence I tried removing them and seems the events are getting pumped into the app).

Frankly, I do not think they are the root-cause the problem (though I suspect minInterval may be playing some role in the whole thing). Anyhow, since it seems to be working, for now, let me use it and see and where it goes.

Thanks for your help - you have been very helpful. I have recommended this meteor-mysql in the Meteor Trello core feature list to be considered as feature (as additional alternative to mongodb)