I was asked recently to confirm a consistent state of data in a non-transactional MySQL table after a failing statement updating multiple rows did not complete successfully.

Hmmm, this is what I did.

• Created a MEMORY table
• Populated with some data, and a Primary Key
• Updated the Primary Key so that it failed with a Duplicate Key Error after updating only half the rows
• Confirmed that the rows that were updated, were, and the rows that were not updated, were not

DROP TABLE IF EXISTS mem1;
CREATE TABLE mem1(
i1  INT UNSIGNED NOT NULL PRIMARY KEY,
c1 CHAR(10) NOT NULL,
dt TIMESTAMP)
ENGINE=MEMORY;

INSERT INTO mem1(i1,c1) VALUES (1,'a'), (2,'b'), (3,'c'), (4,'d'), (5,'e');
SELECT * FROM mem1;
+----+----+---------------------+
| i1 | c1 | dt                  |
+----+----+---------------------+
|  1 | a  | 2007-06-14 17:26:29 |
|  2 | b  | 2007-06-14 17:26:29 |
|  3 | c  | 2007-06-14 …

Damien Seguy had an interesting observation on my use of self joins for generating rolling sums yesterday:

What about using a user variable to make the rolling sum from the beginning? Something simple enough like this :

set @sum := 159;
// to start with the original number of post, or 0 if you prefer.
set @mn := 0;
// for the month number
select @mn := @mn + 1 as MonthNumber Month, Added, @sum := @sum + Added as RunningTotal from ResultListing1;

Damien, of course, is correct, that I could have used a user variable like the above to generate the rolling sum, instead of using a self join. However, there are some complications. First, let's look at what happens if I rewrite Lenz' original GROUP BY statement to include a user variable to calculate running totals:

Finally! Some MySQL ActionScript drivers. We have a couple choices right now, so bookmark the one you like - asSQL, Asql.

I don’t claim to be a security expert, but I’d like opinions from people in the field, as well as database experts that view security highly. Here are some opinions from a discussion with Chad and Lenz a while ago. What do you consider a security hole, that warrants immediate action or a release of a server within a sensible timeframe?

• Remotely exploiting MySQL without login credentials
• Remotely crashing MySQL without login credentials

The above two are definite problems. What about:

• denial of service attacks
• data loss
• data changes
• data insertion

Chad tells us, “security is policy enforcement.” And the policy should state: “the service should always be available to authorized people, never to …

In the first article in this series on archiving strategies for online transaction processing (OLTP) database servers, I covered some basics: why to archive, and what to consider when gathering requirements for the archived data itself. This article is more technical. I want to help you understand how to choose which rows are archivable, and how to deal with complex data relationships and dependencies. In that context, I'll also discuss a few concrete archiving strategies, their strengths and shortcomings, and how they can satisfy your requirements, especially requirements for data consistency, which as you will see is one of the most difficult problems in archiving.

Let’s say when you have to run a batch process monthly, you can survive with times like 10 minutes. I can imagine a lot of seasoned DBAs right now ROFL about my insignificant 10 minutes. The point here is I was developing this process and some test cases, so my usual trial/error methodology doesn’t scale very well with 10 minute offsets.

So I borrowed an idea from a colleague: why not moving all the database to memory? It’s not so big and I have 2G of ram. But, could I change all tables (~20) to MEMORY in one line or so?

Since this was a Ruby on Rails project, I used the rails console to be able to mix SQL and Ruby. My first try:

conn.tables.each do |t|
  conn.execute "ALTER TABLE #{t} ENGINE=MEMORY"
end

First error: foreign …

MySQL AB, developer of the world's most popular open source database, today announced that Zimbra is using the MySQL database to deploy millions of email accounts as part of its rapidly growing open source messaging and collaboration system.

MySQL Connector/Net 5.1.2 a new version of the all-managed .NET driver for MySQL has been released.

Connector/Net 5.1 represents a change in how we package our products. Until now, we've shipped our core provider and the Visual Studio integration bits as separate downloads. This has become a bit of a problem. Often we would fix a bug that involved changing code both in the VS package and in the core provider. This provided a versioning problem where users would need to upgrade both products to see the benefit of the bug fix. To solve this, we've decided to discontinue Tools for Visual Studio as a separate product and have, instead, integrated it into a new Connector/Net installer. We hope this provides a better "out of box" experience for our users.

All previous versions of Tools for Visual Studio should be uninstalled prior to installing this product.

Version 5.1.2 works with all versions of MySQL …

With our current focus on the "12 Days of Scale Out" I thought I'd mention some observations from a recent visit to China.  I was there mostly on vacation, but I was able to spend a couple of days on the front end in some meetings with customers and press in Beijing through our partner there, Great Open Source.

The highlight of my visit was meetings with Sina.com (the largest portal in China) and Baidu.com (the largest search engine company).  Both are large scale users of MySQL with literally hundreds of MySQL servers at the core of their operations.

These companies have to deal with the same challenges as their US or European counterparts, but …

This article goes along similar lines to the piece I did on the security of MySQL. I came across another blog that asked about programming tips that really should not be followed in the real world. I thought it might be interesting to get some ideas of what areas of MySQL or database theory that is taught as "religion" at University or even in starting positions that just don't work in the real world in some situations.

I will get the ball rolling by adding a few points that are often drummed into people, but don't work in all situations.

read more