PlanetMysql.ru — информация о СУБД MySQL

 Intellisense Support in Visual Studio

One of the cool features coming in Connector/NET 6.5 is the support in Visual Studio 2010 of Intellisense for MySql files.

As you probably know, the capability of editing mysql files (in the way of both .mysql raw files included in the solution and stored procedures/functions from Server Explorer) has been around for a while, yet a missing piece of the puzzle was to have advanced completion of sql statements in those files… until now.

Let’s explore the set of features currently implemented as part of Intellisense support.

Note: For completion intellisense features to work a database connection must be properly configured, see images below

For Server Explorer items, the connection is the root of the elements.

For .mysql files, the connection is individually configured using the Connect to MySql toolbar button.

Also to trigger the completion features you just either use the standard Visual Studio shortcut “Control+J” or start typing the name of an object. Both ways to trigger intellisense work in all syntactical contexts.

a) Table Completion

Table completion means showing the list of tables for the current connection database in the proper syntactical context. Basically this means any part of a sql sentence where the parser expects a table. Some examples of this feature follow:

After the “from” clause in a select, you can press Control+J and get a list of all the tables from the current connection database.

After selecting one of the options, the text appears wrapped in quotes (to cover the case when the table name has whitespaces or is a mysql reserved word).

Or you can start typing the name of a table, and the list shows only those items that match.

Another example, after an inner join.

Another example, in an update statement. Also notice that you can have several sentences on the file, but the previous sentences must be correct syntactically (included being finished with a semicolon).

Another example, on the insert statement.

As expected, combinations like insert into select, also work.

Table completion for a delete statement.

For show create table statements.

For truncate statements.

And of course there are many others.

b) Column Completion

Column completion means showing the list of columns for the current connection database table or tables in the proper syntactical context. Basically this means any part of a sql sentence where the parser expects a column. Some examples of this feature follow:

After a select without from clause you can get, by Control+J, the list of columns for all tables in the current database.

If you type c, the lists shown filters out items whose name doesn’t start with the characters typed.

Things get more interesting when there is a context restricting the tables to use, like here, after the “*, “.

What if you add the same table several times with different alias? Then the list of columns is prefixed with the alias name, not the table name. Also works in where clauses.

“Set” clause in Update statements.

“Order by” clauses.

“On” clause in a left join select statement.

Column completion on insert into statements.

And other cases not showed here.

If you want to see Intellisense within a stored procedure definition, the rest of the stored procedure (excluding the sentence currently being edited) must be syntactically correct.

c) Stored procedure name Completion

Stored procedure name completion means showing the list of stored procedures in the proper syntactical context.

For the call statement, you get the list of stored procedures; notice that when you highlight one, its signature appears as a tooltip.

For those of you interested in compiler technology, behind the scenes these intellisense features are powered by an ANTLR generated parser, this parser will also be the foundation for other interesting features…

Anyway, let us know what you think, if there are other things you want to see on Intellisense support or in the Connector/NET in general. (Or you can open a bug / feature request at http://bugs.mysql.com, Connector/NET category).

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Connector/Net 6.5.0, a new version of the all-managed .NET driver for MySQL has been released.  This is a beta release of our newest connector and comes with several new features.  It is of beta quality and should not be used in any production environment.  It is appropriate for use with MySQL server versions 5.0-5.6

It is now available in source and binary form from here and mirror sites (note that not all mirror sites may be up to date at this point-if you can't find this version on some mirror, please try again later or choose another download site.)

This new versions brings new features such as

• Exception and command injector support
• Millseconds support
• Better partial-trust support
• Better intellisense including auto-completion when editing stored procedures or .mysql files
  

These features are not yet documented in the shipping documentation.  We will be posting a series of posts to our blog outlining these new features and will have them fully documented by GA.

You can leave comments here on this blog or you can also post questions on our forums at http://forums.mysql.com/. 

Enjoy and thanks for the support! 
PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Connector/Net 6.3.8, a new version of the all-managed .NET driver for MySQL has been released.  This is a maintenance release to our 6.3 release chain and includes 40 changes and fixes.

Version 6.3.8 is appropriate for use with versions of MySQL 5.0-5.5.

It is now available in source and binary form from here and mirror sites (note that not all mirror sites may be up to date at this point-if you can't find this version on some mirror, please try again later or choose another download site.)

The release is also available for download on My Oracle Support (MOS).

Enjoy and thanks for the support!
PlanetMySQL Voting: Vote UP / Vote DOWN

When I first got in touch with C it was in the early 1980's. I was a sysadmin at a Swedish telco operator (then THE Swedish Telco operator, Televerket, nowadays called Telia) for a system used for software development for a PABX system called A345 in Sweden, better known as Meridian in the rest of the world (co-developed by Televrket and Nortel). The Meridian system was the biggest of the non-custom built PABXes in those days. The language used to program it was called SL-1 (Switching Language 1) and the development system, like editors (vi / ined), compilers etc was running on a Unix system.

This sure was one of the earliest commercial uses for Unix, the Unix variant was version 6 and was not a BSD or anything like that, this was way before BSD really. Rather, the system was built by Interactive Systems which was the first commercial Unix vendor. This was version Interactive 2.5, based on Unix version 6, mind you. sh and csh only, no bash. Lot's of hardcoded stuff in the shell etc. The hardware it was runniing on was a PDP-11/70.

I knew a bunch of programmiing languages already, like Pascal, Basic, APL (yes! APL), Fortran etc. Also, I had done my fair share of microcompter assembly (mostly on 6502 and some 6800 CPUs). As the system we were sysadmining was so Unix focused, everyone who got in touch with it had to know some C, so eventually I was sent on a C-class (not a C++-class that is ;-). This changed my life, to a large extent.

Having access to a reasonably high-level language like C, that was still so close to assuembly and hardware (did I tell you I was a hardware geek also?), was just what I wanted. Close to the hardware, but high-level enough so you could write reasonably sized systems with it without spending too much time on individual bits and bytes, except when you wanted / needed to.

Of the things I have learnt over the years in the IT-industry, like Windows, Linux, SQL, database, optimization, multi-threading, you name it, none of them has been even close to being as useful as those C-classes I took nearly 30 years ago.

Dennis Ritchie, the creator of C, really got C completely right. And thing is, C was very close to being perfect even at the first try. Look at "The C programming Language" book, this has to be one of the most concise book on programming I know of. And I still use this book. It's factual, at times funny and always complete. And this in a very small package, really amazing compared to the 650 page books on C# or something of today. They say C is so complex, so why does it take just over 100 pages to explain it? And 600 something to get started with a language that is supposedly "easier to learn"?

Want to know how to write solid code from the ground up? Learn C, even if you will eventually develop in some other laguage. Want to learn hos coomputers work? Learn C. Want to understand how Windows works? Learn C (this is how I learnt Windows some 20 years ago, using Windows 3.1 and 3.11. I was writing Windows apps in C (and this is how I still, to this day, develop Windows apps), and suddenly I knew how Windows worked, without even trying).

Too bad that Dennis Ritchie is dead, but the legacy of the world greatest programming language, C, lives on. C has been used to develop applications on all kinds of levels, from end-user applications to databases and infrastructure to operationg systems. If C++ better that C? Probably so, but some features of C are gone in C++ and some things that C++ adds aren't so great. What about C# then? Same there. What about Java? Nope, it's a good language and a good environment, but I still prefer C, which is not to say that Java doesn't have it's merits. And then we come to things like Tcl/Tk, Perl, PHP, Ruby etc? Useful tools alright, but not really proper programming langauges in my mind, and not proviing the insights you get by understanding C.

Over the years, when I have been with a customer or used some program and something broke, knowing C meant I could much faster diagnose what was wrong and how to fix it. Pascal / Fortran / APL and all those languages never did that for me. C required an understanding of the environment that the code was to execute in, but once you had that understanding, things got A LOT easier, and you suddenly knew things that you didn't before, which weren't directly related to the task at hand, but you knew it.

Rest in peace Dennis, and thank you for bringing C and Unix to the world. I know that my life would have been very different without C and Unix.

/Karlsson
Born to code in C. Using vi (as emacs didn't fit in the memory of that PDP-11/70).
PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Connector/Net 6.4.4, a new version of the all-managed .NET driver for MySQL has been released.  This is an update to our latest GA release and is intended for full production deployment.

Version 6.4.4 is intended for use with versions of MySQL from 5.0 – 5.5

It is now available in source and binary form from here and mirror sites (note that not all mirror sites may be up to date at this point of time- if you can’t find this version on some mirror, please try again later or choose another download site.)

The release is also available for download on the My Oracle Support (MOS) and will be available from Oracle eDelivery.

This release includes several bug fixes including a fix to using Windows authentication.  Please review the change log and documentation for a review of what changed.

Enjoy and thanks for the support!

PlanetMySQL Voting: Vote UP / Vote DOWN

The 95 percentile for query response times is and old concept; Peter and Roland blogged about it in 2008. Since then, MySQL tools have calculated the 95 percentile by collecting all values, either exactly or approximately, and returning all_values[int(number_of_values * 0.95)] (that’s an extreme simplification). But recently I asked myself*: must we save all values? The answer is no. I created a new algorithm** for calculating the 95 percentile that is faster, more accurate, and saves only 100 values.***

Firstly, my basis of comparison is the 95 percentile algo used by mk-query-digest. That algo is fast, memory-stable, and very proven in the real world. It works well for any number of values, even hundreds of thousands of values. It saves all values by using base 1.05 buckets and counting the number of values that fall within the range of each bucket. The results are not exact, but the differences are negligible because a 10ms and 13ms response time are indiscernible to a human. Any algo that hopes to handle very large numbers of values must approximate because not even C can store and sort hundreds of thousands of floats (times N many attributes times N many query classes) quickly enough.

So when I finished the new algo, I compared it to the mk-query-digest algo and obtained the following results:

FILE                         REAL_95     OLD_95     NEW_95  OLD_DIFF NEW_DIFF  OLD_TIME NEW_TIME   FILE_SIZE  OLD_RSS  NEW_RSS
nums/500k-1-or-2               1.751      1.697      1.784    -0.054   +0.033     12.12     9.37     4500000    3.88M    2.63M
nums/100k-1-or-2               1.749      1.697      1.794    -0.052   +0.045      2.42     1.88      900000    3.88M    2.63M
nums/50k-trend-1-to-9          6.931      6.652      6.995    -0.279   +0.064      1.24     0.90      450000    3.88M    2.63M
nums/25k-trend-1-to-5          3.888      3.704      3.988    -0.184   +0.100      0.64     0.47      225000    3.88M    2.63M
nums/21k-1-spike5-1            0.997      0.992      2.002    -0.005   +1.005      0.55     0.42      189000    3.88M    2.63M
nums/10k-rand-0-to-20         19.048     18.532     19.054    -0.516   +0.006      0.29     0.21       95079    3.86M    2.62M
nums/10k-rand-0-to-10          9.511      9.360      9.525    -0.151   +0.014      0.29     0.21       90000    3.86M    2.62M
nums/4k-trend-1-to-7           5.594      5.473      6.213    -0.121   +0.619      0.14     0.09       36000    3.86M    2.63M
nums/1k-sub-sec                0.941      0.900      0.951    -0.041   +0.010      0.07     0.04        9000    3.80M    2.62M
nums/400-half-10              10.271      9.828     10.273    -0.443   +0.002      0.05     0.03        3800    3.79M    2.62M
nums/400-high-low             10.446     10.319     10.446    -0.127        0      0.05     0.03        3800    3.79M    2.62M
nums/400-low-high             10.445     10.319     10.475    -0.126   +0.030      0.05     0.03        3800    3.79M    2.63M
nums/400-quarter-10           10.254      9.828     10.254    -0.426        0      0.06     0.03        3700    3.79M    2.62M
nums/153-bias-50              88.523     88.305     88.523    -0.218        0      0.05     0.03        1500    3.79M    2.62M
nums/100-rand-0-to-100        90.491     88.305     90.491    -2.186        0      0.05     0.03         991    3.79M    2.62M
nums/105-ats                  42.000     42.000     42.000         0        0      0.05     0.03         315    3.75M    2.61M
nums/20                       19.000     18.532     19.000    -0.468        0      0.04     0.03          51    3.79M    2.62M
nums/1                        42.000     42.000     42.000         0        0      0.04     0.03           3    3.75M    2.61M

 
I generated random microsecond values in various files. The first number of the filename indicates the number of values. So the first file has 500k values. The remaining part of the filename hints at the distribution of the values. For example, “50k-trend-1-to-9″ mean 50k values that increase from about 1 second to 9 seconds. Number and distribution of values affects 95 percentile algorithms, so I wanted to simulate several possible combinations.

“REAL_95″ is the real, exact 95 percentile; this is the control by which the “old” (i.e. the mk-query-digest) and new algos are compared. The diffs are comparisons to this control.

Each algo was timed and its memory (rss) measured, too. The time and memory comparisons are a little bias because the mk-query-digest module that implements its 95 percentile algo does more than my test script for the new algo.

The results show that the new algo is about 20% faster in all cases and more accurate in all but one case (“21k-1-spike5-1″). Also, the new algo uses less memory, but again this is a little bias; the important point is that it doesn’t use more memory to get its speed or accuracy increase.

The gains of the new algo are small in these comparisons, but I suspect they’ll be much larger given that the algo is used at least twice for each query. So saving 1 second in the algo can save minutes in data processing when there’s tens of thousands of queries.

Instead of explaining the algorithm exhaustively, I have upload all my code and data so you can reproduce the results on your machine: new-95-percentile-algo.tar.gz. You’ll need to checkout Maatkit, tweak the “require” lines in the Perl files, and tweak the Bash script (cmp-algos.sh), but otherwise I think the experiment should be straight forward. The new algo is in new-algo.pl. (new-algo.py is for another blog post.)

My ulterior motive for this blog post is to get feedback. Is the algorithm sane? Is there a critical flaw that I overlooked? Do you have a real-world example that doesn’t work well? If you’re intrepid or just curious and actually study the algo and have questions, feel free to contact me.

* By “recently asked myself” I mean that some time ago Baron and I wondered if it was possible to calculate 95 percentile without saving all values. At that time, I didn’t think it was feasible, but lately I thought and coded more about the problem.

** By “a new algorithm” I doubt that this has never been attempted or coded before, but I can’t find any examples of a similar algorithm.

*** By “saves only 100 values” I mean ultimately. At certain times, 150 values may be saved, but eventually the extra 50 should be integrated back into the base 100 values.

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Connector/Net 6.4.3, a new version of the all-managed .NET driver for MySQL has been released.  This is a GA release and is intended for full production deployment.

Version 6.4.3 is intended for use with versions of MySQL from 5.0 – 5.5

It is now available in source and binary form from [http://dev.mysql.com/downloads/connector/net/6.4.html] and mirror
sites (note that not all mirror sites may be up to date at this point of time
- if you can’t find this version on some mirror, please try again later or choose another download site.)
The release is also available for download on the My Oracle Support (MOS) and will be available from Oracle eDelivery.

** New features found in 6.4 include (please see release notes for more information) **

* Windows Authentication*
This release includes our new support for Windows authentication when connecting to MySQL Server 5.5.

* Table Caching *
We are also introducing a new feature called table caching.  This feature makes it possible to cache the rows of slow changing tables on the client side.

* Simple connection fail-over support *

We are also including some SQL generation improvements related to our entity framework provider.   Please review the change log that ships with the product for a complete list of changes and enhancements.

Enjoy and thanks for the support!

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Connector/Net 6.2.5, a update to our all-managed .NET driver for MySQL has been released.  This is an update to our legacy 6.2 version line. All new development should be using a more recent product such as 6.4.3.

Version 6.2.5 is intended for use with versions of MySQL from 4.1 – 5.1.  It is not suitable for use with MySQL 5.5 or later.

It is now available in source and binary form from [http://dev.mysql.com/downloads/connector/net/] and mirror
sites (note that not all mirror sites may be up to date at this point of time
- if you can’t find this version on some mirror, please try again later or choose another download site.)

This update includes more than 45 fixes from 6.2.4.  Please review the change log that is included with the product to determine the exact nature of the changes.

Enjoy and thanks for the support!

PlanetMySQL Voting: Vote UP / Vote DOWN

PlanetMySQL Voting: Vote UP / Vote DOWN

 

I'm the boards of two companies (Pentaho, Revolution Analytics) that are starting to see a lot of customer traction around Big Data. More and more companies in media, pharma, retail and finance are doing advanced analysis, reporting, graphing, etc with massive data sets. It made me wonder what other areas of the technology stack might evolve with the trend towards Big Data.  Obviously, there's new middleware layers like Hadoop and Map Reduce, and we're also seeing the emergence of NoSQL data management layers with Cassandra, MongoDB, MemBase and others.  But what about programming languages?  

OpenGamma CEO and resident genius Kirk Wylie wrote a post recently about why he wants a new programming language.

So why don't I have this language yet? Well, partially because programming language craftsmanship is hard. I'm pretty sure I'm not good enough to do it, which is usually my default criteria for saying something is Really Hard.

But I think as well the k3wl languages coming out are coming out of language requirements of the Top 10% crowd. They're the ones good enough to actually write the languages, and they're going to write a language that makes them happy. But then you end up with Scala, and then you end up with this monstrosity, and then you make me cry. A language in which that thing is even possible will never be a candidate as a Journeyman Programming Language.

You know who's going to do it? Someone like Gosling, who set about with the needs of the journeyman programmer in Java. But the state of the art has moved on, and Java just isn't suitable anymore.

Who I would really like to do it is Anders Hejlsberg. I am a very big fan of C#-the-Language. It's just that .Net-the-Ecosystem is so Microsoft-specific and horrific it'll never catch on in the wider world, no matter what Miguel de Icaza thinks.

This got me thinking about the challenge of the current complexity in Big Data systems.  Today, you have to be near genius level to build systems on top of Cassandra, Hadoop and the like today.  These are powerful tools, but very low-level, equivalent to programming client server applications in assembly language.  When it works it's great, but the effort is significant and it's probably beyond the scope of mainstream IT organizations.  (That's one reason that Revolution's R product has appeal, but R is a specialized statistical analysis tool, not a general purpose language.)

Could the Big Data complexity be factored out somehow with a new general purpose programming language?  No doubt. Having worked with Anders on the creation of Delphi many years back, this is right up his alley.  Or maybe we already have a good starting point with Erlang, Scala and Google's Go.  Go is particularly interesting having been designed by Rob Pike and Ken Thompson of Bell Labs / Unix fame.

What's been your experience in programming Big Data systems?  What do you think's needed?  Let me know in the comments below.

Zack Urlocker is an investor, advisor and board member to several startup software companies in SaaS and Open Source. He was previously the EVP of Products at MySQL responsible for Engineering and Marketing. He built the MySQL Enterprise subscription strategy and product line. MySQL was sold to Sun for $1 billion and is now part of Oracle Corporation. He is also a marathon runner, blues guitarist and fan of Interactive Fiction. 

• Kirk Wylie: Blog, OpenGamma
• Wikipedia: Erlang, Scala, Go, Hadoop, Cassandra, MongoDB
• Web sites: Erlang, Scala, Go, Hadoop, Cassandra, MongoDB
• Companies: Cloudera, CouchOne, 10Gen, NorthScale, Riptano

PlanetMySQL Voting: Vote UP / Vote DOWN