There’s a change of behaviour in MySQL 5.1.31 for Row Based Replication, if you have InnoDB transactions that also write to a MyISAM (or other non-transactional engine) table. It’s a side effect of fixing Bug #40116. Take this simple example:

Transaction 1: INSERT INTO myisam_tbl (item, val) VALUES (1, 0);
Transaction 1: INSERT INTO innodb_tbl (item, val) VALUES (1, -1), (2, -1);
Transaction 1: START TRANSACTION;
Transaction 1: UPDATE myisam_tbl SET val=val+1 WHERE item=1;
Transaction 1: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=1;
Transaction 2: START TRANSACTION;
Transaction 2: UPDATE myisam_tbl SET val=val+1 WHERE item=1;
Transaction 2: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=2;
Transaction 2: COMMIT;
Transaction 1: COMMIT;

After this, the Master innodb_tbl would look like this:

If you are using InnoDB Hot Backup and a recent version of mysqld (at least 5.0.67 or higher, including 5.1.30, though it may be later versions), your backup will run fine and output OK! at the end, as it should.

Except for one thing.

The binary log file and position do not appear in their rightful place. Here’s a snippet of the output from the backup:

innobackup: MySQL binlog position: filename 'Warning', position (Code 1287):
'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
090210 03:55:04  innobackup: innobackup completed OK!

That’s pretty misleading — looks like the backup completed OK, but it did not show us the binary log position. What about the ibbackup_binlog_info file?

[mysql@db3:~] more ibbackup_binlog_info
Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
[mysql@db3:~]

Yikes! What happened to the …

Because I was asked….What should I set my my.cnf parameters to? What are good default values? How much memory should I allocate to the db if I have X amount of ram? What is a good starting point for the mysql config files?

You’re not really Googling for a my.cnf to use are you? You probably are, Shame on you!  The best thing to do is to test before you make changes, and find the best configuration for your application.  But your not going to are you? oh well I might as well accommodate you. I make no claims these will work for everyone. In fact if you hire me later on I may look at you funny after I analyze your system, and may call you funny names behind your back for using the wrong settings.  Because there are a lot of people out their who are only database folks 5 minutes at a time (that’s what these posts are about), they are probably going to stick the my-huge.cnf and go with that anyways.  So why not throw …

I vaguely recall a couple of blog posts recently asking something like "what's the formula to compute mysqld's worst-case maximum memory usage?" Various formulas are in wide use, but none of them is fully correct. Here's why: you can't write an equation for it.

The most popular equation, of course, is the one in the error log after a crash: "It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections". This was never true -- not even when ISAM and friends were the only storage engines, which was before most of us remember. And at a minimum, most servers these days need to consider the added memory for InnoDB: specifically, the buffer pool. (There's also an innodb_additional_mem_pool_size but it's typically set pretty small, like 20M).

But even that's not enough. Staying with InnoDB, we also need to count the data dictionary. This can take many gigabytes on servers with a lot …

I’ve been wanting to try something with libdrizzle since I added the server protocol interface. After an hour or two of hacking, I came up with:

lap> sqlite3 farm
SQLite version 3.5.9
sqlite> CREATE TABLE animals (
   ...>   name  VARCHAR(255) NOT NULL,
   ...>   sound VARCHAR(255));
sqlite> INSERT INTO animals VALUES ("Cow", "Moo");
sqlite> SELECT * FROM animals;
Cow|Moo
sqlite>

Next, using a new program I hacked together in the libdrizzle examples directory:

./sqlite_server -m -v farm 54321

Now, in another terminal:

lap> mysql -h 127.0.0.1 -u root -P 54321
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: libdrizzle+SQLite

mysql> INSERT INTO animals VALUES ("Sheep", "Baa");
Query OK, 0 rows affected (0.14 sec)

mysql> SELECT * FROM animals;
+-------+-------+
| name  | …

Have you prepared yourself? mysql> select 1234567890 - unix_timestamp(); +-------------------------------+ | 1234567890 - unix_timestamp() | +-------------------------------+ | 25091 | +-------------------------------+ Not much longer now!

Recently, a lot of new non-relational databases have cropped up both inside and outside the cloud. One key message this sends is, "if you want vast, on-demand scalability, you need a non-relational database".

If that is true, then is this a sign that the once mighty relational database finally has a chink in its armor? Is this a sign that relational databases have had their day and will decline over time? In this post, we'll look at the current trend of moving away from relational databases in certain situations and what this means for the future of the relational database.[more]

This is #6 in a series of blog postings about MySQL Performance Schema.

A timer is a mechanism that gives some idea of how long an event takes. A platform is a CPU chip (or multiple chips) and an operating system. Different platforms have different timers available. MySQL searches for the best timers, picks one as the default, and allows users to change the default.

The platform I’m using while writing this is a single-CPU x86 1.6GHz laptop with Linux 2.6.18. I can find out what timers there are by looking at PERFORMANCE_TIMERS.

mysql> SELECT * FROM performance_schema.performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE       |      1596965174 |                1 |              8 |
| NANOSECOND  |      1000000000 |                1 |           1345 …

Hi,

We are pleased to announce MONyog 3.0 - a major new release. MONyog is rapidly becoming a cost-effective alternative to expensive proprietary tools for MySQL monitoring.

Unlike databases like SQL Server & Oracle, MySQL does not provide powerful APIs to gather extensive profiling information about each and every query. MySQL developers and DBAs have largely used on a combination of the following techniques to find problem SQL:

1. SHOW PROCESSLIST
2. Slow Query Log and General Query Log
3. SHOW PROFILES (available from MySQL Community version 5.0.37)
4. Capturing and filtering tcpdump output

To overcome the shortcomings of profiling features in the MySQL server, MySQL Proxy was introduced which allows developers to route queries via MySQL Proxy and then extract profiling information from MySQL Proxy. (It also provides other features like load balancing, query rewriting, …

Introduction

Clickjacking is a malicious technique of tricking web users into revealing confidential information or taking control of their computer while clicking on seemingly innocuous web pages. A vulnerability across a variety of browsers and platforms, a clickjacking takes the form of embedded code or script that can execute without the user's knowledge, such as clicking on a button that appears to perform another function (credit: Wikipedia).

Clickjacking is hard to combat. From a technical standpoint, the attack is executed using a combination of CSS and iFrames, which are both harmless web technologies, and relies mostly on tricking users by means of social engineering. Additionally, the only server side technique against …