Funniest thing I’ve seen all day. Found it here. It’s a post on how to study MySQL internals. Yes, it has come to this.
Browsing Posts in MySQL
I started working on a project today that uses the MySQL relational database to save data on the back-end. One of the packages available for Fedora 10, my current desktop OS, is the mysql-gui-tools package. This includes the MySQL Administrator and MySQL Query Browser programs. I had those already installed, but after a few minutes I knew that the Query Browser just wasn’t going to work for me. I kept trying to copy queries into the little window, and errors were getting into it somewhow. And even if that weren’t happening, I couldn’t get it to resize and not being able to look at more than a couple lines at a time was driving me nuts. So I hopped over to OTN and grabbed Oracle SQL Developer. SQL Developer was built with Oracle in mind but it can connect to a variety of database systems.
I’ve been doing some MySQL database work today. I’ve got to confess that I find working with MySQL to be more than a bit painful. I really think it is unfortunate that it became so popular and is now everywhere. I really rather work with PostgreSQL. But what is, is – so I’ve finally decided to just dig in. After about 10 minutes the first thing I had to do was ditch the MySQL Query Browser. I switched over to Oracle SQL Developer – which is not open but is free as in beer.
Once I get my database squared away, which is taking longer than I expected, I’ll be working on a php project with the CodeIgniter framework. I should document all the steps. Maybe today I’ll do screen shots on setting up SQL Developer for MySQL. It aint rocket science, but might be useful down the road.
Anyone who looks at the stuff I tagged to delicious yesterday will see that I ran into some articles on MySQL replication. It all started when I stumbled across an article on setting up a 4 node master-master set up on debian. I knew that Master-Slave was available for MySQL – but not master-master. So I did some quick digging and saved all those links for later, since I don’t have time just right now to really dig in.
That type of load balancing and fault tolerance can get very, very expensive in the commercial database world. I’m extremely interested to see just how it works, what the drawbacks are, etc. Of course now I’m going to need to dig up some hardware to build a cluster. That will have to come first. After that – I’ll let you know how it goes.
Via slashdot – a Register article about the LSST (Large Synoptic Survey Telescope) that will be built in Chile and send data to the U.S. How much data? They are thinking it will be around 30 Terabytes a night.
What caught my eye and quite frankly surprised me, was the last paragraph
The data reduction pipelines are developed in C++ and Python. They rely on approximately 30 off-the-shelf middleware packages/libraries for parallel processing, data persistence and retrieval, data transfer, visualization, operations management and control, and security. The current design is based on MySQL layered on a parallel, fault-tolerant file system.
Today one of the guys on my team helped me create a job that uses SSIS to move data from Oracle to MySQL. Here is the funny part. The Oracle tables only really had 3 data types. Varchar2, Date and Number. When I created the tables on the MySQL side I did Varchar2 -> Varchar, Date -> Datetime and Number -> Decimal. The job didn’t like the varchar stuff. It balked at what it viewed as going from a unicode type to a non unicode type.
Well you can build a good old DTS package inside an SSIS package- and DTS is not so finicky. So that’s what we did.
I created 2 tables in MySQL for each table that I would push over. One is ‘live’ and the other temp. (There were 4 in Oracle, so 8 total). The job truncates the temp table, fills it, then renames the live table to a different name, renames the temp table as the live table and then renames what was the live table to the temp table for the next go around.
Eventually, the owner of the MySQL database intends to move it to Oracle. Then this will go away, but for now it was really a quick and easy solution. It took us a couple hours but we had never done it before. (My partner had done other SSIS packages pulling from Oracle but not going to MySQL – his stuff all goes to SQL Server)
I’ve found Howto Forge to be a helpful resource. I was perusing their forums and someone had asked for a tutorial on getting MySQL talking to Microsoft Access. This seemed like something I could do, so I started working on it yesterday.
First I wanted to install MySQL on my Fedora machine. After a quick “yum install mysql” I found out that I had already done so when I installed Fedora. I’ve never really used MySQL much – but I saw that gui tools were available, but I didn’t have them. “yum install mysql-gui-tools” fixed that in roughly two minutes. Then with a little googling, I was able to get connected to MySQL and create a user, etc. That took maybe 20 minutes, as I’d never done it before, and had to figure a couple things out. (The whole thing about MySQL being easier than PostgreSQL is bunk by the way.) Once I had the user set up, I created a table and put a couple rows in it.
Then I moved over to my XP machine (XP Pro with all current patches, service packs, etc.), downloaded the MySQL odbc driver and installed it. I fired up the Data Sources manager dealy and set out to create a DSN. I’ve done quite a bit of that over the years, so it didn’t take any time at all. I hit the ‘test’ button and it connected succesfully. Yeah.
Then I opened Access 2003, created a database and went through the steps to create a linked table – but the odbc connection failed. This was the beginning of the longest, most frustrating part of the whole thing. The odbc driver was working – but not from MS Access. I googled around and found some posts that said the issue was msjet40.dll. But those posts were quite old and installing the version that they said would work, did not solve the issue. I kept digging but wasn’t having any luck and then I found a user comment below this mysql article on access denied errors. They said that
Microsoft Access 2000/2002 Jet/ODBC database engine uses default anonymous logins by default, and you should tweak the following registry key so that the ODBC DSN’s options were used, instead:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC]
“TryJetAuth”=dword:00000000
So I fired up regedit, found that key and switched it. My connection still failed but now it failed from the odbc driver setup as well. When I looked at the connection in Access – it showed the port number as 0. The driver was supposed to provide a default, but it looks like that wasn’t happening. It turned out that I needed to explicitly put in the port number and the character set. (This was tricky. I read the default character set was CP 1252 – but there was no CP 1252 available in the list on the driver. So I just started trying them until CP 1250 worked.) Today, if I have the time, I’ll see if I can connect the other way – from MySQL to Access. If I get it all working both ways, then I’ll put into how to form and submit it to Howto Forge.
