Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Current codebase does not handle 0 source rows well #20

Closed
atc0005 opened this issue Sep 27, 2020 · 1 comment · Fixed by #30
Closed

Current codebase does not handle 0 source rows well #20

atc0005 opened this issue Sep 27, 2020 · 1 comment · Fixed by #30
Assignees
Labels
bug Something isn't working
Milestone

Comments

@atc0005
Copy link
Owner

atc0005 commented Sep 27, 2020

I recreated the source MySQL database in a test LXD container, but initially forgot to insert the test data. When I ran the prototype mysql2sqlite binary I got this output (debug logging):

  INFO[0000] no entries in table access_check_clients for database mailserver, skipping rows retrieval and SQLite database insert
 DEBUG[0000] reading table access_check_recipients from source database mailserver
 DEBUG[0000] Rows retrieved from source MySQL database
 DEBUG[0000] Successfully ran DROP TABLE query for table access_check_recipients
 DEBUG[0000] Successfully ran DROP INDEX query for table access_check_recipients
 DEBUG[0000] Created table access_check_recipients in SQLite database
 DEBUG[0000] Created index for table access_check_recipients in SQLite database
 DEBUG[0000] SQLite database is ready
 DEBUG[0000] Before mysqlRows.Next()
  INFO[0000] no entries in table access_check_recipients for database mailserver, skipping rows retrieval and SQLite database insert
 DEBUG[0000] reading table sender_bcc_maps from source database mailserver

and the application went no further.

The Nagios plugin currently applies a rows count check to both source and mirror databases and exits early if there isn't a match. The main app does check for zero rows, but its later in the code and just before attempting the fetch/insert process.

I'm going to defer fixing this until #19 has been completed so I can track the specific problem instead of continuing to tweak/refine the current code before it ever lands here.

@atc0005 atc0005 added the bug Something isn't working label Sep 27, 2020
@atc0005 atc0005 added this to the v0.1.0 milestone Sep 27, 2020
@atc0005 atc0005 self-assigned this Sep 27, 2020
@atc0005
Copy link
Owner Author

atc0005 commented Sep 27, 2020

I found a workaround.

Modify the config file to allow a greater number of open connections:

   # Maximum number of connections to the MySQL allowed for this application.
-  max_open_connections: 10
+  max_open_connections: 15

The max open connections value got up to 12 before the application completed.

Once I inserted the test data, the open connections dropped to 1. This is testing against a local MariaDB 10.0.x instance, so the behavior is likely different against a remote database server, or even a newer version of MariaDB.

atc0005 added a commit that referenced this issue Sep 27, 2020
Earlier work was done on the `initial-prototype` branch
and has only been slightly cleaned up for this release.

Documentation, CI (GitHub Actions), dependency
monitoring (Dependabot) and a host of other
TODO items have not been completed as of this
specific commit.

Additionally, confirmed bugs such as GH-20 remain and
should be fixed before the first v0.1.0 release.

Some test files from the WhyAskWhy/mysql2sqlite-dev
project are included in this commit. I am authorized
to include them as well as use the project name.

This commit provides two (mostly) working binaries:

- `mysql2sqlite`
  - main binary to sync a MySQL database to a SQLite
    database

- `check_mysql2sqlite`
  - Nagios plugin to validate source MySQL database
    against generated SQLite database

As already noted, much, much work remains to polish and
refactor this code. Even so, I am already using it in
limited production use in order to help shape development
efforts. Already this has proven useful in abandoning the
plan to use Write-Ahead Logging (`WAL`) mode when
interacting with the SQL database and instead use classic
`DELETE` mode instead (compatibility).

refs GH-19
atc0005 added a commit that referenced this issue Sep 27, 2020
Earlier work was done on the `initial-prototype` branch
and has only been slightly cleaned up for this release.

Documentation, CI (GitHub Actions), dependency
monitoring (Dependabot) and a host of other
TODO items have not been completed as of this
specific commit.

Additionally, confirmed bugs such as GH-20 remain and
should be fixed before the first v0.1.0 release.

Some test files from the WhyAskWhy/mysql2sqlite-dev
project are included in this commit. I am authorized
to include them as well as use the project name.

This commit provides two (mostly) working binaries:

- `mysql2sqlite`
  - main binary to sync a MySQL database to a SQLite
    database

- `check_mysql2sqlite`
  - Nagios plugin to validate source MySQL database
    against generated SQLite database

As already noted, much, much work remains to polish and
refactor this code. Even so, I am already using it in
limited production use in order to help shape development
efforts. Already this has proven useful in abandoning the
plan to use Write-Ahead Logging (`WAL`) mode when
interacting with the SQL database and instead use classic
`DELETE` mode instead (compatibility).

refs GH-19
atc0005 added a commit that referenced this issue Sep 29, 2020
For both binaries, check source database rows count early
and skip further processing of that table. This helps prevent
unnecessary connections from accumulating in the pool and
prevents wasted effort.

For the Nagios plugin, the table is only skipped if *both*
databases have empty tables, otherwise it continues to apply
validation checks and will fail early as before.

refs GH-20
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant