The latest from the MySQL community

[…] PlanetMySQL Voting: Vote UP / Vote DOWN Full article:Should I worry about the Query Cache in Aurora ? […]

CREATE FUNCTION SWAP_ENDIAN(inString text)
RETURNS TEXT
DETERMINISTIC
-- Expects a hex string: AbCdEf
-- Returns the string swapped for endianness: EfCdAb

BEGIN
  DECLARE position INT DEFAULT 1;
  DECLARE holder TEXT DEFAULT '';

  WHILE position 

So you can do things like:

SELECT
  FROM_UNIXTIME(
    CONV(
      SWAP_ENDIAN(
        SUBSTRING(
          HEX(
            FROM_BASE64(
              'Yk3XVQ8pAAAAZgAAAGoAAAAAAAQANS4xLjczLWxvZwAAAAAABBAAAAAAAAAAAAAAAA'
              'AAAAAAAAAAAAAAAAAAAAAAAABiTddVEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC'
            )
          ), 1, 8
        )
      ), 16, 10)
  ) AS event_timestamp;

It had been in the making for long time. Google announced that Google Code would be closing, and since then the Continuent team has been hard at work to handle the transition. You can guess it: this operation would have been quicker if it had been done by a small company like we were one year ago, but being part of a large corporation introduces some constraints that have affected our schedule.

However, our wish has always been, and still is, to keep Tungsten Replicator as an open source product, with full functionalities and with the full benefits that the open source development model offers.

Today, Tungsten Replicator is available on GitHub as …

Thank you for attending my July 22 webinar titled “Advanced Query Tuning in MySQL 5.6 and 5.7” (my slides and a replay available here). As promised here is the list of questions and my answers (thank you for your great questions).

Q: Here is the explain example:

mysql> explain extended select id, site_id from test_index_id where site_id=1
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_index_id
         type: ref
possible_keys: key_site_id
          key: key_site_id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index

why is site_id a covered index for the query, given the fact that a) we are selecting “id”, b) key_site_id only …

There are a lot of blog posts on the internet which warn you about using the Query Cache in MySQL.

I was surprised to see that the query cache was enabled in Aurora.

This was the size on a ‘db.r3.large’ instance.

On a ‘db.r3.2xlarge’  instance, it was set to 2460900352 i.e. 2.4GB

I am not sure, if amazon has done something to improve the query cache.

So, do run tests with Aurora and see if the cache suits you.

This blog describes how the Address Sanitizer found bugs in the TokuFT (now PerconaFT) storage library.  TokuFT is the storage library used by the TokuDB for MySQL and TokuMX for MongoDB products.  TokuFT is currently tested with valgrind's memcheck tool.  However, memcheck is very slow compared to native execution, so memcheck is not always used for large tests.  This leads to missed bugs.

The Address Sanitizer is a memory …

Recently, we have discussed BUGs with our local community members. Where the half off people said that, they have seen “Assertion Failure” errors in error log. And the question was what is this at all?

In general “assert” is a code portion put by developer to handle certain things.

Well, i am not a core MySQL developer and my C/C++ knowledge is limited to simple calculator But i will try to explain where all these “Assertion Failure” errors come up.
The exact reason of failing Assertion/crash should be known by core developers. I know how to crash, they know how to fix. You help developers to reproduce a crash filling a bug report, by uploading test cases or maybe the core dump, backtrace etc.
Say, for eg, if you try to create a view after getting “full disk” error you will crash MySQL. See related BUG report -> …

Reprint a PDF for MySQL Injection Test

MySQL Error Based SQL Injection Using EXP

Simple query using group clause for 1 million records resulting in final list of 27 records.

First time takes 0.43053775 secs.
Same query through Stored procedure: First time takes 0.43341600 secs.

So in terms of time, first time they are very close.
Profiling comparison for both can be seen in below figure no_cache_comparison.png where left one is simple inline query and right one is stored procedure query.




There are some actions which are extra in the inline query:

1. freeing items
2. logging slow query
3. cleaning up

Running both second time retrieve data from cache …

My test environment is:
Ubuntu 14.04 Trusty Tahr
MySQL Server version: 5.5.44-0ubuntu0.14.04.1 (Ubuntu)

MySQL uses sql cache to store results of queries that have been executed so that when the same query is executed again it retrieves the result data set from the cache instead of getting it again from db. So it is faster data access.

It is by default enabled in MySQL.

This is interesting since there is one question we ought to ask here whether we should use it or disable it or just leave it as it is who cares :).

Ok, moving forward today's session goals are:

1.     How useful is MySQL cache?
2.     When to use it and when not to use it?
3.     What to do if you do not want to use it?

There are some catchy areas here too like not all your queries will be stored in cache. …