I was discussing how to avoid surprising users and someone pointed out that what seems intuitive and rational to one person is often complete insanity for others. The mental gap between a developer and a user can often be a chasm far too wide to cross. Of all the bug reports I’ve filed against MySQL, here is my all-time favorite:

select * from t where a >= 1.0order by a;

Does not cause an error. I believe it should, because there should be a whitespace before ORDER BY.

Similar syntax errors such as “select 1e0from dual” were also accepted as valid SQL. Much soul-searching later, the official reply from MySQL’s development team:

The server behaves properly here:
- “1″ alone can not be an identifier, because it’s followed by a “.”
- therefore, the lexer parses “1″ as the beginning of a …

There is not magic solution to count table contents especially if you have a client that has a table with 40+ million records and you need to do the filtering using the 'OR' SQL statement.

Original query was the following:

SELECT COUNT(*) from table1 WHERE field1 IN ('val1','val2') OR field2 IN ('val3','val4');

First benchmark before doing optimization showed me results after 4 minutes.

My

Most of the time, when people say “scalability” they mean any of dozens of things. Most of the time, when I say it I mean exactly one precisely defined thing. However, I don’t claim that’s the only correct use of “scalability.” There is another, in particular, that I think is very important to understand: the inherent limitations of the system. This second one doesn’t have a single mathematical definition, but it’s vital nonetheless.

I’ll frame the discussion by asking this: how scalable is your database?

Using the two definitions I like to use the most, I answer the question in this way.

1. Scalability in terms of the Universal Scalability Law is the degree to which you can add more workers (or units of hardware) and get equal returns in terms of system throughput.
2. Scalability in terms of inherent …

I’m really looking forward to this year’s Percona Live MySQL Conference. This is always THE event of the year for me in the MySQL conference circuit. It’s also the first year I haven’t been a speaker! I’ve been a speaker since 2007 but this year things were too uncertain for me to submit a proposal in time.

As usual, the real highlight of the conference is seeing and talking to everyone. Technical sessions are also great, but honestly I can usually study up on technical things without going to a conference. However, nothing can replace the benefit of meeting all the dedicated MySQL community members in the hallways and at meals, and talking to MySQL-related businesses in the expo hall. Year after year, this conference has been what makes things happen: technical innovations, product launches, business initiatives, career changes, you name it — …

Teradata results. Funding for DataXu. The chemistry of data. And more.

For 451 Research clients: Oracle launches major update to MySQL open source database bit.ly/TSONAt

— Matt Aslett (@maslett) February 8, 2013

For 451 clients: Analyzing the chemistry of data bit.ly/TSOV2R By @451wendy Treating sensitive data like dangerous chemicals

— Matt Aslett (@maslett) February 8, 2013

Teradata: Q4 net income $112m on revenue up 10% to $740m, FY net income $419m on revenue up 13% to $2.7bn. bit.ly/14FNS8L (PDF)

— Matt …

When you enable binlogs in the my.cnf file you can either set the log-bin flag to true, or you can set it to a path and file name prefix such as this:

[mysqld]
log-bin=/path/to/binlogs/mysql-binlog

This changes the default location where binlogs are stored.  The problem is that when you connect to mysql there is currently no way to query the server to find out if that path has been changed, and what it currently is.  This means you can't be sure where any server's binlogs are actually stored.
Ok, so they're not really missing, but it's a known issue that mysql doesn't make them easy to find.  The server obviously knows the path internally, but it doesn't make this information available. Bug #35231 has been open on this issue since 2008 and is currently being ignored.

Why?
This is such a trivial change …

Extract from THE SCALE-OUT BLOG by Robert Hodges (CEO, Continuent)http://scale-out-blog.blogspot.com Data management is undergoing a revolution. Many businesses now depend on data sets that vastly exceed the capacity of DBMS servers. Applications operate 24x7 in complex cloud environments using small and relatively unreliable VMs. Managers need to act on new information from those systems in

I wonder if the MySQL archaeologists out there would be willing to unearth some (presumably ancient) history for me. Why does the logging configuration merit special mention in the version_comment variable?

The more I think about this, the more bizarre it seems. I enabled logging. The version reported by the server changed. No, really, is my server somehow a different version of MySQL now?

130203 15:39:55 [Note] ./bin/mysqld: ready for connections.
Version: '5.6.7-rc-log'

I assume there’s a good story behind this somewhere. I’m thinking a priest, a rabbi, and Monty walk into a bar, and black vodka is probably involved at some point too :-)

Somebody ran into a problem after reading about the MySQL CREATE statement and the AUTO_INCREMENT option. They couldn’t get a CREATE statement to work with an AUTO_INCREMENT value other than the default of 1. The problem was they were using this incorrect syntax:

CREATE TABLE elvira
( elvira_id    int unsigned PRIMARY KEY AUTO_INCREMENT=1001
, movie_title  varchar(60))
  ENGINE=InnoDB
  CHARSET=utf8;

It raises this error:

      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1001
, movie_title …

I wrote pt-archiver for jobs like deleting or archiving rows from a big table in small chunks. These days, that’s the kind of task I like doing inside the database, and Shlomi’s magical common_schema feels a lot more suited for this than an external Perl script.

When I say it’s magical, it really does feel magical. It’s amazing how he’s created an entire expressive scripting language that runs in MySQL and feels just right for the job.

Right now I’m watching this kind of stuff scroll by in my terminal:

+---------------------+
| rows_deleted_so_far |
+---------------------+
|             2871119 |
+---------------------+
1 row in set (7 min 42.67 sec)

+---------------------+
| rows_deleted_so_far |
+---------------------+
|             2872119 |
+---------------------+
1 row in set (7 min 42.75 sec)

Notice that the execution …