The PHP replication and load balancing plugin has a configuration setting to run all transactions on the master. But how to detect the start of a transaction? I got a hint to look at a flag in the client-server protocol which tells us, if we are in a transaction. I was not aware of the flag and, I loved the idea when I heard it. The plugin wouldn’t have to worry about how the client starts a transaction. A PHP client can start a transaction by turning off autocommit via API (e.g. mysqli_autocommit()) or executing SQL (BEGIN, START TRANSACTION, SET AUTOCOMMIT=0). It is time consuming to catch them all, if possible at all.

Server status flag from the protocol

I hacked mysqlnd to print …

The PHP replication and load balancing plugin has a configuration setting to run all transactions on the master. But how to detect the start of a transaction? I got a hint to look at a flag in the client-server protocol which tells us, if we are in a transaction. I was not aware of the flag and, I loved the idea when I heard it. The plugin wouldn’t have to worry about how the client starts a transaction. A PHP client can start a transaction by turning off autocommit via API (e.g. mysqli_autocommit()) or executing SQL (BEGIN, START TRANSACTION, SET AUTOCOMMIT=0). It is time consuming to catch them all, if possible at all.

Server status flag from the protocol

I hacked mysqlnd to print …

You might have faced circumstances wherein you require transferring your MySQL databases to some other SQL Server. In such situation, it is necessary to create a database dump, which would comprise the structure of database tables and probably a list of SQL statements. This dump can be used for backup purposes in any distressing event of database corruption or inaccessibility. In MySQL, a database is dumped using the 'mysqldump' client. Sometimes you face problems while performing this operation. This unusual behavior is normally caused due to database corruption. As a consequence, you might lose access to your precious database components. To overcome this problem, you need to go for MySQL recovery through a reliable third-party utility.

For instance, when you attempt to dump one of your important MySQL databases, you may receive the following error message on your screen:

“[root@www diaendomet]# mysqldump …

Social media is buzzing with the news and views of bloggers. From the database side of this glorious media, we have picked out some of the gems for you in this edition of Log Buffer, which is the Log Buffer #223.

Oracle:

Jonathan Lewis was presenting the keynote at the Turkish Oracle User Group conference and he blogs about a discussion over there.

A new Myth arrives Index Rebuilds and Nial litchfield has an interesting take on it.

Why is my SYSAUX Tablespace so Big? …

We all know that a megabyte in binary system is not the same as one million bytes (in decimal system). But have you actually cared much about it? I have to admit I haven't. I know there is a small rounding error, but by and large I always treated 2^10 = 1 kB = 1024 bytes and 10^3 = 1 kB = 1000 as the same thing. (Update: Opening sentence was edited to remove units MB and MiB since it seems even I managed to use them backwards! The math in this article is correct. The rest of the article uses MB, GB and TB mostly to refer to binary magnitudes, which is apparently incorrect. See comments for wikipedia links and discussion.)

More importantly, when you move into larger numbers, rounding errors usually become even less important. Unfortunately, in this case they become bigger:

read more

One of item I always look at SHOW ENGINE INNODB STATUS to see if there are any transactions spending very long time in ACTIVE state. In the perfect world if you’re running online system you should not see transactions spending more than couple of seconds in ACTIVE state. Especially ACTIVE transactions which do not currently run any query are suspicious. There are however cases when screaming fire about ACTIVE transactions alone would be misleading. There is a whole set of applications which run quite fine while having ACTIVE measured in hours. It is JAVA applications which often run in AUTOCOMMIT=0 mode and do not explicitly commit transactions unless there were any writes. If database is configured in READ-COMMITTED transaction mode it is actually fine from performance point of view as Innodb does not have to preserve row versions going back to start of transactions.

There is however a better metric, which in most cases will …

Dolphin shirts available at an event near you!

The New MySQL Dolphin shirts have arrived and you can get yours at upcoming events where the MySQL Community Team will be appearing! Come meet Keith Larson at Southeast LinuxFest starting June 10th or Dave Stokes at the Community Leadership Summit July 23rd. These are high quality t-shirts in a majestic gray tone featuring with Sakila the dolphin. These were highly sought after at an April conference but most of them went to the MySQL Engineering Teams. But now our second batch has arrived.

Not going to either of those show? Then keep watching Planet.MySQL.Com for future opportunities to …

In our last customer project we had around 600 Gbyte of data in a MySQL database. Because this database consumed a significant amount of our disk space and backups with the InnoDB backup tool took pretty long we wanted to find out if we could get rid of some of the tables.
This application was growing over the last 10 years and it was not clear if some tables are still in use or not.

But how to find out when a table was touched last? MySQL/InnoDB theoretically could know about but does not report this information.

Fortunately the operating system command stat knows when a file was last accessed (read) and modified (written).

For example stat on the InnoDB log file:

stat ib_logfile0
  File: `ib_logfile0'
  Size: 5242880         Blocks: 10240      IO Block: 4096   regular file
Device: 811h/2065d      Inode: 14689226    Links: 1
Access: …

While Oracle deserves some praise for its donation of OpenOffice.org code to the Apache Foundation, it is disappointing again to see a legitimate open source market contender that has been marginalized by miscommunication and mismanagement of the project by a large vendor.

OpenOffice.org, warts and all, was probably the most significant competition for Microsoft Office for years and in many ways demonstrated the advantages of open source, helping usher in wider use of it, as well as greater usability. OO.o was in fact my reason for originally investigating and moving to open source software more than a decade ago. Regardless of past mismanagement of community and technology, that competitive factor has been diminished greatly since Oracle took ownership of OO.o. Now, after prompting a fork — as has …

MySQL Replication is something that is used by many, many MySQL users, and here at Recorded Future we are no exception. In our case, the slaves are used for different purposes, and as we develop our system so much and so fast, so sometimes things happen which could have been avoided. Like something getting executed in the master that really should not end up in a slave at all, and which would cause all sorts of problems on the slave.

Also, some things that work fine on the master can sometimes cause things to break on the slave, a typical such issue is a big operation on the master that when executed on the slave would cause a lock timeout. And you could argue that the lock timeout should be increased, but the question is how much, and frankly, do you really want those locks hanging around? And in some cases, it's the replication thread that has a lock timeout.

…