How to find and kill an advisory lock query?
When an application or PostgreSQL query encounters an advisory lock, identify and terminate the query holding it so the system can continue.
EXPLORE THE TOPIC
Database.
30 articles on this topic
MySQL ERROR 1206: The total number of locks exceeds the lock table size
A query on an InnoDB table with a very large number of rows may fail with ERROR 1206 (HY000): The total number of locks exceeds the lock table size.
MySQL Replication: 'Got fatal error 1236'
In MySQL replication, the slave retrieves data from the master's binlog and replays it locally. If the slave stops with error 1236, as shown below, how can we fix it…
How to fix "Cannot load from mysql.proc. The table is probably corrupted"
If MySQL reports "Error: Cannot load from mysql.proc. The table is probably corrupted", its schema may be a different version from the running server.
Installing MariaDB 10 on Ubuntu 12.04 LTS
Installing MariaDB 10 on Ubuntu 12.04 LTS using MariaDB's repository. Import the key and install the repository: sudo apt-get install python-software-properties sudo apt-key adv --recv-keys
How to enables command-line history in SQL*PLUS
SQL*PLUS (sqlplus) does not let you press the up or down arrow to browse previous commands. It only lets you inspect one previous command, making repeated commands inconvenient.
Show running processes in Oracle DB
Oracle tip: a script displays queries running on an Oracle database, similar to show processlist in MySQL.
How to turn off Oracle password expiration
By default, Oracle user passwords expire after 180 days and must be changed. To remove the expiry, change PASSWORD_LIFE_TIME on the user's profile.
Export mysql database based on a where condition
MYSQLDUMP exports data from MySQL databases. It is commonly used to export all data from a table or database, but it can also export selected data…
Installing MySQL 5.6 using the MySQL yum repository
Oracle has released a MySQL Yum repository to make installing and updating MySQL server easier. Initially it supports Red Hat/Oracle Enterprise Linux/CentOS 6 and Fedora 18 and 19…
[Bug] Invalid of GRANT command will breaks replication
MySQL 5.6 has a bug to watch for: running GRANT privileges in PhpMyAdmin may cause a slave to stop replicating (bug report: http://bugs.mysql.com/bug.php?id=68892). After using MySQL 5…
How to compress mysqldump output with gzip/bzip2
mysqldump exports MySQL data in SQL format and is commonly used for backups. Normally these backups are not compressed.
How to list stored procedures and show procedure code in MySQL
To list the stored procedures in MySQL and display their procedure code, follow these steps. Listing MySQL stored procedures: list the procedures in the system. mysql> SHO…
Find top frequently using queries on MySQL
MySQL tip: find the ten most frequently used queries over a period using tcpdump or mysqlbinlog to collect and rank them.
How to copy a table in MySQL
In MySQL, a table's structure and data can be copied easily. For example, create table_new with the structure and data from table_name as follows.
How to uses mysqldump to get structure or schema data only
mysqldump normally exports both database structure and data. Add -d or --no-data to export only the structure or schema.
How to remove/purge MySQL binary logs
MySQL binary logs consume disk space. Configure their expiry carefully and use MySQL commands to purge them rather than deleting files from the operating system.
Tips for speeding up dump and import data in MySQL
Importing large SQL datasets into MySQL can take a long time. These tips speed it up by adjusting indexes, inserts, autocommit, and constraint checks.
Check MySQL database size using SQL query
SQL queries to check space used by MySQL databases and tables. Check size of all database: run the query below to check each database's size. SELECT TABLE_SCHEMA
How to disable InnoDB storage engine on MySQL
MySQL enables the InnoDB storage engine by default. If you do not need it, disable it to save system resources. To disable InnoDB…
A Comparison of Pros and Cons between InnoDB and MyISAM
MySQL has many table storage engines, with InnoDB and MyISAM among the most popular. This article compares their features, performance, advantages, and disadvantages.
How to streaming export and import MySQL database to other server
You can dump a database and simultaneously import it on another machine by using nc (netcat) to stream data to the destination.
How to convert all MyISAM tables to InnoDB in a MySQL Database
To change multiple tables from the MyISAM storage engine to InnoDB, run SELECT CONCAT... to generate the conversion commands, then run the resulting commands…
Fix missing dependency libmysqlclient.so.15 when installing MySQL-python via yum
CentOS 5 machines running a MySQL version newer than the one supplied with the OS may have trouble installing MySQL-python through yum or rpm. Both report that libmysqlclient_r.so.15 is missing…
MySQL server hardware, OS and configuration tuning tips
MySQL server hardware, OS and configuration tuning tips. Hardware and OS: use a 64-bit OS to support more memory. Do not use software RAID. Avoid RAID 5 and use RAID 10 instead. A battery-backed…
Mass killing MySQL connections
Too many MySQL connections or locked tables can slow a server. Mass-killing selected stalled connections can restore service with minimal downtime.
How to find all tables use particular storage engine on MySQL
MySQL supports various table engines, including MyISAM, InnoDB, MEMORY, and NDB. Finding which engine a table uses may seem complicated, but it is straightforward…
MySQL replication: 'show master status' shows 'Empty set (0.00 sec)'
After preparing two servers for MySQL replication with identical data, the next step is setting up replication. This requires running show master status to check…
How to update the max_connections in MySQL
The "too many connections" error during a database query occurs when the number of connections exceeds max_connections. MySQL normally allows…
Installing MySQL Cluster
This setup uses four machines: two storage nodes, mysql-ndb-1 and mysql-ndb-2; one API node, mysql-api-1; and one management node, mgmt.