MySQL trouble shooting

Content

• max_open_files warning during mysqld startup
• Troubles after NON recommended upgrade path
• Com_* counters not updated in SHOW STATUS
• ERROR 1300 (HY000): Invalid utf8 character string
• MySQL crashes during import
• MySQL workbench gives an openGL error

Problem

[Warning] Changed limits: max_open_files: 1024  max_connections: 100  table_cache: 457

[Warning] Could not increase number of max_open_files to more than 1024 (request: 1070)

Explanation

The operating system hard limit of open files was exceeded.

Analysis

Finding the soft and hard limits of open files for your account you can find like this:

# ulimit -Sa | grep "open files"
open files                    (-n) 1200

# ulimit -Ha | grep "open files"
open files                    (-n) 8192

This corresponds to:

mysql> show variables like ...
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| open_files_limit  | 1200  |
| table_cache       | 512   |
| max_connections   | 100   |
+-------------------+-------+

When open files is adjusted and MySQL server is restarted the parameter will be adjusted automatically.

Solution

Adjust this parameter manually:

# ulimit -n 1200

or at the command in ~/.profile or in /etc/profile or add the following values to /etc/security/limits.conf

mysql            hard    nofile          8192
mysql            soft    nofile          1200

MyISAM table format can change in major releases (N.m) and also in minor releases (n.M).

Which MyISAM table formats are used can be found like this:

SELECT table_schema, table_name, engine, version
  FROM information_schema.tables
 WHERE table_type = 'BASE TABLE';

+--------------+------------+--------+---------+
| table_schema | table_name | engine | version |
+--------------+------------+--------+---------+
| test         | by         | MyISAM | 10      |
| test         | test       | MyISAM | 7       |
+--------------+------------+--------+---------+

The following MyISAM table versions were used by the different MySQL releases:

MySQL generally recommends that you dump and reload your tables from any previous minor version to upgrade to next minor version (e.g. 4.0 -> 4.1 -> 5.0 -> 5.1 -> 5.2 -> 6.0). Upgrading by just coping the datafiles within minior versions in general is ok (read upgrade recommendations).
Nevertheless users are only coping the files also between major versions. This is NOT recommended by MySQL. But it works mostly.
Potential problems are known: Sort orders have changed in 4.1 and 5.0 with the result that older table formats could result in corrupted tables, locked up servers. Datatypes have changed. In release 4.0/4.1 NLS support (utf8) was added, Replication did not work anymore after NON recommended upgrade path, Com_* counters where not properly updated, etc.

Com_* counters not updated in SHOW STATUS

Using old MySQL 4.0 libraries against a 5.0 database caused problems with the Com_* counters (SHOW STATUS LIKE 'Com_%'). These counters were not updated correctly and some delta calculations were negative (mysqladmin extended -c 10 -d ...), which should never happen.

After removing the old 4.0 MySQL library the problem disappeared.

Dump and load the production database into the test system: During load we received the following error:

mysql> CREATE TABLE  `...`(a INT);
ERROR 1300 (HY000): Invalid utf8 character string: '...'

After setting the following line in the dump file from utf8 to latin1 the import worked fine.

/*!40101 SET NAMES utf8 */;

The exact reason of this problem is unknown. It was not reproduceable on an other system. It could be related to bug #9163.

For setting up replication it was tried to do an import from production. This import failed with the following error message:

# mysql -u root kapow < kapow.sql

ERROR 1300 (HY000) at line 23: Invalid utf8 character string: 'ler'

After removing the SET NAMES line from the dump file we run into a segmentation fault error:

# mysql -u root kapow < kapow.sql

Segmentation fault

This error was analyzed by MySQL support and was recognized as bug #9163.

The following workarounds can be offered:

1. ALTER TABLE ... to rebuild the table creation DDL statement.
2. Migrate to 5.1 (not recommended for production systems) where error should be fixed?

The symptoms can be verified as follows:

• SHOW CREATE TABLE ... gives corrupt statement.
• thus mysqldump creates corrupt statement in the dump file:

CREATE TABLE `Boligc` (
...
`grund` decimal(10,2) default NULL,
`bygge_år;

# glxinfo
# xdpyinfo | grep GL

Add

Load         "glx"

into the Module section in /etc/X11/xorg.conf.