Changes (as compared to 4.72) include:

Features:
* This release adds a new major feature to MONyog: Custom SQL Counters (“CSC”) and Custom SQL Objects (“CSO”). For details refer note below and the program documentation.
* Drastically improved performance in log analysis if the option to ‘replace literals’ was selected.

Bug fixes:
*If a query contained the literal substring ‘connect’ MONyog could hang during general log analysis.

More about Custom SQL Counters:

A “Custom SQL Counter” (CSC) is based on any user-defined SQL query returning a result set. The array returned by MySQL from the SQL query populates a MONyog Object (a ”Custom SQL Object” (CSO) in this case) . This is exposed as a javascript array that may be referenced in MONyog counter definitions like any MONyog object.

The primary reason for introducing this feature is to utilize information available  in Information_Schema (as well as Performance_Schema of MySQL 5.5+) that is not exposed in the basic SHOW statements we have been using till now. In addition to the fact that – in some cases –  I_S has more information than what SHOW  returns it is also accessed using a SELECT statement what makes sorting, filtering, JOINs, use of aggregate functions etc. possible. Also MySQL ‘forks’, various plugins and third-party storage engines will often populate I_S with data not available with the ‘upstream’ MySQL server from Oracle. With this new feature you can use such information for monitoring as well.

But CSO’s are not restricted to SELECT FROM I_S and SELECT FROM P_S. Any SQL-statement  returning a result set may be specified.  Examples:
* Specially framed SHOW statements (using WHERE and LIKE clauses for instance).
* Maintenance statements (example: “CHECK TABLE” – but be aware of (engine-specific) LOCK behaviour with such statements).
* Queries on user data.  For instance if you have a support ticket system you may frame a query telling how many support tickets have been inactive for more than 24 hours.
* Also a CALL statement may be used if it returns a single (no more and no less) result set.

This build ships with 13 pre-defined CSO’s.  To understand them follow the steps:

1) Go to TOOLs tab .. Customization .. Manage Custom SQL Objects ..  Add/Edit Custom SQL Objects. You will see the 13 pre-defined CSO’s display in the left menu. As an example select the ‘DiskInfo’ item.  The User Defined SQL-query displays in the ‘SQL’ box. Sample interval and retention timeframe specific for this CSO may be changed as per your preference and you may specify for which MySQL server(s) this particular CSO should be collected.  Also note that one or more ‘Key columns’ are defined. This/they must be a column or a set of columns returning (a) unique (set of) value(s) (similar to a UNIQUE KEY in MySQL). Without defining such properly the CSO will not be usable in the following step.

Now enable it and click ‘Save’ when you are done with all. This query now executes on the MySQL server(s) where you defined and enabled it and a MONyog object named  ”DiskInfo’ will populate and be exposed for counter definitions.

We will here further enable a few more CSO’s:  enable the ‘Table_Size’, Database_Size’, ‘Data_Types’ and ‘Storage_Engine’ pre-defined CSO’s.

2) Go to  go to TOOLS tab .. Customization .. Manage Groups and enable the ‘Disk Info’ Group. This pre-defined group contains pre-defined CSC’s using the CSO’s you enabled in step 1).

3) Now go to Monitors/Advisors page, select the ‘Disk Info’ group that now displays at the bottom. You will see 5 new counters in that group that in various ways reference the CSO’s that we just enabled (click the counter name and next ‘Customize’ as usually to see the javascript code). You may customize those further as you would do with any counter in MONyog.

Note that some of the predefined CSO’s require specific server versions and/or configurations (like the ‘InnoDB plugin’ with MySQL 5.1 (or MySQL 5.5+), ndb_cluster enabled and even in one case the server must a PerconaServer build).  If this requirement is not met, the SQL query may return NULL for specific values or it may  simply return an error.  In such cases MONyog will display ‘n/a’ for CSC’s based on non-populated CSO’s.

Also note that as the sample interval and retention frame setting for CSO’s are independent of the setting for built-in SHOW-based counters and also independent of each others, every CSO is handled by a seperate thread  by MONyog internally. Also every retrival of a CSO will open a new connection to MySQL, retrive the result and close the connection after data have been retrieved (and MONyog itself will of course report these connections in Dashboard and in Monitors/Advisors .. Connection History .. Attempts). This implementation is chosen in order both to avoid ‘bottlenecks’ in a single connection (as MySQL unfortunately does not allow parallel queries in a single connection) and in order to reduce the number of open connections at any time as much as possible.

How many CSC’s/CSO’s you may have enabled will depend on the capabilities of your system and the number of servers registered. A lot of CSO’s collected with a short sample interval from a lot of MySQL servers will obviously create additional load (I/O and network traffic in particular).  A rough guideline is that with a large number of servers and 10 or more CSO’s enabled you should not use sample intervals lower than a minute on an ‘average’ system. If you have only a few CSO’s and/or a few MySQL servers you will be able to use lower sample intervals.  We are currently benchmarking this and will publish details as soon as we have and when this new feature has been optimized as much as possible.

We have one significant benchmark currently though: With 500 MySQL servers monitored from a single MONyog instance, 10 of the predefined CSO’s enabled to all MySQL servers with a sample interval of 5 minutes it runs fine causing almost insignificant additional load on a 64 bit Linux box with same hardware configuration as described as our primary test system for MONyog  here in this Blog.

Stay tuned at this Blog.  Likely more information about this (examples, benchmarks) will appear here soon.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Comments(1)

Changes (as compared to 9.32) include:

Bug fixes:
* Fixed two regression bugs in Autocomplete introduced in 9.31: 1) a performance regression if the option to “Show suggestion as you type in SQL editor” was enabled. 2) Table alias support was broken.
* The Autocomplete popup windows opened by Ctrl+Enter and Ctrl+Space could ‘pop down’ where it should ‘pop up’ resulting in the content being partly invisible. This bug was introduced in 9.31.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Comments(0)

Introduction:

If you have been using MONyog you will know that any metrics/counters may be identified with either of the ‘states’ STABLE, WARNING and CRITICAL. For those metric where it is relevant MONyog ships with settings for this that are (to the extend possible) commonly agreed ‘best practices’ by MySQL DBA’s. Metrics in a WARNING or CRITICAL state display with a (yellow or red) ‘marker’ indicating that this metric is not in a STABLE state. For metrics in a CRITICAL state further an alert can be sent (as a mail alert and/or a SNMP trap).

If the ‘best practices’ we have implemented in MONyog do not fit your environment or use case, it can easily be customized. Open the MONyog ‘Monitors/Advisor’ page, click on the name of the metric you want to adjust (it is a link). A small popup opens. As an example let us take the ‘Connection usage’ in the ‘Current connections’ group. In the popup you will see the settings for this metric. They are defined like this

Warning = 75
Critical = 85

To change the settings click ‘Customize’ in the popup and next ‘View Advanced’ to see all options. It will open in a new browser tab. If you are willing to allow the number of current connection to reach 90% of the max_connections allowed before the state should change to CRITICAL  just enter “90″ as the value for the threshold of the CRITICAL state and save.

Differentiating thresholds for different MySQL servers:

What we described above will have effect for all MySQL servers monitored. It was also meant to be an introduction only.  The real  question to be dealt with here is different however: Is it possible to have different ‘state’ settings for different MySQL servers?

The answer is YES, and it is very simply to do actually. Say you monitor 2 servers that you in MONyog have named “Testserver” and “Production” respectively, and that you want a CRITICAL setting of “90″ for “Testserver” and “80″ for “Production”. Then the CRITICAL setting is not a constant (applying for all MySQL servers) anymore, but a condition needs to be added. MONyog exposes connection details to different MySQL servers monitored as Javascript objects that can be referenced with a simple if/case-condition. And it is very simple to work with. Just replace the constant with a (javascript-)function like this

function()
{
if(MONyog.Connections.ConnectionName == “Testserver”) return 90;
if(MONyog.Connections.ConnectionName == “Production”) return 80;
}

Below a (reduced) screenshot of values entered in the customizations page:

.. and now save. That is all. Now the CRITICAL condition for this metric is defined differently for the MySQL servers “Testserver” and “Production”.

Note that everything happens centrally at the MONyog level and there is nothing to do on the MySQL servers monitored. And also there is no need for any other program or utility (such as a database client or whatever) to do this. MONyog is agent-less and self-contained.

The objects that can be referenced in the customization interface are listed in the MONyog documentation here along with the explanation of the ‘MONyog Object Model’: http://www.webyog.com/doc/MONyog/MONyog_object_model.htm

In the above image we have defined different WARNING and CRITICAL values for another metric (‘percentage of max_allowed reached’ in the ‘Connection history’ group) for two servers: For both servers WARNING is set at 84% and CRITICAL at “Testserver” is set at at 90% and at “Production” CRITICAL is set at 87%. You will notice the red and yellow ‘markers’ displaying accordingly.

(There is one more detail you should be aware of: MONyog will not necessarily alert using mail or SNMP first time a CRITICAL value has been reached. There is a setting for this as well. The default setting is that the CRITICAL condition must have been in existence for 5 consecutive data retrievals from MySQL. This setting is specific for every MySQL server registered in MONyog and is available  in the MONyog ‘Register servers’ page for each server, but the value can be overridden for an individual metric by setting the “RetryOverride” value from the customizations page. Please be aware of this when testing or you may become confused why you don’t get the alerts you’d expect)

Download the MONyog whitepaper: http://www.webyog.com/en/whitepapers/MONyogWhitePaper.pdf
Read the MONyog documentation online: http://www.webyog.com/doc/index.php
Download MONyog TRIAL: http://webyog.com/en/downloads.php
Purchase MONyog: http://webyog.com/en/buy.php

Comments(0)

Changes (as compared to 9.31) include:

Bug fixes:
* Typing using the numerical keypad typed nothing in the SQL editor.  This bug was introduced in 9.31.
* With Japanese interface enabled some favorites items could fail to display.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Comments(0)

Changes (as compared to 9.3) include:

Features:
* Improved the handling of (built-in and stored) functions and stored procedures in Autocomplete. This involves 1) a stored program is now handled also if a ‘fully qualified routine name’ is not used. 2) introduced a Ctrl+Shift+Space keyboard shortcut that will display the parameter-list of a routine when the cursor is positioned inside it. 3) while writing a routine call the parameter list will highlight the current parameter. Also note that we have also reversed the behavior of Ctrl+Space and Ctrl+Enter keyboard shortcuts for Autocomplete – this in order to comply with most IDE’s and advanced editors.

Bug fixes:
* Schema Sync could generate an incorrect ALTER TABLE statement with an A TIMESTAMP .. ON UPDATE CURRENT_TIMESTAMP column on source.  This bug was introduced in 8.4.
* On Wine the status line (at the button of the program window) did not display. This was introduced in 9.3.
* The Keyboard CTRL+SPACE did nothing on Wine. Now CTRL+SPACE and CTRL+ENTER beve identically on Windows and Wine
* On Wine the autocomplete popup window painted with a frame  hiding some details.
* When Japanese was selected for the program interface ‘Copy Database/Table’ returned an error. Also this was introduced in 9.3.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Comments(3)

SSL-users in MySQL seem to cause quite a lot of problems for people. Quite a lot af bug reports have been posted to bugs.mysql.com over time that have most or all been classifed as ‘not a bug’. Numerous discussions exists on the Internet as well and people find weird workarounds like directly manipulating the mysql.user table.  I write this Blog because yesterday I found a discussion in a Forum where a dozen of self-appointed ‘experts’ tried to help a newbie with this causing only more confusion.

First problem: Let us try

DROP USER /*!70101  IF EXISTS */ ssss@localhost — just a ‘sidekick’, but not the point here
CREATE USER ssss@localhost;
GRANT UPDATE ON ttest.* TO ssss@localhost REQUIRE SSL;
GRANT SELECT ON ttest.* TO ssss@localhost;

Some people expect that a client would connect without encryption and would be able to SELECT from table (but not UPDATE) in the `ttest` database. But connection without encryption is not possible at all. SSL is required for authentication. Let us SHOW GRANTS:

SHOW GRANTS FOR ssss@localhost;
/* returns

Grants for ssss@localhost
——————————————————-
GRANT USAGE ON *.* TO ‘ssss’@'localhost’ REQUIRE SSL
GRANT SELECT, UPDATE ON `ttest`.* TO ‘ssss’@'localhost’
*/

Second problem:

DROP USER /*!70101 IF EXISTS */ ssss@localhost
CREATE USER ssss@localhost;
GRANT UPDATE ON ttest.* TO ssss@localhost REQUIRE SSL;
GRANT UPDATE ON ttest.* TO ssss@localhost;

Some people expect that this will “REVOKE”/REMOVE ‘REQUIRE SSL’ from the user as it is not specified in the last statement. But it does not:

SHOW GRANTS FOR ssss@localhost;
/* returns

Grants for ssss@localhost
——————————————————-
GRANT USAGE ON *.* TO ‘ssss’@'localhost’ REQUIRE SSL
GRANT UPDATE ON `ttest`.* TO ‘ssss’@'localhost’
*/

From the returns from SHOW GRANTS it is clear that SSL settings are global for user (used for authentication) and in GRANT syntax linked to the “USAGE” privilege (or rather “no-privilege acronym”). This is expected, it is documented but still confuses a lot of people.

This will do:

GRANT USAGE ON ttest.* TO ssss@localhost REQUIRE NONE;
/* returns

Grants for ssss@localhost
———————————————–
GRANT USAGE ON *.* TO ‘ssss’@'localhost’
GRANT UPDATE ON `ttest`.* TO ‘ssss’@'localhost’
*/

(and “GRANT UPDATE ON ttest.* TO ssss@localhost REQUIRE NONE;” would do excatly the same BTW in this case)

The point:

IMHO it is an illogical mess-up that SSL settings for a user are specified along with specific privileges (in a GRANT statement). A more logical way to specify SSL settings for a user would be to have the REQUIRE clause with CREATE USER – ie.

CREATE USER ssss@localhost REQUIRE SSL;
GRANT UPDATE ON ttest.* TO ssss@localhost;
(and then an ALTER USER statement would also be nice)

However historically “GRANT” is a much older statement in MySQL than “CREATE USER” (“CREATE USER” was introduced in MySQL 5.0 and “GRANT” very much earlier). So that is where we are now – trapped by history here we cannot make things logically right unless breaking compability!

I also recognize that many people/clients will never issue a CREATE USER statement as GRANT will create the use implicitly (if not exists) provided that the ‘no_auto_create_user’ SQL-mode  is not SET. But then the option to specify REQUIRE could be in both places.

Similar considerations actually also applies to

Third problem: How to change a password for a user? It may be done like this (not manipulating the mysql.user table directly):

GRANT USAGE ON *.* TO ‘ssss’@'localhost’ identified by ‘newpw’;

Again I think an ALTER USER statement would be the logically correct way:

ALTER USER ‘ssss’@'localhost’ identified by ‘newpw’;

.. as also password is not linked to specific privileges, but to user and authentication as such.

Comments(4)

Changes (as compared to 4.71) include:

Bug fixes:
* When connected to Linux distributions with a 3.x kernel the information about Disk I/O could fail to display properly in Dashboard and Monitors/Advisors pages of MONyog.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Comments(0)

Changes (as compared to 4.7) include:

Bug fixes:
* Accessing the replication tab could crash MONyog in rare cases with specific replication setups and where SHOW MASTER STATUS returned an empty result.
* Fixed an issue where user defined generic (javascript) functions added for use by customizations did not work as expected.
* Small UI fixes.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Comments(0)

Please note: This Blog was updated with a few clarifications on October 21st 2011.

As we announced in the release Blog for SQLyog version 9.3 we here publish instructions and tools for localizing SQLyog.

To understand how localization works you should first open the SQLyog 9.3 installation folder on your system. Inside it there is a (SQLite) database file named “L10n.db”. It contains all strings in all localizations distributed by us (currently English and Japanese) displayed by SQLyog GUI. If you have selected to run SQLyog in a non-English language SQLyog will load the localized strings from this file and use them for display and not the English strings.  To enable more languages just copy an updated “L10n.db” file with support for more languages into the SQLyog installation folder.  

The tools for localization are available from the SQLyog Community repository at Googlecode (http://code.google.com/p/sqlyog/). It is the contents of the ‘localization’ folder in the ‘source’ tab. From here on we will use the term SQLyog localization kit for the contents of this ../localization/ folder. To check out the localization kit for the latest SQLyog GA release use the link https://sqlyog.googlecode.com/svn/branches/stable_ga/localization/(and https://sqlyog.googlecode.com/svn/trunk/localization/ for the latest public release whether beta, RC or GA) for your SVN client (TortoiseSVN recommended for most Windows users). It contains tools and resources needed to add more languages/localizations to the “L10n.db” file.

Let us start listing the content:

* In the ‘bin’ folder you will find a “L10n.db” database completely identical to the one shipped with the SQLyog installer.

* In the ‘Strings’ folder you will find a XML-file named “Config.XML” and (in a recursive “String” folder) a folder for each language supported (named “en” and “ja”). In each of those folders you will find a number of XML files (following ‘Android XML’ specification) with English and localized Japanese strings respectively.

* The batch file ‘compile.bat’.  It is a batch script calling a ‘compiler’ that generates an updated “L10n.db file” based on the content of the “Strings” folder.

* The ‘Tools’ folder is not something you should normally care about. It contains some resources, shared libraries and executables etc. (such as the ‘compiler’ referred to above).  The content of it is used by the ‘compile.bat’ file – and not by user/translator directly.  

To make a translation there are bascially 4 steps:

1)

First step is to define a new language in the “Config.XML” file. We will assume that we are translating to German. In the passage that looks like

<language>
<ja langname=”日本語” />
</language>

.. add support for German like this:

<language>
<ja langname=”日本語” />
<de langname=”Deutsch” />
</language>

“de” is the language code and “Deutsch” is how it will display in the SQLyog UI. Please note that we use the language codes from this Microsoft document. It does not matter currently actually, but it may later be used for auto-detection of some LOCALE-specific parameters (decimal format, date format etc.), so please stick to the Microsoft recommendation unless there is a compelling reason not to do so. Also be careful not to change the encoding of the file. It is UTF8 and should remain so.

2)

Make a copy of the “en” folder and rename the copy “de”.

3)

Now comes the hard work. Translate the XML files in the “de” folder from English to German. And again be careful not to change the encoding of the files.

Also note the details:

* XML standards for encoding of special characters must be honoured (‘&apos;’ for “‘” (apostrophe) etc.). The characters that should be encoded are  & (&amp;), < (&lt;), > (&gt;) , ” (&quot;) and ‘ (&apos;) – and NO MORE! This is XML and not HTML!

* occurrences of “%d” and “%s” must be kept as they are and should be in the same order they appear in “en”. Failure to do so will result in an error in the next step.

* Also keyboard shortcuts are handled from the XML files. Actually there are two kinds of keyboard shortcuts in SQLyog (like in most Windows programs):

a) One kind is ‘static shortcuts’.

Those are the ones the you find listed in the “help .. keyboard shortcuts’ menu. Let’s have a look at a randomly picked string in English here:

<string name=”1027″>Schema Synchronization Tool (Ctrl+Q)</string>

The substring “(Ctrl+Q)” should not be translated. This kind of keyboard shortcuts are not localized. “(Ctrl+Q)” is just part of what is displayed. It is a constant string. The functionality of this type of shortcut is hardcoded into SQLyog.

b) ‘language dependent shortcuts’.

This is a Windows feature that it seems a lot of people don’t really know about. To understand what it is (if you don’t) just open SQLyog (in English) and press the ALT key. You will see that some letters in the menu become underlined.  For instance in the menu item “Table”, the “a” is underlined. You may next open the table menu by pressing “a” and even continue selecting from the submenu pressing a single letter key. In Windows this is achieved by preceeding “a” with an “&” in the menu (where it is not displayed – it only ‘codes’ the “a” to be a shortcut). So Windows ‘knows’ this menu item as “T&able” internally. That string is also what the “L10n.db” database contains.  Do not forget here that in the XML files “&” needs to be encoded as “&amp;”. So the German string in the XML file for the “Table” menu item could be “T&amp;abelle” (if you want to keep “a” as the ALT-keyboard shortcut in German). When choosing this type of shortcuts you should be careful not to use the same more than once in same menu level and you should of course try to follow de-facto standards as used in localized Windows itself, utilities that ship with Windows (Notepad etc.) as well as commonly used Windows programs (like office suites, browsers etc.).

Also you may use XML/HTML-comments in the XML-files.  That could be useful for instance if more people share the work of a translation or if there is something that you want to identify easily later.

A concluding comment on editors: Some editors may have issues with UTF8 and they should not be used. Notepad will work fine but it does not support syntax highlighting, auto-indenting and similar ‘developer features’ that are convenient to have. We have checked with Notepad++ that works fine – but there are undoubtedly dozens of editors available out there that will do the job. And also do not use a Word Processor – it should be a plain text editor.

4)

Once you have completed the translation of the XML files just execute the ‘compile.bat’ file from command line. German strings from the XML files you translated will be inserted to the ‘L10n.db’ database in the ‘bin’ folder. The updated ‘L10n.db’ can now be copied into the SQLyog installation folder and SQLyog will have the option for German language.  

Finally please note that we have a category in our Forums dedicated to localization.  If you are facing a problem feel free to discuss with us or fellow countrymen for details of a translation.

Comments(0)

We are pleased to announce the release of MONyog 4.7 GA. Below is a brief on new features:

Dashboard

The world is moving towards tablets (I agree iPad in the title is a misnomer) and most of them don’t support Adobe Flash. As you know MONyog dashboard charts were on Flash and did not work on tablets. With this release we have switched to HTML5 charts. Not only do they work on all Smartphones and Tablets, they are faster than Flash charts. Hence, desktop users also gain from this release.

Flash charts used elsewhere in MONyog are also changed to slick HTML5 charts.

Embedded in this post are screen-shots with relevant section zoomed-in on an iPad.

MONyog dashboard in action

Disk Usage Info

Wayback Machine

Who doesn’t like Wayback Machine? Well, MONyog gets one of its own. A neat way of tracking MySQL variables/Status and Queries fired while looking at historic “Number of threads connected” & “Number of slow queries” data. The user gets a chance to see the graphs of “Number of threads connected” & “Number of Slow Queries”, typically a spike on this chart would need attention. Zooming on this spike gives details like Status/Variables/Queries in that zoomed time range. We can also get details at a certain point in time.

Wayback Machine

Replication Tab

Monitoring replication is now even easier. Thanks to the all new ‘Processlist’ like interface to monitor MySQL replication. This tab details the current MASTER & SLAVE STATUS.

MONyog customers can download the latest installer from Webyog’s Customer Portal.

To evaluate MONyog, please download the 30-day trial.

We are very excited about this release, and hope you like it. We would love to hear from you.

Regards,
Chirag
Team MONyog

Comments(2)