Changes (as compared to beta 2) include:

Features:
* Register/Edit server page now has tabbed interface which eases navigation.
* Monitor/Dashboard charts can now be exported to an image file (pdf, jpeg or png). This was disabled in the 5.0 betas but is now functional again.

Bug fixes:
* MONyog was not sending email alerts if the ‘SMTP encryption type’ was set to ‘No Encryption’. This bug was introduced in 5.0 beta 2.

Additionally lots of UI issues and usability issues with the new interface were fixed.  We are not aware of any such issues with this RC release and expect to go GA soon. Please report issues soon  if you encounter any.

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

Comments(0)

Changes (as compared to 9.51) include:

Features:
* When performing CREATE/ALTER TABLE operation from inside the Schema Designer now a dialog with a tabbed interface similar to the CREATE/ALTER TABLE tab will open.
* It is now optional if Query Builder will create statements with ‘fully qualified tablenames’ or not.
* Implemented Alt+Ins, Alt+Del, Alt+Up and Alt+Down for inserting, deleting, moving up/down the rows in CREATE|ALTER TABLE interface when the focus is on respective grids. The tooltip for the toolbar buttons will now show this shortcuts.

Bug fixes:
* In Data Sync a low setting of ’max_allowed_packet’ on source server could result in CONCAT_WS() used for checksums’ calculation returning NULL . We did not consider this and as a consequence rows could fail to sync without warning. We will now abort the job with an error message (if user has selected to ‘abort on error’) or print an error to the sja.log file and the console (if user has selected not to ‘abort on error’ ) informing that some rows may have been skippped in such cases.
* The fix for empty string defaults in 9.51 was not complete. It was (unlike 9.5) possible to create such default but it did not display as ” after creation in ALTER TABLE.
* If multiple databases with same name existed only differing in lettercase SQLyog would generate certain statements using the ‘mostly lowercased’ database name. This bug was introduced in 9.5.
* Autocomplete runs in a separate connection, but also when Autocomplete was disabled this connection was created for no reason.
* SQLyog sometimes waited unnecessarily long (30 seconds – what is the client timeout setting we use internally) to report that SSH connection could not be established. This happened for instance if a wrong RSA passphrase was entered. In such and other cases there is no reason to wait for client timeout.
* Small fixes – mostly GUI fixes to the new CREATE/ALTER TABLE interface.

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

Comments(0)

Baron Schwartz from Percona has  just re-invented the SQLyog Query Profiler.
Refer: http://www.mysqlperformanceblog.com/2012/02/20/how-to-convert-show-profiles-into-a-real-profile/

The SQLyog Query Profiler was introduced in SQLyog 8.o in February 2009 – exactly 3 years ago. The funny thing is actually that Baron Schwartz reviewed it before release (at friendly commercial terms) at the time. The SUM-aggregation and the ORDERING that we did (and it was solely our decision – not Baron’s advice) in SQLyog 8.0 to the result from SELECT FROM I_S.PROFILES is the same as Baron now announces as “something I developed”.

Well ..

1) First let me make it clear that I am not complaing.  I believe Baron when he tells that he does not remember details of the SQLyog Query Profiler today. When I use the term funny above I mean it. Baron is welcome to re-use it even if he forgot and had to re-invent it (and also we re-use his ‘Change Of STATUS VARIABLES Due To Execution Of Query’ profiling concept from the original Maatkit release as well).

2) But *developed* is a pretty strong term to use for a rather trivial query (just a SELECT .. SUM() ..  GROUP BY .. ORDER BY .. basically), I think.  Just like there are only 12 notes in (western) music and it is difficult to write music that does not resemble previous music somehow, it is also difficult to write queries that do not resemble previous queries (I know chess-players would disagree – with 64 squares and 32 pieces  possibilities seem infinite – but maybe the rules of chess allow for more creative combinations than the rules of SQL? I belive so.  It probably also applies to music – but not to SQL IMHO ).

Happy PROFILING (with any tool you choose)! And Chess-playing as well!

Comments(2)

In this previous Blog I described an example on how to build a Custom Object in MONyog. But there is one problem you may encounter and that is, that when querying Information_Schema and Performance_Schema, it can sometimes be difficult or impossible to be sure to have a unique column (or a unique set of columns)  in the result set as neither of those generally guarantee uniqueness with a(ny) subset of columns. And a unique column (or a unique set of columns) is required for a MONyog Custom SQL Object.

A common way to add a unique row-identifer to a result set is like this (note the use of  ”assignment operator” (“:=”) as different from “comparison operator” (“=”) ):

SET @t = 0;
SELECT @t:=@t+1 AS uniq, id FROM thetable ORDER BY uniq;

.. but using this is not possible with MONyog. Only a single SQL-query can define a MONyog Custom Object (currently – we may add an option for multiple queries later though). But there is a simple workaround: a SUBQUERY in the FROM-clause can be used to reduce the above two queries to a single query:

SELECT @t:=@t+1 AS uniq, id FROM (SELECT @t:=0) AS derived, thetable ORDER BY uniq;

Actually you can add one more subquery SELECTING from `mytable`and use an ORDER BY here to ensure that the lowest value of `id` (the column you are retrieving in this simple example) will also have the lowest value of the unique identifer you created. Doing so may ease building the javascript-part of a Custom Counter as there will be less conditions to test for and sorting can be avoided (data returned are simply ‘better prepared for scripting’). Such an example (with an AGGREGATE and a GROUP BY and a little formatting added as well) could be like this:

SELECT @t:=@t+1 AS uniq, id, COUNT(id)
FROM
(SELECT @t:=0) AS derived1,
(SELECT id FROM thetable ORDER BY id) AS derived2
GROUP BY id
ORDER BY uniq;

MySQL documentation has a reservation though. It says “As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed.” But this reservation  seems not to apply if the assignment is a “SUBQUERY in the FROM-clause”.  It is a still a separate statement as far a I can understand – and also I have not been able to provoke an unexpected behaviour with the construction no matter what SQL syntax I tried (JOINs, GROUP BY .. HAVING etc.).

(And I did not invent this myself.  The tip appears as a user comment in the MySQL documentation)

Comments(0)

This is my considerations/thoughts after reading:

http://karlssonondatabases.blogspot.com/2012/02/why-i-think-sqlmode-is-useless.html
http://ronaldbradford.com/blog/why-sql_mode-is-essential-even-when-not-perfect-2012-02-16/

It is not possible to remove SQL_mode and in particularly not to remove ‘non-strict’ modes without breaking a lot of applications. The problems occurs with a table like this

CREATE TABLE `test`.`strict`(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`col1` VARCHAR(10) NOT NULL,
`col2` VARCHAR(10),
PRIMARY KEY (`id`));

.. and an INSERT statement like this:

INSERT INTO `test`.`strict`(`col2`) VALUES (‘string’);

Such ‘incomplete INSERT’ (where  not all columns are listed) will fail in strict mode for non-NULLABLE columns that do not have a DEFAULT (`col1` in this case – not `id` because AUTO_INCREMENT is equivalent to a DEFAULT in the context) – because:

1) The first thing the server will check is, whether a DEFAULT has been defined.  Since not the case for `col1`, proceed to step 2.
2) Next check is if it is possible to insert NULL. Obviously not possible for `col1` here as the column is declared NOT NULL. Proceed to step 3.
3) Third check is if ” (empty string) can be inserted for string columns and ’0′ (ZERO) for numericals. ‘strict’ mode does not allow it.

Server is left with only one alternative: to return an error. What is does and what is also one of the intentions with ‘strict’ mode. But if the applications needs it it will have to set SESSION sql_mode to a ‘non-strict’ mode. Not all users and admins on application-level has the privilege to change GLOBAL sql_mode (and it could also affect other applications) or make the column NULLable or add DEFAULTs.

Don’t forget that many servers serve a wide variety of applications. The discussion has a tendency to center around high-profiled deployments with a single application on the server.

I also think that is rarely a problem to have ” (empty string) instead of NULL in a table. But having ’0′ (ZERO) instead of NULL can lead to very misleading results in calculations (of an average for instance). The statements “this person ows me nothing” and “I don’t know if he ows me anything” are definitely not the same. So this is something to be careful about when working in ‘non-strict’ mode: all non-specified numbers INSERTed are set to ZERO.

Also quite a lot of applications will still use plain INSERTs for User Mangement (what they should not – they should use GRANT|REVOKE instead). That caused problems for some applications (including Workbench) with MySQL 5.5 were new columns were added to the user table. So also here there are some compability concerns (but I believe that the columns were made NULLable in recent releases).

So in conlusion I don’t think we will (or should) get rid of sql_mode soon (and if somebody thinks that applications affected should be rewritten I cannot disagree, but I am not so naive that I think it will happen soon).

What I request however is a little consistence. The Windows installer/configurizer will set a ‘strict’ global mode as default. Installing on Windows from the ZIP-archive and any installation method on *nix will not. If the Windows installer team had the intention to improve the server, why didn’t they take it to the server team instead? We have seen quite a lot of cases where people have migrated data from a hosted application to a local Windows machine (for testing, for learning or whatever) and then something suddenly doesn’t work as before because they are not aware of the differences in the default setup on different platforms and what effects it has.

Comments(0)

Changes (as compared to beta 3) include:

Features:
* A (simplified) Chinese translation is now included.
* Implemented Alt+Ins, Alt+Del, Alt+Up and Alt+Down for inserting, deleting, moving up/down the rows in CREATE|ALTER  TABLE interface when the focus is on respective grids. The tooltip for the toolbar buttons will now show this shortcuts.

Bug fixes:
* SQLyog sometimes waited unnecessarily long (30 seconds  - what is the client timeout setting we use internally) to report that SSH connection could not be established. This happened for instance if a wrong RSA passphrase was entered. In such and other cases there is no reason to wait for client timeout.

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

Comments(0)

Changes (as compared to beta 1) include:

Features:
* Added an option to add users to admin group.
* Columns in the replication tab can now be sorted.

Bug fixes:
* Beta 1 was not able to recognize multiple email addresses in the Notification settings page.
* Added an option of setting the default landing page which was not available in MONyog 5.0 Beta 1.
* Fixed a problem with replication tab not displaying data when there was a connection error.
* Total Size, Data Size and Index Size labels in Disk Info were interchanged in beta 1.
* Sometimes the Dashboard & Trends chart did not plot data.
* Sometimes the email subjects for stable mails appeared as critical alerts.
* Fixed usability & UI issues in all browsers.

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

Comments(0)

Changes (as compared to beta 2) include:

Features:
* It is now optional if Query Builder will create statements with ‘fully qualified tablenames’ or not.

Bug fixes:
* Autocomplete runs in a separate connection, but also when Autocomplete was disabled this connection was created for no reason.
* Small fixes – mostly GUI fixes to the new CREATE/ALTER TABLE interface.

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

Comments(0)

Accidentially I came across the statement “SHOW GRANTS requires the SELECT privilege for the mysql database.” in MySQL  documentation (http://dev.mysql.com/doc/refman/5.1/en/show-grants.html).

It is not quite true. Any user can “SHOW GRANTS [FOR himself]” with no privileges at all. But more important: SELECT priviege is requried on database-level,  Privilege to the privileges tables is not enough.  See

SHOW GRANTS;
/*returns

Grants for me@%
—————————————————–
GRANT USAGE ON *.* TO ‘me’@'%’
GRANT SELECT ON `mysql`.`user` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`tables_priv` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`procs_priv` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`db` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`columns_priv` TO ‘me’@'%’*/

SHOW GRANTS FOR root@localhost;
/*returns

Error Code: 1044
Access denied for user ‘me’@'%’ to database ‘mysql’ */

Anybody having SELECT privilege to the mysql database can read logs if you use log tables. And unlike when logging to files (where you can specify logfile paths)  there is no option to specify another database for the log tables.

I think it is a serious security flaw. The reason is that a log may contain data. That may be private data (email addresses, bank account numbers .. you name it). Consider a statement like

UPDATE `identity`SET  `bank_account_no` =  ….. WHERE social_security_id = ….. ;

You should have SELECT privilege to the `identity` table to see those data. But if you can read logs you need not.

I don’t claim all applications would send such statements (but I believe that some will do).  You can avoid it to some extent by using user variables, hashes, or by writing complex statements with JOINs and SUBQUERIES so that you don’t need to ‘ping-pong’ data from the server to the application and back (but it may conflict with performance in particular if SUBQUERIES are used).

So you should be careful with logging to tables. Not only is performance not good, but there are security concerns. Who should be allowed to see the logs? (but still log tables may be convenient for a test/development scenario though.)

I posted this bug report: http://bugs.mysql.com/bug.php?id=64215

Comments(1)

Changes (as compared to beta 1) include:

Bug Fix:
* If multiple databases with same name existed only differing in lettercase SQLyog would generate certain statements using the ‘mostly lowercased’ database name. This bug was introduced in 9.5.

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

Comments(0)