MySQL-Misc
From DevRandom
clientutils
mysqladmin cannot add/drop tables but can create databases
mysqladmin can change passwords but not add/drop users
mysqladmin can be used to shutdown both local and remoteservers
grants
user_info table is created by MySQL administrator and is not used with GRANTS
Database privilege change only applies with the next USE statement
Changes to global privileges and passwords do not apply to connected clients
MySQL uses sorting of user and host columns to see which ones apply. Host matching is done before user matching.
USER() function returns the actual user name, CURRENT_USER() returns the matched user per the privilege tables
innodb
LOCK TABLES will implicity commit any pending transactions
If there are any locked tables UNLOCK TABLES will also implicity commit any pending transcations
If a connection is terminated without a commit, then a rollback happens
When a deadlock is detected the transanction that modified the least number of rows is rolled back. If a deadlock is not detected the transactions have to timeout and all the deadlocked transactions are rolled back.
For foreign keys both columns should be off the same data type and indexed
Using raw disks with the innodb tablespace will require 2 restarts
REPAIR TABLE does not work on Inndb tables
For binary portability, innodb also requires database and table names need to be lowercase
Secondary indexes in Innodb also contain the corresponding primary key
Unlike MyISAM , Innodb does not store the row count so select count(*) requires a table scan
DROP TABLE is one of the statements that causes an implicit commit of preceding uncommitted statements in the transaction.
Avoid using FOR UPDATE or LOCK IN SHARE MODE without indexes. This will cause row locks on all rows in the table.
myisam
There are no buffers for caching MyISAM data. MySQL relies on the underlying OS
A READ LOCAL lock only applies to MyISAM tables & is used for concurrent inserts at the end of the table
If you run out of disk space while adding rows to a MyISAM table there is no error. MySQL will try to resume when space is freed up.
key_buffer_size is used for storing indexes. You want to be able to fit all the .MYI (Indexes) on this buffer.
A READ LOCAL lock only applies to MyISAM tables & is used for concurrent inserts at the end of the table With MySQL 5.0 MAX ROWS can be upto 256TB (6 bye pointer).
If you prefix length of a column in FULL TEXT indexes, MySQL ignores it
locking
A READ LOCAL lock only applies to MyISAM tables & is used for concurrent inserts at the end of the table
By default readers block writers & Writes block both readers and writers.
If locking multiple tables, all tables must be locked in a single LOCK statement
A READ LOCAL lock only applies to MyISAM tables & is used for concurrent inserts at the end of the table
If tables are already locked LOCK TABLE blocks and does not return till it get the LOCKS
A 2nd LOCK TABLE implicitly releases all the locks you currently hold.
A terminated connection will also release all locks
LOCK TABLES will implicity commit any pending transactions
If there are any locked tables UNLOCK TABLES will also implicity commit any pending transcations
Trying to insert data after acquiring a READ lock results in an error
By default write requests take priority over read. Write requests can be given a lower priority by using th modifiers LOW_PRIORITY or DELAYED. LOW_PRIORITY is available for all statements that change data. DELAYED is available for INSERT and REPLACE operations only. Read requests can be given a higher priority by using HIGH_PRIORITY
Be careful with persistent connection settings like mysql_pconnect. If a script cannot release a lock obtained in using a persistent connection then subsequent scripts using the same connection id will block. Same applies to transactions that are not committed or rolled back.
windows
mysqld-nt has support for named pipes
mysqld-max-nt and mysqld-max has additional support for storage engines
mysql server that runs as a windows service only reads options from a options file
shared-memory and named pipes are not enabled by default
Timezone tables need to be downloaded and installed separately on Windows and HPUX
If you run Mysql administrator on the same server you can use it to stop/start the server mysqld --console will show errors on the console
Use <dname>.sym files to create symbolic links on Windows systems
Others
Timezone tables need to be downloaded and installed separately on Windows and HPUX
UTF8 takes 1 to 3 bytes to store a character. USC2 takes 2 bytes for all chars
For foreign keys both columns should be off the same data type and indexed
Federated tables do not support transactions
Archive engine only supports SELECT and INSERT
Triggers cannot be used to cancel an operation. They can only be used to filter data
There is a grant table called host and it exists only for historical reasons user_info table is created by MySQL administrator and is not used with GRANTS
Database privilege change only applies with the next USE statement
Changes to global privileges and passwords do not apply to connected clients
Merge tables can consist of both compressed and uncompressed tables
A variable by default applies to the SESSION unless keyword GLOBAL is used
No sort or join buffer is allocated to a client unless it performs that operation
A single user can have multiple threads. While a user may appear multiple times in the threads tab, a given user will only appear once on the User Connections tab.
To tell the server to use only specific character sets the option has to be compiled at source
SHOW engines shows the list of available storage engines. A values of DISABLED means its compiled but disabled
No sort or join buffer is allocated to a client unless it performs that operation
With MySQL 5.0 MAX ROWS can be upto 256TB (6 bye pointer).
query_cache_wlock_invalidate determines whether write lock on a table will invalidate the query cache for that table
STRICT_MODE sql-mode allows zero DATE fields. NO_ZERO_DATE or NO_ZERO_IN_DATE needs to be enabled. Or use TRADITIONAL mode
ERROR_FOR_DIVISION_BY_ZERO only gives a warning and replaces values with NULL
Triggers won't work on system tables in the mysql database
