When setting up a query using a temporary lookup table, I got this error:

ERROR 1137 (HY000): Can't reopen table: 'tmp_journals'

It transpires that since 4.1 the way MySQL handles temporary tables has changed. This affects joins, unions and subqueries. There is an obvious fix:

mysql> CREATE TEMPORARY TABLE tmp_journals_2 LIKE tmp_journals;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tmp_journals_2 SELECT * FROM tmp_journals;
Query OK, 3228659 rows affected (2.01 sec)
Records: 3228659  Duplicates: 0  Warnings: 0 

Then the query is easy:

SELECT COUNT(1) cnt, journal_invoice_ref
FROM tmp_journals
GROUP BY journal_date 
HAVING cnt > 10000</pre>

UNION

SELECT COUNT(1) cnt, journal_invoice_ref
FROM tmp_journals_2
GROUP BY journal_invoice_ref
HAVING cnt < 10

Recently Alexandr announced new Percona-XtraDB-9.1 release, and now it is good time to summarize features we have and what is expected soon.

This release contains long waited features from 5.0:

• extended slow.log
• USER/TABLE/INDEX/CLIENT_STATISTICS + THREAD_STATISTICS ( coming in release-10)

Extended slow.log is now even more extended, there is additional information for each query:

PLAIN TEXT CODE:

1. # Bytes_sent: 4973  Tmp_tables: 1  Tmp_disk_tables: 1  Tmp_table_sizes: 7808

That is you can see how many bytes was returned by query, was temporary table used,
was it disk table or in-memory, and how big was temporary table.

Also you can profile each individual …

Here's a very interesting article for those of us who work with big data: Data, data everywhere in the Economist.  One interesting quote predicts the emergence of a new database professional: the data scientist: "Chief information officers (CIOs) have become somewhat more prominent in the executive suite, and a new kind of professional has emerged, the data scientist, who combines the skills of software programmer, statistician and storyteller/artist to extract the nuggets of gold hiddenRead More...

It is now one year since a few colleagues and I left Sun to start our own company, Monty Program Ab (after which more have joined). A lot has changed during the year. For instance we ended up producing a full fork of MySQL rather than focusing on the Maria engine as I planned a year ago.

This February we released the first stable version of MariaDB, version 5.1.42, which is our enhanced and backwards compatible MySQL branch/fork. You can download it from the askmonty.org website. Please test it out and comment upon it here or on Launchpad, the code host for the …

Today I pushed up the initial patch which adds XA support to Drizzle’s transaction log. So, to give myself a bit of a rest from coding, I’m going to blog a bit about the transaction log and show off some of its features.

WARNING: Please keep in mind that the transaction log module in Drizzle is under heavy development and should not be used in production environments. That said, I’d love to get as much feedback as possible on it, and if you feel like throwing some heavy data at it, that would be awesome What is the Transaction Log?

Simply put, the transaction log is a record of every modification to the state of the server’s data. It is similar to MySQL’s binlog, with some substantial differences:

• The transaction log is composed of …

..kind of cool.

Here is a good use case for compressed MyISAM:
We have a rather large database that doesn't get updated very frequently.
We have several slave servers that have this database, but because the database is big, they have a bit of a hard time querying it.

Disk I/O is a big issue for us and compressing the data, offloads some of that onto the CPU for decompression. It also allows the system cache to keep more of the tables in memory.

This database only gets updated every few weeks and it's pretty much a manual process.
To update the slave servers, we simply copy the MyISAM tables over to them (yes, you can do that) and because they are compressed, they are easier to send over the network.

Unlike Archive tables, compressed MyISAM can have indexes (Archive tables now allows 1 index) so you can use them for fast querying.

Compressed MyISAM …

On an entirely unrelated note to the MySQL protocol discussions happening yesterday, the MySQL protocol is now the default protocol in Drizzle as of Monday’s tarball (3/15). Drizzle supports a limited version of the MySQL protocol, only supporting the subset of commands Drizzle cares about (no server-side prepared statements, replication, or deprecated commands due to SQL query equivalents). Not all MySQL clients have been fully tested with it, but our entire test suite is using it now with the libdrizzle MySQL implementation. The latest release of libdrizzle also includes defaulting to the MySQL protocol and port for Drizzle …

Just a quick bit of news to let you all know that additions to the standard Kontrollbase and Kontrollkit userguides are being halted while we migrate the documentation to a new wiki system run by the very nice Trac software. You will be able to access the Kontrollbase and Kontrollkit documentation at http://wiki.kontrollsoft.com when it [...]

I have been talking about this for a while, now at last I have found the time to get started! Below is a picture from my 2008 MySQL User Conference presentation. It illustrates how engine level replication works, and also shows how this can be ramped up to provide a multi-master HA setup.


What I now have running is the first phase: asynchronous replication, in a master/slave configuration. The way it works is simple. For every slave in the configuration the master PBXT engine starts a thread which reads the transaction log, and transfers modifications to a thread which applies the changes to PBXT tables on the slave.

Where to get it

I have pushed the changes that do this trick to PBXT 2.0 on Launchpad. The branch to try out is …

I use it in Oracle and notice there are 10 days missed, for example:

ORCL> select to_date('4/10/1582','dd/mm/yyyy') SHOW_DATE from dual

SHOW_DATE -------------- 04/10/1582 ORCL> select to_date('4/10/1582','dd/mm/yyyy') + 1 SHOW_DATE from dual

SHOW_DATE -------------- 15/10/1582 Say What? the date after 4/10/1582 is 15/10/1582.

But in MySQL i try it but i didn't see this case, example:

mysql