MySQL Troubleshooting: What To Do When Queries Don't Work
Sveta Smirnova
Language: English
Pages: 266
ISBN: 1449312004
Format: PDF / Kindle (mobi) / ePub
Stuck with bugs, performance problems, crashes, data corruption, and puzzling output? If you’re a database programmer or DBA, they’re part of your life. The trick is knowing how to quickly recover from them. This unique, example-packed book shows you how to handle an array of vexing problems when working with MySQL.
Written by a principal technical support engineer at Oracle, MySQL Troubleshooting provides the background, tools, and expert steps for solving problems from simple to complex—whether data you thought you inserted doesn’t turn up in a query, or the entire database is corrupt because of a server failure. With this book in hand, you’ll work with more confidence.
- Understand the source of a problem, even when the solution is simple
- Handle problems that occur when applications run in multiple threads
- Debug and fix problems caused by configuration options
- Discover how operating system tuning can affect your server
- Use troubleshooting techniques specific to replication issues
- Get a reference to additional troubleshooting techniques and tools, including third-party solutions
- Learn best practices for safe and effective troubleshooting—and for preventing problems
Blowing the Bridge: A Software Story
Java: The Complete Reference (9th Edition)
The Second Machine Age: Work, Progress, and Prosperity in a Time of Brilliant Technologies
Visualizing Time: Designing Graphical Representations for Statistical Data
shared between threads, it locks access to the resource to prevent concurrent access to the same resource by other threads. This is done for data consistency. But at the same time, such protection can lead to deadlocks. These deadlocks are hard to diagnose and theoretically should never happen, but because they have turned up in the past, I’ll describe what to do when you suspect it. As an example, I will create a deadlock of this kind using a test case from a bug report. This bug is not related
lock struct(s), heap size 320, 1 row lock(s), undo log entries 2 MySQL thread id 184, query id 93 localhost root TABLE LOCK table `test`.`t` trx id 0 26244356 lock mode IX RECORD LOCKS space id 349 page no 3 n bits 88 index `PRIMARY` of table `test`.`t` trx id 0 26244356 lock_mode X locks rec but not gap Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 4; hex 00000006; asc ;; 1: len 6; hex 000001907504; asc u ;; 2: len 7; hex 0000000032081c; asc 2 ;;
FILE_NAME: /home/ssmirnov/mysql-5.5/data/sbtest/sbtest.ibd EVENT_NAME: wait/io/file/innodb/innodb_data_file COUNT_READ: 5236 COUNT_WRITE: 0 SUM_NUMBER_OF_BYTES_READ: 85786624 SUM_NUMBER_OF_BYTES_WRITE: 0 *************************** 4. row *************************** FILE_NAME: /home/ssmirnov/mysql-5.5/data/sbtest/sbtest.frm EVENT_NAME: wait/io/file/sql/FRM COUNT_READ: 7 COUNT_WRITE: 0 SUM_NUMBER_OF_BYTES_READ: 1141 SUM_NUMBER_OF_BYTES_WRITE: 0 *************************** 5. row
acquainted with the MySQL source code. The “MySQL Internals” page on MySQL Forge is good start. I also recommend the book Expert MySQL by Dr. Charles A. Bell (Apress). You can also find useful information in the books Understanding MySQL Internals by Sasha Pachev (O’Reilly) and MySQL 5.1 Plugin Development by Andrew Hutchings and Sergei Golubchik (Packt). At some point, of course, you have to dive into the MySQL source code itself. I won’t describe how to deal with core files here in detail,
table is somehow corrupted. In such cases it can make sense to make binary copy of a single table and move it to the test server. By interaction with the MySQL server Online Taken when the MySQL server is running Offline Taken when the MySQL server is stopped By interaction with the MySQL server objects Cold Taken when no operation is allowed for the MySQL server. The server should be either stopped or blocked from modifying its own files. The advantage of such backups is that
