SHOW GLOBAL VARIABLES ... autocommit?? What?

The autocommit variable is a session only variable, right? Easy to confirm by:
mysql> select @@global.autocommit;
ERROR 1238 (HY000): Variable 'autocommit' is a SESSION variable

But if I do this another way, I get a different result:
mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)

To me, this is a bug, right? But before I report it, I wanted to see if you agree, or? Might there be stuff out there that relies on this strange behaviour. And it gets worse, I'm afraid. In a new MySQL session:
mysql> show session variables where variable_name = 'tmp_table_size' or variable
_name = 'autocommit';
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| autocommit | ON |
| tmp_table_size | 13631488 |
+----------------+----------+
2 rows in set (0.00 sec)

mysql> show global variables where variable_name = 'tmp_table_size' or variable_
name = 'autocommit';
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| autocommit | ON |
| tmp_table_size | 13631488 |
+----------------+----------+
2 rows in set (0.01 sec)
Looks fair, right. So now lets set these two variables in this session:
mysql> set tmp_table_size = 102400;
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
OK, now let's look at the result, in global and session scope again:
mysql> show session variables where variable_name = 'tmp_table_size' or variable
_name = 'autocommit';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| autocommit | OFF |
| tmp_table_size | 102400 |
+----------------+--------+
2 rows in set (0.00 sec)

mysql> show global variables where variable_name = 'tmp_table_size' or variable_
name = 'autocommit';
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| autocommit | OFF |
| tmp_table_size | 13631488 |
+----------------+----------+
2 rows in set (0.00 sec)

Whoa, this doesn't look right, does it? I change one variable and it affects GLOBAL scope, but not for the other variable. And there is a difference between these two variables, which is that autocommit is really a session only variable! So why in heavens name does it pop up when I do a SHOW GLOBAL VARIABLES? And why is the global value changed when I set the session value? This seems to be how it works for all session only variables?
There might be some sense in this, that I do not understand, so please enlighten me! But if this is the case, then the docs are wrong! Like MySQL manual section 5.1.5 that says this: Several system variables exist only as session variables and then goes on to list these, which are all show as above as both local and global (but always with the same value). The same section also states that Most of them are not displayed by SHOW VARIABLES which isn't true at all, actually, they are ALL displayed by SHOW VARIABLES!

And MySQL manual section 12.5.5.41 says that With the GLOBAL modifier, SHOW VARIABLES displays the values that are used for new connections to MySQL which actually isn't true either (just because I set AUTOCOMMIT to 0, and that is the vale displayed by SHOW GLOBAL VARIABLES, new connection will still have AUTOCOMMIT on).

So, what do you say? Am I just confused? Is the docs wrong, or even right in some awkward way? Or is this a bug? Or? And if you are wondering, the effect on the corresponding INFORMATION_SCHEMA tables is the same...

/Karlsson

Drag-and-Drop in C on Windows, the easy way

OK, yeasterday I published a post where I complained about the complexities of Drag-and-Drop in plain C on Windows, and how this was related to Object Orientation gone bad. Why do you need three or 4 objects, jsut to drag a string of text from one place to another?

Well, then, if you agree that this is outrageous, I have help for you now. I just published my plain C library that does just this. You simple Win32 C program need not worry about OLE or COM. About IDataObject or interfaces or any of that stuff. Just usse hardcore C, like all macho developers like myself do. You need my library though, and I admit I AM cheating a bit in it, but actually, it does the trick and it follows the rules, both from COM/OLE and Win32 points of views.

Now, to drag and receive a text string, you need my library and you need to call 4 plain C functions or so, nuthin' else: Init the library, register as a target for a drop, start dragging and cleanup as a target for a drop. Then you need to handle the drop itself of course, but this is easy enough.

So the help is now available on Sourceforge and there is the source for the library, a Visual Studio project for this, complete with a sample, and a full PDF document describing the whole thing.

So, happy hacking, and don't drag your feet!
/Karlsson

Of OO gone bad and how I played a trick on Microsoft

Look back at the early / mid 1990's and if there was one paradigm that would rule the world after that, it was the everything would Object Oriented after that. I was pro-OO as a general idea also, and still am, but I believe that there are still stuff to solve. But hey, that wasn't the early 1990's, everything OO was supposedly good. SQL with OO extensions (remember the ORDBMS ideas? I still have Stonebreakers book, and it's worth reading, but nothing much came out of it).

I think there are a whole bunch of reasons why OO didn't end up being as important as it was foreseen to be back then (it's still important and widely used, I know that, but if you were there in the early 1990's OO rage, you could even go to the bathroom or have a beer without creating an object, and as we realize, after a few of those "Beer objects" it turns more and more difficult to create them), and you could go to a bar and chat a lady up without getting involved with polymorphism (and that is NOT a "bad" disease, and if you are not into OO, it's supposed to be a good thing. But ladies in bars rarely understand that).

Anyway, back to the original discussion. What killed the all encompassing OO world was, among other things, the web. Once WWW was stamped in every ones heads, all such theoretical stuff as OO was pushed to the side, and more traditional, but simple, efficient and workable solutions such as PHP and MySQL came around, none of them terribly OO.

So assuming that OO didn't die, but became less important (as did those consultants of those days that didn't write single line of code, or set up a single server in the world or anything, but despite this was the highest paid in the industry, because they were "OO consultants". Writing diagrams, and charts and going to meeting, expecting that awful code to write itself, so they didn't have to. Once that era was over, some of their firms got prestigious jobs at solid and reliable companies such as Enron). That was a long parenthesis, by the way, I'm getting good at that.

Whoa, will I ever get to the point? Yeah, yeah, I'm getting there. One thing on everybodies mind at the time was 32 bit computing and the next Windows release, Windows 95! Windows 95 used the "new" Windows 32-bit API Win32 (Hey, Microsoft marking had a long thought about THAT name), to replace Windows 3.11 and predecessors 16-bit... (You get a hug from me if you can guess the answer) Win16.

So one might think that Microsoft would be smart now. Win32 was a good API, tried in Windows NT and was solid and reliable. And a solid 32 bit API it was and is (I was programming in Win16 way back in 1992 or so). So the new cool GUI features of Windows 95 that would revolutionize the world and create world peace (except possibly for parts of the middle east, they had to wait til Windows 98) would just be added to the, now rather massive, Windows 32 API. That was easy to use as it was now clean 32-bit (Win16 was a mix, at beat) and doing it in C would make it easy to add any cool OO technologies on top of that. That's the good thing with C, you can interface just about anything to it. So this was NOT what Microsoft did.

No, the power of OO was still in effect during the time when Windows 95 was developed. OK, of the main applications languages on Windows at the time, few, among them C, Visual Basic and them some weirdo scripting stuff like "Hypercard" (Oh please, let's forget that. Don't bring that up again!). No, Microsoft invented COM. Which is a pure OO layer. And OO is the future!

Well, there is an issue there. Doing full OO on top of non-OO is pretty easy, the other way around not. So COM turned out to be much less complex than expected, as it had to interface with VB and stuff like (people had large stopped caring for C now, it wasn't OO you know. But VB was easy to "use" (which means that anyone can write useless and bad code in no time at all) so that was OK. And in the process, Microsoft wanted to convert the C people into C++ developers, so here came MFC.

Now, the world of OO wasn't as simple as that, not even for dear old Microsoft. Even though stuff like Informix had an OO SQL, and C++ was OO as was Smalltalk and a bunch of other languages, and some had had OO "extensions" added to them (the technical terms for OO features in a non-OO language is "lipstick on a pig"), this was not as bright and shiny. C was known for being reasonably portable and simple to access, but that was because the concepts of C are based on something fundamental, which is how a computer works. And int datatype is what the CPU sees as an integer. A pointer is a CPU reference. Easy to understand and use, once you get the idea.

The would of OO though was based firmly in a swamp of OO consultants driving the world of OO and IT and world peace (see above) by shouting at each other and when that didn't help, writing long book, documents and drawings, that they threw at each other. Results was not world peace, surprisingly, but that we ended up with OO that was there and that worked, but that was different all over the place. Some had multiple inheritance, others didn't. Some were basically non-OO and had had some OO lipstick painted on them by some high-ranking OO consultant, and some were so OO that basically the only keyword in the language used was object. And some had interfaces and others didn't. And some implementations of some OO stuff had templates, others didn't (you know who you are, but I am not naming names here). And they were all objects, and they were all incompatible, to to the rescue came, not the good lord, but the web. And all this OO stuff was left as it is, and forgotten about.

So here I am, finally getting to the point. I am working on my MyQuery MySQL Query Tool version 3.1 now, and most of it is done. It's pretty packed with cool features for a DBA and all that. Since version 3.0 I have also had dictionary views. These were useful, and you could double-click on, say, a table in one of them, and it would be pasted to the editor window. Useful, yes, but the way to do such an operation, i.e. copying data from one window to another, should be done with Drag-and-Drop. And Drag-and-Drop first appeared in Windows 95, and it does require some OO language, actually. It is very OO. Gosh! I have a text here, I take it with the mouse, drag it to another window, and release the mouse button, why can't I do that I do any other things? Like interacting with the mouse? You need to know when the mouse comes to a window? Handle a message! When it leaves a Window? Handle a message! When a button is clicked? Well, fact is, you handle a message! UNLESS! You are entering the window whilst dragging something! Then you need to create a whole bunch of COM objects! Why in heavens name is this necessary?

Well, that last question I can't answer, but I have been digging into this now on and off for a few weeks, and I know now that COM isn't as bad as it looked from the start. COM really is implemented in C anyway, it's just the Microsoft has done a crap job out of telling you how this works and has only provided a select number of interface, where they tell how the interfaces works from the user of the interface POV, such as a VB developer gets to know how to do this or that, but not how it gets from an OO COM object into a distinct non-OO VB application.

Well, that was the case until now. Eat my dust, Billy Gates! In the course of creating Drag-and-Drop for MyQuery, I felt this such a useful effort for someone else (and myself in future projects), that I implemented a generic C layer on top of stupid COM. I looked at what it takes to do a simple Drag-and-Drop with COM in VB and with MFC Com objects. Tell you what, my approach means less code. It really does. I only expose very few functions, and once that is done, to drag: Call two functions (both in my library), simple. And then to register for a Drag operation (this is not the kind of "Drag operation" that you can get by a shady doctor in San Francisco though) is two simple calls. And when an object is called, what happens? You have two choices: A callback function is called, or, hold your breath: A message is received. With all the data you need!

The library will be on Sourceforge, or possibly somewhere else, shortly.

And just to be clear. I don't mind OO. But I donät think OO as a generic concept was never done right! Which is why the importance of OO has diminished, in my mind. Java is OO alright, but this is more a convenience than a fundamental driving factor behind it, it just makes developers job easier, hopefully, which of course isn't bad per se, but we hoped for more, much more. And we did NOT look forward to, 15 years later, spending two weeks of dragging the non-objects out of COM, just to support bl**dy Drag-and-Drop!

/Karlsson
Not being 100% objective here

A minimal MySQL ODBC application

I got the question on how small a packaged application with a built-in MySQL server could be. I had an idea that it could be made very small, but I didn't have any exact numbers. The platform was Windows, so I decided to try to build an application like this, to test it.
I had a few requirements:

• A minimal amount of registry settings.
• I didn't want to spend too much time on the application itself, so this should be simple.
• I wanted to have a full blow installation package, i.e. install application and then run it, so no servers to be started or something like that. Just install and run.
• No assumptions about the Windows PC where this was installed (I actually in the end made one such simplification. I decided to use a fix MySQL port number).
• Installer was InnoSetup, because I like it and I know it well.
  

So, I started building a simple dialog based Windows application, using ODBC. Took me very short time, a few hours at the most (202 lines of code, plus a dialog resource). The next step was the ODBC thingy. ODBC really insist that something is installed, at least that Windows knows where an ODBC driver is (but a DSN isn't required. And with some tricks you can get around even having a known driver). The good things was that I can call the driver anything I like, so I choose the name of my application (MiniODBC). I then needed to have my installation program to tell ODBC about this, and this is done with a registry setting:
HKLM\Software\ODBC\ODBCINST.INI\MiniODBC and then set the value Driver to the full path to the ODBC driver (which is part of my application, and is placed in the application directory. This is perfectly OK).

OK, so now for the most important part of this, the MySQL server? What do I need. Note that this is for a minimal setup! I need the server itself of course, mysqld.exe. Then I need a language file, errmsg.sys from an appropriate language directory. Then I need some database files, or rather, to make things really small, I could have ignored this and have the application create the single table I needed, and have MySQL create the InnoDB data and logfiles (yes, I'm using InnoDB). To simplify a bit, I decided to ship with a small default InnoDB file though (10M). I realize this is a large part of what is distributed, but I know it can be fixed, so I leave it at this for now. I also included mysqladmin.exe, so I can shut down the server nicely from the application.

I also need a my.ini. Except for the usual options, I include skip_grant_tables so I don't have to ship with all the usual MySQL host, db, user etc. tables. I used one database only, with one table, so ond db.opt file and one .frm file, and that's it.

As far as the installation goes, this was made really simple, I just copied the files, and the set one registry setting, the ODBC one mentioned above, and tweaked the my.ini file to match the installation directory. So what is missing? Starting the MySQL server maybe you say? I decided to do this in the application. No big deal really, and as I put the MySQL server and .ini file in the same directory as the application. An excerpt of the code looks like this:
char szMysqld[MAX_PATH];
static char szMysqlAdmin[MAX_PATH];
szMyIni[MAX_PATH + 80];
char szFile[MAX_PATH];
size_t i;

switch(nMsg)
{
case WM_INITDIALOG:
/* Get the directory where we are right now. */
GetModuleFileName(NULL, szFile, sizeof(szFile));
for(i = strlen(szFile); szFile[i] != '\\'; i--)
;
szFile[i + 1] = '\0';
strcpy(szMysqld, szFile);
strcat(szMysqld, "mysqld.exe");
strcpy(szMysqlAdmin, szFile);
strcat(szMysqlAdmin, "mysqladmin.exe");
strcpy(szMyIni, "--defaults-file=\"");
strcat(szMyIni, szFile);
strcat(szMyIni, "my.ini\"");

/* Start MySQL. */
ShellExecute(hDlg, NULL, szMysqld, szMyIni, NULL, SW_HIDE);

And yes, I'm aware that this isn't 100% optimal, but it works as an example (no UNICODE string support, limited overflow checking etc).

In conclusion, having built an InnoSetup package with this content, the installation package took up a bit more than 2Mb compressed (and this could have been reduced if I hadn't shipped the InnoDB files). After installation, I ended up with an installation directory with 13 files (14 after the first run: the MySQL .err file) taking up about 25 Mb of disk space, more than half of this being data itself.

If you want a copy of the application and source (well, without the source, this is a pretty limited application, to say the least), drop me an email and I'll fix that for you.

/Karlsson
Who has to do some Linux work soon, I have done much too much Window stuff lately

On Open Source Project and comercializing them

I got to think about the commercialization of Open Source software project happen these days. Many of them have been acquired by larger companies, or are about to, and I think a discussion here is important. By which I in no way think I have all the answers, quite the opposite, this post is more about raising questions, and maybe propose a few possible answers.

So, do we really need commercialization of the project? And if you ask me (but I am a Sales Engineer after all, so what do you expect from me), I think the answer must be yes. And then the question is how this is best done. I think we can agree that Open Source, as a development model is just great, but to keep it going, something more is needed. And there are reasons why we want commercialization of these projects. Among them, in my mind, are:

• A commercial entity is needed for many things related to the project, such as partnerships, contacts, employees etc.
• Some things are best done on a commercial basis. Certain things that are part of development of a larger project, things where it is just too hard to find Open Source volunteers to work on the project. And there are such aspects, such as Q&A, docs etc.
• And last but not least, long term stability. In the world of large legacy enterprise installations, things are different than in your average webshop or more modern enterprises, such as Google, Yahoo etc. There just isn't competence enough to drive development of important software projects yourself. You just cannot expect them to take up continued development of some Open Source project, if the project should fail or the main developers leave the project or something. In that instance, someone must pick up where the Open Source project left off, and continue development, at least for existing paying users.

So, how do we go ahead with this then? Most successful Open Source project of some size has some kind of commercial sponsors anyway. But we don't want these to "kidnap" the project anyway. Not because they are evil, but because it just happens. In the case of MySQL for example, it did happen to an extent. Largely, the whole development of MySQL ended up with the MySQL company. The effects if this may be debated, but it did happen, this is no secret. And we are trying to fix this, but it is not that easy. There are some reasons for this, like the dual-licensing model, but I think that could have been fixed I think, if it had been considered earlier on.

My idea for a solution to this dilemma is to let the project have two different entities drive it, the Open Source Company and the Project. These two are closely linked, but are still separate. The different responsibilities should be clear, as should any licensing issues. If we look at Red Hat versus Fedora, I think this is pretty good model. One might debate that RHEL is a bit slow in reacting to new features and developments, and Fedora too fast, but these are details.

Commercial Open Source is what we need a model for. And in my mind then, this model should not be there at the cost of the Open Source Project itself. No, they should vitalize each other, but still have different goal and end users, largely. And no, I do not for one minute believe that a reasonable large Open Source Project could survive just by adding tech support and PS to an existing project, and live from that. I am a strong believer in the fact that the commercial Linux vendors, like Red Hat, SuSE etc has done the Linux a much bigger favour than you think. The reason is that having a commercial vendor behind something like Linux, means that many more dare trying it and using it. You think everyone can use and learn Linux? And anyone can support it? Well, if you have a system that is at the core of your business or is critical to what you do in general, then you have to ensure long term stability. And I mean long term. Asking a question on a Linux form doesn't help in the long run. You might get an answer today, but what about tomorrow? And buying tech-support from some Local Linux expert is fine, but what about long terms? No, we need commercial entities here, just like we need the Open Source projects themselves. And I think that we should be looking for a model where these two can live side-by-side, not in competition, but adding value to each other. I think RedHat has done a good job here. I think MySQL is improving a lot here, but I this still need to be discussed.

/Karlsson
Getting philosophical