MySQL is developing rapidly.  And today it does not only happen along a single track but in multiple directions simultaneously.

This is due to both server ‘forks‘ as well as various plugins and 3rd party storage engines available.   Each of those will often add  information that can be fetched from the server – typically in the form of additional Information_Schema tables. This content can now also be utilized for monitoring MySQL servers with MONyog. MONyog 4.8  implements monitoring based on Custom SQL Objects.  It is basically just a SQL query. Any query returning a result set can be used for monitoring.  MONyog will collect results from the query and you may define counters on it exactly as you want.

It is really very simple.  In this example we will utilize the `accounts` table that was added to the `performance_schema` database in MySQL 5.6.3 (but you could also use the ‘user statistics plugin’ originally published as a Google patch and now distributed with MariaDB) . Basically only two steps are required:

First step: Navigate to TOOLs tab .. Customization .. Manage Custom SQL Objects .. Add/Edit Custom SQL Objects and here enter the query. Note that you must also define a unique column (or a unique set of columns).

Also define the sample interval and the retention timeframe for this particular query and on what MySQL servers the query should be executed.  MONyog will now start collecting the data as you have specified.

Second step: Navigate to Monitors/Advisors tab .. Add new Monitor/Advisor. The result of the query you defined in first step is exposed as a javascript array to the MONyog javascript engine. Enter the  javascript defining the content, display, formatting etc. of the new counter in order to make it display as you want. Add an advisor text as well  if you want.

You have now created a Custom SQL Counter based on the Custom SQL Object Object that was defined in first step. You may create a new counter group for the new counter what we have done here:

.. but in this particular case it would also make perfect sense to add the new counter to the existing ‘current connections’ group. Also note that if you want to define WARNING and CRITICAL thresholds on a counter and alert on it, the SQL and/or javascript must be framed in such a way that the counter returns a plain number.

We would like to hear your feedback – and not at least how you use this in your environment. What are you monitoring now that you did not before?

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

Comments(2)

We wish all our customers, users and partners a Merry Xmas and a Happy New Year.

The year of 2011 has been a terrific year for us.  But first let us have a look at where we started 10 years ago. The first version of SQLyog was released in the spring of 2002 – what will mark our 10 year anniversary as a company early next year. In the period since then we have seen quite a lot of other MySQL clients come and go. We are happy to see most of our existing customers upgrade regularly, to see our user base increase, and we are happy to do our best to provide the solutions requested by users and to keep pace with MySQL development.

And also 4½ years ago we added MONyog to our portfolio. So there will be another important anniversary to celebrate in 2012 as well. MONyog has also proved to be robust and long-lived.

We are continuing to develop both programs aggressively in interaction with users and partners. And the combination of SQLyog and MONyog under one hood has enabled us to achieve important strategical partnerships across the MySQL ecosystem.

And don’t forget that Webyog is not only tools for MySQL. Also Visifire has now become a very respected charting solution for Silverlight, WPF and Windows Phone. Several benchmarks and reviews list Visifire as the most efficient and most competitive solution. Our IssueBurner application provides an integrated issue-tracking and helpdesk solution in a single and very competitive package. And add to this CloudMagic that in a second finds various information from Cloud-based services and presents it all in a unified interface.

The diversity of the different solutions that Webyog now offer is important not only for us as a company but also for users, because it ensures robustness of our company and guarantees continued support for all customers also if one of the ecosystems where we operate should face a slowdown or setback for a period. We have the robustness to continue developing what we want and what we have planned in a turbulent world.

We have more in our bag that we will share with you soon, Stay tuned!

Comments(0)

We are delighted to announce the release of MONyog 4.8 GA. This release is all about customization – to be precise Custom SQL Objects (CSO) and Custom SQL Counters (CSC).

The above diagram shows how Monitors & Advisors (MySQL Counters) are populated. MONyog has a repository of SQL Queries which are executed in regular interval of time & the results are stored in SQLite database of MONyog. These results are exposed as JavaScript objects and are referenced to populate Monitors and Advisors (MySQL Counters).

With CSO, you can add your own SQL queries to this repository & customise counters based on that. Discussed below are some cases on how useful CSOs can be.

• Maintenance statements, for eg. CHECK TABLE, REPAIR TABLE etc.
• You could query on Information Schema to monitor the size of a table. You can also configure it to alert when it crosses a threshold.
• Keep yourself updated with the worst performing indexes.
• Third party MySQL forks have some non standard tables. For eg. you can query on Statistics table of Percona MySQL fork to fetch information about unused index.
• You can monitor MySQL clusters too, by querying Ndbinfo Schema.

Refer the following posts for detailed release notes:

• MONyog 4.8 Beta 2 Released
• MONyog 4.8 Beta 1 Released

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,
Team MONyog

Comments(1)

Hello!

We are delighted to announce the release of SQLyog 9.5 GA. This release is geared towards enhancing the user experience.

The major changes are:

• The conversion of modal dialogue boxes to a tabbed interface for creating and altering tables, and defining table properties.
  • Creating a table involves – defining columns, and often, creating indexes and foreign keys. Before, these actions had to be done one after the other, which was annoyingly slow. Now, you can define columns, and create indexes and foreign keys – all in a single interface, which gives you a single CREATE/ALTER TABLE query to execute. Not only is creating tables easier, it is also much faster!
  • Modal dialogue boxes limit one from multitasking. Previously, when in the middle of creating a foreign key, if some information had to be looked up from a different table (or even the same table), it required one to close all the dialogue boxes, get the information, and then start again from scratch! Now, while working on creating a table, you can go to another tab, check for some details and return back to creating the table.
• History and Info tabs now appear alongside the Query tab. As the content of these two tabs is not dependent on a specific Query tab, it is logical to not have these under the Query tab. Additionally, these tabs consume one third of the system resource that they did before.
• Content of History and Info tabs is now searchable.

The all new create table interface

Refer the following posts for detailed release notes:

• SQLyog 9.5 Beta 2 Released
• SQLyog 9.5 Beta 1 Released

SQLyog customers can download SQLyog 9.5 GA from the Customer Area.

To evaluate SQLyog 9.5 GA please download a 30-day Trial.

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

Cheers,
Team SQLyog

Comments(4)

Changes (as compared to beta 1) include:

Features:
* Search is now possible in INFO tab/text mode and in  HISTORY tab. CTRL+F will open the ‘Find’ dialog.
* Foreign Key lookup now works across databases.

Bug fixes:
* A default for a BIT datatype could not be specified properly. Now b’..’ and x’..’ notation may be used when specifying column defaults.
* Stability- and GUI-fixes with the new CREATE/ALTER TABLE interface.
* When connected to pre-4.1 servers non-ASCII characters could garble.

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

Comments(5)

Changes (as compared to beta 1) include:

Features:
* GUI fixes and internal optimizations with Custom SQL Objects and -Counters introduced in beta 1.
*  Some of the predefined Custom SQL Objects included with MONyog were updated

Bug fixes:
* MONyog returned a garbage string to the Linux ‘ps’ command.
* Saving a Custom SQL Object to a MONyog instance under load could be slow.
* Automatic purging of data collected as Custom SQL Objects did not always happen as defined by ‘retention timeframe’ setting.

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

Comments(4)

The headline above has two parts: 1) some letters 2) a question mark. The question mark is the important thing here! It resolves to:

* Can we debug stored programs in MySQL?
* Do you debug stored programs in MySQL?
* How do you debug stored programs in MySQL?

We have tried, but we gave up! In the early stages of our IssueBurner application we actually used Stored Procedures quite a lot. As the complexity increased debugging became so tedious that we recoded the application ‘the traditional way’ coding the logic in the application code instead.

In simple cases you may of course add a little debugging code that writes some data to user variables, temporary tables etc. Next remove them or comment them when you want to turn debugging off. If you want to work more systematically you can add a IN-parameter (debug: integer) to a Stored Procedure paramer-list and CALL mysp(….,0|1) what would then control if the stored program should enter or bypass debugging code when executing.

It should not be like that in the 21st century. Debugging options should be part of the server code itself and there should be an API for it. You should be able to execute line-by-line, set breakpoints, view the status of variables, handlers and cursors for every step  just like you would do in a true Integrated Development Environment (IDE). Stored programs is code just like application code is.

Some years ago (I think around 3 years ago) I joined a ‘vote’ on the MySQL website where I had an option to vote for my personal priorities of future MySQL development. An API for debugging stored programs was one of the options listed and I cast all my 10 votes to it.

I would not live without Stored Functions and Events. They will also most often be pretty simple and thus rarely cause problems. Stored Procedures is another matter – they can be and will often need to be very complex to be usable for what you want to achieve (and let us forget Triggers here – they are close to being a joke in MySQL).

I have seen some solutions claiming to be able to debug MySQL Stored Procedures. What I have seen all use some kind of (very simple, really) emulation (like replacing loops with sequential statements, local variables with user variables or they will rewrite the original SP to a ‘cascaded series’ of SP’s calling each others). All what I have seen fails with examples just a little bit more complex than trivial (a few nested loops, some handlers or cursors and similar is usually enough to get weird results).

I write this because we now again had a request for a ‘stored program debugger’ in SQLyog. We would be happy to develop it (it is around 5 years ago we discussed first time I think), but without proper API support from the server we will not even attempt it. Past attempts are not encouraging.

I wonder:
* Does anybody know about any progress with stored programs debugging API/functionalities in MySQL?
* How do you debug your stored programs?

Comments(3)

The MySQL implementation of the BIT type has had severe criticism over time.

I think the famous Blog by Baron Schwartz here will be known to a lot of people. It both complains about the implementation as such and deals with bugs in the early 5.0 implementation. Most (if not all) of the regular bugs have been fixed. However the complaints about the implementation are still valid. I use to post this link to those of our users having difficulty with BIT and advise to use  a TINYINT instead (at least instead of a BIT(1)).

It is also a part of the story that many users do not understand that a BIT is a numerical type and instead use it as a BOOL/BOOLEAN (what it is not). A BIT(n) may of course be used as an ‘n-array of booleans’ – but it requires tedious and careful coding in the application and with other clients (command line, GUI clients) the result is often confusing – unless the client has some client-side ‘smart handling of BIT’ added (what SQLyog among others has).

But what I did not realize clearly before was however that a BIT only applies to storage on disk. There is no ‘memory-type’ corresponding the ‘storage-type’ BIT in MySQL.

It is actually indirectly clear from the MySQL documentation on CAST()/CONVERT(). You may cast to BINARY[(N)], CHAR[(N)], DATE , DATETIME, DECIMAL[(M[,D])], SIGNED [INTEGER], TIME, UNSIGNED [INTEGER]. You cannot cast to a BIT because in memory there is no BIT at all. In memory a BIT is represented by a BIGINT UNSIGNED. I don’t think this is documented properly and I also am  not sure if this was properly considered at the time of implementation. It looks to me like BIT was ‘rushed through’ 6 years back without proper considerations.

It also means that the mostly used BIT variant – BIT(1) – is memory-inefficient as compared to TINYINT.

I posted a few related bug reports recently:
http://bugs.mysql.com/bug.php?id=63470
http://bugs.mysql.com/bug.php?id=63505

.. and the most simple test case illustrating what I am writing here is probably this:

DROP TABLE IF EXISTS bittest;
CREATE TABLE bittest(b BIT(2));
INSERT INTO bittest VALUES (b’11′);
SET @bit = (SELECT b FROM bittest LIMIT 1);
SELECT @bit; – returns “3″ as an integer

Also you may actually do things like  SELECT LOG(SELECT BIT-value FROM sometable);
– using a logaritmic function

In every respect a BIT behaves as BIGINT UNSIGNED unless it is fetched directly from storage to a client.

For me the considerations actually started with this bug (that I did not post myself, but I realized at once I saw it, that the basic problem was a server problem and not a client problem):  http://bugs.mysql.com/bug.php?id=63457

It is not the point that Workbench does weird things here (then I would have many points!  ). Though I am not able to understand how it reaches the result it does, it does not really matter much in the context.   The point is that even a MAX|MIN aggregate on a BIT returns an INTEGER. And that happens along with inconsistent metadata in this particular case.  This is how you confuse clients!

MySQL should rather have implemented a true BOOLEAN than than the BIT in my opinion. Because people use a BIT as a BOOLEAN mostly – and they get confused frequently. Clients are blamed for the server behaviour and implementation.

Comments(0)