Don’t forget to send in your suggestions for new quizzes!

This quiz originated on FreeNode#mysql, where someone asked how to count the number of newline-separated “fields” in a TEXT column. For the purposes of the quiz, I’ve changed the idea slightly but you should be able to appreciate the usefulness of this method for any x-separated data that you have to deal with.

Given the following data…

mysql> SELECT * FROM t;
+-----------+
| s         |
+-----------+
| aba       |
| abacad    |
| abacadaea |
+-----------+

…create a query which counts the number of occurrences of the character ‘a’ in each line, e.g.

mysql> SELECT s, <something> AS count_a
    -> FROM t;
+-----------+---------+
| s         | count_a |
+-----------+---------+
| aba       |       2 |
| abacad    |       3 |
| abacadaea |       5 |
+-----------+---------+

[hint]Hint: You need …

I was in London last week for Seedcamp, and spent the weekend in Riga for the MySQL Developer's Meeting (I was there presenting a Drizzle presentation).

I am supposed to be in Phoenix right now.

Where am I?

Minneapolis.

This story begins on Sunday late night when I find myself sick and not able to fly. So I delay my Monday morning flight till Tuesday (I am supposed to be doing a keynote tomorrow for Astricon at 9AM).

Sick in this context is nice fever and a good pounding headache.

So Tuesday morning I get up in Riga and make the flight to Amsterdam. What do I discover? The ticketing agent reticketed for the next day but did so with a paper ticket.

I obviously lack a paper ticket.

I get the agent in Amsterdam to reissue the paper ticket as an e-ticket. She hands me the boarding pass and then mentions to me "you have twenty …

I was in London last week for Seedcamp, and spent the weekend in Riga for the MySQL Developer's Meeting (I was there presenting a Drizzle presentation).

I am supposed to be in Phoenix right now.

Where am I?

Minneapolis.

This story begins on Sunday late night when I find myself sick and not able to fly. So I delay my Monday morning flight till Tuesday (I am supposed to be doing a keynote tomorrow for Astricon at 9AM).

Sick in this context is nice fever and a good pounding headache.

So Tuesday morning I get up in Riga and make the flight to Amsterdam. What do I discover? The ticketing agent reticketed for the next day but did so with a paper ticket.

I obviously lack a paper ticket.

I get the agent in Amsterdam to reissue the paper ticket as an e-ticket. She hands me the boarding pass and then mentions to me "you have twenty …

Why you do not use GRANT ALL ON *.*?

I was with a client today, and after rebooting a MySQL 5.0.22 instance cleanly with /etc/init.d/mysqld service, I observed the following error, because you always check the log file after starting MySQL.

080923 16:16:24  InnoDB: Started; log sequence number 0 406173600
080923 16:16:24 [Note] /usr/libexec/mysqld: ready for connections.
Version: ‘5.0.22-log’  socket: ‘/var/lib/mysql/mysql.sock’  port: 3306  Source distribution
080923 16:16:24 [ERROR] /usr/libexec/mysqld: Table ‘./schema_name/table_name’ is marked as crashed and should be repaired
080923 16:16:24 [Warning] Checking table:   ‘./schema_name/table_name’

Now, I’d just added to the /etc/my.cnf a number of settings including:

myisam_recovery=FORCE,BACKUP

which explains the last line of the log file. When attempting to connect to the server via the mysql client I got …

I don’t think I have heard anything from the Falcon team for a while. What’s new? Did the project really stall when Jim Starkey left, as Vadim Tkachenko wondered might happen?

One technique that I use to understand MySQL Internals (and ZFS internals in the past) is to look at bug reports. They have lots of useful information in them.

One example is Bug#32149 Long semaphore wait for adaptive hash latch. The bug synopsis could very well have been Why are global locks in a storage engine such a BAD idea. There is lots of useful information about this and the workaround MySQL server uses to work with storage engines that do.. Interesting!

Do you know of any other interesting bugs that document MySQL internals?

It was announced last week that Sun is investing in Infobright to fuel the growth of the new www.infobright.org open source community and the Infobright Community Edition (ICE) open source data warehouse.  The announcement from Infobright can be found here.  The open source data warehouse from Infobright is based on MySQL.  See more press here.

To learn more about data warehousing with Infobright and MySQL, look at this article done by MySQL Product Management.

…

Thank you very much for the great reactions and contributions we received since we put our first alpha build out last week. We have incorporated patches submitted by the community and further improved some code (even though we’re currently busy sitting together at our Database Group Developer Meeting). So we’ve prepared the next packages and put them on our server. We also updated (cleaned up) the build instructions as to remove a few dependencies which are no longer necessary with the latest code. This should make building the source and running the binaries more easy.

MySQL Workbench OSS 5.1.2 Alpha - Linux

Source Tar Ball

ftp://ftp.mysql.com/pub/mysql/download/gui-tools/mysql-workbench-5.1.2-alpha-linux.tar.gz
…

With open source software I can install reasonably complete software and try it with my data. This way I get to see how it works in a realistic setting without having to rely on benchmarks and hoping they are a good match for my environment. And I get to do this without having to deal with commercial software sales people.

So I glad to hear the Infobright had gone open source as I have been wanting test a column based database for a while. I was even happier that it was a MySQL based engine as I would already know many of the commands. I decided to run some of the same tests I had run when comparing InnoDB and MyISAM for reporting (http://dbscience.blogspot.com/2008/08/innodb-suitability-for-reporting.html ).  InnoDB performed better than MyISAM in my reporting tests so I’m going to compare Infobright to InnoDB.

The …

Hi5 is one of the world's largest social networks, with over 56 million monthly visitors. It's a company that demands maximum scale and performance from its infrastructure.

As such, it's no surprise that Hi5 recently opted to go with PostgreSQL as supported by EnterpriseDB.

PostgreSQL? Isn't that an open-source database? It can handle that load?

Indeed.

Hi5 runs hundreds of PostgreSQL servers in one of the world's largest commercial OLTP PostgreSQL installations. All Hi5 subscriber data, including user profiles, metadata associated with user photos, and comments, is stored on the company's PostgreSQL databases...In June 2008, the PostgreSQL-based system delivered more than 18.5 billion page views, serving nearly 11 million visitors to the …