MySQL Questions & Answers

Table of Contents

• Search with special characters
• Why is InnoDB disabled?
• How to find MySQL system information?
• What is the difference between MySQL certified server and community server?
• MySQL monitoring
• MySQL backup
• Corrupt MyISAM table
• How to compile MySQL
• Test restore procedure
• Reset a MySQL user password
• Reset the MySQL root user password
• How to enable the InnoDB plugin
• Storage Engines shipped with MariaDB / MySQL
• Compiling MySQL Cluster ndb-test fails
• NDB information schema does not show up
• Hyper Threading (HT) enabled?
• How to make a patch for MariaDB?
• Where does the InnoDB AUTO-INC waiting come from?
• My character encoding seems to be wrong. How can I fix it?
• I think my Slave is not consistent to its Master any more. How can I check this?
• My MySQL Server is swapping from time to time. This gives hick-ups in MySQL. How can I avoid this?
• How can I find which I/O scheduler my device is using?
• How can I find why my mail is not sent?

Search with special characters

This Question has been moved to the Forum.

How to find MySQL system information?

This Question has been moved to the Forum.

What is the difference between MySQL certified server and community server?

Question: What is the difference between MySQL certified server and community server?

Answer:

MySQL certified server	MySQL community server
Chosen software by MySQL Based on internal quality/feature completeness	Serves as base for Certified Server New MySQL Forge helps get contributions!
Profits from community testing Receives additional internal/external testing	Receives community and basic internal testing
Contains no untested/certified feature	May contain features for community testing
Infrequent major releases	Frequent releases (early and often) Under active development
Tiered patch releases (every month to once/qtr)	Contains patches plus new features
Recommended to partners, ISV's, Enterprise deployment	Not recommended to partners, ISV's
Certified on most popular platforms	Offered on over two dozen platforms
Formal support through MySQL AB	Informal support via forums, etc.

MySQL monitoring

Question: How can I monitor MySQL related system information, status and activities?

Answer: MySQL monitoring solutions

MySQL backup

This Question has been moved to the Forum.

Question: Is LVM snapshot a feasible way to take MySQL/InnoDB backups?

Answer: It depends! If you stop MySQL it should work well. If you have MyISAM tables only, then FLUSH TABLES WITH READ LOCK should guarantee a consistent backup. With InnoDB a LVM snapshot should work as well because it is the same situation as in a sever crash. It becomes a problem when the InnoDB log files are located on a different disk than the data files. FLUSH TABLES WITH READ LOCK is in this situation not sufficient for InnoDB because InnoDB still may write some data in the background which can corrupt your LVM snapshot over 2 devices.

There were some cases reported where MySQL/InnoDB refused to recover from a LVM snapshot backup with core dump:

InnoDB: Progress in precents: 0 1 2 3 mysqld got signal 11;

Innobase never approved LVM snapshots as valid way of taking backups! If these are technical or marketing reasons I do not know.

If you choose LVM snapshot as a backup method we recommend to do a restore-test of the backup and restart the database to see if it recovers successfully. Then you are sure your backup is a valid one.

Literature

1. [ 1 ] MySQL Backups using LVM Snapshots
2. [ 2 ] MySQL Server Backup, Restoration, And Disaster Recovery Planning Presentation
3. [ 3 ] Using LVM for MySQL Backup and Replication Setup
4. [ 4 ] Logical volume management
5. [ 5 ] Backup of MySQL Databases on Logical Volumes

Corrupt MyISAM table

Question: How does a corrupt MyISAM table look like?

Answer: InnoDB tables should not get corrupted at all. MyISAM tables can get corrupted after system failure. You should NEVER run corrupted MyISAM tables. I can even get worse! Do always a check after a crash. How you can find if a table got corrupted and how you can repare it again is shown below:

mysql> CHECK TABLE test;
+-----------+-------+----------+-------------------------------------------------------+
| Table     | Op    | Msg_type | Msg_text                                              |
+-----------+-------+----------+-------------------------------------------------------+
| test.test | check | warning  | 1 client is using or hasn't closed the table properly |
| test.test | check | error    | Key in wrong position at page 3072                    |
| test.test | check | error    | Corrupt                                               |
+-----------+-------+----------+-------------------------------------------------------+
3 rows in set (0.05 sec)

mysql> SELECT COUNT(*) FROM test;
ERROR 145 (HY000): Table './test/test' is marked as crashed and should be repaired

mysql> REPAIR TABLE test;
+-----------+--------+----------+--------------------------------------------------+
| Table     | Op     | Msg_type | Msg_text                                         |
+-----------+--------+----------+--------------------------------------------------+
| test.test | repair | warning  | Number of rows changed from 11000000 to 10000000 |
| test.test | repair | status   | OK                                               |
+-----------+--------+----------+--------------------------------------------------+
2 rows in set (29 min 17.80 sec)

061004  9:43:50 [ERROR] /usr/local/bin/mysqld: Table './test/test' is marked as crashed and
                should be repaired
061004 10:13:24 [Note] Found 10000000 of 11000000 rows when repairing './test/test'

How to compile MySQL

Question: How do I compile MySQL on platforms were no binaries are provided?

Answer: Recently we wanted to run MySQL on a 64bit PPC Linux platform. We compiled it as follows:

CC="gcc" CFLAGS="-O3 -mpowerpc -m64 -mcpu=powerpc" CXX="gcc" \
CXXFLAGS="-O3 -m64 -mpowerpc -mcpu=powerpc" \
./configure --prefix=/app/mysql/5.0.37

make

make install

If you never compiled something on your Linux machine before maybe some necessary tools are missing:

• gmake
• autoconf
• automake
• libtool
• m4
• bison

Further very often there are some header files of standard libraries not installed:

• libreadline-dev
• libncurses5-dev

If you install all those it should work...

MySQL Documentation: Installing MySQL from a Standard Source Distribution

Test restore procedure

Question: Why should I regularly test my restore procedure?

Answer: You should test your restore procedure on a regular base to make sure it actually works, when you really need it.

See the following real life examples MySQL users were experiencing: When I backup the database with the command:

shell> mysqldump --user=root -p --hex-blob --max_allowed_packet=128M -x -t test > test_dump.sql

everything works fine. But when I try to restore the database I get the following error:

mysql --user=root -p --max_allowed_packet=128M test < test_dump.sql
ERROR 1153 (08S01) at line 87: Got a packet bigger than 'max_allowed_packet' bytes

In this case you possibly would have found the problem already before you have to do the real emergency restore.

Reset a MySQL user password

Question: How do I reset a Password for a MySQL user?

Answer: For a regular MySQL or MariaDB user you can reset the Password with the SET PASSWORD command:

mysql> SET PASSWORD FOR 'app_owner'@'%.mysite.com' = PASSWORD('secret');

Consider, that the user in MySQL always consist of a username AND a domain name.

Literature

[1] SET PASSWORD Syntax

Reset the MySQL root user password

Question: How do I reset the MySQL root user password?

Answer: To reset the MySQL or MariaDB root user password you have 2 possibilities:

1. Restart the mysqld with an init-file where you reset the root password.
2. Restart the mysqld with the skip-grant-tables option and then reset the root password.

Possibility one: Restart the mysqld with the init-file parameter:

1. Create a file with the reset commands in a location where nobody else than the MySQL user has access to:

   -- reset_root_user_password.sql
   UPDATE mysql.user SET password = PASSWORD('secret') WHERE user = 'root';
   FLUSH PRIVILEGES;

2. Hook this reset command file into your my.cnf

   # my.cnf
   [mysqld]
   init-file = /home/mysql/secret/reset_root_user_password.sql

3. Stop or kill mysqld

   shell> kill `cat <datadir>/<host_name>.pid`

4. Verify that mysqld was stopped properly:

   shell> pgrep mysqld

5. Start mysqld

   shell> /etc/init.d/mysql start

   Now you should be capable to use the new root user password.
6. Remove the init-file parameter again from the my.cnf and delete the reset_root_user_password.sql script.

This methode requires only one database restart.

Possibility two: Restart the mysqld with the skip-grant-tables parameter:

1. Add the skip-grant-tables parameter to your my.cnf:

   # my.cnf
   [mysqld]
   skip-grant-tables = 1

2. Stop or kill mysqld

   shell> kill `cat <datadir>/host_name.pid`

3. Verify that mysqld was stopped properly:

   shell> pgrep mysqld

4. Start mysqld

   shell> /etc/init.d/mysql start

5. Now you can login without any password:

   shell> mysql --user=root

6. Reset the root user password:

   mysql> UPDATE mysql.user SET password = PASSWORD('secret') WHERE user = 'root';
   mysql> FLUSH PRIVILEGES;

7. Remove the skip-grant-tables parameter from the my.cnf.
8. Restart the mysqld again to protect its security.

   shell> /etc/init.d/mysql restart

This methtode requires for security reasons two database restarts.

You can use both methods with mysqld command options as well.

Literature

[1] How to Reset the Root Password [2] MySQL Server Command Option init-file [3] MySQL Server Comand Option skip-grant-tables

How to enable the InnoDB plugin

Question: How do I enable the InnoDB plugin?

Answer: You can enable the InnoDB plugin in 3 different ways:

• In the MySQL Client with the INSTALL PLUGIN command.
• When starting the mysqld with command line parameters.
• In the MySQL configuration file:

# my.cnf
[mysqld]
ignore_builtin_innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;\
innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;\
innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

Pleased make sure that the plugin-load parameter is a one-liner.

You get the following possibilities:

Distribution	Version	Type	Maker	Version
MySQL	5.0	built-in only	Innobase	?
MariaDB	5.1	built-in only	Percona	1.0
MySQL	5.1	built-in and plugin	Innobase	? / 1.0
MariaDB	5.2	built-in only	Percona	1.0
MySQL	5.5	built-in only	Innobase	1.1

Storage Engines shipped with MariaDB / MySQL

Question: What storage engines do I get with MySQL and MariaDB?

Answer: Depending on the release and the distribution you are using you get different Storage Engines. The details you can find in the following matrix:

mysql> SHOW ENGINES;

MySQL

Engine	5.0	5.1	5.5
ARCHIVE	YES	YES	YES
BerkeleyDB	NO
BLACKHOLE	YES	YES	YES
CSV	YES	YES	YES
EXAMPLE	NO	NO
FEDERATED	YES	NO	NO
InnoDB	YES	YES	DEFAULT
ISAM	NO
MEMORY	YES	YES	YES
MRG_MYISAM	YES	YES	YES
MyISAM	DEFAULT	DEFAULT	YES
ndbcluster	DISABLED	NO

Remarks:

• From MySQL 5.0 to 5.1 BerkleyDB (BDB) and ISAM Storage Engines were removed and Federated Storage Engine was disabled.
• In 5.5 it looks like the EXAMPLE and the FEDEREATED Storage Engines are not included any more.
• With 5.5 InnoDB is the new default Storage Engine.
• The NDB Storage Engine is not support with MySQL 5.5 yet.

MariaDB

Engine	5.1	5.2	5.3
ARCHIVE	YES	YES	YES
Aria		YES	YES
BLACKHOLE	YES	YES	YES
CSV	YES	YES	YES
EXAMPLE	YES	YES	YES
FEDERATED	YES	YES	YES
InnoDB		YES	YES
MARIA	YES
MEMORY	YES	YES	YES
MRG_MYISAM	YES	YES	YES
MyISAM	DEFAULT	DEFAULT	DEFAULT
OQGRAPH		YES
PBXT	YES	YES	YES
SPHINX		YES	YES

Remarks:

• In MariaDB 5.1 the FEDERATED Storage Engine is integrated in the form of the FederatedX Storage Engine.
• InnoDB was not taken into MariaDB 5.1. PBXT acts as a substitute for InnoDB.
• In MariaDB 5.2 the Maria Storage Engine was renamed into Aria and InnoDB comes back in form of XtraDB.
• The new OQGraph Storage Engine was added with MariaDB 5.2 as well.
• OQGraph did not build with MariaDB 5.3.0 any more but it is still included in the source code. So I assume this is a bug of this early alpha release.

For more information about the different Storage Engines available see also MySQL Pluggable Storage Engines

Compiling MySQL Cluster ndb-test fails

Question: When I want to compile flexAsynch I get some odd compiling errors.

Answer: We had a similar problem, when we added one include directive the problem disappeared.

When we compiled MySQL Cluster 7.1.9a as follows:

./configure --with-plugins=max --with-ndb-test ; make -j 4

we got the following error message:

g++ -DHAVE_CONFIG_H -DNDEBUG   -I. -I../../../../include -I../../../../storage/ndb/test/include -I. \
-I../../../../include -I../../../../storage/ndb/include -I../../../../include -I../../../../mysys \
-I../../../../storage/ndb/include -I../../../../storage/ndb/include/util \
-I../../../../storage/ndb/include/portlib -I../../../../storage/ndb/include/logger \
-I../../../../storage/ndb/include/mgmapi -I. -I../../../../include \
-I../../../../storage/ndb/include -I../../../../include -I../../../../storage/ndb/include \
-I../../../../storage/ndb/include/ndbapi -I../../../../storage/ndb/include/util \
-I../../../../storage/ndb/include/portlib -I../../../../storage/ndb/test/include \
-I../../../../storage/ndb/include/mgmapi -I../../../../storage/ndb/include/kernel \
-I../../../../storage/ndb/src/ndbapi -I../../../../storage/ndb/include/debugger \
-I../../../../ndb/src/mgmapi -I../../../../ndb/src/mgmsrv -I../../../../ndb/include/mgmcommon \
-DDEFAULT_PREFIX="\"/usr/local\"" -O3 -fno-implicit-templates -fno-exceptions -fno-rtti -MT atrt-db.o \
-MD -MP -MF .deps/atrt-db.Tpo -c -o atrt-db.o `test -f 'db.cpp' || echo './'`db.cpp
main.cpp: In function ‘bool parse_args(int, char**)’:
main.cpp:560: error: ‘lstat’ was not declared in this scope
main.cpp:741: error: ‘lstat’ was not declared in this scope
main.cpp:749: error: ‘S_ISREG’ was not declared in this scope

560   if (argc > 1 && lstat(argv[argc-1], &sbuf) == 0)
741     if (lstat(tmp.c_str(), &sbuf) != 0)
749     if (!S_ISREG(sbuf.st_mode))

This only happens with the --with-ndb-test directive but NOT without! So we assume, that there must be something wrong in the sources, which does not show up on the developers machines...
By just adding the following line to all failing parts (there were 2 or 3 of them):

#include <sys/stat.h>

it worked out for us.

NDB information schema does not show up

Question: When I start my MySQL 7.1 Cluster the NDB information schema does not show up. What can I do?

Answer: This happens in some cases. The reason is not known to us. Seems something like a bug.
When you look at the log MySQL seems to be aware of the NDB information schema:

110125  9:00:24 [Note] NDB: Creating mysql.ndb_schema
110125  9:00:24 [Note] NDB: Flushing mysql.ndb_schema
110125  9:00:24 [Note] NDB Binlog: CREATE TABLE Event: REPL$mysql/ndb_schema
110125  9:00:24 [Note] NDB Binlog: logging ./mysql/ndb_schema (UPDATED,USE_WRITE)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+

As mentioned in Bug #54552 ndbinfo missing although activated after online upgrade a mysql_upgrade will help to solve the problem:

shell> mysql_upgrade

mysql> SHOW PLUGINS;
+------------+----------+----------------+---------+---------+
| Name       | Status   | Type           | Library | License |
+------------+----------+----------------+---------+---------+
| ndbcluster | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ndbinfo    | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
+------------+----------+----------------+---------+---------+

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ndbinfo            |
| test               |
+--------------------+

Hyper Threading (HT) enabled?

Question: How can I find if Hyper Threading (HT) is enable on my machine?

Answer: /proc/cpuinfo will tell you if Hyper Threading (HT) is enabled on your machine or not:

# cat /proc/cpuinfo | egrep 'siblings|cpu cores' | sort | uniq
cpu cores       : 2
siblings        : 4

If the values of cpu cores and siblings are equal, then hyper threading is DISABLED otherwise ENABLED.
On some Linux distributions (RedHat, CentOS) you can even set Hyper Threading online: Is hyper-threading enabled on a Linux system?

How to make a patch for MariaDB?

Question: I have found a bug for MariaDB and have fixed it. How do I make a patch for it?

Answer: I was told by the MariaDB developers that they would like to have it like this:

diff -up orig.cc new.cc

Where does the InnoDB AUTO-INC waiting come from?

Question: With SHOW INNODB STATUS we have seen under high load a lot of transactions waiting on AUTO_INC. Where does it come from?

Answer: This is a bug in MySQL. It looks as follows:

------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test/test` trx id 0 447488123 lock mode AUTO-INC waiting

This bug will be fixed in newer MySQL 5.1 releases (>= 5.1.47) but not in MySQL 5.0.

For more details refer to:

1. InnoDB auto-inc scalability fixed
2. Bug #16979: AUTO_INC lock in InnoDB works a table level lock

My character encoding seems to be wrong. How can I fix it?

Question: It looks like my data are somehow wrong in my MySQL database. I get some strange characters. How can I fix those?

Answer: This happens when you use the wrong encoding to fill in your data in the database. How this happens and how to fix it again you can find in the following lines:

mysql> CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

mysql>\! locale

# Wrong encoding!!!
mysql> SET NAMES latin1;

mysql> INSERT INTO test VALUES (NULL, 'äöü', NULL);

# Data seems to be correct but are not:
mysql> SELECT data, HEX(data) FROM test;
+--------+--------------+
| data   | HEX(data)    |
+--------+--------------+
| äöü    | C3A4C3B6C3BC |
+--------+--------------+

# Set right enconding:
mysql> SET NAMES utf8;

# Wrong umlaut encoding for latin1 column:
mysql> SELECT data, HEX(data) FROM test;
+--------------+--------------+
| data         | hex(data)    |
+--------------+--------------+
| äöü       | C3A4C3B6C3BC |
+--------------+--------------+

# Fix encoding
mysql> ALTER TABLE test MODIFY data VARBINARY(64);
mysql> ALTER TABLE test MODIFY data VARCHAR(64) CHARACTER SET utf8;

mysql> SHOW CREATE TABLE test\G
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

# Now the data are displayed correctly and encoding matches the CHARACTERSET definition of the column
mysql> SELECT data, HEX(data) FROM test;
+--------+--------------+
| data   | hex(data)    |
+--------+--------------+
| äöü    | C3A4C3B6C3BC |
+--------+--------------+

# Convert encoding now to latin1
mysql> ALTER TABLE test MODIFY data VARCHAR(64) CHARACTER SET latin1;

# Now the data are displayed correctly and encoding matches the CHARACTERSET definition of the column
mysql> SELECT data, HEX(data) FROM test;
+--------+-----------+
| data   | hex(data) |
+--------+-----------+
| äöü    | E4F6FC    |
+--------+-----------+

mysql> SHOW CREATE TABLE test\G
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=PBXT DEFAULT CHARSET=latin1

If the table was created with utf8 character set the procedure is as follows:

ALTER TABLE test MODIFY data VARCHAR(64) CHARACTER SET latin1;
ALTER TABLE test MODIFY data VARBINARY(64);
ALTER TABLE test MODIFY data VARCHAR(64);

See also Convert latin1 to UTF-8 in MySQL

Solution: We have written a small application which repairs this issues. If you need our help please get in contact with us.

I think my Slave is not consistent to its Master any more. How can I check this?

Question: I think my Slave is not consistent to its Master any more. How can I check this?

Answer: The best way to do it is using the Maatkit-Tools.
In the Maatkit-toolbox there is mk-table-checksum and mk-table-sync. How to use it you can find as follows:

Check

On Master:

mk-table-checksum --create-replicate-table --empty-replicate-table --replicate=test.checksum \
u=root,h=127.0.0.1 \
--tables=test.test

On Slave:

mk-table-checksum --replicate=test.checksum \
--replicate-check=1 \
u=root,h=127.0.0.1 \
--tables=test.test

Sync

On Slave:

mk-table-sync --sync-to-master  --print \
h=127.0.0.1,u=root,D=test,t=test

Then run the queries on the master...

My MySQL Server is swapping from time to time. This gives hick-ups in MySQL. How can I avoid this?

Question: My MySQL Server is swapping from time to time. This gives hick-ups in MySQL. How can I avoid this?

Answer: First of all you have to make sure, that MySQL does not over-allocate memory:

# free
             total       used       free     shared    buffers     cached
Mem:      16431968    5736652   10695316          0     127876    2449008
-/+ buffers/cache:    3159768   13272200
Swap:     19802108          0   19802108

# ps aux | grep -e 'mysqld ' -e 'VSZ' | cut -b-120
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
mysql     2020  1.8  6.1 3166896 1010840 ?     Sl   08:06  11:09 /home/mysql/product/mysql-5.6.2/bin/mysqld

If you checked this and it looks OK you can check the swappiness of your system:

# cat /proc/sys/vm/swappiness
60

# sysctl -a
# sysctl vm.swappiness

If the value is bigger than 0 you can set it either on the fly or make it permanent:

# sysctl -n vm.swappiness=0

or

#
# /etc/sysctl.conf
#
vm.swappiness=0

# sysctl -p /etc/sysctl.conf

Which I/O scheduler?

Question: How can I find which I/O scheduler my device is using?

Answer: The default I/O scheduler (cfq) may not be optimal for database workloads. If you are I/O limited, try an other I/O scheduler. To find which I/O scheduler currently is used the following command might help:

cat /sys/block/<device>/queue/scheduler

noop and deadline often give better results than cfq or anticipatory for InnoDB workloads.

Mail problems

Question: How can I find why my mail is not sent?

Answer: To find this the best is to simulate this manually step by step. The following sequence of commands might help:

telnet localhost smtp
EHLO www.fromdual.com
MAIL FROM: contact@fromdual.com
RCPT TO: oli.sennhauser@fromdual.com
DATA
Subject:Title

bla bla bla
.
QUIT

Here you should see step by step why it is not working.