7. MySQL Installation

Main Points

• Planning: MySQL Gameplan Planning how to implement a functional MySQL architecture for Earth Chronicle.
• MySQL Installation: Initial Attempt Basic walk through of the first attempt to install the three MySQL components.
• Completing MySQL Installation Focusing on installing the database and creating a working MySQL instance.
• GUI Tools: Testing MySQL OK, the database instance seems to be up, can I test it with the GUI Tools?

The purpose of this section is to get started with a MySQL database; something I've taken classes in, but have never done with Earth Chronicle before. I've used Access databases freely (with all their problems), because they'll function (technically), and I can easily copy the Access .mdb files up to the production server. I can use MS SQL Server 2005, .mdf, files on my dev box; it's just as easy to design and edit tables in VWD with them, as Access databases. MS SQL Server databases have the major advantage that they're a viable web database... just not on my hosting account where it's not supported.

Enter the need for MySQL. My hosting account Does have a MySQL database available, and they're very powerful. Up to this point, I've never tried to run MySQL on my dev box; there is a reason I'm not a DBA or a Server Admin, I hate that stuff. I deal with it when I have to, and it's become clear that if I'm going to do database-driven web projects that I want to do... now, I have to. Therefore, I'm going to try to install a development instance of MySQL and see if I can do serious development with it. Then we'll see if I can push it to production and what tradeoffs I may have to live with.

Planning: MySQL Gameplan

MySQL Installation on Windows is a three part process. So far as I know, there's nothing that says you "have" to do all three parts. However, in the downloads section of http://www.mysql.com/, I identified three important components. In the extensive troubleshooting and research I've done since then, I've read about many other people and their installations. In every case, they've all installed the same three components. So while you can choose to set up your system as you please, this three piece MySQL architecture seems to be a .NET standard.

1. You need to download the database. As of this writing, it's called MySQL Community Server; I'm installing version 5.1. This was a little confusing to me; I don't want to set up a server, much less one for a community, however, this must be a DBA's idea of a good name for MySQL's primary database. Rest assured that whatever its name, this is it. There is even an option to optimize it for your development machine, I suppose the database instance runs in "lite" mode which plays nicely with all the other programs on your computer.
2. You need to download the GUI Tools, I'm installing version 5.0. Specifically, MySQL Administrator is critical, though the other tools in the package are handy as well. MySQL Administrator allows you to create and manage users, configure the database, and create and edit tables through a GUI that's "similar" to Visual Studio's database design and creation tools. I can also create backups, a .sql file, that I can upload to my production server to keep the two databases in sync. This is a feature Microsoft does not include in their free Express software for MS SQL Server. There's also a query browser to run input queries and review and edit the output, as well as a tool to migrate databases to MySQL.
3. You need a connector. Since I'm working in ASP.NET, my preferred connector is the .NET Connector, version 6.1.2, though there's also a more generic ODBC connector available; I downloaded version 5.1.6. The ODBC connector is platform independent, but in my case, it's a precaution that I hope not to install. All the MySQL examples for working with ASP.NET assume you're using the .NET connector, and the code samples for the .NET connector are much more relevant to the code I write and am familiar with. But if the .NET connector won't run, I could use the ODBC connector as a possible fall back position.

Here are the requirements for this project...

• I need the same code to work in production and on the dev box. If it doesn't, that would be a deal breaker. While file-based databases (.mdb & .mdf) have their limitations, they are extremely portable, and support this goal. Now, the web.config files will naturally have to be customized to connect to "their" MySQL instance, but if the .cs files require modified formats, I would have to jettison MySQL.
• I need to be able to use backups to restore either database. For example, I need to be able to backup the database on the dev box and use it to "restore" the production server database. This would allow me to "copy" the database from dev to production and vice versa, just like I copy the website back and forth. If I can't, that would be a deal breaker.
• I need to be able to set up a functional MySQL database on both the dev box and in production.
• I need the .NET connector, or more accurately the code I write with its classes, to serve as the bridge between the database and the .NET application. There may be alternatives if the .NET connector doesn't work, I could attempt an ODBC connector, or something else, but that's distinctly plan B.
• I need to be able design and edit database schema as easily as possible. I'm hoping the MySQL GUI tools are as functional and powerful as the tools that come with Visual Web Developer.

This is a huge project, but otherwise I can only run Access databases in both production and on my dev box. They have very limited application for the kind of programming that I would like to do... and would have liked to have done a long time ago. Therefore, I'm going to attack these questions and see if I can get... close enough that MySQL will prove servicable.

MySQL Installation: Initial Attempt

OK, so let's get started downloading each of these components from the downloads section of http://www.mysql.com/; then we can start installing and configuring software. The downloads were sufficiently easy that you shouldn't need a walk through and details; in addition downloading, installation, and configuration instructions are notoriously subject to change. Just grab the most recent generally available versions, and when in doubt, accept the default settings unless you have a reason not to.

Installing MySQL Database Server

The MySQL wizard offers a Typical setting, but it wants to install to my overflowing C: drive. Therefore, I selected Custom, accepted the default installation options, but installed to the D: drive. Next it wants to run the Configuration.

1. The Configuration wizard recommends Detailed Configuration. A standard option is available, but the questions I'm asked during "Detailed" make sense to me as a programmer, so that's how I completed configuration.
2. I'm asked for my server type. There are settings for shared servers where the DB will be "live" but sharing space, and dedicated servers where the DB will Be the server. However, the default is a "Developer Machine" option which will basically run "MySQL lite" it sounds like. I chose this.
3. Next I'm asked for my database usage, multifunctional, transactional, etc. Not being sure what my DB usage is, multifunctional is selected and sounds the most generic so I accept this setting. It's optimizing two different kinds of database engines, something called InnoDB for fast transactions and MyISAM for high speed storage.
4. MySQL wants to know where the InnoDB files should be kept. I route them to my largest drive.
5. I'm asked for the number of concurrent connections, so I accept the default setting, Decision Support (OLAP) is for 20 connections or less. That sounds quite safe to me.
6. I'm now asked for my TCP/IP and SQL mode preferences. I'm not sure what TCP/IP I should use for my dev box, so I'm leaving it on the default settings; the TCP/IP box is checked and set to port 3306. The SQL mode has the Strict mode checkbox checked. Sounds good to me.
7. Here's another biggie, it wants to know what character set to use for data storage. Latin1 is the default, but I'm selecting UTF8 instead. It offers better support for multilingual sites, and I want to build towards that goal. A true multi-lingual site stores all text in the database with a language identifier and selects text for the page in the language the user specifies. Running the database in UTF8, is therefore critical to me, or I won't have the necessary character encodings to store those languages on the database.
8. I'm asked if I want to include MySQL as a Windows service, it recommends checking this box, naming the service MySQL and launching the server automatically. I accept all these defaults. It does not recommend adding a path reference to make it easier to access the DB from a DOS command prompt, and I leave the checkbox blank.
9. This screen asks me for password information. That makes me a little nervous. It's tempting to change this to an anonymous account, but that's not how it's going to run on the production server unless I'm very much mistaken. Therefore, I'm saving the password to a secure location and setting it as inaccessible from a remote computer.
10. Finally I click the execute button.
11. It throws an error when it tries to start the service. My dev box is running Windows 2000, so this may be the problem. I therefore backed up and unchecked the option for the Windows service, and I do check the command prompt option in case this is my only available action. Now I'm not offered the password screen. However, the Start service option is greyed out and the process completes.

Installing the .NET Connector

1. I start up the MySQL connector for ASP.NET, and choose custom configuration. Once inside I don't actually change the defaults, though it's nice to see the features it has. I'm especially interested to see what classes are available and to try out the Visual Studio integration; I'm using Visual Web Developer (VWD), so I don't even know if it will work for sure.
2. It installs the files and runs through various options. At last, I click finish.
3. I will have to see if VWD has the new database listed in its database explorer or server explorer when I next open it up. I also need to see if there's some way to copy the database so that I can push it up live. If not, I will probably have to go back to the MySQL website and download MySQL Admin. However, so far so good.

OK, I've installed everything, but the entire system behaves as if I've done nothing. VWD hasn't changed anything, or added new features, a MySQL option is not available anywhere in the Database Explorer and Add new Connection. Nor can I find anything in the installed MySQL documentation that's even relevant, much less useful. I'm going to skip installing the GUI tools for now, I need to get this sorted out first.

Completing MySQL Installation

The next step is to hit the internet. The MySQL Forum lists a case with my exact issue. Loading MySQL 5.1 on a Windows 2000 SP4 system, and receiving the error "Cannot Create Windows Service for MySQL. Error:0". The guru who responded says he's had this error frequently when trying to do a normal install from the .msi files on his PC. He therefore provides the code for a .bat file which I modified and tried to run. However, when triggered in this fashion, the .msi file for MySQL 5.1 doesn't actually install any files. Everything else in the .bat file runs fine and the .NET connector and MySQL GUI tools install beautifully. However, the MySQL server itself installs no files, only directories, and so the MySQL instance can't be created. I'm pretty confused. I hate this kind of configuration crap; this is exactly why I don't enjoy DB and server admin.

Part of the problem may be the MySQL is terribly messy. It throws files and folders in a number of odd locations and doesn't pick up after itself when you uninstall. I therefore don't know if the problem now is the fault of the .msi or if it believes the program is already installed because I haven't been able to locate one of these pieces of post-uninstall trash. Most of the locations are specified now in my .bat file. However, one of the sneaky locations may have been lying around since the very first time I tried to install this, C:\Documents and Settings\All Users\Application Data\MySQL\. Now that I've gotten that one, let me try running the .bat again. :( No luck. I've now burned 24 hours just trying to install this database. No wonder Microsoft guys use MS SQL even though it's nowhere near as good. Getting MySQL set up is turning out to be a pain in the A$$. If I had any other options right now, I would have trashed this right about now. *sigh*

I went back to the MySQL boards and did some more research. There's an odd page in the documentation in a chapter about something completely different; but it describes certain error messages that may be thrown after installation including "Cannot Create Windows Service for MySQL. Error:0" It says the Windows service already exists, and recommends deleting the service.

OK, I'm having problems just deleting the service. The procedure in the online MySQL documentation is out of date, and the link to Microsoft's website is defunct as well. No help there. I therefore Googled windows services and I've at least located the Services dialog. It's located under Start>Settings>Control Panel>Adminstrative Tools>Services. So all the command line stuff was just DBA preference, there's a perfectly decent GUI for this that no one has mentioned. However, once I get there, the supposed root of the problem, the fact that MYSQL is already registered, turns out to be incorrect. There's no such service installed. I sweep back through all the "spots" where MySQL leaves trash, and I've now confirmed as well as I can, that there can be no other pieces running around my computer. This includes removing the .NET connector, etc., etc. My computer is as MySQL-less as I will ever get it.

I'm ditching the .bat file, and I'm trying the .msi installer manually. I'm tempted to try Typical, however, that forces it to install everything on my tiny (and already maxed out) C: drive. Therefore, I'm running through the exact same process I tried the first time above, including configuring the system mostly for defaults by specifying Developer Machine, using UTF8, and installing as a Windows service with passwords. Cross your fingers. I'll know in about five minutes. You'll know in the next paragraph.

OK, I'm not completely certain what this means yet, however, when I press execute as the final step of the configuration wizard, it runs through each item on the checklist successfully - including start service, and apply security settings. When completed, I receive the these messages...

When I open Start>Settings>Control Panel>Adminstrative Tools>Services, I now see a new "MySQL" service running. It's status is "Started" and "Startup Type" is automatic, so hopefully when I boot up my computer from now on, MySQL will just be there. The sneaky data folder, C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data, is back and has about 30MB of stuff packed in it. I also couldn't get the .NET Connector which has my ADO.NET providers for MySQL to install anywhere except the C: drive. It supposedly is about 20MB. If those 50MB are really the only parts installed on the C: drive, I can live with that.

It turns out that indeed the MySQL instance was working beautifully at this point. By making sure no MySQL pieces were lying around and running the .msi installer, things went right. MySQL was installed and configured, an instance of the database was created, and the Windows service automatically starts when the computer boots up. So far as I'm concerned MySQL now runs transparently on my dev box and I don't have to worry about it. That's wonderful.

[chroniclemaster1, 2009/11/20]

I'm still chicken though, I wish there were some way to do a quick test just of the DB. The only way I know to check it is to create a connection in VWD. Now the GUI tools ought to do this as well. It may be time to install them and see what my options are. Since the .NET Connector still doesn't seem to be working properly in VWD, let's take a crack at the GUI tools next.

It turns out that the connector was fine. The problem is VWD. Visual studio offers a number of services to support 3rd party plugins like the integrated tools from MySQL. However, Microsoft decided that VWD shouldn't support plugins. So unless you've spent several hundred dollars (or more) on Microsoft development software, you won't get integrated development tools. This basically means you need to set up the connection in the web.config file manually (there are plenty of examples on the MySQL forums). After that, the GUI Tools are turning out to be far better and more user friendly than anything Microsoft has available.

[chroniclemaster1, 2009/11/20]

GUI Tools: Testing MySQL

Back when I took my first classes in PHP, we used a program called phpMyAdmin to manage the MySQL database. Today, MySQL has released it's own admin tool, MySQL Administrator, along with a query browser that allows you to see your result sets before programming them into a stored procedure. MySQL Administrator is critical for me, since this is how my hosting company says I can move data between my dev box and the production server; however, right now I just want to see if the database has really installed correctly... finally... not that I don't trust it. ;) Once I test that out, then I can take another crack at the .NET connector.

Installing MySQL GUI Tools

1. First of all I get a splash screen that says the GUI tools are not Open Source. They're copyright protected. Wow, the .NET Connector and database are open source.
2. *Rolling Eyes* OK, it's copyrighted, I get it. Now I have to accept the copyrighted license agreement.
3. I respecify the installation directory to the same MySQL folder on my D: drive where the database is installed. I now have "MySQL Server 5.1" and "MySQL Tools 5.0" folders side by side.
4. I choose custom setup, though after seeing all the options, I leave the defaults alone. There are a group of components common to all the applications, and the three tools: MySQL Administrator for managing my MySQL database, a query browser, and a tool to migrate other databases to MySQL. It's only 30MB total on the D: drive, so I might as well have them handy. I'd only save 3-5MB getting rid of any one of the three, anyway.
5. The installation finished up nicely, so let's see what we've got.

:D OK, now that the GUI tools are installed, my Start>Programs has a MySQL option. It includes a MySQL System Tray Monitor. When it comes up in the system tray, I right click and it says "MySQL - Running". This may be the happiest thing I've seen all day. Between this and the MySQL entry in Services, I'm pretty sure that things are finally running properly. Next, I'll try creating a connection in VWD. Just to be on the safe side, I'm going to restart the computer. Hopefully, that will fix my VWD issues, and also give the .NET Connector a chance to be detected by both VWD and the system. See you in a couple minutes.

All right, we're restarted and I've checked "Services" which started automatically as I hoped. I had to start "MySQL System Tray Monitor" manually, but once it was up it says "MySQL - Running" as before. I'm now starting VWD, but when I go to the Change Data Source dialog, there is no ".NET Framework Provider for MySQL" option, either explicitly or cataloged under <other>.

Since I've hit a brick wall, I'll open up MySQL Administrator to check things out. I'm immediately presented with an ominous connect to database dialog box. Between the inability of the connector to give me anything in VWD and the fact that I've no idea what settings to use here, it's time to hit the internet again. OK, I found an answer to the MySQL Administrator connection. Using Server Host: "localhost" and the default Port: "3306" (which I did not touch during setup or here), I'm able to login using the default Username: "root" and the default password "mysql". I've confirmed that my database instance is configured to use UTF8, and I've even set up a new user "AspNet" for my ASP.NET applications to use. No guarantees that I've done it correctly. The GUI Tools are as simple as they seem and setting up new users and controlling fine-grained access rights is point and click easy. [chroniclemaster1, 2009/11/20]

I used the GUI Query Browser to run some queries against the default tables with character set information. Besides UTF8, they have several multi-byte options; as you would expect, all of them are East Asian languages, different Chinese, Japanese and Korean character sets. All the pieces of the GUI tools seem to be working. Better, they confirm that the database is running on my dev box, and I can even run queries against it. Let's call testing on the database instance and the GUI tools complete. :D

Now I just need to nail down the .NET Connector and actually build a webpage that can access my database. In the process of doing more research, there's a possibility written up vaguely in a couple places that plug-ins can't be installed to Visual Web Developer. That's not definitive, but I can't rule out the possibility based on the tests I've done. If true I would need to program manually against the .NET Connector without the aid of VWD integration tools. That will be our next test; if successful, then VWD just doesn't support the plug-ins and if not, something else is going on. So check out our work with the ASP.NET connector and MySQL test application. I've also found that my Start>Programs>MySQL menu from the now includes a folder for the connector which allows me to access the help files, something I'd not been able to do before. Hopefully that will help to build a .NET application that displays MySQL data. As noted above, all the integrated tools really did was provide the connection string, which I got off the internet, and you can get it off the next page. I'm sure they also let you use VWD for table creation and editing, but I already like MySQL Administrator better. [chroniclemaster1, 2009/11/20]