The MySQL Table Checksum, part of the MySQL Toolkit (having to be renamed soon) is an invaluable community tool for use with MySQL. Most sites or installations of any volume will use MySQL Replication for one purpose or another, be it for read scalability, backups, testing, upgrading etc.

Why is it needed?
There are two primary compelling reasons. First, MySQL replication is an asynchronous process and there is no absolute guarantee that the Master Database and the Slave Database are the same (By definition that can be different). Second, MySQL does not provide any tools relating to checking, managing, reporting differences. Luckily the community has addressed this present lack of product feature in current versions of MySQL. Special thanks to Baron who has made this happen.

Should I be concerned?
Yes, you should. While some people will not like that I’ve made this alarming comment, a lot of organizations use MySQL Replication for backups. It would be unprofessional to be ignorant to make the assumption.

How to I get it?
Download MySQL Table Checksum. It is recommended that you download all components in the toolkit as there are many valuable tools then just this one.

MySQL Table Checksum will require Perl which is generally always installed with any Linux distribution by default (For Windows you need to do yourself). You will also require the Perl Modules DBI and DBD::mysql. Use CPAN for easy installation of these modules.

These are the steps I did (NOTE: this is a specific version, you should always check for newer versions)

wget http://internap.dl.sourceforge.net/sourceforge/mysqltoolkit/mysqltoolkit-1204.tar.gz
tar xvfz mysqltoolkit-1204.tar.gz
cd mysqltoolkit-1204/bin
./mysql-table-checksum

If everything is installed correctly, you should see.

Usage: mysql-table-checksum [OPTION]... HOST [HOST...]

Errors in command-line arguments:
  * No hosts specified.

mysql-table-checksum checksums MySQL tables efficiently on one or more HOSTs.
Each HOST is specified as a DSN and missing values are inherited from the first
HOST.  If you specify multiple HOSTs, the first is assumed to be the master.
For more details, please use the --help option, or try 'perldoc
mysql-table-checksum' for complete documentation.

How do I use it?
Start with the documentation, RTFM

perldoc mysql-table-checksum

There are a number of ways to run MySQL Table Checksum and different means of using arguments etc. This is what I do.

By Default:

./mysql-table-checksum u=root,p=sakila,h=localhost

DATABASE TABLE       CHUNK HOST      ENGINE      COUNT       CHECKSUM TIME WAIT STAT  LAG
xxx      table1          1 localhost InnoDB       NULL     1678710928    0    0 NULL NULL
xxx      table2          1 localhost InnoDB       NULL     3023415523    0    0 NULL NULL
xxx      table3          1 localhost InnoDB       NULL     1692517818    0    0 NULL NULL
xxx      table4          1 localhost InnoDB       NULL              0    0    0 NULL NULL
xxx      table5          1 localhost InnoDB       NULL     2295061143    0    0 NULL NULL
xxx      table6          1 localhost InnoDB       NULL     2238111875    0    0 NULL NULL
xxx      table7          1 localhost InnoDB       NULL      823770692    0    0 NULL NULL
xxx      table8          1 localhost InnoDB       NULL     2313561225    3    0 NULL NULL
xxx      table9          1 localhost InnoDB       NULL     3524358173    0    0 NULL NULL
xxx      table10         1 localhost InnoDB       NULL              0    0    0 NULL NULL

The output will provide a checksum via the MySQL CHECKSUM function. As this doesn’t provide a row count, the COUNT column is NULL.

A better method is to use the –replicate function. This provides the results into a Database Table (handy for lots of things) as well as getting table counts. This requires a pre-requisite table. For this example I’ve added it to the test database schema.

$ mysql -uroot -psakila test
mysql > CREATE TABLE checksum (
                db         char(64)     NOT NULL,
                tbl        char(64)     NOT NULL,
                chunk      int          NOT NULL,
                boundaries char(64)     NOT NULL,
                this_crc   char(40)     NOT NULL,
                this_cnt   int          NOT NULL,
                master_crc char(40)         NULL,
                master_cnt int              NULL,
                ts         timestamp    NOT NULL,
                PRIMARY KEY (db, tbl, chunk)
             );

My execution for this using this table

./mysql-table-checksum u=root,p=sakila,h=localhost --replicate=test.checksum

DATABASE TABLE    CHUNK HOST      ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
xxx      table1       1 localhost InnoDB       2236 cdd6689dbb23ff547540561c4815c717b3d01bf3    0 NULL NULL NULL
xxx      table2       1 localhost InnoDB       3157 f4d538a7a83168acb2cf3374a6edc4949809e723    0 NULL NULL NULL
xxx      table3       1 localhost InnoDB         99 daa0d8403feb200beac5db6456e92c86de6a9b84    0 NULL NULL NULL
xxx      table4       1 localhost InnoDB          0                                     NULL    0 NULL NULL NULL
xxx      table5       1 localhost InnoDB       1006 118b996e10f76153b786479b15f134c08cd21b62    0 NULL NULL NULL
xxx      table6       1 localhost InnoDB    2499397 6a60e07f45d5980bb5de3bd75e1b2ce1e5be25b8   29 NULL NULL NULL
xxx      table7       1 localhost InnoDB      11879 00929c2d6b9278f585b6b7346816bb7a0e483b9e    0 NULL NULL NULL
xxx      table8       1 localhost InnoDB        675 e8e17f9ed15986965ff678ca0c26cd5397ea272d    0 NULL NULL NULL
xxx      table9       1 localhost InnoDB    1730498 373e7fb3fec77b1d0edcfb6a5e28619e9c91d5e4   25 NULL NULL NULL
xxx      table10      1 localhost InnoDB    8557931 7913803bf65bbcf18e679255c657dba53045b88e   99 NULL NULL NULL

The screen output is great, I actually run the following command keeping the output. See later for more info,

./mysql-table-checksum u=root,p=sakila,h=localhost > checksum.mysql4.replicate.`date +%y%m%d.%H%M` &

The table contents are a little different.

mysql> select * from checksum limit 1G
*************************** 1. row ***************************
        db: xxx
       tbl: table20
     chunk: 1
boundaries: 1=1
  this_crc: f7c4d19f6342105f97a4b2770ee7b9094e57a6f2
  this_cnt: 2236
master_crc: f7c4d19f6342105f97a4b2770ee7b9094e57a6f2
master_cnt: 2236
        ts: 2007-11-08 15:37:16

What is most impressive is that in a Master/Slave environment these commands are all replicated, and a simple comparison of data in this table on each slave will confirm if your database is in sync. The docs also confirm, the structure of the tables are not compared so a slave may indeed have some changes.

Migration?
I’ve been able to use the checksum to test a MySQL 4 to MySQL 5 migration. I’ve been able to confirm via checksums as an initial verification that the data has indeed been loaded correctly.

The issue was using the log files for comparison between to instances is the TIME column value changed. As the format of the rows is spaced, it was not possible to easily cut as each schema name was a different length. A small inconvience for the benefit in the results.

I have found however that optimizing the schema in 5, e.g. INT for BIGINT and TIMESTAMP for DATETIME did cause the checksums to fail.

Conclusion
This is a quick introduction, this tool has a lot to offer and is only one in a whole toolkit.

I needed to start mysql without privileges after a database restore today, and while confirming the correct option which was –skip-grant-tables I came across an option which made me laugh.

$ mysqld --verbose --help
...
  --sporadic-binlog-dump-fail
                      Option used by mysql-test for debugging and testing of
                      replication.
...

And here is the Official Manual Entry

If you didn’t get your proposal in for MySQL Conference 2008 , that’s too bad.

I often wondered from past conferences why submissions were needed so early, like 5 months before. Well, as being invited to be part of the MySQL Conference committee this year I now know why, and have a greater appreciation. With near 300 submissions, it takes time to review them, and this is just the first step in a number to get to a completed schedule for next years conference.

Also getting a sneak preview of what’s to come is really cool! I’m already excited.

This month I continued my Performance Analysis talks at the Local NY MySQL Meetup. Previous discussions can be found here.

Our focus was a more in-depth look at gathering and reviewing MySQL Status and your applications SQL statements using MySQL Proxy. Even after preparing the slides over the weekend Jan added more functionality that was particularly interesting. So today while addressing a client issues I further extended this work to do even more funky monitoring.

Today’s monitoring.lua script does:

• Logs to file, Date/Time, Query Time, Response Time, Rows Affected, Normalized SQL and Actual SQL for each query
• Has histogram of tables used with read/write figures.
• Has histogram of SQL with avg and max execution times.

You get the best of both worlds, you can see SQL access live, get statistics of this, and then be able to drill down to every instance of a given SQL statement for more information.

You can get a copy of my slides Here. You can get a full tar of my demo work here. This includes MySQL Proxy, My Bench for some benchmarks and the Sakila Sample Database.

I am of the attitude, the day you stop learning something is the day you die. I’m not prepared to induce MySQL into both sides of that equation, however some days it never ceases to amaze me what little thing I didn’t know about MySQL.

Today I saw in reviewing SQL statements for an application SELECT LAST_INSERT_ID(). No big deal, that is expected, however I then saw UPDATE … SET id=LAST_INSERT_ID(id+1) WHERE …

Having never seen this syntax I was forced to review it’s usage. See MySQL Documentation

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

Seems like a layman’s idea of Sequence support, but you are still restricted with the problems of a manual implementation. Transactions support, and if you use MyISAM, table level locking.

I was asked a question today, “DATETIME vs TIMESTAMP. When to use which & why?”

It’s a good MySQL introduction question, here are some general considerations for choosing one.

Do you need Date values other then an EPOCH value? (i.e. before 1970) If the answer is yes, then DATETIME is required.
If you do not however, then TIMESTAMP is the best choice for a few reasons.

1. The TIMESTAMP columns uses 4 Bytes to record it’s value, while DATETIME uses 8 bytes. Using the smallest storage is always a best practice for all columns.
2. The TIMESTAMP column supports the CURRENT_DATE syntax in the CREATE TABLE command. This enables the column to have a default value for INSERT or for UPDATE, but not both. Indeed this is the only data type that allows for any default value that is not a constant.
3. All date functions (at least the ones I use) work equally as well with TIMESTAMP and DATETIME.

I have yet to find any benchmarking to indicate any performance differences of not selecting TIMESTAMP.

And just for a piece of trivia, the DATE datatype is 3 bytes, the TIME datetype is 3 bytes, so why is the DATETIME 8 bytes?
Yes, for those that intend to reply I do know the answer, however others readers may not. Comments please!

If you have not been to the www.mysql.com website, it’s a new look.

Yesterday I attempted to get a working MySQL environment to support the number of utilities we all use including mytop, innotop, mybench, mysqltoolkit. These products require a number of Perl Dependencies, and while that may be a rather trivial task under Linux and with the power of cpan, working on Solaris is a whole different story.

For the record, I’m working with Solaris 9 SPARC 64bit.

I won’t detail you with how hard it was to get to this point, except to say thanks to Jeremy, Baron and Frank so far. Here is where I’m at.

You need a number of pre-requisites, most from sunfreeware.com

Pre-Requisites

$ wget ftp://ftp.sunfreeware.com/pub/freeware/sparc/9/libiconv-1.11-sol9-sparc-local.gz
$ wget ftp://ftp.sunfreeware.com/pub/freeware/sparc/9/gcc-3.4.6-sol9-sparc-local.gz
$ wget ftp://ftp.sunfreeware.com/pub/freeware/sparc/9/make-3.81-sol9-sparc-local.gz
$ gunzip libiconv-1.11-sol9-sparc-local.gz gcc-3.4.6-sol9-sparc-local.gz make-3.81-sol9-sparc-local.gz
$ pkgadd -d libiconv-1.11-sol9-sparc-local
$ pkgadd -d gcc-3.4.6-sol9-sparc-local
$ pkgadd -d make-3.81-sol9-sparc-local

$ wget http://search.cpan.org/CPAN/authors/id/A/AB/ABURLISON/Solaris-PerlGcc-1.3.tar.gz
$ gunzip Solaris-PerlGcc-1.3.tar.gz
$ tar xvf Solaris-PerlGcc-1.3.tar
$ cd Solaris-PerlGcc-1.3
$ perl Makefile.PL
$ make install
$ wget http://search.cpan.org/CPAN/authors/id/M/MS/MSCHWERN/ExtUtils-MakeMaker-6.36.tar.gz
$ gunzip ExtUtils-MakeMaker-6.36.tar.gz
$ tar xvf ExtUtils-MakeMaker-6.36.tar
$ cd ExtUtils-MakeMaker-6.36
$ perl Makefile.PL
$ make install

Back to installing

$ PATH=/usr/local/bin:$PATH;export PATH
$ PATH=/usr/perl5/5.6.1/bin/:$PATH;export PATH

$ perlgcc Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for Term::ReadKey
netcontrol1:/tmp/TermReadKey-2.30# make install
make: *** No rule to make target `/usr/perl5/5.6.1/lib/sun4-solaris-64intConfig.pm', needed by `Makefile'.  Stop.

This is where the trail ends as my friend google has not helped me past this point, so I’m turning the community. If anybody can assist please let me know.

I realized recently from observation that some smaller websites which use MySQL do not have a working backup and recovery strategy. Some may have a backup approach but it will not work in a recovery. As part of a number of discussions on Backup & Recovery here is part 1.

Using straight ‘cp’ for Backup

Using ‘cp’ to simply copy your running database *DOES NOT WORK*. You may be lucky, but in a world of guarantees, this is no guarantee that your can recovery your system. Why is this so.

• The data is not consistent during the backup. If it takes say 5 minutes to copy your files, they are copied probably in alphabetical order, what if data is written during the backup to a table starting with ‘A’ and a table starting with ‘Z’, the A file has already been copied.
• When using MyISAM, Indexes are not flushed to disk unlike Table data. This means that while MyISAM has the facility of recover and rebuild indexes using myisamchk, you need to know if you have corruption, you need to rebuild your tables offline, and there is not assurance your data can be corrected.
• Likewise with using Innodb, Data and Indexes are not flushed to disk. While Innodb does flush the redo logs to enable crash recovery, I have seen on a production website, granted 4.1 that Innodb failed to recover and caused major downtime and serious business ramifications.

Using ‘cp’ correctly

In order to copy your database, there are two different ways. The first is to shutdown your database first then copy your data files. Be sure to also copy your my.cnf file, this is important in a recovery process. The second is to use FLUSH command. For example.

mysql> FLUSH TABLES WITH READ LOCK;
-- In second session copy all data
mysql> UNLOCK TABLES;

The advantage is you don’t have to shutdown your instance, the disadvantage is that FLUSH TABLES will halt any WRITE statements to your database, so depending on the time to copy your data files that could have significant effect on your system. In a high write environment the FLUSH command could also take a significant time to complete.

I’ll leave with one simple tip. You should always have sufficient diskspace on your machine for performing the backup. This is important for two parts. First, if you say compress your backup during the process, you are taking a longer time then a straight copy and your database is unavailable longer. Second, over 90% of any recovery occurs with last nights backup, and the time take to uncompress the backup affects your recovery time, particularly in a large system. Your ‘cp’ should indeed be a two phase process. First ‘cp’ on the local server for a local backup, then have an off server compressed backup.

In my next Part, I’ll discuss more alternatives to improving your backup strategy.

References

FLUSH TABLES

You have to love error messages some times. Today is was “ERROR 1289 (HY000): The ‘UNKNOWN’ feature is disabled;”

For those interested.

mysql> select version();
+-------------+
| version()   |
+-------------+
| 6.0.0-alpha |
+-------------+
1 row in set (0.00 sec)
mysql> set session sql_mode=NO_ENGINE_SUBSTITUTION;
Query OK, 0 rows affected (0.00 sec)

mysql> create table T2 (id INT UNSIGNED NOT NULL) ENGINE=InnoDB;
ERROR 1289 (HY000): The 'UNKNOWN' feature is disabled; you need MySQL built with 'UNKNOWN'
to have it working

In this case it’s obviously something that’s not quite right, so being a good community contributor I raised a bug. You can read more at Bug #29373.

MySQL values the contributions from the community, even as trivial as documentation or error messages. If you have an inquiry raise it at the forums or the lists, or if it’s obviously an issue raise it in the Bugs System

When you use SHOW STATUS can can restrict with the LIKE syntax, allowing for a subset of values. For example:

mysql> SHOW GLOBAL STATUS LIKE 'Com%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Com_admin_commands       | 0     |
| Com_alter_db             | 0     |
| Com_alter_table          | 0     |
| Com_analyze              | 0     |
| Com_backup_table         | 0     |
| Com_begin                | 0     |
| Com_change_db            | 0     |
| Com_change_master        | 0     |
...

That’s great, but sometimes you want specific values. Using WHERE can achieve this. For Example.

mysql> SHOW GLOBAL STATUS WHERE VARIABLE_NAME IN (’Com_insert’,'Innodb_buffer_pool_pages_latched’,'threads_running’);
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Com_insert                       | 0     |
| Innodb_buffer_pool_pages_latched | 0     |
| Threads_running                  | 1     |
+----------------------------------+-------+
3 rows in set (0.00 sec)

Cool, the downside is you loose the wildcard capability, however you can string commands together with LIKE

mysql> SHOW GLOBAL STATUS WHERE VARIABLE_NAME LIKE 'innodb%' OR VARIABLE_NAME LIKE  'com%';

Last night at the MySQL NY Meetup we continued on from a very successful July presentation on “Practical Performance Tips & Tricks”. I must admit after speaking and standing all day for the MySQL DBA Bootcamp for the Oracle DBA it was a stretch, and we didn’t cover all material as expected, but the evening was still very productive to everybody. Links are here for my August Presentation and July Presentation.

Thanks to Marc and the team from LogicWorks for again sponsoring our NY Meetup Event. We don’t get the beer and food any other way.

As a consultant working on client sites even in the time from the previous meeting, I see a number of simple steps that can be applied to every single MySQL environment and last nights talk placed some focus on this. The following is the homework that has been set for attendees, so next month we can have a panel discussion and then continue more on the analysis of MySQL.

This homework focuses on three areas. It seems a lot, but it will be worth it.

1. Collection of SQL statements (in various forms)
2. Monitoring of raw MySQL Status information
3. Review of Schema Data Types for more optimal storage

Collection

This information is to be collected.

• Production is to have slow query log enabled and long_query_time =1
• Daily review of Production slow query log to view the longest running and most frequent (Slide Aug-13)
• All development and test environments have general log and slow query log enabled.
• All application paths to be tested in development or testing, to capture all SQL. Use self documenting approach (Slide: Aug-21)

Outputs
Your outputs will include offending SQL statements via the Slow Query log that require investigation, extra credit for doing an EXPLAIN and a SHOW CREATE TABLE for each table in the query for analysis of improving the queries.
You will have a review of every SQL statement. A simple desk check will determine are there any queries which are exact duplicates, are there any queries that could be combined (as they use the same where condition). Are there any queries that are repeated a lot, but with only changing values. These are a clear candidate for combining into a single query.

MySQL Status Information

The MySQL Status can provide help into where to look for problems. This is the raw fundamentals that either home grown scripts or MySQL Network Monitoring and Advisory Service use to provide monitoring of important information (e.g. Buffer Hit Ratio) and can provide alerts of present or potential pending problems. We will not be reviewing all these status (~250) or developing any detailed analysis, however this is designed to show you the building blocks.

You should collect figures for 1 minute, 1 hour and 1 day that represent appropriate load on your system.

1 Minute

$ mysqladmin -uroot -p -r -i 1 -c 60 extended-status | grep -v " | 0 " > minute.log

1 Hour

$ mysqladmin -uroot -p -i 60 -c 60 extended-status > hourly.log

1 Day

$ mysqladmin -uroot -p -i 3600 -c 24 extended-status > daily.log

Extra credit for downloading Statpack and doing an analysis of the statistics. The trick is to take the first and last status output from each of the Hourly and Daily logs to get a 1 hour picture and a 1 day picture.

Review Schema

For reviewing the schema, try to use a static production sized copy (e.g. a copy of production in a test environment). This ensures you don’t make changes that will cause your production system to fail, and before and after figures are for the same data set as it’s not changing during production use.

• Calculate size of present data
• Review schema for immediate size improvements (Slide Jul-59)
• Apply size improvements and re-run to see size improvements.

SQL

Calculate Size of all schemas

select table_schema, count(*) as tables,
           sum(data_length+index_length)/1024/1024 as total_mb,
           sum(data_length)/1024/1024 as data_mb,
           sum(index_length)/1024/1024 as index_mb
from information_schema.tables
group by table_schema
order by 3 desc;

Calculate Size of all tables for given schema

use test;
select table_name,engine,row_format,avg_row_length,table_rows,
          (data_length+index_length)/1024/1024 as total_mb,
          (data_length)/1024/1024 as data_mb,
          (index_length)/1024/1024 as index_mb
from information_schema.tables
where table_schema=database()
order by 6 desc;

We have all done it in the past, and probably most people that read this (will admit| or lie) to still doing it, but everybody must start making an effort to improving MySQL security in the usage on your MySQL Installation, including just on your laptop, and in presentations that people read.

I spotted a reference article on Planet MySQL this evening and without looking at the details the syntax presented typifies two basic and fundamental 101 MySQL security issues.

1. Always, always, always have a password for a MySQL account, especially for the ‘root’ user.
2. Don’t use the ‘root’ user unless you really have to. The SUPER privilege is just that SUPER, there are many things you really don’t want every person accessing to have. In a larger environment you want to ensure good principles, but also in single developer environment.

I was asked on Thursday at a seminar I was giving. “Our developers login as ‘root’ for development and use ‘root’ for MySQL access, is this bad?”. To which my simple response was “Yes”.

I think MySQL should improve the product and not allow a root user without a password. You can of course use mysql_secure_installation to set this, but it’s not a mandatory step. You can also use SQL_MODE to ensure users can’t be created without a password, in both instances the horse has already bolted.

While using the latest MySQL 5.1.20 yesterday I came across another situation that was not expected as with previous editions of MySQL. The background is experimenting with DRBD. When I configured MySQL to startup with a /etc/my.cnf file with data on a DRBD partition I got a failed startup error message with mysqld_safe.

$ bin/mysqld_safe &
[1] 12615
070720 10:10:42 mysqld_safe Starting mysqld daemon with databases from /drbd/data
070720 10:10:42 mysqld_safe mysqld from pid file /drbd/data/newyork.localdomain.pid ended

Ok. Well this happens so I went to the data directory to look for `hostname`.err.

$ cd /drbd/data
$ ls -l

What the! There is no error log. Then the discussion started about this. Apparently mysqld_safe now uses syslog (e.g. /var/log/messages) for logging messages. Ok, but where is the line between mysqld_safe and mysqld. There was some confusion here about this, however investigation of /var/log/messages showed what was necessary to at least find the error.

$ tail -30 /var/log/messages
Jul 19 16:59:51 newyork mysqld_safe[6156]: Starting mysqld daemon with databases from /drbd/data
Jul 19 16:59:54 newyork mysqld[6160]: /usr/local/mysql/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
Jul 19 16:59:54 newyork mysqld[6160]: [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: The first specified data file ./ibdata1 did not exist:
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: a new database to be created!
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Setting file ./ibdata1 size to 10 MB
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Database physically writes the file full: wait...
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Log file ./ib_logfile0 did not exist: new to be created
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Setting log file ./ib_logfile0 size to 5 MB
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Database physically writes the file full: wait...
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Log file ./ib_logfile1 did not exist: new to be created
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Setting log file ./ib_logfile1 size to 5 MB
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Database physically writes the file full: wait...
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Doublewrite buffer not found: creating new
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Doublewrite buffer created
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Creating foreign key constraint system tables
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Foreign key constraint system tables created
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Started; log sequence number 0 0
Jul 19 16:59:54 newyork mysqld[6160]: [ERROR] /usr/local/mysql/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13)
Jul 19 16:59:54 newyork mysqld[6160]: [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13)

To ensure backward compatibility now with previous versions of MySQL, it is necessary to specific in my.cnf the log-error parameter.

#my.cnf
[mysqld]
log-error = /drbd/data/mysql.err

Starting gives you expected error logging.

$ more /drbd/data/mysql.err
070720 10:16:30 mysqld_safe Starting mysqld daemon with databases from /drbd/data
/usr/local/mysql/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
070720 10:16:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
070720 10:16:30  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
070720 10:16:30 mysqld_safe mysqld from pid file /drbd/data/newyork.localdomain.pid ended

So, you need to read the fine print in the Changes in release 5.1.20 (25 June 2007) 5.1 Manual you get.

Incompatible change: mysqld_safe now supports error logging to syslog on systems that support the logger command. The new –syslog and –skip-syslog options can be used in conjunction with the –log-error option to control logging behavior, as described in Section 5.3.1, “mysqld_safe — MySQL Server Startup Script”. The default is to use syslog, which differs from the previous default behavior of writing an error log file. To maintain the older behavior, use the –log-error option. (Bug#4858)

If you read mysqld_safe — MySQL Server Startup Script you also see the following note.

Note

As of MySQL 5.1.20, the default error logging behavior is to write errors to syslog on systems that support the logger program. This differs from the default behavior of writing an error log file for versions prior to 5.1.20. To maintain the older behavior, use the –log-error option.

RTFM those release notes!

MySQL recently released 5.1.20 beta. I just ran into another gotcha, which prompted me to post this draft I’ve had for a while.

rbradford@newyork:/opt/mysql51$ scripts/mysql_install_db
Installing MySQL system tables...
070709 23:24:08 [Note] Plugin 'InnoDB' disabled by command line option
OK
Filling help tables...
070709 23:24:08 [Note] Plugin 'InnoDB' disabled by command line option
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h newyork password 'new-password'
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

The latest information about MySQL is available on the web at

http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

Wait, there is an Innodb error message I’ve not seen . I’ll have to look into that.

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                        | Transactions | XA  | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO  | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO  | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO  | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO  | NO         |
| FEDERATED  | YES     | Federated MySQL storage engine                                 | YES          | NO  | NO         |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO  | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO  | NO         |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
8 rows in set (0.00 sec)

mysql> show plugins;
+------------+--------+----------------+---------+---------+
| Name       | Status | Type           | Library | License |
+------------+--------+----------------+---------+---------+
| binlog     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| partition  | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| ARCHIVE    | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| BLACKHOLE  | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| CSV        | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| FEDERATED  | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| MEMORY     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| InnoDB     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| MyISAM     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
+------------+--------+----------------+---------+---------+
10 rows in set (0.00 sec)

Yet, it works fine! So what does message “[Note] Plugin ‘InnoDB’ disabled by command line option” really mean. On closer inspection it doesn’t say Error, however it’s a different message from previous installs, so it requires more investigate to know what’s happening.

I’m at some internal MySQL training today at our US HQ. In a room of 11 people we are all using our laptops for access to additional machines. The beauty of this is we have Linux (Ubuntu 5.10, 6.10, 7.04, Red Hat Fedora), Mac OS/X and Windows Operating Systems on various staff machines. It’s great to realize we are not locked into just one infrastructure, and we have the ability to all do our work effectively and MySQL works just as well across these technologies. I can’t however SSH from my iPhone yet, unlike my previous Nokia E62 PDA.