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)

Changes (as compared to 9.33) include:

Features:
* All CREATE TABLE and ALTER TABLE GUI functionalities will now open in a non-modal tabbed interface.  The new tab appears in the SQL area (alongside QUERY tabs etc.). With the new interface some minor bugs with the old interface have been fixed as well. Also the SQL generated is cleaner as regards formatting and redundancy. Note that in this beta the old dialogs are still used by Schema Designer.
* HISTORY and INFO tabs will now also appear in the SQL Area. The rationale is that those are not related to a specific (set of) query(ies). We plan to move the DATA tab up there as well for same reason.
* When SQLyog opens, the SQL Area will new open the HISTORY tab, INFO tab, and a single QUERY tab. Other tabs belonging in this area can be opened on demand. HISTORY and INFO tab display are persisitent across session (ie. if one is closed when SQLyog closes down it will not diplay when SQLyog is opened next time – but may be opened by user, of course).

Bug fixes:
* The BIT datatype was not supported properly in text-mode.
* On COMMUNITY if the SQL Area was increased to fill all vertical space, the divider could not be dragged back.
* In ‘Execute SQL-script’ it is now optional if the complete script should abort or not if an error was encountered.  Before it always aborted. The fix does not apply to HTTP tunnel where we will still ‘abort on error’ always.
* Copying to clipboard could erroneously raise an ‘out of memory’ error.  This was a miscalculation.
* ‘Import External Data’ wizard now supports SHIFT+CLICK selection of objects.
* The connection manager GUI’s of the SJA wizards did not handle SSL certicates properly (the main program window did not suffer from this).
* If an ENUM/SET column was returned using an alias the drop-down combo in the RESULT GRID did not display.
* Underscores were not handled properly by Autocomplete.
* Syntax highlighting is now not used in INFO tab. It made sense for the CREATE statement only and was disturbing in other contexts.

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

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)

Update: Thank you all for making this campaign a colossal success. We are extending this offer till Dec 2nd, 23:59 PST. Many corporate users requested us to extend the offer as they require time to get official approval. Pick your copy right now! Use coupon code blkfrdy11 to get 50% off.

–

Happy Thanksgiving!

Black Friday is back with a bang. Get a flat 50% discount on all Webyog products. Yes, you read it right, a flat fifty percent discount. Hurry, this offer expires Nov 28th Dec 2nd, 23:59 PST. Use coupon code blkfrdy11 to avail the discount on any purchase. Buy Now.

Still using SQLyog Community Edition?

Upgrade to the feature rich SQLyog Professional/Enterprise/Ultimate Edition. This offer is literally too good to pass up. Compare features.

Worried about your MySQL server’s health?

Monitor it like a PRO. Get MONyog – MySQL Monitor & Advisor. Your MySQL DBA in a box! Check what it has to offer.

Want to extend your maintenance period?

Don’t think twice, head right away to Customer Area & save a flat 50%. On extension, you get free access to all future releases of the respective products for one year.

Hurry, this offer expires Nov 28th Dec 2nd, 23:59 PST. Use coupon code blkfrdy11 to avail the discount on any purchase. Buy Now.

Regards,
Chirag
Webyog, Inc.

Comments(5)

I have for the first time been spending some time trying to understand the performance_schema. It is not easy to understand everything unless you are very well-versed in server internals (what I am not) and much information available here is probably more useful for server developers and testers than for ordinary users. But anyway some of the P_S tables are reasonable simple and useful. For instance the ‘accounts’ and ‘hosts’ tables (introduced in MySQL 5.6x) are immensely useful for everybody.

The reason why I spent time with this now was (I am totally egoistic, of course) that we just released the first beta in a new MONyog release cycle where you may expose P_S contents for monitoring. Please refer this Blog.

However I have two concerns with the P_S implementation. This derives from the fact that P_S is to some extent  updateable for users having sufficient privileges. You may for instance disable/enable ‘consumers’ and decide whether information should be recorded in counts of CPU clock-cycles or microseconds etc. Also some tables may be truncated in order to discard old information and free memory.

The interface for this is standard SQL statements (UPDATE, TRUNCATE). IMHO this leads to two problems/concerns:

1) Server restart. If the server restarts all P_S settings are reset to default. The UPDATEs from last running instance are lost. I am  simply missing a mechanism to load the UPDATEs again automatically at startup. I do not favour  introducing a lot of new server startup options and variables (that would be around 100 I think if all cases should be covered and there already are more than enough options IMHO). One option could be a P_S configuration file (‘performance_schema.ini’) or maybe an EVENT executing at startup (“at startup” could be a useful schedule setting for an EVENT in other contexts as well – but it is not in any server version currently). Also the problem cannot be worked around an automated way currently on Windows as far as I can see, as on this platform the server is not started by a ‘script’ but by an executable (the ‘Windows Service Manager’ reading details from a registry key). On Unix-type platforms you may (I think so!) modify the server startup script adding a call to Perl/Python/whatever script performing the updates on the P_S tables.

2) Multi-user/multi-admin environment. If for instance GRANTS for ‘peter’@'thishost’ are like “GRANT CREATE USER, SELECT,UPDATE ON *.* TO ‘peter’@'thishost’ WITH GRANT OPTION;” then peter may “GRANT SELECT,UPDATE ON performance_schema.* TO ‘jimmy’@'thathost’;”. Now both peter and jimmy can UPDATE the P_S tables that are updateable. But the problem is that one of the users may do so without the knowledge of the other resulting in unexpected results. The only solution I found was LOCKING (example: “LOCK TABLES performance_schema.setup_consumers READ;“). I don’t find it fully satisfactory however, but I have no better solution with current implementation and also no usable idea for an improved implementation.  

Any thoughts?

Comments(5)