In two separate instances, prepared statements used up all my database's connections and completely locked it. To the point that we had to restart the mysql server and in the other case, physically reset the server (not something you usually do with linux servers).
Apparently, one table had a read lock on it, which made the prepared statement wait a bit too long and it decided to flood the database connections till it would got an answer. Only the table was still locked and the database came to a screeching halt. The last thing I saw on my screen was a bunch of the same prepared statement on my process list screen, completely taking up all the slots and then.... I lost my connection to see the process list.
I don't really understand how the perl script and php page that ran the prepared statement could be so aggressive or where was it defined to continue opening new connections till they got an answer.

This is definatly …

I’ve reposted the slides from my Spockproxy talk (Spockproxy is a Sharding only version of the MySQL proxy).  Since I’ve have to move this web site it’s been some effort to move all of the files into their new homes.

These slides are in a variety of formats and have loads of great information if you’re considering a sharding solution – even if that is not Spockproxy.  Of course once you see how easy it is you’ll put it on your short list.

The slides are available at http://www.frankf.us/projects/spockproxy/  If you want to hear the talk you’ll have to invite me.

Spockproxy is a custom version of the MySQL Proxy that is optimized (dedicated) to range based sharding.  This is not done with Lua so it is perhaps less flexible but if you are trying to do sharding it is one of the easiest solutions to set up – and will require minimal, if any, changes to your application code.

Is is available as an open source down load at Sourceforge.

Topics for this podcast:

* Open source getting, and going without VC investment
* Oracle contributions to Linux and open source
* Sun’s latest moves with MySQL database and version 5.1
* Linux in high-end computing

iTunes or direct download (25:50, 6.0 MB)

How would one concatenate strings from a column (multiple rows) into a single row using MySQL? I see its possible with MS SQL Server 2005 and above. Any incite into how to achieve this in MySQL would be much appreciated.

MS SQL Server 2005 - Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT Web_Account_ID,
GroupNameConcat = REPLACE(
        (
        SELECT
                Web_Account_Group_Name_ID AS [DATA()]
        FROM
                tblWebAccountGroup WAG
        WHERE
                WAG.Web_Account_ID = WA.Web_Account_ID
        ORDER BY
                Web_Account_Group_Name_ID
            FOR XML PATH ('')
        ), ' ', ',')
FROM tblWebAccounts WA
ORDER BY Web_Account_ID

Query Results Example

Source: …

Hi all,

Today I'd like to continue a blog series in which I highlight Web application tutorials for NetBeans 6.5. A few changes have been made to tutorials, among which is the featuring of MySQL as the database of choice.

This sixth entry in the series will cover the tutorial, "Performing Inserts, Updates, and Deletes (CRUD)".

This tutorial shows you how to use NetBeans IDE 6.5 and JSF 1.2 (Woodstock) components to build a web application that can create, retrieve, update, and delete database rows. The application provides a drop-down list of master data along with a synchronized detail table. Users of the application can add to, update, and delete the records in the detail table and from its associated database.

MySQL is the database used in this version of the tutorial.

This is the …

Hi all,

Today I'd like to continue a blog series in which I highlight Web application tutorials for NetBeans 6.5. A few changes have been made to tutorials, among which is the featuring of MySQL as the database of choice.

This fifth entry in the series will cover the tutorial, "Building a Tree From Database Data".

This tutorial shows you how to dynamically build a tree structure from data in a database. Using NetBeans IDE 6.5, you build a two-page application, the first page of which includes a JSF 1.2 (Woodstock) Tree component. You populate the first-level nodes in the Tree with names from a database, and the second-level nodes with the trips for that person. The trip nodes are links to a second page, which displays the details for that trip.

MySQL is the database used in this version of the tutorial.

Hi all,

Today I'd like to continue a blog series in which I highlight Web application tutorials for NetBeans 6.5. A few changes have been made to tutorials, among which is the featuring of MySQL as the database of choice.

This fourth entry in the series will cover the tutorial, "Using Databound Components to Access a Database".

In this tutorial, you use NetBeans IDE 6.5 to create and deploy a web application that displays master-detail data from a database that is packaged with the IDE. In the application, you select a person from a JSF 1.2 (Woodstock) drop-down list, and the application displays a table that shows all the trip records for that person.

MySQL is the database used in this version of the tutorial.

A new look to an existing tutorial. Be sure to check this one out.

…

Hi all,

Today I'd like to continue a blog series in which I highlight Web application tutorials for NetBeans 6.5. A few changes have been made to tutorials, among which is the featuring of MySQL as the database of choice.

This third entry in the series will cover the tutorial, "Developing a Visual Web JSF Application".

In this tutorial, you use the NetBeans IDE and JSF 1.2 (Woodstock) components to create and run a simple web application, Hello Web. The example application asks you to input a name and then displays a message that uses that name. At first, you implement this page with an input field. Then you replace the input field with a drop-down list from which the user can choose a name. The drop-down list is populated with names from a database table.

MySQL is the database used in this version of the …

I have written a new book on SQL DML. This is a total beginner book: how to commit and rollback, how to query, how to add data, etc.

Probably not of interest to most of the people who read this blog but if you know of anyone completely new to SQL, this would make a great Christmas present. Only 14.95. It is completely vendor agnostic, although the examples all use Oracle and MySQL.

You can view the Table Of Contents, Preface and Index here. I plan to release some of the chapters for free on the blog and will make the PDF of the book available at a discount. I have several more books like this (DDL, Intro to Relational Databases and Cloud Computing) under construction. I also plan to do some …