This post is a lab experiment learning from migration to the Percona Xtradb Cluster (Galera) and a very unexpected DEADLOCK scenario which took me back to basics. (root@localhost) [test]>insert into app values (1); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction Little background: We needed to work on migrating the […]
It's time to summarize the year of 2016. As a kind of a weird
summary, in this post I'd like to share a list of MySQL bug
reports I've created in 2016 that are still remaining "Verified"
- Bug #79831 - "Unexpected error message on crash-safe slave with max_relay_log_size set". According to Umesh this is not repeatable with 5.7. The fact that I've reported the bug on January 4 probably means I was working at that time. I should not repeat this mistake again next year.
- Bug #80067 - "Index on BIT column is NOT used when column name only is used in WHERE clause". People say the same problem happens with INT and, what may be even less expected, BOOLEAN columns.
Oracle released MySQL 5.7.15 recently, earlier than
expected. The reason for this "unexpected" release is not clear
to me, but it could happen because of a couple of security
related internal bug reports that got fixed:
- "It was possible to write log files ending with
.cnfthat later could be parsed as option files. The general query log and slow query log can no longer be written to a file ending with
.cnf. (Bug #24388753)
- Privilege escalation was possible by exploiting the way
REPAIR TABLEused temporary files. (Bug #24388746)"
Let me concentrate on the most important fixes to bugs and problems reported by Community users. …[Read more]
Megabytes of text had been written already on InnoDB locking and
deadlocks. Still, even very simple cases of deadlocks while
working with a table having only one row sometimes make people
wonder what happened and why.
Today I want to check if this topic is explained well in the manual and existing blog posts and understood properly. So, it's an exercise for my dear readers and those who like to report bugs as much as I do.
Let's consider a very simple example. In session #1 with default transaction isolation level execute the following:
CREATE TABLE `tt` (
`id` int(11) NOT NULL,
`c1` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into tt values(1,1); -- insert a row there
select * from tt; -- check that we have row (1,1)
select * from tt …
The other day, I was troubleshooting a deadlock, and I wondered if any of the table’s columns were referenced by any foreign keys (fks) from any other tables in the instance.
Well, this is actually very simple with information_schema (I_S):
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME='parent';
Where `parent` is the name of the table you’re searching for.
Note this query does not restrict on the database, or schema, name, but that could easily be added (or any other number of conditions). Here is an example where I only return the most useful columns (which could be useful for determining said conditions):
SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME='parent';
If …[Read more]
One common cause for deadlocks when using InnoDB tables is from the existence of foreign key constraints and the shared locks (S-lock) they acquire on referenced rows.
The reason I want to discuss them though is because they are often a bit tricky to diagnose, especially if you are only looking at the SHOW ENGINE INNODB STATUS output (which might be a bit counter-intuitive since one would expect it to contain this info).
Let me show a deadlock error to illustrate (below is from SHOW ENGINE INNODB STATUS\g):
------------------------ LATEST DETECTED DEADLOCK ------------------------ 111109 20:10:03 *** (1) TRANSACTION: TRANSACTION 65839, ACTIVE 19 sec, OS thread id 4264 starting index read mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 1 MySQL thread id 3, query id 74 localhost 127.0.0.1 root Updating UPDATE parent SET age=age+1 WHERE id=1 *** (1) WAITING FOR THIS LOCK TO BE …[Read more]
A question I recently saw on Stack Overflow titled Faster way to delete matching [database] rows? prompted me to organize my thoughts and observations on the subject and quickly jot them down here.
Here is the brief description of the task: say, you have 2 MySQL tables a and b. The tables contain the same type of data, for example log entries. Now you want to delete all or a subset of the entries in table a that exist in table b.
Solutions Suggested By Others
DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);
DELETE a FROM a INNER JOIN b on a.id=b.id;
DELETE FROM a WHERE id IN (SELECT id FROM b)
The Problem With Suggested Solutions
Solutions above are all fine if the tables are quite small and the …[Read more]