The binary logging format in MySQL has been ROW by default since MySQL 5.7, yet there are still many users sticking with STATEMENT or MIXED formats for various reasons. In some cases, there is just simple hesitation from changing something that has worked for years on legacy applications. But in others, there may be serious blockers, most typically missing primary keys in badly designed schemas, which would lead to serious performance issues on the replicas.

As a Support Engineer, I can still see quite a few customers using STATEMENT or MIXED formats, even if they are already on MySQL 8.0. In many cases this is OK, but recently I had to deal with a pretty nasty case, where not using ROW format was found to cause the replicas to silently lose data updates, without raising any replication errors! Was it some really …

The Question Recently, a customer asked us:

After importing a new section of user data into our Tungsten cluster, we are seeing perpetually rising replication lag. We are sitting at 8.5hrs estimated convergence time after importing around 50 million rows and this lag is climbing continuously. We are currently migrating some of our users from a NoSQL database into our Tungsten cluster. We have a procedure to write out a bunch of CSV files after translating our old data into columns and then we recursively send them to the write master using the mysql client. Specifically our import SQL is doing LOAD DATA LOCAL INFILE and the reading in a large CSV file to do the import. We have 20k records per CSV file and we have 12 workers which insert them in parallel.

Simple Overview The Skinny

In cases like this, the slaves are having trouble with the database unable to keep up with the apply stage …

Introduction In this article, we are going to see how a relational database executes SQL statements and prepared statements. SQL statement lifecycle The main database modules responsible for processing a SQL statement are: the Parser, the Optimizer, the Executor. A SQL statement execution looks like in the following diagram. Parser The Parser checks the SQL statement and ensures its validity. The statements are verified both syntactically (the statement keywords must be properly spelled and following the SQL language guidelines) and semantically (the referenced tables and column do exist in the database). During... Read More

The post How does a relational database execute SQL statements and prepared statements appeared first on …

I recently wrote about non-deterministic queries in the replication stream. That’s resolved by using either MIXED or ROW based replication rather than STATEMENT based.

Another thing that’s not fully handled by STATEMENT based replication is temporary tables. Imagine the following:

1. Master: CREATE TEMPORARY TABLE rpltmpbreak (i INT);
2. Wait for slave to replicate this statement, then stop and start mysqld (not just STOP/START SLAVE)
3. Master: INSERT INTO rpltmpbreak VALUES (1);
4. Slave: SHOW SLAVE STATUS \G

If for any reason a slave server shuts down and restarts after the temp table creation, replication will break because the temporary table will no longer exist on the restarted slave server. It’s obvious when you think about it, but nevertheless it’s quite …

You might find a warning like the below in your error log:

130522 17:54:18 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
Statement: INSERT INTO tbl2 SELECT * FROM tbl1 WHERE col IN (417,523)

What do MariaDB and MySQL mean with this warning? The server can’t guarantee that this exact query, with STATEMENT based replication, will always yield identical results on the slave.

Does that mean that you have to use ROW based (or MIXED) replication? Possibly, but not necessarily.

For this type of query, it primarily refers to the fact that without …

Even when the output of EXPLAIN doesn’t show “using temporary”, a temporary file may still be used in certain cases.

That’s not to say the query needs the temporary file to actually resolve the query (like what you’d see from the need for a derived table). But rather, the temporary file I’m speaking of is due to binary logging.

In particular, you can see this easily if using InnoDB, (most commonly) row-based binary logging, and you issue a large transaction, say a large UPDATE (large meaning something larger than the size of binlog_cache_size). In this case, you’ll notice a temporary file being …

In many programing languages it is possible to shorten if statements using what’s called the ternary operator. It is sometimes referred as the “one line if statement” or the “short if statement”. This can help at times to produce cleaner code, however use this operator wisely as it is not always best to be used for more complicated statements.

PHP Example of an if statement

1
2
3
4
5
6
7
8

if($nFoo > 0)
{
   echo "I'm at the work.";
}
else
{
   echo "I'm at home.";
}

PHP Example using the ternary operator

1

      echo $nFoo > 0 ? "I'm at the work." : "I'm at home.";

The expression (expr1) ? (expr2) : (expr3) evaluates to expr2 if expr1 evaluates to …

The good: row based replication solves some problems when replicating the result of non deterministic functions, such as UUID() or NOW().
The bad: row-based replication may break existing applications, where you count on the quirks of statement based replication to execute conditionally (updates base on @@server_id, for example), and may perform badly on updates applied to very large tables.

…