We had a question in #mysql on freenode yesterday, asking if there was a function to strip digits from a string. The answer is of course - not natively.

I’ve been playing around with Functions and Stored Procedures a bit lately though, trying to familiarise myself with the MySQL implementation fully, and wrote this quick function which does the job, although only in 5.0:

DELIMITER //
 
DROP FUNCTION strip_digits //
 
CREATE FUNCTION strip_digits (str VARCHAR(50))
  RETURNS VARCHAR(50)
  BEGIN
    DECLARE sub_start INT DEFAULT 0;
    DECLARE res VARCHAR(50) DEFAULT '';
    
    WHILE sub_start < LENGTH(str)+1 DO
    
     IF SUBSTRING(str,sub_start,1) REGEXP '[[:alpha:]]' THEN
…

We had a question in #mysql on freenode yesterday, asking if there was a function to strip digits from a string. The answer is of course - not natively.

I’ve been playing around with Functions and Stored Procedures a bit lately though, trying to familiarise myself with the MySQL implementation fully, and wrote this quick function which does the job, although only in 5.0:

DELIMITER //

DROP FUNCTION strip_digits //

CREATE FUNCTION strip_digits (str VARCHAR(50))
RETURNS VARCHAR(50)
BEGIN
DECLARE sub_start INT DEFAULT 0;
DECLARE res VARCHAR(50) DEFAULT '';

WHILE sub_start < LENGTH(str)+1 DO

IF SUBSTRING(str,sub_start,1) REGEXP '[[:alpha:]]' THEN
SET res = CONCAT(res,SUBSTRING(str,sub_start,1));
END IF;

SET sub_start = sub_start + 1;

END WHILE;
RETURN res;
END;
//

Here’s a couple …

Having more thoughts on code as of late. I have been doing research into sparse indexes since I believe that is the answer to one issue I have with long term storage of data in the archive engine. AKA you have years worth of data and you only want a month's view at a time. I don't want to use a BTREE, since it would grow to being fairly large, so the answer is to just allow someone to set intervals
and allow a jump point to an approximate row. The push down condition code that was added in 5.0 should make this fairly trivial to do.

On a different note I updated mod_index_rss to support RSS2 and enclosures. What is cool about this is that my automagic tools for grabbing files from rss2 feeds will now work with anyone who is publishing files with mod_index_rss.

So what does mod_index_rss do in the first place? It just takes …

Zimbra, previously known as Liquid Systems, is starting to emerge from stealth mode.  Last week at LinuxWorld in San Francisco, they exhibited in the MySQL booth (along with other LAMP based applications and partners) showing off their new open source email solution.  Maybe open source email seems like old hat to some folks, but Microsoft Exchange has a lock on IT managers around the world, and Zimbra is fully exchange compatible. It has its own rich web client and is also fully compatible with Outlook as well as any POP3 or IMAP email client. 

What that means is IT managers can start using Zimbra in conjunction with their existing exchange servers and get the full benefit of email and calendaring at a fraction of the price of Microsoft's closed source …

I am not a huge fan of Java (though I have come to like it) and certainly not a big believer in hype, so it has taken me some time to investigate Lucene.

Once upon a time I needed to search the details of approximately 2-3 million “documents” (call it 2GB of data), serving up the results many times a second. MSSQL’s fulltext search was simply laughable with this relatively small load; many queries would not even return at all (unless one wished to wait for hours, and one did not), so MySQL was tried instead.

While it was a lot better than MSSQL’s, this is unfortunately much like observing that having a concrete block slammed repeatedly into your face is a lot better than being dead. True but useless. Neither of them scaled very well, so growth prospects were dim.

So today (years afterward), on a whim, I decided to try Lucene in this environment and see what it could do. I very crudely indexed …

A few days ago I was contacted by a user that was getting table full errors. I explained that by default MySQL uses 4 byte pointers for row data and if the data file grows larger than 4 bytes MySQL will run out of space for pointers. I also explained about max_rows and avg_row_length and how they can be used with alter or create table to give himself larger row pointers. He took this information and happily altered his table expecting all of his data to be there and MySQL to begin adding new rows. What he got was a new table with only a few hundred rows of data. He pinged me in a panick asking what he did wrong. Having never seen such a thing myself I logged into the box. I checked mysqld.err and found that for the previous 10 days or so mysqld had been writing corrupt index warnings to the log. Fortunately he had a slave that we could recover the data from.

After some research the best explanation I can offer as to what went wrong is that …

Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table.

By marc

While I was reading the final version of Agile Web Development with Rails today, I noticed that one of the pages, page 53, looked completely different than it had when I'd read the first "Beta Book" version of it (back when it was page 51). Three footnotes had crept up onto the page, two of them after the line, "That wasnÂ’t hard now, was it?" Apparently it was harder than the authors had originally thought. One of the footnotes has a special warning for people running Mac OS X 10.4; another addresses a MySQL compatibility issue; and a third warns about an error you might get if you were still running an example from earlier in the book. I went back and looked at the earlier versions of the book, and the first beta copy had no footnotes; …

In order to better document MySQL on Mac, I have recently acquired a Mac Mini. This brings my collection of desktop machines to three:

The black machine is a Windows box, the white machine runs Linux, and now a Mac Mini.

The new addition closer up:

This is the first Mac I have had regular use of, so I am sure I have some learning to do. For now I just need to get MySQL and the GUI tools installed, then find where they hide the command-line.