Brief chat with a buddy about MySQL index size limitation

(2:44:19 PM) Buddy: so many hacks in mysql :) like, you can't use functions as a default value for a column unless that function is called CURRENT_TIMESTAMP which is a single exception
(2:44:59 PM) Me: ug :)
(2:45:59 PM) Buddy: index lengths are a major limit..I've been creating columns with the value of unhex(sha1(value)) and creating my unique indexes on that
(2:46:57 PM) Me: what's that expression?
(2:47:11 PM) Me: i think i have an idea... :)
(2:47:19 PM) Buddy: returns a 20 byte binary sha1 hash
(2:47:25 PM) Me: yeah..bleh :)
(2:48:59 PM) Me: i guess that's just for enforcing the uniqueness? Or would you look up by that too? (thus you have to remember to call unhex(...)) :)
(2:49:33 PM) Buddy: if I wanted to lookup using that index, then it would be lookup too
(2:49:47 PM) Me: yeah
(2:49:59 PM) Buddy: so my queries are like select value from table where valuesha1=unhex(sha1(thingIwant));
(2:50:14 PM) Me: bleh :)
(2:54:09 PM) Buddy: as long as your index is less then 767 bytes yer ok :)
(2:54:32 PM) Me: what are you indexing on that's bigger? you might have told me before...
(2:54:57 PM) Buddy: URLs :)
(2:55:00 PM) Me: ahh

:eye roll:

Oracle Universal Installer can't be run in a directory with spaces in the name (Unix), or else it bombs! This is apparently due to lack of quotes in the runInstaller script, where a local variable is being set to pwd.

Once again...

Once again I'm thwarted slowed down by a database limitation that, to me, seems silly and arbitrary.

4000 character limit in SSIS

A blogger's "Five Months With MySQL Cluster"

"We discovered early on with cluster that we would have to redesign our application. Our DB was highly relational. Almost no data could be put on the site without data from other tables. We used a lot of joins. We learned (later) that joins in the cluster are not a good idea. Neither are sub-selects."
[runs away]

To be fair, he did then say: "So, we wrote some proof of concept scripts for our application. We were very happy. Very few issues were found. Nothing anywhere near show stopping."

I'm just not too keen on working around those little things called foreign keys, joins and such.

http://doughboy.wordpress.com/2007/06/07/five-months-with-mysql-cluster/

Yikes...

NOTE: This post is ancient, no longer applicable, and should be ignored. I say this because somehow I see this post still being accessed!

I am evaluating database clustering solutions. At the moment I am reading the MySQL documentation. I'm very skeptical but hey, I want to give it a fair chance.

The more I read though, the scarier it looks. Here are some of the "unsupported or missing features":

# Foreign key constraints. The foreign key construct is ignored, just as it is in MyISAM tables.
# Savepoints and rollbacks. Savepoints and rollbacks to savepoints are ignored as in MyISAM.
# Durability of commits. Commits are replicated, but there is no guarantee that logs are flushed to disk on commit.

http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations-unsupported-missing.html

On other pages we have such gems as:

# Online adding or dropping of data nodes is not currently possible. In such cases, the entire cluster must be restarted.

But they also claim MySQL gives you "99.999%" uptime, or "five 9s" uptime. How they arrived at this number is beyond me, especially if you can't add nodes (what if one machine dies and you need to replace it?).

Like I said...yikes.