SKIP LOCKED is a new feature in MySQL 8 that many will find valuable.  If allows you to not wait about for locked records and work on what is available -- the unlocked records.
How To Use SKIP LOCKED The MySQL world database has 274 records in the city table where the countryCode field equals 'USA' there are 274 records. From past interactions, we somehow know there are some records with the ID field greater than 4000.
On MySQL shell number 1, start a transaction and lock some records mysql>START TRANSACTION; mysql>SELECT * FROM city WHERE ID > 4000 and countryCode = 'USA'; There will be 66 records.
On MySQL shell number number 2, start a transaction and lets try to lock the records starting at IS 3990 and up. mysql>START TRANSACTION; mysql>SELECT FROM city WHERE id > 3990 and countryCode='USA'
FOR UPDATE SKIPPED LOCKED; There will be 10 records.  The records …

The MySQL Enterprise Audit plug-in is part of the MySQL Enterprise Edition (available through a paid license). Basically, Enterprise Audit tracks everything that is happening on your MySQL server, and can be used to protect/detect the misuse of information, and to meet popular compliance regulations including HIPAA, Sarbanes-Oxley, and the PCI Data Security Standard.

MySQL Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity executed on specific MySQL servers. Designed to meet the …

Here's something that has puzzled me for several weeks.
Right after migrating  MySQL from 5.6 to 5.7, we started experiencing random xtrabackup failures on some, but not all, of our slaves.
The failures were only happening when taking an incremental backup, and it would always fail on the same table on each slave, with errors similar to the following:

171106 13:00:33 [01] Streaming ./gls/C_GLS_IDS_AUX.ibd
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for …

Introduction Last week, Burkhard Graves asked me to answer the following StackOverflow question: And, since he wasn’t convinced about my answer: I decided to turn it into a dedicated article and explain how UPSERT and MERGE work in the top 4 most common relational database systems: Oracle, SQL Server, PostgreSQL, and MySQL. Domain Model For … Continue reading How do UPSERT and MERGE work in Oracle, SQL Server, PostgreSQL and MySQL →

The post How do UPSERT and MERGE work in Oracle, SQL Server, PostgreSQL and MySQL appeared first on Vlad Mihalcea's Blog.

Authors: Sunny Bains, Jiamin Huang (University of Michigan)

What is Transaction Scheduling?

Locking is one of the most popular mechanisms for concurrency control in most database systems, including Oracle MySQL. One major question, however, seems to have been overlooked by all database vendors:

Q: When multiple transactions are waiting for a lock on the same object, which one(s) should get the lock first?…

There’s a GTID_SUBTRACT function, and the manual shows how to write your own cheap GTID_UNION, but not a GTID_INTERSECT. Fortunately it’s easy to write your own, as it’s just subtracting twice.

CREATE FUNCTION GTID_INTERSECT(g1 TEXT, g2 TEXT)
RETURNS TEXT DETERMINISTIC
RETURN GTID_SUBTRACT(g1, GTID_SUBTRACT(g1, g2));

What use is it?

SET @slave_executed = '33738f8c-c1a5-11e7-8fc3-0a002700000f:1-681577,
421d139e-04b9-11e7-b702-0050569935dc:1-13764443,
52b9a949-d79d-11e3-80dd-0050568d193e:1-1207378:1207380-1261803:1261805-1267098:1267100-1267416:1267418-1589733';

SET @master_executed = '33738f8c-c1a5-11e7-8fc3-0a002700000f:1-730294,
421d139e-04b9-11e7-b702-0050569935dc:1-13764443,
52b9a949-d79d-11e3-80dd-0050568d193e:1-1207378:1207380-1261803:1261805-1589733';

SET @master_purged = '33738f8c-c1a5-11e7-8fc3-0a002700000f:1-681582,
…

Today I am going to share with you how to create insert update delete operation with validation in codeigniter 3 application with demo examplethis tutorial will help to create simple CRUD Create Read Update Delete Operation application using MySQL Database with validation As we know well we al

Today I am going to share with you how to create insert update delete operation with validation in codeigniter 3 application with demo examplethis tutorial will help to create simple CRUD Create Read Update Delete Operation application using MySQL Database with validation As we know well we al

MySQL Change default directories

While reading this heading everybody things like, yeah its pretty old topic, we can get many articles by googling. But you know what sometimes well known things never work for us. This time it happened for me, my bad. I have done this many times. But most of us changed the default Data Directory only. Only a few of us thinking about change MySQL default data,binlog, error log directories.

I can easily change the Data directory, But while enabling binlog, and error log I got an error.

mysqld: File '/mysql/binlog/mysql-bin.index' not found (Errcode: 13)

So this is the reason for wrote this blog.

My Server Setup:

OS: Ubuntu 14.04LTS 64Bit

MySQL: 5.5.58

Destination Directories:

Data: /mysql/data

Binlog: /mysql/data

…

There are a few MySQL or mixed MySQL/other-DBs jobs in the UK if you are looking to get into the industry or move up:

• Senior/Team Lead MySQL DBA - London Waterloo
• DBA - MySQL - sevenoaks
• MySQL DBA - Up to 55,000 - Surrey
• Senior Cloud Database Engineer - MySQL, Linux, AWS
• …