SELECT OUTFILE should be able to write to a popen pipe. And it should be able to write to a URL. And it should be able to do XML, JSON, YAML, and the various known database dump formats, and various known script pickling and serialization formats.

LOAD DATA INFILE should be able to read from an popen pipe. And it should be able to read from a URL. And it should be able to understand XML, JSON, YAML, and the various known database dump formats, and various known script pickling and serialization formats.

In the MySQL command line client, it should be able to source a URL on the command line. Inside the MySQL command line client, it should be able to source from a URL and from a popen, and tee to a URL and a popen.

It's been an interesting experience. Most of my classmates are from a rather different pool of "computer geek" than either the west coast "open source gods", or from big corporate IT. Most of them are from very small IT/dev shops. There are two guys from a Texan WISP. There is a guy who's the sole IT guy from an out of control academic dev project that is about to centralize all their random Oracle, Access, and MS-SQL databases into MySQL. There is the IT guy from a company that makes venetian blinds. The IT guy from a company that manufactures farm trailers and horse trailers. There is a semifreelance investigative reporter who wants to use MySQL for storing and sharing collected government data. There is an academic from a bio/gen research dept who's moving to MySQL.

As for the class, going in, I was afraid it was going to be a lot of memorization of configuration parameters, deep explanations of the query optimizer, and lots of very …

A

This morning I was looking for something on Ohloh and realized I should “stack” maatkit. Since I couldn’t find “maatkit” nor “mysql toolkit” in Ohloh, I created a new project for it at:

http://www.ohloh.net/projects/10083

If you’re on Ohloh, stack it!

www.ohloh.net is a neat social networking tool for open source software. Instead of searching freeware lists, search ohloh, and you can find reviews, # of people using the software, and direct links to download pages. My profile is at:

http://www.ohloh.net/accounts/8446http://www.ohloh.net/accounts/8446
and you can see my stack at:
http://www.ohloh.net/accounts/8446/stacks/default

This is the last day I'm taking off work to hack on mk-table-sync, and I thought it was time for (yet another) progress report. Here's what I have done so far. (Click through to the full article to read the details).

One of the major parts of my job is/was developing the interface to our MIS that handles hundreds of connections on a nightly basis from other MIS systems which push data in a CSV format. These CSV files contain information that then needs to be pushed into our MIS (thousands of inserts and updates, plus some deletes).

Now, creating the insert statements are a breeze with the "insert into tbl0(...) values(...),(...),..." MySQL syntax. I can push thousands of inserts in a single query, the only limit being the MySQL packet size, hence the 'ish' in the title. But what about updates? before now I've resorted to just running the updates as single queries, which lets face it, is slow. So lets look at how we can speed this up.


The Meat

First create a test table:

create table test0(sid int, staffid varchar(255), name varchar(255), unique(sid, staffid));


…

A

Since few days I was debugging a case from Yahoo! Japan team (thanks to Akira Kitada) where the query runs fine in about 0 secs with the 5.1.16 build where as the same query takes almost close to 2 minutes with the latest 5.1.22 release.

One can simply identify that this is a optimizer issue as it is not picking the right index. Lets consider the following two tables urls and urls_categories…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

| urls  | CREATE TABLE `urls` (
  `url_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `url` VARBINARY(2000) NOT NULL,
  `url_status` …

… I didn’t get two-way sync done, and I didn’t get the Nibble algorithm done. That much I expected. But I also didn’t get the current work released tonight because I’m paranoid about breaking things. I’m trying to go through all the tools and write at least a basic test for them to be sure they can do the simplest “unit of work” (such as mk-find running and printing out that it finds the mysql.