ISoft Wiki - User contributions [en]

User:Jmckinstry

2016-12-14T23:29:45Z

Jmckinstry: /* About Justin */

== ISoft History ==
Justin was hired in March of 2009, where he began by learning the ins and outs of ITrack Pro and AX.

Currently, he's the main developer for ITrack AX, Replikwando, and a wide swath of tools both internal and external. He also does development work on random projects as they come up, guides with tech support in their endeavors, and does his best to keep interdepartmental communications flowing smoothly. Hats are changed frequently!

== Education and Experience ==
Justin graduated with a Bachelor's Degree in Computer Science (minor in Mathematics) in December of 2008. He's been doing technical support for computers, printers, network connections and infrastructure, televisions, telephones, and server administration for four years, with another two years of programming.

During his time at ISoft, Justin has:
* Written code for AX, Pro, Enterprise, and Presage
* Written web code for LX Warehouse, Presage Scanner, an auction lotting tool (and evaluated/debugged/made suggestions for other large production sites and tools)
* Written scripts for database updating, timed program checks, massive directory manipulation, bidirectional public key file exchanges
* Developed/Maintained internal tools (including Replikwando, a mysql tokenizer, a harebrained symmetrical encryption scheme, and a call tracker)
* Developed external GPL code that ISoft made use of (and made sure the code changes were public, of course):
** [https://code.launchpad.net/~isoft-data-systems/mysql-binlog-parser/trunk mysql-replication-listener]: Fixed a few bugs, added support for writing binlog statements out (so you can inline edit them!)
* Participated in the deployment, maintenance, and upkeep of more than a dozen production databases:
** CPU and Memory utilization
** Errand program debugging and correction
** Establishing, maintaining, and debugging networks on both ends of the performance spectrum (servers using erroring T1 lines is interesting)
* Enjoyed tons of (almost exclusively MySQL and sqlite) database shenanigans:
** Installing and configuring the servers and related programs
** Writing, debugging, optimizing, and tracking queries, stored procedures, functions, views
** Creating and implementing archiving and backup solutions
** Recovering 'lost' data and restoring 'lost' databases
* Created script files for nightly export of large (not terabyte-scale) data sets
* Learned Javascript from scratch well enough to do group project work within 48 hours
* Helped Support streamline and correct processes in an effort to keep things running smoothly (bless our Support staff!)
* Reviewed weekly error logs and reports, distilling recurring issues and relaying the problems and potential causes & solutions to the project managers
* Assisted in the execution of bi-monthly auction days by clerking, helping maintain data consistency and reporting, proving outcomes to customers, and providing post-auction statistics
* Created an inventory appraisal tool that (for auctions at least) has had a 5% margin of error for the past two years running
* Acted as backup System Administrator (mini-Brian, if you will), assisting in recovery from power and network outages, suffering HDD setups, poor network/hardware performance, and making tool recommendations on occasion

And some other stuff. You know. ;)

== Public Keys ==
* Keybase.io: A817266B53CD3F9AB144416C9ABC91ED61DE9BE0 (https://keybase.io/jmckinstry/key.asc)
* pgp.mit.edu: 9ABC91ED61DE9BE0 (https://pgp.mit.edu/pks/lookup?op=get&search=0x9ABC91ED61DE9BE0)

== About Justin ==
While not being an extreme outdoorsman, Justin does like to hunt and fish. Occasional hiking and biking also occur. During the rest of his spare time he enjoys reading, drumming, and playing Rock Band and other video games. He also likes to write small programs using C/C++/javascript/python just for fun.

He once fixed a mangled barcode with a pen and a steady hand.

[[Category:ISoft Employees]]

User:Jmckinstry

2016-11-04T19:53:44Z

Jmckinstry: /* Education and Experience */

== ISoft History ==
Justin was hired in March of 2009, where he began by learning the ins and outs of ITrack Pro and AX.

Currently, he's the main developer for ITrack AX, Replikwando, and a wide swath of tools both internal and external. He also does development work on random projects as they come up, guides with tech support in their endeavors, and does his best to keep interdepartmental communications flowing smoothly. Hats are changed frequently!

== Education and Experience ==
Justin graduated with a Bachelor's Degree in Computer Science (minor in Mathematics) in December of 2008. He's been doing technical support for computers, printers, network connections and infrastructure, televisions, telephones, and server administration for four years, with another two years of programming.

During his time at ISoft, Justin has:
* Written code for AX, Pro, Enterprise, and Presage
* Written web code for LX Warehouse, Presage Scanner, an auction lotting tool (and evaluated/debugged/made suggestions for other large production sites and tools)
* Written scripts for database updating, timed program checks, massive directory manipulation, bidirectional public key file exchanges
* Developed/Maintained internal tools (including Replikwando, a mysql tokenizer, a harebrained symmetrical encryption scheme, and a call tracker)
* Developed external GPL code that ISoft made use of (and made sure the code changes were public, of course):
** [https://code.launchpad.net/~isoft-data-systems/mysql-binlog-parser/trunk mysql-replication-listener]: Fixed a few bugs, added support for writing binlog statements out (so you can inline edit them!)
* Participated in the deployment, maintenance, and upkeep of more than a dozen production databases:
** CPU and Memory utilization
** Errand program debugging and correction
** Establishing, maintaining, and debugging networks on both ends of the performance spectrum (servers using erroring T1 lines is interesting)
* Enjoyed tons of (almost exclusively MySQL and sqlite) database shenanigans:
** Installing and configuring the servers and related programs
** Writing, debugging, optimizing, and tracking queries, stored procedures, functions, views
** Creating and implementing archiving and backup solutions
** Recovering 'lost' data and restoring 'lost' databases
* Created script files for nightly export of large (not terabyte-scale) data sets
* Learned Javascript from scratch well enough to do group project work within 48 hours
* Helped Support streamline and correct processes in an effort to keep things running smoothly (bless our Support staff!)
* Reviewed weekly error logs and reports, distilling recurring issues and relaying the problems and potential causes & solutions to the project managers
* Assisted in the execution of bi-monthly auction days by clerking, helping maintain data consistency and reporting, proving outcomes to customers, and providing post-auction statistics
* Created an inventory appraisal tool that (for auctions at least) has had a 5% margin of error for the past two years running
* Acted as backup System Administrator (mini-Brian, if you will), assisting in recovery from power and network outages, suffering HDD setups, poor network/hardware performance, and making tool recommendations on occasion

And some other stuff. You know. ;)

== Public Keys ==
* Keybase.io: A817266B53CD3F9AB144416C9ABC91ED61DE9BE0 (https://keybase.io/jmckinstry/key.asc)
* pgp.mit.edu: 9ABC91ED61DE9BE0 (https://pgp.mit.edu/pks/lookup?op=get&search=0x9ABC91ED61DE9BE0)

== About Justin ==
While not being an extreme outdoorsman, Justin does like to hunt and fish. Occasional hiking and biking also occur. During the rest of his spare time, he enjoys reading, drumming, and playing Rock Band and other video games. He also likes to write small programs using C/C++/javascript just for fun.

He once fixed a mangled barcode with a pen and a steady hand.

[[Category:ISoft Employees]]

Crash Dumps

2016-09-13T19:20:58Z

Jmckinstry: Created page with "Crash dumps are large generated files when Windows is running a program that has an error that the program cannot survive. This large file contains the entire set of memory the p..."

Crash dumps are large generated files when Windows is running a program that has an error that the program cannot survive. This large file contains the entire set of memory the program was using as it crashed, including both stack (the program's running state) and heap (the generic RAM that's being used for whatever purpose the program wanted), so depending on the program it can be MASSIVE.

If these files are handed to a developer who knows the version of the program that was running, the dev can then see exactly what the program was doing at the time of the crash, which makes duplication of a crash substantially easier.

Crash dumps are only generated if the machine is asked to generate them.

=Generating Crash Dump Files=
These are enabled by creating a registry key for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Windows Error Reporting\LocalDumps. There is no other way to turn on crash dumps, the user's registry must be changed. After making this change, up to 10 full crash dumps will be left in the folder "%TEMP%\crash_dump".

A crash dump will be made any time any program on the system crashes, and the dump will be named after the executable that crashed.

Crashes that come from debug binaries (which are not normally given to customers) are much easier to debug than release binaries, but both can be used.

A registry script file can be found at [https://svn.isoftdata.com/ITrack/trunk/Utility/Scripts/Crash%20Dumps/Enable%20Error%20Dumps.reg Enable Error Dumps.reg].

=Disabling Crash Dumping=
Since dump files can be quite large, they should be turned off for normal clients once you have obtained what you need. This is done by removing the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Windows Error Reporting\LocalDumps, and then deleting the crash dump folder (in our case, "%TEMP%\crash_dump").

A registry script file can be found at [https://svn.isoftdata.com/ITrack/trunk/Utility/Scripts/Crash%20Dumps/Disable%20Error%20Dumps.reg Disable Error Dumps.reg].

=Developer Usage=
When you receive a crash dump, Visual Studio makes it somewhat easy to dig through it. Just open the dump as a solution, and it will run as one. If Visual Studio can find the files on your machine to link up to what's being seen in the crash dump, it will work just like a normal Debug run that has been paused at the time of crash.

Crashes that come from debug binaries (which are not normally given to customers) are much easier to debug than release binaries, but both can be used.

File:MONYog DiskInfo Example.png

2016-03-10T17:34:12Z

Jmckinstry:

File:MONYog Charts.png

2016-03-10T17:19:08Z

Jmckinstry:

File:MONYog Processlist.png

2016-03-10T16:58:41Z

Jmckinstry:

File:MONYog QueryAnalyzer.png

2016-03-10T16:52:51Z

Jmckinstry:

File:MONYog RealTime.png

2016-03-10T00:15:02Z

Jmckinstry:

File:MONYog bell.png

2016-03-09T23:59:06Z

Jmckinstry:

File:Monyog Server List.png

2016-03-04T23:46:39Z

Jmckinstry: A picture of what the Server List looks like and where to find it.

A picture of what the Server List looks like and where to find it.

Writing MySQL queries

2016-01-25T17:29:35Z

Jmckinstry: Protected "Writing MySQL queries": Duplicates internal documentation. Discussion with page admin is ongoing ([edit=sysop] (indefinite) [move=sysop] (indefinite))

== Formatting ==
When writing queries for production (for use in code, or in reports) there are some steps you can take to make your queries easier to read.

=== Case ===
Query text that references MySQL elements (basic query syntax, functions, etc) should be in all uppercase letters, while references to elements of a database structure should be in lowercase. For example, instead of
<source lang="MySQL">
select PANTS from tableofstuff Where date(teh_date) = Now()
</source>

you would write

<source lang="MySQL">
SELECT pants FROM tableofstuff WHERE DATE(teh_date) = NOW()
</source>

=== Backticks ===
When writing production queries, all references to database names, table names, column names, index names, etc should be enclosed in backticks.

For example, instead of
<source lang="MySQL">
SELECT shit FROM creek
</source>

you would write

<source lang="MySQL">
SELECT `shit` FROM `creek`
</source>

=== Newlines ===
Add a newline before every major part of the query (FROM, JOINs, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT). i.e., instead of
<source lang="MySQL">
SELECT `butt_size`, `ugliness` FROM `mothers` WHERE `fat` = 'True' ORDER BY `ugliness`
</source>

you would use

<source lang="MySQL">
SELECT `butt_size`, `ugliness`
FROM `mothers`
WHERE `fat` = 'True'
ORDER BY `ugliness`
</source>

=== Referencing table names ===
MySQL allows you to reference column names without specifying the table name, as long as the column name is not ambiguous in your query. In other words, this:

<source lang="MySQL">
SELECT `hat` FROM `thing1` JOIN `thing2` ON `something` = `somethingelse`
</source>

is a valid query as long as `hat` is a column in either `thing1` or `thing2`, and not both. The same goes for the columns `something` and `somethingelse` - they can only be in one of the tables involved in the query.

However, the way you SHOULD write the query is like so:

<source lang="MySQL">
SELECT `thing1`.`hat` FROM `thing1` JOIN `thing2` ON `thing2`.`something` = `thing1`.`somethingelse`
</source>

In large queries, it is MUCH easier to debug/comprehend queries that tell you the table names of all columns. Also, if a `hat` column is added to the `thing2` table at some point in the future, the former query will produce an error, while the latter will continue working.

=== Aliases ===
It is possible to give your tables aliases using the AS command, so that you can refer to them with an alternate name in a query.

If you are joining a table to itself, it is necessary to give the table an alias at least once.

However, in any other case, it is generally not a good idea in production code - when debugging large queries written by someone else, it's generally a lot easier to figure out where the 20 columns in a SELECT are coming from if the correct table name is used, so you don't have to figure out what "pl.name" is, or where "is.amount" might be in the database.

== Things you just shouldn't do ==
These are not suggestions. If I catch you doing these things in reports or in code, I *will* find you, and inflict serious physical pain upon you.

I am vigilant, and quick to anger when exposed to shoddy query-writing. It's not worth the risk.

=== SELECT * ===
Sure, it's fine for quick queries when you want to see what's sitting in the database. But when you're writing queries for a product, just don't do it.

You should read this [http://parseerror.com/sql/select*isevil.html explanation] of why it's a bad idea - or you could just take my word for it. Whatever it takes to keep you from committing a query that will return an undefined number of rows.

=== Reference a comma-separated list of tables ===
Much to my consternation, most introductions to query-writing seem to start off by showing you an example query like this:
<source lang="MySQL">
SELECT `table1`.`something`, `table2`.`something_else`
FROM `table1`, `table2`
WHERE `table1`.`key` = `table2`.`key`
</source>

Which is perfectly valid SQL, to be sure - but it makes for poor queries, for 2 reasons:
* It's often inefficient
* It takes more work to understand what's happening in the query

This is the sort of query you should be writing:
<source lang="MySQL">
SELECT `table1`.`something`, `table2`.`something_else`
FROM `table1`
JOIN `table2` ON `table1`.`key` = `table2`.`key`
</source>

==== It's more efficient ====
As rows from table2 are joined to table1, rows that do not match the requirements of the ON clause are dropped - in a query that references several tables, this is important.

If all the relationships are defined in the WHERE clause, what happens is that MySQL builds a data set that contains the [http://en.wikipedia.org/wiki/Cartesian_product cartesian product] of all the tables and then narrows them down afterward using the WHERE clause.

MySQL tries its best to do this intelligently, but why expect it to read your mind? Write your JOINs so that after every single JOIN, the dataset will be as small as possible.

==== It's easier to read ====
Generally speaking, the tables that you join together will have an established relationship. When you are reading a query, it should be easy to see how the different parts of it relate to each other.

As long as every JOIN has an ON clause that defines its relationship to the previous tables, this should be the case.

== Common traps ==
Over the history of ITrack, there have been hundreds of bugs fixed in reports, as well as the software itself, that were the result of a logical error in a query.

By using what you know about the database structure, you should be able to write queries that you can guarantee will always return the correct number of results. Here are some tips:

=== Stupid table joins ===
Many of the queries you write will involve 2 tables that have a 1-to-1 or 1-to-many relationship with each other.

This means that you have one table that holds a bunch of records, where each entry links to exactly 1 row in another table - for example, every record in an "inventory" table will have a relationship to a single "inventory type" record in another table.

You may know that inventory records have an inventory type, and you will probably have to write a query that joins them together at least a few times.

When that time comes, you may think to yourself "but how do I know which fields to use to join them together? What will I be joining on?"

Your first step should always be '''LOOK AT THE PRIMARY KEY'''. If you're joining the inventorytype table onto the inventory table, ''look at the primary key'' of the inventorytype table. That way, you will know what fields are the bare minimum that must be referenced in your query.

=== Confusing MySQL by treating numbers as strings ===
The short version: if you are dealing with numbers in MySQL, never enclose them in quotes. The value '25' is different from the value 25.

The longer version (which isn't really that long, and you should totally read it right now) is [http://code.openark.org/blog/mysql/beware-of-implicit-casting well-explained by the smart Mr. Noach].

=== Getting bad data when grouping and summarizing information ===
This is such an important topic that I have written an entire doc on it.

Logical errors won't stop your query from running, but they will result in bad data. If you're writing queries, you need to familiarize yourself with [[The GROUPing pitfall]].

Seriously. Logical errors in your queries will fuck you over. Pay attention.

User:Jmckinstry

2015-09-14T22:19:16Z

Jmckinstry: /* Education and Experience */

== ISoft History ==
Justin was hired in March of 2009, where he began by learning the ins and outs of ITrack Pro and AX.

Currently, he's the main developer for ITrack AX, Replikwando, and a wide swath of tools both internal and external. He also does development work on random projects as they come up, guides with tech support in their endeavors, and does his best to keep interdepartmental communications flowing smoothly. Hats are changed frequently!

== Education and Experience ==
Justin graduated with a Bachelor's Degree in Computer Science (minor in Mathematics) in December of 2008. He's been doing technical support for computers, printers, network connections and infrastructure, televisions, telephones, and server administration for four years, with another two years of programming.

During his time at ISoft, Justin has:
* Written code for AX, Pro, Enterprise, and Presage
* Written web code for LX Warehouse, Presage Scanner, an auction lotting tool (and evaluated/debugged/made suggestions for other large production sites and tools)
* Written scripts for database updating, timed program checks, massive directory manipulation, bidirectional public key file exchanges
* Developed/Maintained internal tools (including Replikwando, a mysql tokenizer, a harebrained symmetrical encryption scheme, and a call tracker)
* Developed external GPL code that ISoft made use of (and made sure the code changes were public, of course):
** [https://code.launchpad.net/~isoft-data-systems/mysql-binlog-parser/trunk mysql-replication-listener]: Fixed a few bugs, added support for writing binlog statements out (so you can inline edit them!)
* Participated in the deployment, maintenance, and upkeep of more than a dozen production databases:
** CPU and Memory utilization
** Errand program debugging and correction
** Establishing, maintaining, and debugging networks on both ends of the performance spectrum (servers using erroring T1 lines is interesting)
* Enjoyed tons of (almost exclusively MySQL and sqlite) database shenanigans:
** Installing and configuring the servers and related programs
** Writing, debugging, optimizing, and tracking queries, stored procedures, functions, views
** Creating and implementing archiving and backup solutions
** Recovering 'lost' data and restoring 'lost' databases
* Created script files for nightly export of large (not terabyte-scale) data sets
* Learned Javascript from scratch well enough to do group project work within 48 hours
* Helped Support streamline and correct processes in an effort to keep things running smoothly (bless our Support staff!)
* Assisted in the execution of bi-monthly auction days by clerking, helping maintain data consistency and reporting, proving outcomes to customers, and providing post-auction statistics
* Created an inventory appraisal tool that (for auctions at least) has had a 5% margin of error for the past two years running

And some other stuff. You know. ;)

== Public Keys ==
* Keybase.io: A817266B53CD3F9AB144416C9ABC91ED61DE9BE0 (https://keybase.io/jmckinstry/key.asc)
* pgp.mit.edu: 9ABC91ED61DE9BE0 (https://pgp.mit.edu/pks/lookup?op=get&search=0x9ABC91ED61DE9BE0)

== About Justin ==
While not being an extreme outdoorsman, Justin does like to hunt and fish. Occasional hiking and biking also occur. During the rest of his spare time, he enjoys reading, drumming, and playing Rock Band and other video games. He also likes to write small programs using C/C++/javascript just for fun.

He once fixed a mangled barcode with a pen and a steady hand.

[[Category:ISoft Employees]]

Replikwando Messages

2015-08-06T19:54:40Z

Jmckinstry: /* Checking Who Has Repushed Recently */

=Replikwando Messages=
Replikwando currently has a messaging back-end (using libITMySQLMessageFramework) that allows it to send and receive information. While there are plans for implementing a GUI interface to do this (at which point this documentation will change to reflect that), these sections will get you by.

==Usage Examples==
*[[Database:Replikwando_Message#Repush|Repushing]]
*[[Database:Replikwando_Message#Version%20Update|Update Version]]
*[[Database:Replikwando_Message#Status|Get Current Status]]
==Messages==
===REPLIKWANDO_MESSAGE_REPUSH===
Inputs
*RepushTime (seconds, int, optional) - When to restart. If unspecified, Replikwando will choose
*PostBack (True/False, enum, optional) - Declares if a response is wanted back. If unspecified, Replikwando will not respond
Outputs
*Success (True/False, enum) - If Replikwando will really restart
*Reason (error message, string, opt) - The reason Replikwando won't be restarting, if any

===REPLIKWANDO_MESSAGE_RESTART===
Inputs
*PostBack (True/False, enum, optional) - Declares if a response is wanted back. If unspecified, Replikwando will not respond
Outputs
*None

===REPLIKWANDO_MESSAGE_REQUEST_STATUS===
Inputs
*ReplikwandoDataObject (object name, string, optional) - The specific data you wish to get. If unspecified, Replikwando will respond with a generic string. See below for object values
Outputs
*Value (object value, string) - The object's value, or a generic output if no object was specified

====Object Values====
REPLIKWANDO_STATUS_OBJECT_STARTED_AT // Returns the date/time Replikwando started at

REPLIKWANDO_STATUS_OBJECT_TOTAL_QUERIES // Total queries executed

REPLIKWANDO_STATUS_OBJECT_QUERIES_IN_MEMORY // Number of queries in memory currently

REPLIKWANDO_STATUS_OBJECT_QUERIES_IN_TABLE // Number of queries in the sqlite table

REPLIKWANDO_STATUS_OBJECT_CURRENT_STATUS // Replikwando's current running status

REPLIKWANDO_STATUS_OBJECT_CURRENT_ERROR // Replikwando's current stopping error. Empty String if everything is okay (Not implemented yet)

REPLIKWANDO_STATUS_OBJECT_REPUSH_TIME // The time Replikwando will re-push. Empty String if no repush is scheduled

REPLIKWANDO_STATUS_OBJECT_MOST_RECENT_QUERY // The most recent query Replikwando has handled (includes both successful and failing queries, and is dependent on the state of Replikwando)

REPLIKWANDO_STATUS_OBJECT_VERSION // Replikwando's static version

===REPLIKWANDO_MESSAGE_SKIP_QUERY===
Inputs
*PostBack (True/False, enum, optional) - Declares if a response is wanted back. If unspecified, Replikwando will not respond
Outputs
*Success (True/False, enum) - True

===REPLIKWANDO_MESSAGE_FINISHED_REPUSH===
Inputs
*ToDatabase (table name, string) - The name of the database that had its data finish pushing
*ToConnection (connection name, string) - The name of the connection that the ToDatabase table can be found in
Outputs
*None

===REPLIKWANDO_MESSAGE_GET_INI===
Inputs
*None
Outputs
*Value (ini values, string) - The replication.ini file in all its glory.

===REPLIKWANDO_MESSAGE_GET_INI_VALUE===
Inputs
*Header (header, string) - The replication.ini value's header
*Name (setting name, string) - The replication.ini value's name
Outputs
*Found (True/False, enum) - True if the value was found, and Value will be set, False otherwise and Value will not be set
*Value (value, string, optional) - The replication.ini's recorded value for [Header]Name, if any

===REPLIKWANDO_MESSAGE_SET_INI_VALUE===
Inputs
*Header (header, string) - The replication.ini value's header
*Name (setting name, string) - The replication.ini value's name
*Value (setting value, string) - The replication.ini value's actual data
Outputs
*None

===REPLIKWANDO_MESSAGE_UPDATE===
Inputs
*Version (version, string) - The version of Replikwando that the file data represents
*IsService (True/False, enum) - True if the update included as Data is the service version
*Data (file data, blob) - The file data that makes up the file. May be very large
Outputs
*None

==Database Interaction==
The basic setup of a message being sent to replikwando goes like this:
#Initialize the message
#If the message requires inputs, add them
#Post the message
#If you expect a response, check v_responses until you get one
#Close the message if you wanted a response

Here are some examples:
===Getting Status===
SELECT heartbeat.f_message_init('<uuid_handle>') INTO @messageID;

SELECT heartbeat.f_message_post(@messageID, 'REPLIKWANDO_MESSAGE_REQUEST_STATUS', 'False', 'True');

SELECT * FROM heartbeat.v_responses where messageid = @messageID;

===Getting Specific Status===
SELECT heartbeat.f_message_init('<uuid_handle>') INTO @messageID;

SELECT heartbeat.f_message_attach_data(@messageID, 'Input', 'ReplikwandoDataObject', 'REPLIKWANDO_STATUS_OBJECT_STARTED_AT');

SELECT heartbeat.f_message_post(@messageID, 'REPLIKWANDO_MESSAGE_REQUEST_STATUS', 'True', 'True');

===Restarting Replikwando===
SELECT heartbeat.f_message_init('<uuid_handle>') INTO @messageID;

SELECT heartbeat.f_message_post(@messageID, 'REPLIKWANDO_MESSAGE_RESTART', 'False', 'False');

===Updating Replikwando===
SELECT heartbeat.f_message_init('<uuid_handle>') INTO @messageID;

SELECT heartbeat.f_message_attach_data(@messageID, 'Input', 'Version', '<version>');

SELECT heartbeat.f_message_attach_data(@messageID, 'Input', 'IsService', 'False');

CREATE TEMPORARY TABLE t_update_data(`data` MEDIUMBLOB);

<insert the file blob data into `t_update_data`.`data`>

SELECT heartbeat.f_message_attach_data(1446, 'Input', 'Data', `data`) FROM t_update_data;

SELECT heartbeat.f_message_post(@messageID, 'REPLIKWANDO_MESSAGE_UPDATE', 'True', 'False');

===Checking Who Has Repushed Recently===
SELECT `timestamp`, `messagetype`, `value` AS `database`, `received` AS `watcher_has_received`, IF(`finished` IS NULL, 'False', 'True') AS `watcher_has_handled`
FROM heartbeat.message
LEFT JOIN messagedata USING (messageid)
WHERE `timestamp` > NOW() - INTERVAL 4 HOUR
AND `messagetype` = 'REPLIKWANDO_MESSAGE_FINISHED_REPUSH'
AND `name` = 'ToDatabase'
ORDER BY `timestamp` DESC;

=Replikwando Statuses=
*OK: Replikwando believes everything is operating as intended.
*ErrorWaiting: Replikwando is still pushing data and reporting in, but it will need to repush when its scheduled time comes.
*Stopped: Replikwando has either been turned off at the local computer or has failed to report in for more than an hour.

Replikwando Messages

2015-08-06T19:52:03Z

Jmckinstry: /* Database Interaction */

=Replikwando Messages=
Replikwando currently has a messaging back-end (using libITMySQLMessageFramework) that allows it to send and receive information. While there are plans for implementing a GUI interface to do this (at which point this documentation will change to reflect that), these sections will get you by.

==Usage Examples==
*[[Database:Replikwando_Message#Repush|Repushing]]
*[[Database:Replikwando_Message#Version%20Update|Update Version]]
*[[Database:Replikwando_Message#Status|Get Current Status]]
==Messages==
===REPLIKWANDO_MESSAGE_REPUSH===
Inputs
*RepushTime (seconds, int, optional) - When to restart. If unspecified, Replikwando will choose
*PostBack (True/False, enum, optional) - Declares if a response is wanted back. If unspecified, Replikwando will not respond
Outputs
*Success (True/False, enum) - If Replikwando will really restart
*Reason (error message, string, opt) - The reason Replikwando won't be restarting, if any

===REPLIKWANDO_MESSAGE_RESTART===
Inputs
*PostBack (True/False, enum, optional) - Declares if a response is wanted back. If unspecified, Replikwando will not respond
Outputs
*None

===REPLIKWANDO_MESSAGE_REQUEST_STATUS===
Inputs
*ReplikwandoDataObject (object name, string, optional) - The specific data you wish to get. If unspecified, Replikwando will respond with a generic string. See below for object values
Outputs
*Value (object value, string) - The object's value, or a generic output if no object was specified

====Object Values====
REPLIKWANDO_STATUS_OBJECT_STARTED_AT // Returns the date/time Replikwando started at

REPLIKWANDO_STATUS_OBJECT_TOTAL_QUERIES // Total queries executed

REPLIKWANDO_STATUS_OBJECT_QUERIES_IN_MEMORY // Number of queries in memory currently

REPLIKWANDO_STATUS_OBJECT_QUERIES_IN_TABLE // Number of queries in the sqlite table

REPLIKWANDO_STATUS_OBJECT_CURRENT_STATUS // Replikwando's current running status

REPLIKWANDO_STATUS_OBJECT_CURRENT_ERROR // Replikwando's current stopping error. Empty String if everything is okay (Not implemented yet)

REPLIKWANDO_STATUS_OBJECT_REPUSH_TIME // The time Replikwando will re-push. Empty String if no repush is scheduled

REPLIKWANDO_STATUS_OBJECT_MOST_RECENT_QUERY // The most recent query Replikwando has handled (includes both successful and failing queries, and is dependent on the state of Replikwando)

REPLIKWANDO_STATUS_OBJECT_VERSION // Replikwando's static version

===REPLIKWANDO_MESSAGE_SKIP_QUERY===
Inputs
*PostBack (True/False, enum, optional) - Declares if a response is wanted back. If unspecified, Replikwando will not respond
Outputs
*Success (True/False, enum) - True

===REPLIKWANDO_MESSAGE_FINISHED_REPUSH===
Inputs
*ToDatabase (table name, string) - The name of the database that had its data finish pushing
*ToConnection (connection name, string) - The name of the connection that the ToDatabase table can be found in
Outputs
*None

===REPLIKWANDO_MESSAGE_GET_INI===
Inputs
*None
Outputs
*Value (ini values, string) - The replication.ini file in all its glory.

===REPLIKWANDO_MESSAGE_GET_INI_VALUE===
Inputs
*Header (header, string) - The replication.ini value's header
*Name (setting name, string) - The replication.ini value's name
Outputs
*Found (True/False, enum) - True if the value was found, and Value will be set, False otherwise and Value will not be set
*Value (value, string, optional) - The replication.ini's recorded value for [Header]Name, if any

===REPLIKWANDO_MESSAGE_SET_INI_VALUE===
Inputs
*Header (header, string) - The replication.ini value's header
*Name (setting name, string) - The replication.ini value's name
*Value (setting value, string) - The replication.ini value's actual data
Outputs
*None

===REPLIKWANDO_MESSAGE_UPDATE===
Inputs
*Version (version, string) - The version of Replikwando that the file data represents
*IsService (True/False, enum) - True if the update included as Data is the service version
*Data (file data, blob) - The file data that makes up the file. May be very large
Outputs
*None

==Database Interaction==
The basic setup of a message being sent to replikwando goes like this:
#Initialize the message
#If the message requires inputs, add them
#Post the message
#If you expect a response, check v_responses until you get one
#Close the message if you wanted a response

Here are some examples:
===Getting Status===
SELECT heartbeat.f_message_init('<uuid_handle>') INTO @messageID;

SELECT heartbeat.f_message_post(@messageID, 'REPLIKWANDO_MESSAGE_REQUEST_STATUS', 'False', 'True');

SELECT * FROM heartbeat.v_responses where messageid = @messageID;

===Getting Specific Status===
SELECT heartbeat.f_message_init('<uuid_handle>') INTO @messageID;

SELECT heartbeat.f_message_attach_data(@messageID, 'Input', 'ReplikwandoDataObject', 'REPLIKWANDO_STATUS_OBJECT_STARTED_AT');

SELECT heartbeat.f_message_post(@messageID, 'REPLIKWANDO_MESSAGE_REQUEST_STATUS', 'True', 'True');

===Restarting Replikwando===
SELECT heartbeat.f_message_init('<uuid_handle>') INTO @messageID;

SELECT heartbeat.f_message_post(@messageID, 'REPLIKWANDO_MESSAGE_RESTART', 'False', 'False');

===Updating Replikwando===
SELECT heartbeat.f_message_init('<uuid_handle>') INTO @messageID;

SELECT heartbeat.f_message_attach_data(@messageID, 'Input', 'Version', '<version>');

SELECT heartbeat.f_message_attach_data(@messageID, 'Input', 'IsService', 'False');

CREATE TEMPORARY TABLE t_update_data(`data` MEDIUMBLOB);

<insert the file blob data into `t_update_data`.`data`>

SELECT heartbeat.f_message_attach_data(1446, 'Input', 'Data', `data`) FROM t_update_data;

SELECT heartbeat.f_message_post(@messageID, 'REPLIKWANDO_MESSAGE_UPDATE', 'True', 'False');

===Checking Who Has Repushed Recently===
SELECT *
FROM heartbeat.message
LEFT JOIN messagedata USING (messageid)
WHERE `timestamp` > NOW() - INTERVAL 4 HOUR
AND `messagetype` = 'REPLIKWANDO_MESSAGE_FINISHED_REPUSH'
AND `name` = 'ToDatabase'
ORDER BY `timestamp` DESC;

=Replikwando Statuses=
*OK: Replikwando believes everything is operating as intended.
*ErrorWaiting: Replikwando is still pushing data and reporting in, but it will need to repush when its scheduled time comes.
*Stopped: Replikwando has either been turned off at the local computer or has failed to report in for more than an hour.

Known Replikwando Errors

2015-08-06T18:29:56Z

Jmckinstry: /* Bad Behavior */

== Errors in Replikwando ==
Replikwando is made to handle inconsistent internet connections, MySQL databases that get shut off randomly, and things like that. However, sometimes things happen that Replikwando isn't able to handle automatically in a smart fashion. It uses error logs to tell the administrator when something odd has come up and should probably be looked into.

This wiki page is meant to help explain what the different errors are, as well as what needs to be done to handle them (or if they can be safely ignored).

== Error Logs ==
=== FromConnectionError Log ===
This error log contains errors and warnings coming from the company's local database.

==== Lost Connection To The Database ====
* '''Cause'''
Replikwando wasn't able to see the local database. This can happen if the MySQL service is stopped or restarted, or if the process was too busy to respond for a moment, or if the network connection to the server host quit working for a moment (if the MySQL server isn't hosted locally).

* '''Resolution'''
Replikwando is built so that things like this can happen. Therefore, it's safe to ignore this error. Replikwando will just reconnect to the database when it comes back.

* '''Similar Errors'''
MySQL Server Has Gone Away

==== The Following Query Was Reported And Skipped ====
* '''Cause'''
Someone enabled the skip-query interface for replikwando and then told it to skip a query. The query is then logged in the error log to record what was skipped in case the administrator needs to go back and evaluate it.

* '''Resolution'''
This error can be ignored, as it is purely for informational purposes.

=== ToConnectionError Log ===
This error log contains errors generated from interacting with the remote MySQL database.

==== Lost Connection To The Database ====

* '''Cause'''
Replikwando wasn't able to see the remote database. This can happen if the MySQL service is stopped or restarted, or if the process was too busy to respond for a moment, or if the internet connection for the site drops.

* '''Resolution'''
Replikwando is built so that things like this can happen. Therefore, it's safe to ignore this error. Replikwando will just reconnect to the database when it comes back.

* '''Similar Errors'''
MySQL Server Has Gone Away

==== Duplicate Key ====
* '''Cause'''
This error means that it tried to push a key to the remote database, but it already had the values being specified. This is normally caused by a query that gets sent, is received by the server, but the server reply just doesn't get back to Replikwando before it tries again. That means that if the remote server goes down or the internet connection is interrupted for even a moment, this can occur.

* '''Resolution'''
It is safe to ignore this error.
Newer versions no longer halt on this error, as it is indicative of the data already being on the remote server.

==== There is no '*'%' registered ====
* '''Cause'''
This error occurs when the remote server is asked to run a stored procedure (like a function or trigger), but the user who created it or is defined as the executor no longer exists.

* '''Resolution'''
You do not need to stop Replikwando to fix this issue.
Dump the functions from the remote database into a text file, find-and-replace all instances of the obsoleted name with the name of a valid user, and re-insert them.

=== QueueConnectionError Log ===

==== Cannot find file: <path><something.bin> ====
*'''Cause'''
This message appears when Replikwando cannot locate the data file. Replication.ini is probably pointing to the wrong location.

*'''Resolution'''
Open the company's SQLYOG and run this query:
<source lang = 'MySQL'>
SHOW VARIABLES LIKE '%data%';
</source>
The results list should show you the path to where MySQL looks for the data file. Edit replication.ini and set the binlog path to where SQLYOG says the data file is located.

==== Database Is Locked ====
* '''Cause'''
This error occurs when one thread/process/person is writing into the SQLite database file while another is trying to use it. It means that whatever got the error wasn't able to query the table or write data into it. It's most common to see this error when an administrator is running queries against the SQLite database file while Replikwando is running (which is safe to do, but it can get annoying having to run your query more than once).

* '''Resolution'''
It is safe to ignore this error.
Replikwando does not halt on this error; it just tries to do what it was doing again, until it succeeds.
This error should no longer occur (a spinlock was implemented to resolve it).

==== Failed to get the last binlog position ====
* '''Cause'''
This warning occurs when Replikwando looks for where it left off last time in your binary log, but can't find an entry. This is NORMAL to see when you're starting up a new session (no LocalQueueFile.sqlite file). If you see it during normal execution, something bad happened to the binary logging.

* '''Resolution'''
It is safe to ignore this error if you see it when running a new instance of Replikwando. If it happens during normal operation, you should check to see that
**MySQL server binary logging is enabled
**The internal MySQL tables that handle logging aren't corrupted (use the query 'SHOW BINARY LOGS' to verify)
**The binary log files haven't been moved or something

== Bad Behavior ==
===Repushing===
====Repush finishes, but no record is left in the messages table, so it never resyncs====
* '''Cause'''
If errors occur during repush, Replikwando refuses to notify HTP that the database should be synced, or else data would be lost.

* '''Resolution'''
Correct whatever is causing the errors during repush.

As of 1.8.0, the following error will show when this problem occurs: "Repush failed! No message sent to Heartbeat; Database will not sync to HTP!"

==== Tables and views push, but functions and procedures are missing ====
* '''Cause'''
This issue occurs when Replikwando does not have permission to SELECT from mysql.proc. See [http://dev.mysql.com/doc/refman/5.0/en/show-create-procedure.html this MySQL doc page] for more information.

* '''Resolution'''
Replikwando must be given SELECT privs to mysql.proc. This table shows functions and procedures associated with all databases on a server, but cannot be altered unless you for some reason give the Replikwando user UPDATE status. Just don't do that :)

====Replikwando2.exe sits at "Waiting for Active Thread" and never begins a repush====

*'''Possible Cause'''
*The client's database has crashed.

*'''Resolution'''
*Delete and re-create the client's database on our side.

==== Replikwando runs and seems to work right, but dies during repushes ====
*'''Possible Cause'''
*MySQL Server version is higher than <replikwando_install_folder>/bin/mysqlbinlog.exe (ie: server is 5.6, mysqlbinlog.exe is 5.5)
**The ToConnection log will show that queries are getting mangled during the repush.

*'''Resolution'''
*Update <replikwando_install_folder>/bin/mysqlbinlog.exe with a newer version

===General===
==== Replikwando2 (the gui version) 'works', but ReplikwandoService doesn't ====
* '''Cause'''
'It doesn't work' isn't a working description of an error.
This problem has several manifestations:
* ReplikwandoService immediately exits without even getting started
* ReplikwandoService starts, but won't repush
* Repushing works, but keeping up-to-date does not
* ReplikwandoService will not install

There are a whole bunch of things that could cause this.
* The service might be running under a user that doesn't have permissions/access to necessary files
* File permissions are screwed up
* Files became corrupted after Replikwando2 got started
* Magical kernel-level call interception
* Firewall exceptions exist for one executable or the other

* '''Resolution'''
* Make sure the user account being used to run ReplikwandoService has access to all necessary files
* Turn off antivirus (or, even better, add Replikwandoservice to the whitelist for the AV)
* Make sure there are entries in any running firewall program

==== Replikwando2 successfully re-pushes, but then pushes tons of junk data ====
*'''Possible cause'''

Replikwando is not using the correct mysqlbinlog.exe file.

*'''Resolution'''

*Change replication.ini's [fromserver].mysqlbinpath to be the same bin path that the MySQL instance is running
**Windows: This query will tell you where to look for the right bin folder: <source lang = 'MySQL'>SHOW VARIABLES LIKE 'basedir';</source>
**Linux: The Replikwando install creates a bin/mysql/mysqlbinlog.exe entry that you should point to

*'''Possible cause'''

The client's binlog file is corrupted.

*'''Resolution'''

*Run this query in their SQLYOG:

<source lang = 'MySQL'>
SHOW BINARY LOGS;
</source>

If you see any of the following, the binary log is corrupt:
*Binlog names are inconsistent ('mysqlbinlog-000001.bin', 'servername-000001.bin' in the same list)
*Binlog names repeat (seeing 'mysqlbinlog-000001.bin','mysqlbinlog-000001.bin','mysqlbinlog-000001.bin' repeated over and over and the file sizes are identical)

*First, make a backup of the client's entire ITrack database, in case something terrible happens.

*Next, run this query in the client's SQLYOG:

<source lang = 'MySQL'>
RESET MASTER;
</source>

You should now be able to have Replikwando successfully re-push the client's data with normal replication afterwards.

====Server is full of mysqlbinlog.exe instances====
*'''Possible Cause'''
*Replikwando has lost the handle to mysqlbinlog.exe.
**It is unknown why this happens, but is a system-level issue.

*'''Resolution'''
*Kill all but the most recent instance of mysqlbinlog.exe. Replikwando spawns these regularly, so if a mysqlbinlog.exe process has been around for awhile and is not changing, it's probably safe to kill it.
**Do NOT kill an active mysqlbinlog.exe! Replikwando is actively receiving data from it, and things can go horribly wrong if you kill it!
*A more permanent resolution is to use a different spawning method (we currently use ::CreateProcess). I believe (but may be wrong) that this issue occurs because ::CreateProcess returns COULDNT_CREATE_PROCESS or something like that, even though the process is made. It could also be an error in the handling of the closing of a windows-based thread, both of which should be investigated.

*'''Potentially Resolved''' in 1.8.0.

Known Replikwando Errors

2015-08-06T18:28:27Z

Jmckinstry: /* Bad Behavior */

== Errors in Replikwando ==
Replikwando is made to handle inconsistent internet connections, MySQL databases that get shut off randomly, and things like that. However, sometimes things happen that Replikwando isn't able to handle automatically in a smart fashion. It uses error logs to tell the administrator when something odd has come up and should probably be looked into.

This wiki page is meant to help explain what the different errors are, as well as what needs to be done to handle them (or if they can be safely ignored).

== Error Logs ==
=== FromConnectionError Log ===
This error log contains errors and warnings coming from the company's local database.

==== Lost Connection To The Database ====
* '''Cause'''
Replikwando wasn't able to see the local database. This can happen if the MySQL service is stopped or restarted, or if the process was too busy to respond for a moment, or if the network connection to the server host quit working for a moment (if the MySQL server isn't hosted locally).

* '''Resolution'''
Replikwando is built so that things like this can happen. Therefore, it's safe to ignore this error. Replikwando will just reconnect to the database when it comes back.

* '''Similar Errors'''
MySQL Server Has Gone Away

==== The Following Query Was Reported And Skipped ====
* '''Cause'''
Someone enabled the skip-query interface for replikwando and then told it to skip a query. The query is then logged in the error log to record what was skipped in case the administrator needs to go back and evaluate it.

* '''Resolution'''
This error can be ignored, as it is purely for informational purposes.

=== ToConnectionError Log ===
This error log contains errors generated from interacting with the remote MySQL database.

==== Lost Connection To The Database ====

* '''Cause'''
Replikwando wasn't able to see the remote database. This can happen if the MySQL service is stopped or restarted, or if the process was too busy to respond for a moment, or if the internet connection for the site drops.

* '''Resolution'''
Replikwando is built so that things like this can happen. Therefore, it's safe to ignore this error. Replikwando will just reconnect to the database when it comes back.

* '''Similar Errors'''
MySQL Server Has Gone Away

==== Duplicate Key ====
* '''Cause'''
This error means that it tried to push a key to the remote database, but it already had the values being specified. This is normally caused by a query that gets sent, is received by the server, but the server reply just doesn't get back to Replikwando before it tries again. That means that if the remote server goes down or the internet connection is interrupted for even a moment, this can occur.

* '''Resolution'''
It is safe to ignore this error.
Newer versions no longer halt on this error, as it is indicative of the data already being on the remote server.

==== There is no '*'%' registered ====
* '''Cause'''
This error occurs when the remote server is asked to run a stored procedure (like a function or trigger), but the user who created it or is defined as the executor no longer exists.

* '''Resolution'''
You do not need to stop Replikwando to fix this issue.
Dump the functions from the remote database into a text file, find-and-replace all instances of the obsoleted name with the name of a valid user, and re-insert them.

=== QueueConnectionError Log ===

==== Cannot find file: <path><something.bin> ====
*'''Cause'''
This message appears when Replikwando cannot locate the data file. Replication.ini is probably pointing to the wrong location.

*'''Resolution'''
Open the company's SQLYOG and run this query:
<source lang = 'MySQL'>
SHOW VARIABLES LIKE '%data%';
</source>
The results list should show you the path to where MySQL looks for the data file. Edit replication.ini and set the binlog path to where SQLYOG says the data file is located.

==== Database Is Locked ====
* '''Cause'''
This error occurs when one thread/process/person is writing into the SQLite database file while another is trying to use it. It means that whatever got the error wasn't able to query the table or write data into it. It's most common to see this error when an administrator is running queries against the SQLite database file while Replikwando is running (which is safe to do, but it can get annoying having to run your query more than once).

* '''Resolution'''
It is safe to ignore this error.
Replikwando does not halt on this error; it just tries to do what it was doing again, until it succeeds.
This error should no longer occur (a spinlock was implemented to resolve it).

==== Failed to get the last binlog position ====
* '''Cause'''
This warning occurs when Replikwando looks for where it left off last time in your binary log, but can't find an entry. This is NORMAL to see when you're starting up a new session (no LocalQueueFile.sqlite file). If you see it during normal execution, something bad happened to the binary logging.

* '''Resolution'''
It is safe to ignore this error if you see it when running a new instance of Replikwando. If it happens during normal operation, you should check to see that
**MySQL server binary logging is enabled
**The internal MySQL tables that handle logging aren't corrupted (use the query 'SHOW BINARY LOGS' to verify)
**The binary log files haven't been moved or something

== Bad Behavior ==
===Repushing===
====Repush finishes, but no record is left in the messages table, so it never resyncs====
* '''Cause'''
If errors occur during repush, Replikwando refuses to notify HTP that the database should be synced, or else data would be lost.

* '''Resolution'''
Correct whatever is causing the errors during repush.

As of 1.8.0, the following error will show when this problem occurs: "Repush failed! No message sent to Heartbeat; Database will not sync to HTP!"

===General===
==== Tables and views push, but functions and procedures are missing ====
* '''Cause'''
This issue occurs when Replikwando does not have permission to SELECT from mysql.proc. See [http://dev.mysql.com/doc/refman/5.0/en/show-create-procedure.html this MySQL doc page] for more information.

* '''Resolution'''
Replikwando must be given SELECT privs to mysql.proc. This table shows functions and procedures associated with all databases on a server, but cannot be altered unless you for some reason give the Replikwando user UPDATE status. Just don't do that :)

==== Replikwando2 (the gui version) 'works', but ReplikwandoService doesn't ====
* '''Cause'''
'It doesn't work' isn't a working description of an error.
This problem has several manifestations:
* ReplikwandoService immediately exits without even getting started
* ReplikwandoService starts, but won't repush
* Repushing works, but keeping up-to-date does not
* ReplikwandoService will not install

There are a whole bunch of things that could cause this.
* The service might be running under a user that doesn't have permissions/access to necessary files
* File permissions are screwed up
* Files became corrupted after Replikwando2 got started
* Magical kernel-level call interception
* Firewall exceptions exist for one executable or the other

* '''Resolution'''
* Make sure the user account being used to run ReplikwandoService has access to all necessary files
* Turn off antivirus (or, even better, add Replikwandoservice to the whitelist for the AV)
* Make sure there are entries in any running firewall program

==== Replikwando2 successfully re-pushes, but then pushes tons of junk data ====
*'''Possible cause'''

Replikwando is not using the correct mysqlbinlog.exe file.

*'''Resolution'''

*Change replication.ini's [fromserver].mysqlbinpath to be the same bin path that the MySQL instance is running
**Windows: This query will tell you where to look for the right bin folder: <source lang = 'MySQL'>SHOW VARIABLES LIKE 'basedir';</source>
**Linux: The Replikwando install creates a bin/mysql/mysqlbinlog.exe entry that you should point to

*'''Possible cause'''

The client's binlog file is corrupted.

*'''Resolution'''

*Run this query in their SQLYOG:

<source lang = 'MySQL'>
SHOW BINARY LOGS;
</source>

If you see any of the following, the binary log is corrupt:
*Binlog names are inconsistent ('mysqlbinlog-000001.bin', 'servername-000001.bin' in the same list)
*Binlog names repeat (seeing 'mysqlbinlog-000001.bin','mysqlbinlog-000001.bin','mysqlbinlog-000001.bin' repeated over and over and the file sizes are identical)

*First, make a backup of the client's entire ITrack database, in case something terrible happens.

*Next, run this query in the client's SQLYOG:

<source lang = 'MySQL'>
RESET MASTER;
</source>

You should now be able to have Replikwando successfully re-push the client's data with normal replication afterwards.

====Replikwando2.exe sits at "Waiting for Active Thread" and never begins a repush====

*'''Possible Cause'''
*The client's database has crashed.

*'''Resolution'''
*Delete and re-create the client's database on our side.

====Server is full of mysqlbinlog.exe instances====
*'''Possible Cause'''
*Replikwando has lost the handle to mysqlbinlog.exe.
**It is unknown why this happens, but is a system-level issue.

*'''Resolution'''
*Kill all but the most recent instance of mysqlbinlog.exe. Replikwando spawns these regularly, so if a mysqlbinlog.exe process has been around for awhile and is not changing, it's probably safe to kill it.
**Do NOT kill an active mysqlbinlog.exe! Replikwando is actively receiving data from it, and things can go horribly wrong if you kill it!
*A more permanent resolution is to use a different spawning method (we currently use ::CreateProcess). I believe (but may be wrong) that this issue occurs because ::CreateProcess returns COULDNT_CREATE_PROCESS or something like that, even though the process is made. It could also be an error in the handling of the closing of a windows-based thread, both of which should be investigated.

*'''Potentially Resolved''' in 1.8.0.

==== Replikwando runs and seems to work right, but dies during repushes ====
*'''Possible Cause'''
*MySQL Server version is higher than <replikwando_install_folder>/bin/mysqlbinlog.exe (ie: server is 5.6, mysqlbinlog.exe is 5.5)
**The ToConnection log will show that queries are getting mangled during the repush.

*'''Resolution'''
*Update <replikwando_install_folder>/bin/mysqlbinlog.exe with a newer version

Known Replikwando Errors

2015-08-06T18:20:20Z

Jmckinstry: /* Server is full of mysqlbinlog.exe instances */

== Errors in Replikwando ==
Replikwando is made to handle inconsistent internet connections, MySQL databases that get shut off randomly, and things like that. However, sometimes things happen that Replikwando isn't able to handle automatically in a smart fashion. It uses error logs to tell the administrator when something odd has come up and should probably be looked into.

This wiki page is meant to help explain what the different errors are, as well as what needs to be done to handle them (or if they can be safely ignored).

== Error Logs ==
=== FromConnectionError Log ===
This error log contains errors and warnings coming from the company's local database.

==== Lost Connection To The Database ====
* '''Cause'''
Replikwando wasn't able to see the local database. This can happen if the MySQL service is stopped or restarted, or if the process was too busy to respond for a moment, or if the network connection to the server host quit working for a moment (if the MySQL server isn't hosted locally).

* '''Resolution'''
Replikwando is built so that things like this can happen. Therefore, it's safe to ignore this error. Replikwando will just reconnect to the database when it comes back.

* '''Similar Errors'''
MySQL Server Has Gone Away

==== The Following Query Was Reported And Skipped ====
* '''Cause'''
Someone enabled the skip-query interface for replikwando and then told it to skip a query. The query is then logged in the error log to record what was skipped in case the administrator needs to go back and evaluate it.

* '''Resolution'''
This error can be ignored, as it is purely for informational purposes.

=== ToConnectionError Log ===
This error log contains errors generated from interacting with the remote MySQL database.

==== Lost Connection To The Database ====

* '''Cause'''
Replikwando wasn't able to see the remote database. This can happen if the MySQL service is stopped or restarted, or if the process was too busy to respond for a moment, or if the internet connection for the site drops.

* '''Resolution'''
Replikwando is built so that things like this can happen. Therefore, it's safe to ignore this error. Replikwando will just reconnect to the database when it comes back.

* '''Similar Errors'''
MySQL Server Has Gone Away

==== Duplicate Key ====
* '''Cause'''
This error means that it tried to push a key to the remote database, but it already had the values being specified. This is normally caused by a query that gets sent, is received by the server, but the server reply just doesn't get back to Replikwando before it tries again. That means that if the remote server goes down or the internet connection is interrupted for even a moment, this can occur.

* '''Resolution'''
It is safe to ignore this error.
Newer versions no longer halt on this error, as it is indicative of the data already being on the remote server.

==== There is no '*'%' registered ====
* '''Cause'''
This error occurs when the remote server is asked to run a stored procedure (like a function or trigger), but the user who created it or is defined as the executor no longer exists.

* '''Resolution'''
You do not need to stop Replikwando to fix this issue.
Dump the functions from the remote database into a text file, find-and-replace all instances of the obsoleted name with the name of a valid user, and re-insert them.

=== QueueConnectionError Log ===

==== Cannot find file: <path><something.bin> ====
*'''Cause'''
This message appears when Replikwando cannot locate the data file. Replication.ini is probably pointing to the wrong location.

*'''Resolution'''
Open the company's SQLYOG and run this query:
<source lang = 'MySQL'>
SHOW VARIABLES LIKE '%data%';
</source>
The results list should show you the path to where MySQL looks for the data file. Edit replication.ini and set the binlog path to where SQLYOG says the data file is located.

==== Database Is Locked ====
* '''Cause'''
This error occurs when one thread/process/person is writing into the SQLite database file while another is trying to use it. It means that whatever got the error wasn't able to query the table or write data into it. It's most common to see this error when an administrator is running queries against the SQLite database file while Replikwando is running (which is safe to do, but it can get annoying having to run your query more than once).

* '''Resolution'''
It is safe to ignore this error.
Replikwando does not halt on this error; it just tries to do what it was doing again, until it succeeds.
This error should no longer occur (a spinlock was implemented to resolve it).

==== Failed to get the last binlog position ====
* '''Cause'''
This warning occurs when Replikwando looks for where it left off last time in your binary log, but can't find an entry. This is NORMAL to see when you're starting up a new session (no LocalQueueFile.sqlite file). If you see it during normal execution, something bad happened to the binary logging.

* '''Resolution'''
It is safe to ignore this error if you see it when running a new instance of Replikwando. If it happens during normal operation, you should check to see that
**MySQL server binary logging is enabled
**The internal MySQL tables that handle logging aren't corrupted (use the query 'SHOW BINARY LOGS' to verify)
**The binary log files haven't been moved or something

== Bad Behavior ==
==== Tables and views push, but functions and procedures are missing ====
* '''Cause'''
This issue occurs when Replikwando does not have permission to SELECT from mysql.proc. See [http://dev.mysql.com/doc/refman/5.0/en/show-create-procedure.html this MySQL doc page] for more information.

* '''Resolution'''
Replikwando must be given SELECT privs to mysql.proc. This table shows functions and procedures associated with all databases on a server, but cannot be altered unless you for some reason give the Replikwando user UPDATE status. Just don't do that :)

==== Replikwando2 (the gui version) 'works', but ReplikwandoService doesn't ====
* '''Cause'''
'It doesn't work' isn't a working description of an error.
This problem has several manifestations:
* ReplikwandoService immediately exits without even getting started
* ReplikwandoService starts, but won't repush
* Repushing works, but keeping up-to-date does not
* ReplikwandoService will not install

There are a whole bunch of things that could cause this.
* The service might be running under a user that doesn't have permissions/access to necessary files
* File permissions are screwed up
* Files became corrupted after Replikwando2 got started
* Magical kernel-level call interception
* Firewall exceptions exist for one executable or the other

* '''Resolution'''
* Make sure the user account being used to run ReplikwandoService has access to all necessary files
* Turn off antivirus (or, even better, add Replikwandoservice to the whitelist for the AV)
* Make sure there are entries in any running firewall program

==== Replikwando2 successfully re-pushes, but then pushes tons of junk data ====
*'''Possible cause'''

Replikwando is not using the correct mysqlbinlog.exe file.

*'''Resolution'''

*Change replication.ini's [fromserver].mysqlbinpath to be the same bin path that the MySQL instance is running
**Windows: This query will tell you where to look for the right bin folder: <source lang = 'MySQL'>SHOW VARIABLES LIKE 'basedir';</source>
**Linux: The Replikwando install creates a bin/mysql/mysqlbinlog.exe entry that you should point to

*'''Possible cause'''

The client's binlog file is corrupted.

*'''Resolution'''

*Run this query in their SQLYOG:

<source lang = 'MySQL'>
SHOW BINARY LOGS;
</source>

If you see any of the following, the binary log is corrupt:
*Binlog names are inconsistent ('mysqlbinlog-000001.bin', 'servername-000001.bin' in the same list)
*Binlog names repeat (seeing 'mysqlbinlog-000001.bin','mysqlbinlog-000001.bin','mysqlbinlog-000001.bin' repeated over and over and the file sizes are identical)

*First, make a backup of the client's entire ITrack database, in case something terrible happens.

*Next, run this query in the client's SQLYOG:

<source lang = 'MySQL'>
RESET MASTER;
</source>

You should now be able to have Replikwando successfully re-push the client's data with normal replication afterwards.

====Replikwando2.exe sits at "Waiting for Active Thread" and never begins a repush====

*'''Possible Cause'''
*The client's database has crashed.

*'''Resolution'''
*Delete and re-create the client's database on our side.

====Server is full of mysqlbinlog.exe instances====
*'''Possible Cause'''
*Replikwando has lost the handle to mysqlbinlog.exe.
**It is unknown why this happens, but is a system-level issue.

*'''Resolution'''
*Kill all but the most recent instance of mysqlbinlog.exe. Replikwando spawns these regularly, so if a mysqlbinlog.exe process has been around for awhile and is not changing, it's probably safe to kill it.
**Do NOT kill an active mysqlbinlog.exe! Replikwando is actively receiving data from it, and things can go horribly wrong if you kill it!
*A more permanent resolution is to use a different spawning method (we currently use ::CreateProcess). I believe (but may be wrong) that this issue occurs because ::CreateProcess returns COULDNT_CREATE_PROCESS or something like that, even though the process is made. It could also be an error in the handling of the closing of a windows-based thread, both of which should be investigated.

*'''Potentially Resolved''' in 1.8.0.

==== Replikwando runs and seems to work right, but dies during repushes ====
*'''Possible Cause'''
*MySQL Server version is higher than <replikwando_install_folder>/bin/mysqlbinlog.exe (ie: server is 5.6, mysqlbinlog.exe is 5.5)
**The ToConnection log will show that queries are getting mangled during the repush.

*'''Resolution'''
*Update <replikwando_install_folder>/bin/mysqlbinlog.exe with a newer version

Replikwando Changelog

2015-07-22T21:00:21Z

Jmckinstry: /* Initial */

=Notes=
'''Current Version: 1.1.7b'''

=Versions=
==Replikwando 1.8.0==
Major version release.
Version numbering scheme changed:
* <MAJ>.<MIN>.<MINMIN>[rev]
* to
* <MAJ>.<MIN>.<rev>[sub]

=== Initial ===
Added ability to handle ROW-based replication.

* CRITICAL - Fixed: If the last query handled by Replikwando since the most recent binlog check was ROLLBACK, and the most recent query processed from the current binlog check was ROLLBACK, then any query that came after it would be erroneously dropped until TopQuery (the UI's last known processed query) was updated.
* CRITICAL - Fixed: Comments that start with `!<number>` no longer have IsTokenComment return TRUE as they are actually MySQL statements protected by version checks.
* CRITICAL - Fixed: A bug where query errors on repush could end up not getting reported or marking the repush as failed.
* Fixed: mysqlbinlog.exe zombie process and pipe handling.
* Fixed: Replikwando begins logging details immediately on startup, instead of when it's knee-deep in connection handling.
* Changed: Promoted fresh sqlite file notification log level from NOTIFY to GENERAL.
* Fixed: TO thread now sleeps between cycles instead of spinlocking and consuming system resources.
* Fixed: BASE64 tokens not being tokenized correctly.
* Changed: BINLOG statements are now replicated instead of being discarded.
* Fixed: A bug where failed To Connection attempts would erroneously report as Heartbeat failures.
* Added: Many string efficiency changes.
* Added: Deeper logging for internal Query Pipes.
* Added: Little notes for the GUI and log for when different repush sections are reached. For debugging.
* Added: Support for Socket/Named-Pipe connections instead of TCP where available.

==Replikwando 1.1.7==
First released version.

===Replikwando 1.1.7b===
Major logging changes documented at [[Replikwando Error Logging]].