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)

In various discussions I have expressed that I think that there are way too many options and variables in MySQL (and it gets worse for every new release). There are simply too many to know and remember them all.  If you manage a server yourself  it is (probably) a minor problem as you should know the options you use and not use those that you don’t know.  But if you are connected to a server that you don’t manage yourself you may get surprises if some rare option you are not familiar with has been set.

The last one I came across is ‘skip-character-set-client-handshake’.  MySQL documentation http://dev.mysql.com/doc/refman/5.5/en/server-options.html says “To ignore client information and use the default server character set, use –skip-character-set-client-handshake; this makes MySQL behave like MySQL 4.0.”

Now what is the idea in having a recent server ‘behave like MySQL 4.0′ ?  There could have been some compatibility concerns in early 4.1/5.0 releases, but now (after 8 years) is it stilll relevant to have? I don’t think so.

The ‘chain breaks’ in the case where a client (we are talking about clients compiled with the C-API or a connector having similar functionalities as mysql_options() in the C-API – what means that it is not PHP we are talking about!) requires character_set_client to be utf8.  The client will execute “SET NAMES UTF8;” after connection just in case the server has a non-utf8 default charset.  It works fine.  SET NAMES works as expected. So far so good. Futher the client is using the ‘reconnect flag’.  Reconnects may take place after connection was lost  in several cases (client was idle for longer than wait_timeout, some tunnel system server (SSH tunnel, VPN tunnel) disconnected client and server, intermittent hardware or networking failures etc.).  The important point to note here is that reconnect is handled by the API transparently for the ‘parent code’ where it is linked. Thus the client cannot know  when to execute “SET NAMES UTF8;” again (as a regular SQL statement).  The client will instead set the connection charset to utf8 in mysql_options() so that utf8 also will also be character set for the connection after a reconnect.  But with the ‘skip-character-set-client-handshake’ -option set, it simply has no efffect (the option is intended to work like this).

As a consequense there is no way not to have non-ASCII characters garble in the client after automatic reconnection in such environment as far as I can see.

We had such report recently. It took some time to figure out the reason. There is also an old related bug here http://bugs.mysql.com/bug.php?id=11972 btw, but this is fixed long ago even though I first thought it might have found its way back to PerconaServer (what was used in this particular case), it was not the case.

It is fair enough to have compability options with older servers for some time.  But is it still after 8 years? Also in a case like this where it breaks the way a client and the server is intended to ‘negotiate’  multiple charsets?

More old options to get rid of?  I am a collector of such!

Comments(2)

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(1)

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)

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)

This is a follow-up om my first Blog on performance_schema that appeared here:

One particular request that we had frequently from our users is to have status variables exposed per database and maybe even per table. Currently this is not possible. Not even with MySQL 5.6.3 with the P_S additions found there. I have frequently experienced that users get frustrated why ‘such a simple thing’ is not possible and I have the impression that they sometimes tend not to believe me!

Examples: the (GLOBAL) status variables ‘slow_queries’, ‘com_update’ etc. has no database/table -level information. For those MySQL users that run a lot of different applications (both standard applications – Forums software, Blogging software etc. – as well as applications dedicated to the particular business of the organization) on same server this would be extremely usable. It is not always easy to identify the reason for a sudden or periodic increase of a status variable. If the database context was exposed it would in most scenarios be possible to identify the application causing this and then it would be much easier to analyze.

It is to some extent possible to get such information from logs of course, but enabling general log has a penalty and parsing logs is complicated alone by the fact that sometimes a ‘fully qualified’ table name is used and sometimes a USE statement + a non-’fully qualified’ table name is used. The USE statement may appear in the general log Gigabytes before statements using it! Also use of log tables (for general log in particular) has penalties.

Retrieving the information from P_S could be framed like this:

SELECT database, table, query, query_time FROM performance_schema.queries WHERE slow_query = 1 and com_insert = 0 ORDER BY query_time DESC;

Also aggregation like “.. GROUP BY database ..” could be useful. If memory is a problem the P_S tables could have aggregated information only. Even that would be helpful in some cases.

I don’t know how much trouble it would be to add such instrumentation. I realize that queries (JOIN’s, UNION’s, subqueries) may reference more tables and even different tables in more databases, but even that should be possible to solve somehow (record it twice if there are two tables referenced, for instance).

I would find a feature/instrumentation like this in P_S extremely useful. More useful (for me) than much of the stuff you’ll find there now.

Comments(0)

What does FLUSH STATUS do? We all know that it will simply reset all status variables (except for ‘uptime’) to same values as immediately after a server restart. Just google it and you will find that behaviour statet on hundreds of sites on the Internet.

But NO – it is not true! Not anymore. MySQL documentation:
http://dev.mysql.com/doc/refman/5.5/en/flush.html

“This option adds the current thread’s session status variable values to the global values and resets the session values to zero. It also resets the counters for key caches (default and named) to zero and sets Max_used_connections to the current number of open connections.”

This bug report http://bugs.mysql.com/bug.php?id=22875 has some background. Before introduction of seperate SESSION and GLOBAL status variables, FLUSH STATUS really did reset (almost) all status variables to same values as immediately after a server restart.  Now it affects SESSION status variables (but why is SUPER privilege required for this?) and a few GLOBAL status variables only.

From the bug report I think that the change of behavior was not really planned and specified properly in advance but came as some kind of surprise – also to some people in the MySQL developer team. I agree with Konstantin Osipov’s (one of those MySQL developers that seemed surprised) comment in the bug report:

“As this behaviour got broken in 4.1, I personally consider it a regression and believe that it should be fixed as a regression. In order to fix it in line with the design of SHOW [SESSION/GLOBAL] STATUS we should implement FLUSH GLOBAL STATUS.FLUSH STATUS behaviour should be modified to not change any global data,  such as:
- key cache counters
- connection counters
- SHOW_LONG global variables.
In other words, FLUSH STATUS should behave as FLUSH SESSION STATUS and only reset session-scope status variables. [SESSION] keyword should be added to the grammar and as optional.Key cache counters, connection counters, SHOW_LONG variables and server-scope status variable (those that have session counterparts) should be reset by FLUSH GLOBAL STATUS. FLUSH SESSION STATUS should no longer require SUPER privilege (connection privilege is enough, and as long as it is checked at connection, no privilege check is necessary).FLUSH GLOBAL STATUS should require SUPER privelege.”

But what happened was that the new behaviour was simply documented and since the problem/the report has been abandoned. It is now 5 years ago. Nothing happened since. The bug report is still open with the status of ‘verified’.

The reason why I am writing this is that I just replied to a concern to a MONyog user on whether MONyog could be fooled by this when calculating counters in the “ALL TIME” timeframe. The answer is that it cannot provided the actual server has implemented the GLOBAL status variable ‘uptime_since_flush_status’. All recent servers have (only very early 5.0 and 5.1 servers (5.0.35-,  5.1.24-) that nobody should use today have not). MONyog will simply discard all collected information older than ‘uptime_since_flush_status’ even if it has not yet been purged from the MONyog embedded database.

But still: Can we soon have that SESSION|GLOBAL switch for FLUSH STATUS, please? What we have now is a mismatch both as regards the semantics and the privileges requried. And 5 years of ‘stand-still’ for a ‘verified’ bug report related to server core features is too much (even though it is probably not the only one).

Comments(0)