'INFORMATION_SCHEMA' Tag

• ROUTINE_PRIVILEGES implementation

  June 22, 2011

  Following up on MySQL security: inconsistencies, and on MySQL bug #61596, I was thinking it may take a long time till the non-existent ROUTINE_PRIVILEGES view is implemented. Here's my own implementation of the view. I've followed the somewhat strange conventions used in the *_PRIVILEGES tables in INFORMATION_SCHEMA, where the IS_GRANTABLE is a separate column, although [...]

  posted in MySQL by shlomi

• MySQL security: inconsistencies

  June 22, 2011

  Doing some work with MySQL security, I've noticed a few inconsistencies. They're mostly not-too-terrible for daily work, except they get in my way right now. The ALL PRIVILEGES inconsistency The preferred way of assigning account privileges in MySQL is by way of using GRANT. With GRANT, one assigns one or more privileges to an account, [...]

  posted in MySQL by shlomi

• Checking for AUTO_INCREMENT capacity with single query

  April 5, 2011

  Darn! This means oak-show-limits becomes redundant. Am I not supposed to speak about it on my coming presentation? Bad timing! You have AUTO_INCREMENT columns. How far are you pushing the limits? Are you going to run out of AUTO_INCREMENT values soon? Perhaps you wonder whether you should ALTER from INT to BIGINT? The answer is [...]

  posted in MySQL by shlomi

• Thoughts and ideas for Online Schema Change

  October 7, 2010

  Here's a few thoughts on current status and further possibilities for Facebook's Online Schema Change (OSC) tool. I've had these thoughts for months now, pondering over improving oak-online-alter-table but haven't got around to implement them nor even write them down. Better late than never. The tool has some limitations. Some cannot be lifted, some could. [...]

  posted in Development, MySQL by shlomi

• Verifying GROUP_CONCAT limit without using variables

  June 10, 2010

  I have a case where I must know if group_concat_max_len is at its default value (1024), which means there are some operation I cannot work out. I've ranted on this here. Normally, I would simply: However, I am using views, where session variables are not allowed. Using a stored function can do the trick, but [...]

  posted in MySQL by shlomi

• How to calculate a good InnoDB log file size - recap

  October 20, 2009

  Following Baron Schwartz' post: How to calculate a good InnoDB log file size, which shows how to make an estimate for the InnoDB log file size, and based on SQL: querying for status difference over time, I've written a query to run on MySQL 5.1, which, upon sampling 60 seconds of status, estimates the InnoDB [...]

  posted in MySQL by shlomi

• SQL: querying for status difference over time

  October 20, 2009

  The InnoDB plugin has a nice INFORMATION_SCHEMA concept: resetting tables. For example, the INNODB_CMP table lists information about compression operation. A similar table, INNODB_CMP_RESET, provides the same information, but resets the values. The latter can be used to measure, for example, number of compression operations over time. I wish to present a SQL trick which [...]

  posted in MySQL by shlomi

• INFORMATION_SCHEMA.GLOBAL_STATUS: watch out

  October 14, 2009

  MySQL 5.1 boasts some new and useful INFORMATION_SCHEMA tables. Among them is the GLOBAL_STATUS table. At last, it is possible to ask questions like: node1> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'innodb_os_log_written'; +-----------------------+----------------+ | VARIABLE_NAME         | VARIABLE_VALUE | +-----------------------+----------------+ | INNODB_OS_LOG_WRITTEN | 512            | +-----------------------+----------------+ 1 row in set (0.00 sec) node1> SELECT * [...]

  posted in MySQL by shlomi

• Useful database analysis queries with INFORMATION_SCHEMA

  November 26, 2008

  A set of useful queries on INFORMATION_SCHEMA follows. These queries can be used when approaching a new database, to learn about some of its properties, or they can be regularly used on an existing schema, so as to verify its integrity.

  I will present queries for:

  * Checking on database engines and size
  * Locating duplicate and redundant indexes
  * Checking on character sets for columns and tables, looking for variances
  * Checking on processes and long queries

  posted in MySQL by shlomi