MySQL Support Blogs

This post was originally published on the MySQL Support Team Blog at https://blogs.oracle.com/mysqlsupport/entry/innodb_locks_analysis_why_is on 14 April 2017.

Consider the scenario that you execute a query. You expect it to be fast – typically subsecond – but now it take not return until after 50 seconds (innodb_lock_wait_timeout seconds) and then it returns with an error:

mysql> UPDATE world.City SET Population = Population + 999 WHERE ID = 130;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

You continue to investigate the issue using the sys.innodb_lock_waits view or the underlying Information Schema tables (INNODB_TRX, INNODB_LOCKS and INNODB_LOCK_WAITS).

Note: The above Information Schema tables with lock and lock waits information have been moved to the Performance Schema in 8.0 as the data_locks and data_lock_waits tables. The sys schema view however …

[Read More]

This post was originally published on the MySQL Support Team Blog at https://blogs.oracle.com/mysqlsupport/entry/working_around_mysql_cluster_push on 5 August 2016.

I worked on an issue recently where a query was too slow when executed in MySQL Cluster. The issue was that Cluster has some restrictions when it comes to push down conditions.

As an example of this, consider the following query using the employees sample database. The query takes a look at the average salary based on how many years the employee has been with the company. As the latest hire date in the database is in January 2000, the query uses 1 February 2000 as the reference date.

Initially the query performs like (performance is with two data nodes and all nodes in the same virtual machine on a laptop, so the timings are not necessarily representative of a production system, though the improvements should be repeatable):

mysql> SELECT …

[Read More]

Consider the scenario that you execute a query. You expect it to be fast - typically subsecond - but now it take not return until after 50 seconds (innodb_lock_wait_timeout seconds) and then it returns with an error:

mysql> UPDATE world.City SET Population = Population + 999 WHERE ID = 130;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

You continue to investigate the issue using the sys.innodb_lock_waits view or the underlying Information Schema tables (INNODB_TRX, INNODB_LOCKS and …

[Read More]

https://dev.mysql.com/doc/en/load-data.html

BIT values cannot be loaded using binary notation (for example, b'011010').

$ cat test.txt

b'101010'
0b111000

-----

CREATE TABLE loadTest (b BIT(6));

LOAD DATA LOCAL INFILE 'test.txt' INTO TABLE loadTest;

/*--------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1406 | Data too long for column 'b' at row 1 |
| Warning | 1406 | Data too long for column 'b' at row 2 |
+---------+------+--------------------------------------*/

-- Note the wrong values:

SELECT BIN(b) FROM loadTest;
/*-------+
| BIN(b) |
+--------+
| 111111 |
| 111111 |
+-------*/

TRUNCATE loadTest;

LOAD DATA LOCAL INFILE 'test.txt' INTO TABLE loadTest (@b) 
  SET b = CAST(CONV(TRIM('''' FROM SUBSTRING_INDEX(@b, …

[Read More]

Somebody asked on Freenode. I don't know why they wanted it. How would you drop all MySQL users who do not have "GRANT ALL ON *.* ... WITH GRANT OPTION"? That is, drop any users who have 'N' in any of the privilege columns in `mysql`.`user`.

My solution shown below. Did you think of a different approach?

My solution ▼

I used SQL to build SQL which built more SQL. Get the list of 'priv' columns from information_schema.columns. Build a query that looks for 'N' in any of those columns (it feels funny to search for a constant in a list of fields instead of a field in a list of constants, but it's perfectly legal). Use the results to build the DROP USER statement.

mysql -BNe "SELECT CONCAT('SELECT CONCAT(''DROP USER '', QUOTE(user), ''@'', QUOTE(Host), '';'') FROM mysql.user WHERE ''N'' IN (', GROUP_CONCAT(column_name), ')') FROM information_schema.columns WHERE table_schema = 'mysql' AND table_name = 'user' AND …

[Read More]

MySQL Support was represented at Oracle OpenWorld 2016 with two engineers: Ligaya Turmelle and Jesper Krogh. We did a total of four talks and spend 20 some hours in total at the Support Stars Bar. We would like to thank all of you who attended. It was good meeting some of you and talk about your experiences - both good and not so good - using MySQL.

If you attended one of our talks and would like to study some of the slides in more details - or you were not able to attend, all of our presentations are available from the Oracle OpenWorld content catalogue (except the Support Stars Bar mini briefing which is available from this blog):

• MySQL DBA Primer [TUT1718]
• …

[Read More]

A customer showed that a particular client reported a less-than-helpful error message when it tried to display some meta-data about a table.

I couldn't repeat the behavior with just a copy of the schema, so I suspected it was because of the size of data in the customer's server - somebody had used an int where they needed a long.

The customer's data was quite large - many hundreds of GB - more than I could …

[Read More]

There is not yet an equivalent to GROUP_CONCAT that produces a JSON array. (There is in MySQL 8, but that's not GA yet.) Until then, you can hack it together with string functions:

SELECT * FROM t;
+------+--------+
| id   | data   |
+------+--------+
|    1 | First  |
|    2 | Second |
+------+--------+

SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', id, 'value', data) SEPARATOR ', '), ']') AS j FROM t;
+-------------------------------------------------------------+
| j                                                           |
+-------------------------------------------------------------+
| [{"id": 1, "value": "First"}, {"id": 2, "value": "Second"}] |
+-------------------------------------------------------------+

Or you can use all JSON functions but hack the grouping:

SELECT j FROM (
       SELECT
         @c := @c + 1 AS c, …

[Read More]

It is soon time for the annual Oracle OpenWorld conference in San Francisco. This year it takes place from 18 to 22 September. There will be a wide range of talks about MySQL and other Oracle products with speakers including Oracle developers, product management, Support, customers, and community members. So there should be something for everyone.

MySQL Support will have two talks this year:

[Read More]

I worked on an issue last recently where a query was too slow when executed in MySQL Cluster. The issue was that Cluster has some restrictions when it comes to push down conditions.

As an example of this, consider the following query using the employees sample database. The query takes a look at the average salary based on how many years the employee has been with the company. As the latest hire date in the database is in January 2000, the query uses 1 February 2000 as the reference date.

Initially the query performs like (performance is with two data nodes and all nodes in the same virtual machine on a laptop, so the timings are not necessarily representative of a production system, though the improvements should be repeatable):

mysql> SELECT FLOOR(DATEDIFF('2000-02-01', hire_date)/365) AS LengthOfService,
              COUNT(DISTINCT employees.emp_no) AS NoEmployees, AVG(salary) AS AvgSalary
         FROM salaries
              STRAIGHT_JOIN …

[Read More]

Workbench announced support for a spatial view in 6.2, but examples are somewhat lacking. Just how do you get a SHP into MySQL?

Download and unpack a SHP file such as these country boundaries.

In the Workbench installation directory, you'll find a program "ogr2ogr" that can convert .shp to .csv. Run it like this:

"C:\Program …

[Read More]

MySQL 5.7 came out with support for JSON, improved geometry, and virtual columns. Here's an example showing them all playing together.

Download citylots.json.

It comes as one big object, so we'll break it up into separate lines:
grep "^{ .type" citylots.json > properties.json

Connect to a 5.7 instance of MySQL.

CREATE TABLE citylots (id …

[Read More]

Hi!

Thanks everyone who attended the hands-on lab session on MySQL Cluster at Oracle OpenWorld today.

The following are the links for the slides, the HOL instructions, and the HOL extra instructions.

Will try to summarize the HOL session below.

Aim of the HOL was to help attendees to familiarize with MySQL Cluster. In particular, by:

1. Learning the basics of MySQL Cluster Architecture
2. Learning the basics of MySQL Cluster Configuration and Administration
3. Learning how to start a new Cluster for evaluation purposes and …

[Read More]

mysql < quarto.sql

Example game play:

mysql> -- Start the game and pass the first piece in the lower nibble
mysql> CALL SetupGame(0x0A);
-----------------------------------------------------------------------+
| rules                                                                |
-----------------------------------------------------------------------+
| Quarto: 4 in a line (row, column, or long diagonal) with at least one bit in common wins.
CALL Play(move); -- high 4 bits are board position, low 4 bits are piece for next player
CALL PrintBoard(base); -- to display the board. Useful bases are 16 and 2. |
-----------------------------------------------------------------------+

+------------------------+
| instructions           |
+------------------------+
| Player 1, play piece A |
+------------------------+

mysql> CALL Play(0x00);
+---------+
| board   |
+---------+ …

[Read More]

Because somebody asked for it on Freenode:

CREATE FUNCTION DATE_TRUNC(field ENUM('microsecond', 'millisecond', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', 'millennium'), source datetime(6))
RETURNS datetime(6)
DETERMINISTIC
BEGIN
  IF field IN ('millisecond') THEN SET source = source - INTERVAL MICROSECOND(source) % 1000 MICROSECOND; END IF;
  IF field NOT IN ('microsecond', 'millisecond') THEN SET source = source - INTERVAL MICROSECOND(source) MICROSECOND; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second') THEN SET source = source - INTERVAL SECOND(source) SECOND; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute') THEN SET source = source - INTERVAL MINUTE(source) MINUTE; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute', 'hour') THEN SET source = source - INTERVAL HOUR(source) HOUR; END IF;
  IF field NOT IN ('microsecond', 'millisecond', …

[Read More]

Hi!

I'm speaking at Oracle Open World this October 29th (Thursday). My Session is a hands-on lab session: HOL3348 on MySQL Cluster.

If you are interested in familiarize a bit with MySQL Cluster this is definitely a session for you.

I will start by briefly introducing MySQL Cluster and its Architecture.

Then I will guide you through the needed steps to:

• Install a local MySQL Cluster
• Start MySQL Cluster
• Connect to MySQL Cluster (using the command line)
• Ways to monitor MySQL Cluster …

[Read More]

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;

[Read More]

and without creating a table to pass the state around (really just an excuse to use the named locks feature).

DELIMITER //
DROP FUNCTION IF EXISTS SET_BREAKPOINT//
CREATE FUNCTION SET_BREAKPOINT()
RETURNS tinyint
NO SQL
BEGIN
        -- Acquire lock 1
        -- Wait until lock 2 is taken to signal that we may continue
        DO GET_LOCK(CONCAT('lock_1_', CONNECTION_ID()), -1);
        REPEAT
                DO 1;
        UNTIL IS_USED_LOCK(CONCAT('lock_2_', CONNECTION_ID())) END REPEAT;
        DO RELEASE_LOCK(CONCAT('lock_1_', CONNECTION_ID()));

        -- Acquire lock 3 to acknowledge message to continue.
        -- Wait for lock 2 to be released as signal of receipt.
        DO GET_LOCK(CONCAT('lock_3_', CONNECTION_ID()), -1);
        REPEAT
                DO 1;
        UNTIL IS_FREE_LOCK(CONCAT('lock_2_', CONNECTION_ID())) END REPEAT;
        DO RELEASE_LOCK(CONCAT('lock_3_', CONNECTION_ID()));

        RETURN 1;
END//

DROP FUNCTION IF EXISTS NEXT_BREAKPOINT//
CREATE FUNCTION NEXT_BREAKPOINT(connection_id int)
RETURNS tinyint
NO SQL
BEGIN
        -- Acquire …

[Read More]

Sometimes replication halts with an error like:

Slave I/O: Got fatal error 1236 from master when reading data from binary log
Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master;

If it's the SQL thread instead of the I/O thread, it might complain about 'Event too big'. The error could also be the other direction, complaining of 'Event too small'.

I rarely see this error actually have anything to do with max_allowed_packet. You can check the largest event in the master's binary log file. Even though the binary log records the length in each entry, mysqlbinlog doesn't expose it, so we have to do the math ourselves:

mysqlbinlog mysql-bin.00XXX | gawk "/^# at / { diff = $3 - prev; prev = $3; } (diff > max) { max = diff } END {print max}" -

If the result is larger than max_allowed_packet, then the problem and solution are exactly what the the error message says. …

[Read More]

Can you use the binary logs to undo a DELETE? Maybe, if you're using ROW format logging. The difference between a delete and an insert event could be just one byte - the one that maps it as a DELETE_ROWS_EVENT or a WRITE_ROWS_EVENT. Let's try it.

I've already populated this table with a few rows:

CREATE TABLE `undo_test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`t` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`v` varchar(20) DEFAULT NULL,
`b` blob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Save a hash so we can see if the undo really works later:

mysql -e "SELECT * FROM test.undo_test" | md5sum > before.md5

Delete an unwanted row:

DELETE FROM undo_test;
Query OK, 1693 rows affected (0.14 sec)

Oops! Forgot the WHERE clause! And of course I don't have any recent backups. Is the data really gone?

mysql> select …

[Read More]