MySQL notes

From Helpful
(Redirected from MySQL)
Jump to navigation Jump to search
Database related

More theoretical - thinking about databases:

Everyday-use notes

This article/section is a stub — probably a pile of half-sorted notes and is probably a first version, is not well-checked, so may have incorrect bits. (Feel free to ignore, or tell me)

history, variants, products, players

This article/section is a stub — probably a pile of half-sorted notes and is probably a first version, is not well-checked, so may have incorrect bits. (Feel free to ignore, or tell me)
This hasn't been updated for a while, so could be outdated (particularly if it's about something that evolves constantly, such as software or research).


Players:

MySQL AB (acquired by Sun, then Oracle.
MariaDB Corporation Ab
Percona
Sun
Oracle


Products:

  • MySQL
  • MariaDB and Percona MySQL are different forks, adding some features
MariaDB is more of a drop-in replacement for MySQL, and conservative beyond that, so largely the same
in various package managers, mysql is in fact mariadb
Maria and Percona codevelop in part, using code from each other
e.g. both add XtraDB, an optional drop-in for InnoDB, which adds MVCC style tunability and more tweakability


  • Oracle MySQL Enterprise Edition
apparently certain developments are only in this


Cluster products: MySQL replication

copies binlogs, applies to slave

MySQL Cluster, integrated since 4.1

extends MySQL into a no-single-point-of-failure cluster.

Percona Cluster / MariaDB Galera Cluster (since 2012ish)

multi-master (kept in sync)

Oracle MySQL Group Replication (since 2016ish)

looks multi-master(verify)

Oracle MySQL Cluster Carrier Grade Edition

looks multi-master(verify)



Memory configuration/tweaking

See Relational database and SQL notes#Memory_configuration

Changing the root password

Some setups seem to disable mysql-root logins (note: unrelated to the *nix root login (though I'm sure there are enough people that use the same password for both roots)) by not telling you what the bare-installation password is, or by hiding it very well.


Unix-style

If so, or if you just forgot or lost it, you can replace it.

To do so, you first need to stop mysql:

/etc/init.d/mysql stop

Start it without any security. WARNING: At this point people could connect and steal, pillage, plunder and otherwise wreak havoc.

If you run a firewall that by default blocks incoming connections on unknown ports, just pick a random port - effectively only people on localhost can connect. Hence the --port (used to be called --master-port)(verify) option in the following:

/usr/bin/mysqld_safe --skip-grant-tables --user=root --port=somerandomportnumberyouthinkup


Note that mysqld_safe runs in the foreground, so start another terminal (or use screen, Ctrl-Z and bg/fg, to...). Connect to this server with a mysql client using your chosen port number, and to the database called mysql:

mysql --user=root --port=yourportnumberfromearlier mysql

That should log you in and give you the mysql> shell. Make sure you've selected the mysql database, then change the root password with:

use mysql;
UPDATE user SET Password=PASSWORD('MyNewPassword') WHERE User='root';

Quit the mysql client shell (Ctrl-D works) and stop the mysqld_safe server (Ctrl-C or a basic kill, both ought to trigger a a proper shutdown), and now you can start mysql the usual way (with proper permission checking) and on the usual port with something like:

/etc/init.d/mysql start


Also, check whether your plain-text password just got logged to ~/.mysql_history and if so, remove it :)


Notes:

  • If you get "ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement," you tried to set the password the regular/proper way (something like SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');), which won't work just now.


Windows

For windows, the method is a bit different. It allows you to specify an init script, which is apparently not due to privilege checks (verify).

TODO: figure out details

password in ~/.my.cnf

You can put login information such as the host, username, password and database to connect to in your .my.cnf, for example:

[client]
host=127.0.0.1
user=appy
password="bppy#2701"

(The double quotes are necessary if there are any unusual characters in your password, such as #)


This makes logins with the command line client transparent -- but be aware what this means securitywise - anyone that can read this file knows your password. At the least, you want to make sure this file can only be read by you, the owning user, by doing:

chmod go-rwx ~/.my.cnf

Foreign keys

Works on InnoDB tables, not the faster-but-less-reliable used-to-be-default MyISAM (apparently there used to be a comment in the docs that it would be implemented in MyISAM in the future. Heh.).

See also the mysql manual on the matter.


To use FKs you have to meet a few conditions:

  • Both referencing and referenced tables must be InnoDB (innodb→myisam gives an error, myisam→innodb is silently ignored)
  • Both referencing and referenced table must have an index on the column (or column tuple, if applicable). Regularly, the target column will be PRIMARY, which is an index too, so this applies mostly to the source column.
  • Indices may not be prefix indices (verify)
  • Must reference exactly the same time (e.g. int→unsigned int or int→bigint will not work)
  • If you use tuple reference, they must be in same order in indices.
  • ...must make sense in other details (e.g. no ON DELETE SET NULL clause applying to a NOT NULL column)


The FK syntax is fairly usual, but for completeness and copy-pasting:

ALTER TABLE tablename ADD CONSTRAINT [optionalconstraintname]
   FOREIGN KEY (columnname)  
   REFERENCES othertablename(othercolumnname)
   ON DELETE CASCADE  
   ON UPDATE CASCADE


As to removing existing FKs, you first check the names of foreign keys by using:

SHOW CREATE TABLE tablename

After learning the name, you can remove it like:

ALTER TABLE tablename DROP FOREIGN KEY foreignkeyname;


See also below for errors related to FKs.

Table type notes

InnoDB

  • Row level locking
  • Transactions
  • Foreign keys

MyISAM (default)

  • Table level locking
  • no transactions
  • database dumps are not guaranteed to be consistent
  • no foreign key constraints (silently ignored)
  • May be 'full' regardless of disk space (the cause is a historic optimization detail, which can be disabled)

BDB (Berkeley DB)

  • Page level locking
  • Removed in recent MySQL versions


Notes:

  • Table creations and the likes are never transactional in MySQL.
  • Some MySQL-specific SQL augmentations, such as INSERT DELAYED, may be specific to certain table types.

Some useful commands

Management

Show how the table in its current state would be created, i.e. show types, foreign keys, constraints, engine, charset and such.

SHOW CREATE TABLE results;

Extensions

An insert that falls back to update: (a mysql-specific extension)

INSERT INTO         wordcount (word,count)  
                    VALUES    ('a_word',1)  
ON DUPLICATE KEY    UPDATE    count=count+1;

...assuming that there's a reason for the key to be duplicate, such as a PRIMARY or UNIQUE constraint on word.


If you've filled up a drive...

This article/section is a stub — probably a pile of half-sorted notes and is probably a first version, is not well-checked, so may have incorrect bits. (Feel free to ignore, or tell me)

(I've managed to fill a drive with InnoDB's ibdata files more than once).


The non-shrinking part

This article/section is a stub — probably a pile of half-sorted notes and is probably a first version, is not well-checked, so may have incorrect bits. (Feel free to ignore, or tell me)

InnoDB's main data file(s), ibdata..., will only grow when actually store more data - it reuses its own space - but they also never shrink.

That matters when you accidentally store a lot of data and fill a drive / logical volume.


To shrink it somewhat manually, the simplest (instruction-wise) is a simple-'n'-stupid:

  1. dump all your data to .sql files
  2. stop mysql
  3. remove the ibdata file
  4. start mysql (which creates ibdata files when they are missing)
  5. import the previously dumped data

Now very useful in production. And note that if you expect the stored data to ever grow this large, it's not very productive either.

ibd per table

If you expect to want to shrink files occasionally rather than rarely, you can make life simpler by using innodb_file_per_table.

After this setting is enabled, each newly created table is stored in its own (.idb) file. If you want to clear the space used by a misbehaving table, you only have to do the dump-delete-restoring for it.


The innodb_file_per_table setting can be helpful for backup - mysql can import .idb files (...that originated in the same mysql version (!)). See [1].

Splitting ibdata into multiple ibdatas

You can tell mysql to use multiple ibdata files.


This makes it possible to construct "use this drive up to this much, continue on that one," which on some setups can be handy.

Example: I have a huge ibdata1, and don't want it any larger on the same partition; I want it to continue on another partition.

innodb_data_home_dir=
innodb_data_file_path=/var/lib/mysql/ibdata1:15723M;/var/lib/mysql-moredata/ibdata2:2G:autoextend


Notes:

  • The 15723M in the example was my ibdata file rounded to the nearest MB (as suggested by this mysql.com page)
  • innodb_data_file_path seems to act as a first-come-first-serve list (that is, the first item with available space is used), so you can set this up far before you actually need it
  • innodb_data_home_dir apparently needs to be empty if you want to use absolute paths
  • You can of course do all sorts of symlinks tricks, such as symlinking ibdata2 from the same directory, or symlinking the directory (mysql-moredata was, in my example), so that most of the work is on the filesystem instead of the config, or the other way around.
  • It may be useful to set innodb_autoextend_increment larger than the default 8M
  • I'm not sure whether there are or are not conditions where it treats your precious data file as something to be zeroed for new use - there are some notes suggesting it may do this, so be very careful.

Backup/Replication

I expect you know your basic theory. Avoid a single point of failure, have an off-site backup if truly critical, that sort of thing.

SQL-level dump (mysqldump)

Dumping:

mysqldump dbasename -u user -p > dbasename.sql

#Or of all databases with:
mysqldump -u user -p --all-databases > dumpall-backup.sql

In practice you'd likely pipe the output through gzip or bzip2 for on-the-fly compression, like:

mysqldump -u user -p --all-databases | bzip2 > mysql.backup.sql.bz2


Restoring:

Since the dump outputs SQL, data can be restored by piping it to the mysql client. (If you have a .gz or .bz2 file you may like to use zcat resp. bzcat)


If you dumped a single database:

cat dbasename.sql | mysql -u user -p  dbasename  

If you dumped all and want to restore all:

cat dbasename.sql | mysql -u user -p

If you dumped all but want to restore a single database:

cat dbasename.sql | mysql --one-database dbasename  

(the last, roughly speaking, this tells mysql to pay attention to database select statements)(verify)


More notes:

  • You won't necessarily dump idempotent SQL, so you may have to use mysqlimport (verify)
  • You can list tables to use/ignore:
    • whitelist tables to dump by mentioning them after the database name,
    • or let it default to all, and optionally blacklist some with --ignore-table=name
  • Restoring these is slow, mostly on account of doing things correctly: generating indices, enforcing foreign keys, and binlogging; count on hours when you have any real amount of data. There are some minor speed improvements:
    • temporarily disable binlogs while restoring for some more speed;
    • temporarily disable FK checks if you know the data is correct (e.g. was dumped from a database with FKs enforced) and while no other data entry will happen.
  • This means it is a good option for disaster recovery and cheap off-site, offline backups, but not for things like five-nine uptime.
  • you may want to wrap the dump in SET AUTOCOMMIT = 0; and COMMIT when restoring it
  • if the engine type originally used is not supporting (or not enabled) in the database server you are restoring it to, the type will be implicitly (and possibly silently(verify)) changed, which may break your app.


Copying the data directory

You can simply copy the data files (ibdata* for InnoDB, subdirs/my* for MyISAM) when the database is not writing to it. This seems one of the few backup options that doesn't have notes like "Won't work with InnoDB," (I believe because it has checkpointing inside its data, so at worst you save automatically recoverable data) though it is somewhat safer to shut down the daemon to ensure the 'not writing' thing. Binlogs will save you from corruption (inconsistencies will be caught when the backup is loaded), but won't necessarily guarantee everything makes it into the backup.

Note however that data rarely if ever works in another version of the daemon, so if you upgrade with any regularity this is not a practical solution.

Slave replication

Slave replication means a master sends all the operations done on it to one (or more) slaves - in other words, it maintains copies of the database using only incremental reads.

Replication is done through MySQL binlogs, which exist to record every operation (essentially data journaling, a binary alternative to keeping incremental .sql files) which is also used to automatically recover from mid-operation crashes - up to the last recorded. The data safety is usually worth the slowdown which is slight in all but a few applications (exceptions including using a database as an huge, on-disk, and hashmap and changing it frequently)

Binlog notes:

  • Binlogs update from a particular data state to another, which is handled internally.
  • Binlogs can be flushed which means the logs will be emptied and start recording from the current database state. (e.g. FLUSH MASTER ((verify) this does exactly that and not more))
  • ...which is good for hard disk space, and can easily be done after a full backup - though details apply to replication setups (verify))
  • Just how up-to-date binlogs actually are depends on whether you set it to sync everything as soon as it can (which is slower)
  • ...and on whether the hard drive actually writes before it says it does. Many don't to seem faster.
  • This also means slaves will lag slightly behind their master.
  • Binlogs, if flished enough, mean that just copying the files that store the database works at all on live systems (verify), even if this counts on minor recovery when restored.

Replication notes:

  • Can be done from/to instantiations on the same host -
  • Slaves can be used to serve only selects, which takes load off the master (Note: slaves are not necessarily up to date, which may mess with assumptions if you use its data to change data in the master. Use with care and consideration).
  • Replication is good against failures, not against, say, mistakenly deleting all your data, as that too will be replicated - that is still a single point of failure.

For backup

In itself, the binlogs store only differences.

If you want to make full database dumps without the implied locking, one trick is to replicate your database to another server (or even another process on the same machine) and dump the database from it.

Using another computer loads the original less, and organizations may often have a test/redundant server around that various other database servers can use for this.

LVM snapshots

LVM is a partition manager that is relatively drive-agnostic and generally quite flexible. Interestingly, it allows making point-in-time snapshots of disk data - onto usused LVM space, which allows them to be copy-on-write.

Which means:

  • Since this is internal and copy-on-write, the initial snapshot command takes next to little to no time, CPU or disk space.
  • However, to guarantee correctness(verify), all reads/writes have to be blocked (LVM freeze)

while the command is being executed

  • The differences to the snapshot(verify) are then recorded as the disk data changes - like a smart disk-based diff. Note that on volatile enough databases this potentially grows the disk space taken to that of a file copy, and the overhead on the way makes this LVM way not worth it.
  • Q: What are the effects on a mysql daemon that didn't sync yet? Recovery to the last binlogged operation? (verify)

Note also this is mostly a shiny version of copying the data directory, so the same limitations apply.

Recovery

'Sorry, we are experiencing database trouble' is bad. In emergencies there is nothing to be done about that, but you may wish to consider that doing a .sql file reinsert takes hours, and may want to realize this before your boss is staring daggers at you.

Replication gives you the option of running read-only on old data -- if your code knows about this and doesn't e.g. assume alterations succeed (which a lot of lazy programming does). Even if the code knows, you probably want to inform your users that the site's not going to be working beyond looking around.

There are two things understood as recovery:

  • Restoring to the latest possible recoverable state
  • Restoring to the last backup
    • LVM data restore to snapshot is almost instant if your database is on its own




Permissions

MySQL has levels at which permission can apply:


Global, that is, per host+user+password combo. Yes, this means that the same username from different hosts may effectively be considered different accounts (although it is common to use the 'any' value for the host, in which case they are not)

DESC mysql.user;
SELECT host,user,password FROM mysql.user; 

Host-level, that is, per database+host combo (in mysql.host).

DESC mysql.host;
SELECT host,db from mysql.host;

Database-level, that is, per database+host+user combo

DESC mysql.db;
SELECT host,db,user FROM mysql.db; 

Table-level, that is, per host+db+user+table combo

DESC mysql.tables_priv;
SELECT host,db,user,table_name FROM mysql.tables_priv;

Even Column-level, that is, per host+db+user+table+column combo

DESC mysql.columns_priv;
SELECT host,db,user,table_name,column_name FROM mysql.columns_priv;

...and also for stored routines.

You can set all of the above with GRANT and REVOKE. See the docs on the GRANT Syntax, also for more general detail.


They all apply, and in the same order; mysql.user allows/denies connections to the mysql server, mysql.db and mysql.host allow/deny access to a database, mysql.tables_priv to tables, etc.

Practically, you should keep the rules as simple as possible, choosing just the one that restricts what you want. For example, web-app backing is generally not served by fine-grained control. People often create a single user with full permissions to the database that backs the app, for localhost and the web server (if those are different hosts).


Table level permissions are usually overkill, and primarily useful to shield yourself from accidents - changes that are never useful.



Viewing all that apply:

-- for the current user:
SHOW GRANTS;     
-- or for some other, optionally for a specific host:
SHOW GRANTS FOR 'user'@'host'


Changing them:

  • A superuser can change all details (obviously)
  • A non-admin user cannot change these rules at all.
  • A non-admin user that has been granted permissions at a level with the 'WITH GRANT OPTION' option has the ability to alter permissions -- at the specified level, and only those permissions they have themselves at the level that GRANT OPTION has been specified. (In multi-user-per-database setup that is supposed to be anally restrictive, this may be too permissive)
    • I believe that technically, you can only add these rules, and edit them by grantind different things with (exactly) the same details, but not delete them(verify) (although GRANT ALL PRIVILEGES is effectively the same thing).


Note that an interface like phpmysql makes administering permissions easier.


Further notes:

  • When you drop tables, any grants related to them are unaffected.


VARCHAR, TEXT. Also character sets and collations

tl;dr:

  • VARCHAR versus TEXT
Prefer VARCHAR when it fits your needs
VARCHAR is bounded by your definition, and stored in the row itself, so faster for smaller pieces of text you frequently use
TEXT is a pointer to storage outside the table, so slower to fetch. However, if you don't always fetch this column, then it leaves the table more compact, and the field itself is more flexible
Also:
indexes matter
TEXT is not allowed in MEMORY tables, so it looks that temporary tables that need TEXT fall back to MYISAM(verify)


  • charset/collation:
You need to declare what the data external to the database (i.e. what you have in the client) is, using SET NAMES.
It helps to make sure no additional client or client library oddities apply of that.


Longer story: Encodings in MySQL are complex, it entangles encoding, collation, character type and storage implementation -- and that's just the server part.


Among other details:

  • the TEXT type can be either returned as text, or binary data (a BLOB) depending on whether the collation is a binary variation (such as utf8_bin) or not (such as utf8_general_ci)
  • (...in fact, implementationwise the only difference between TEXT and BLOB is a single flag)
  • this does not apply to VARCHAR, which will return text even with collation utf8_bin

However, there are a good number of buts to varchars:

  • The amout of space each row takes is limited (65535 bytes? experiment suggests 32k.), which the length of CHAR and VARCHAR fields counts towards (contents of BLOB and TEXT fields do not).
  • ...this also seems to be the base of the maximum size of VARCHAR itself
  • VARCHAR seems to switch to different implementations depending on the size. Starting at 22000 characters of length, I had it interpreted as a MEDIUMTEXT (larger than TEXT, by the way). (The number seems to be rooted in the longest/worst-case multi-byte coding)
  • ...and ANY TEXT/BLOB variation returns as binary data, not text.


TEXT is much like BLOB (internally to MySQL, and in some external aspects). Like BLOB, there are variations on the type, with different size limits. There are four TEXT variations, namely TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT, with the same limitations as the respective BLOBs (256, 64K, 16M and 4G). Using the default TEXT may or may not bite you because of its 64KB limit.

See also http://dev.mysql.com/doc/refman/5.1/en/blob.html


Note the difference between character set and collation. Collation is a sort of functional subdivision of character sets that does not mean varying translation, but does mean different interpretation and may in a few mean a different implementation for storage. For example, the utf8 character set has collations utf8_general_ci, utf8_swedish_ci, utf8_bin, and I believe two dozen more, mostly languge-specific variations.

The more-or-less default UTF8 collation, utf8_general_ci, interprets your data, as do most/all non-binary others. That is, _ci means case insensitive, meaning that binarily non-equal strings may turn out to be seen as equal, which will likely conflict with your ideas of what should happen when you have this text in a PRIMARY or generally UNIQUE field, and/or an INDEX. Use utf8_bin if you want the database to just store your UTF verbatim and not act on it.

Here is behaviour that may not be obvious to you:

  • MySQLdb, VARCHAR column, utf8_general_ci collation: returned as text/unicode text
  • MySQLdb, VARCHAR column, utf8_general_ci collation: returned as text/unicode text
  • MySQLdb, TEXT column, utf8_general_ci collation: returned as text/unicode text
  • MySQLdb, TEXT column, utf8_bin collation: binary data

In the case of MySQLdb, the last means you get ([array.array() objects)

Note that the first three will either give you encoded data or give you automatically decoded python unicode strings (depending on how you connected). The array contains raw data - that is, calling .tostring() on it, will give you the UTF8 data that is inside the TEXT/BLOB field.


That aside, it seems that(verify):

  • A server has a has (config-file) defaults for character_set_server and collation_server.
  • Each database in it also has configurable defaults for character_set_database and collation_database.


  • When a client connects, it negotiates per-connection variables, character_set_connection and collation_connection, based on the last two and the client's configuration/wishes.
  • These last two values in turn control the possible need for translation, both ways:
    • character_set_client, which controls the translation for incoming query/update/insert commands before they are used
    • character_set_results, controlling what needs to be converted to before sending result sets to the client

Why these last two are separate is beyond me, but I'm sure there's someone ever this is useful to.


Note that the client may use its environment to negotiate something for the _connection variables, so the server and database values do not mean 'default for connections without exception.'


I could go on, but basically, "always use SET NAMES 'utf8' (or whatever charset) just after connecting, on each and every connection.", because the only way to be sure all the variables are correct is to make sure the per-connection variables are right. Trusting the database and client to do the right thing is asking for accidental data mangling.

Also, to be compatible and avoid a lot of future recoding, you should really consider using UTF8 for everything.


The variable business can also be explained in longer paragraphs. You actually have two commands, SET NAMES 'x', apparently equivalent to:

SET character_set_client     = 'x';
SET character_set_results    = 'x';
SET character_set_connection = 'x';   -- and this line implies:
SET collation_connection     = the current configured default is for character set 'x'.

If you want to specify a particular collation_connection, use SET NAMES 'x' COLLATE 'y'.

The other option, SET CHARACTER SET 'x', is similar, but more context dependent and so probably not as useful:

SET character_set_client     = 'x';
SET character_set_results    = 'x';
SET collation_connection     = the default for the current database; -- and this implies:
SET character_set_connection = the character set associated with that collation;

See perhaps also [2].


Note that this controls server-side translation, and the intent of this discussion is to match them all up so that at least the server will not change anything. This means that when the client sends UTF8, UTF8 gets stored.

Note that this may still be thwarted by your client doing some recoding.

Libraries

PHP

  • mysql: only partial support of certain features present in MySQL > 4.1
  • mysqli (improved): more support of said features, but only in PHP ≥ 5

(Said features include stored procedures, triggers, views, and precision math)

See also [3]


Python

  • MySQLdb, a.k.a mysql-python, is a DB-API2 interface
    • See also [4] for docs
    • Note: Pre-1.2.1 seems to have UTF8 problems.


Semi-sorted

This article/section is a stub — probably a pile of half-sorted notes and is probably a first version, is not well-checked, so may have incorrect bits. (Feel free to ignore, or tell me)

http://jeremy.zawodny.com/mysql/mytop/


mysql's utf doesn't necessarily cover uniciode

MySQL's default utf type is actually a 3-byte subset, meaning it does not support characters past U+FFFF, so fails on most emoji, CJK, and some characters used in the west.

This has nothing to do with Unicode, this is a design choice in MySQL (apparently mostly ensuring at table create/alter time that VARCHAR of a particular length can always be allocated, and won't lead to runtime errors. Using the utf8mb4 that sane people probably always want to use would force you to shorter definitions)



mysqld_safe

A simple wrapper/guardian script that is commonly used to start the actual mysql daemon as a child process.

mysqld_safe provides some simple sanity checks and such.


See http://dev.mysql.com/doc/refman/5.0/en/mysqld-safe.html


mysqld_safe taking ~100% CPU

This seems to be caused by it not having enough error handling of its own.

The following bug report seems to suggest that the script is too stupid to understand that the daemon may stop without its knowledge (or never start?), and is stuck in an infinite loop of system calls trying to get information about a mysql daemon child process that does not actually exist anymore (you can verify this with a strace).


You'll probably want to restart mysql. Probably the cleanest way is to stop mysql via service management, kill the offending process, and start mysql again. If you don't do that kill, you'll probably then have two mysql_safe processes, a well-behaving one that is the parent of the working daemon, and another one that is taking all CPU trying to contact one that doesn't exist.

Note that you'll probably have to do a kill -9 on the misbehaving process - a standard kill doesn't seem to affect it.


See also:

Overhead has nothing to do with fragmentation

MySQL reports overhead (probably most often spotted via phpmyadmin), but this does not report space wasted by fragmentation caused by mutations. The latter is usually several orders of magnitude more serious in terms of wasted space.

OPTIMIZE will improve both, though.

User management

This article/section is a stub — probably a pile of half-sorted notes and is probably a first version, is not well-checked, so may have incorrect bits. (Feel free to ignore, or tell me)

On timeouts

wait_timeout and interactive_timeout seem to control two things:

  • the time after which an active query is broken off (verify)
  • how long MySQL keeps around idle connections before disconnecting them - if this amount of seconds goes by since the last command was given, the MySQL server decides to drop the connection.

The default value for both is 28800, which is eight hours.

wait_timeout applies to most application connections so it usually matters a lot more than interactive_timeout, which applies to interactive logins such as the the mysql command line tool (it refers to the CLIENT_INTERACTIVE flag which that tool and others set when connecting).


Keeping wait_timeout high helps expose programming middle/back-end bugs such as forgetting to close connections (and possible to release locks), in that it leads to a ton of open sleeping connections that will stay around for eight hours hours, taking a tiny bit of memory, and a mysql connection slot.

It also means that queries that are way too long-running will get cut off -- but consider cases of high load when lowering the value.


If you did not write your code to deal with networking trouble or other reasons for disconnects, you may want to keep this high. If forgotten connection closes are leading to the 'Too many connections' error (since they will always build up) and you cannot debug the code, you may want to set this lower. This is at best a makeshift solution, though.

It seems that wait_timeout and interactive_timeout can also be set per session via a MySQL-specific SET, and sometimes indirectly via connection options / code. I assume it can only be lowered, not set higher than an installation-global setting, which may be low on some virtual hosting.


On data types

On the many BLOBs

MySQL chooses a particular subtype of BLOB at table creation time, which depends on the length you specify on BLOB:

  • TINYBLOB: up to 255 (2**8-1) characters.
  • BLOB: up to 65535 (2**16-1) characters.
  • MEDIUMBLOB: up to 16777215 (2**24-1) characters.
  • LONGBLOB: up to 4294967295 (2**32-1) characters.

If you specify no size, this will imply a TINYBLOB, which is likely to lead to problems.


On enum, and booleans

MySQL's ENUM is somewhat like an input constraint, but it only accepts strings.

It can take up to 65535 distinct values. It will take 1 byte when there are 255 values or fewer (and is defined NOT NULL), 2 bytes for 256 to 65535 values.


As an enumeration, it stores them as integers, so ENUM is ideal when you are storing just a few states, say, 'PENDING', 'BUSY' and 'DONE', and want this distinction stored in a single byte instead of the ~8 bytes that a VARCHAR(7) would imply for this case.

You could do this on the client end, using a TINYINT with values 1,2,3, but you'ld have to figure out what each integer means. With an ENUM, all communication uses strings instead, so that's all the client sees.


As to internals: it uses an 8-bit or 16-bit int. It couldn't use a single bit for a an ENUM over '0','1' (you may want 't','f', 'true','false' 'yes','no', 'y','n', or something else that is clearly a string, though) if it wanted to because it's enumerated as as 1,2.

This means there is little difference between that BOOL NOT NULL, ENUM('true','false') NOT NULL, CHAR(1) NOT NULL, TINYINT NOT NULL (a.k.a. a byte, 0-255) other than in the way the client uses it.

The main difference is that:

  • ENUM implies an extra input check - but returns thigngs as strings, so '0','1' is not a good idea.
  • CHAR(1) is perhaps most obvious when you want to store 't' and 'f'
  • TINYINT(1) is just an integer, you do all the work
  • BOOL/BOOLEAN fields (synonyms for TINYINT(1)) are considered true for any non-zero value. You can use TRUE,FALSE constants that MySQL offers, though this is not strictly SQL-compatible so there is a gotcha or two.


Note that when altering a column from ENUM to INT, the values it sets are those used internally in the enumeration. This may look weird if you were enumerating something like '0','1'.


On BIT and SET

You can use BIT fields (1 to 64 bits) and there are a number of bitwise operations you can do server-side.

Bitwise operations are done with 64-bit arithmetic.

On disk, as many bytes as will fit the amount of bits is needed.

Before 5.0.3~5.0.5, BIT was instead a synonym for TINYINT(1)


SET is similar to BIT in storage requirements, but the bits are used separately. You can use them bitwise, and also use the names you gave them.

The way these fields are handled is nicer than using an appropriately-sized INT in a bitwise way -- but only slightly.


On integers

Integers:

  • TINYINT: 8-bit int: -128 to 127, or 0 to 255 when UNSIGNED.
  • SMALLINT: 16-bit int: -32768 to 32767 or 0 to 65535.
  • MEDIUMINT: 24-bit int (on disk, most likely 32-bit in memory): -8388608 to 8388607 or 0 to 16777215.
  • INT and INTEGER: 32-bit int: -2147483648 to 2147483647 or 0 to 4294967295.
  • BIGINT: 64-bit integer: -9223372036854775808 to 9223372036854775807 or 0 to 18446744073709551615 (but with some gotchas if you do any database-side calculations)


Messages and warnings

InnoDB: The InnoDB memory heap is disabled

Seems to mean that innodb_use_sys_malloc is set to 1, meaning that allocations will be done from system memory and not from InnoDB's own memory allocation (which used to be a better choice than the system allocator on some systems).

Note that this affects whether another innodb memory setting or two is used or not.


Errors

MySQL server has gone away

The formulation is misleading -- it suggests we already had a connection, then lost it. Actually, the message doesn't necessarily mean there was ever a server involved at all.

It can mean pretty much anything related to the connection to the MySQL server.

Reasons include:

  • Attempt to reuse a connection that was since closed (and reconnection is disabled / buggy)
    • ...because of a networking timeout
    • ...because of a MySQL timeout (hosters may configure mysql to disallow long-running queries, and/or to auto-disconnect idling connections)
    • ...because it was KILLed.
    • ...because MySQL is shutting down(verify)
    • ...some other reason for being disconnected
  • You sent a packet larger than the current max_allowed_packet value (i.e. query size limit too low / packet too large)
  • multiple forks of a client try to use the same connection to a MySQL server
  • implication of / past bug in MYSQL_OPT_READ_TIMEOUT / MYSQL_OPT_WRITE_TIMEOUT


But also more trivial reasons like:

  • Hostname lookup failure
  • There is no MySQL server running at the specified network location
  • A firewall prevents you from connecting
  • The MySQL server's connection limit prevents you from connecing
  • MySQL security prevents you from connecting
  • MySQL crashed while we were connected


See also:


(Foreign key errors)

MySQL seems to have two FK errors for most of the a dozen or two possible problems:

Errno 150 "Can't create table './dbname/#sql-something.frm' (errno: 150)"

Probably the most common FK error, which can mean:

  • one of the above requirements wasn't met
  • the table you are referencing does not exist (e.g. misspelled)
  • you are trying to drop a UNIQUE or INDEX on something that has a FK
  • a number of other things
Errno 152 ('Error on rename')

Likely means that there is no foreign key constraint with that name on that table (probably because you misspelled it)

#1452 - Cannot add or update a child row: a foreign key constraint fails ...

Means that the data currently in the table violates the constraint you are trying to add. You will have to clear that up first.

This article/section is a stub — probably a pile of half-sorted notes and is probably a first version, is not well-checked, so may have incorrect bits. (Feel free to ignore, or tell me)

"ib_logfile0 of different size"

MySQL/InnoDB-related error.In the logs, it will show something like:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 52428800 bytes!

This happens when you change the innodb_log_file_size and forget to remove the current logfiles before restarting MySQL.

MySQL is happy to create new ones with the size you have specified when they do not exist, so the solution is to remove the ib_logile files (BUT first make sure the last shutdown was clean -- otherwise you may need the logfiles for recovery).


(From one view, MySQL is too dumb to realize this value can change. From another, MySQL is playing safe, and assumes that a changing size could indicate some problem.)

ERROR: the age of the last checkpoint is ...

ERROR: the age of the last checkpoint is [...]
which exceeds the log group capacity [...].
If you are using big BLOB or TEXT rows, you must set the
combined size of log files at least 10 times bigger than the
largest such row.

This refers the log of recent changes (filenames starting with ib_logfile), which are used for crash recovery, specifically to detect commands whose changes were applied to memory but not yet written to the table on disk. The error reports that something was not logged to the logfile(verify) meaning that the change cannot be recovered if the server crashes (fairly immediately) after.


In practice, it's proably that your innodb_log_file_size was left at the default 5MB, and you are using large amounts of literal data (large text/binary data are the usual culprits).

If you want such binary data commits to be replayable, set innodb_log_file_size higher. Note that setting it high means crash recovery may take longer, so set it to something that will comfortably fit your largest request, times some constant (the error suggests 10). The order of 100MB isn't crazy if you do things like storing large blocs in your database, while 1GB is overkill for most uses.

Note that when you change this size in the configuration, you have to remove the current logs before restarting mysql.


(does load affect this?(verify))

You can't specify target table 'name' for update in FROM clause

This article/section is a stub — probably a pile of half-sorted notes and is probably a first version, is not well-checked, so may have incorrect bits. (Feel free to ignore, or tell me)

Basically, you can't use the same table you want to UPDATE or DELETE from in a condition, for example in a subquery that uses (a join involving) that same table.

My guess is that it does this to avoid correlated subqueries, which is fairly sensible, as you should always avoid those if you can.

In some cases, explicitly selecting into a temporary table may be the useful solution.

It also bombs out e.g. when DELETEing using a subquery that is conceptually just a temporary table, in which case the error is not very sensible. You can get around it without explicitly using temporary tables by wrapping the subquery in another one: SELECT * FROM (subquery) AS dummyname.


Unknown table engine 'InnoDB'

Possible reasons, the more obvious first, include:

  • not having InnoDB compiled in
  • having skip-innodb in your my.cfg/my.ini
  • InnoDB failing to initialize, e.g. because of
    • logfile corruption (check your mysql error log)
    • having an innodb setting that makes it fail to start up. Even innocent looking settings can do this.



18446744073709551615

This article/section is a stub — probably a pile of half-sorted notes and is probably a first version, is not well-checked, so may have incorrect bits. (Feel free to ignore, or tell me)

...which is 2^64-1 (i.e. -1 as a 64-bit signed taken as an unsigned)

Seems related to a cases of overflow, and a few bugs past and present.


phpmyadmin notes

Configuration: Mostly in config.inc.php, which you can copy from config.sample.inc, or perhaps from another installation where you figured a bunch of things out.


See also