0

I've been researching this for a few hours with no luck. Basically it looks like my server's my.cnf file isn't being read at all.

I've searched my server, and there's only one my.cnf file on it, located at /etc/mysql/my.cnf. Its ownership is root:root.

I'm running Ubuntu 10.04 64bit on a Linode.com server. I have the latest versions of MySQL and PHP installed.

I've edited the my.cnf file, commented out "skip-innodb", and have set innodb to be the default storage engine using

default-storage-engine = innodb

And then restarted mysql.

But when I do show engines, MyISAM is still coming up as the default engine.

Also - none of the innodb settings I've added to the my.cnf file are being read. For example, I have this in my.cnf:

innodb_buffer_pool_size=4G

But in phpmyadmin, InnoDB is showing as having a buffer pool size of 8,192 KiB.

Similarly, I have this in the my.cnf:

innodb_data-file_path = ibdata1:500M:autoextend

But in phpmyadmin, it's reading as ibdata1:10M:autoextend.

It doesn't look like MyISAM info is being read from the my.cnf file either. The my.cnf file has skip-external-locking queried out, but it's showing as "on" in phpmyadmin.

So - yeah, it looks like nothing in the my.cnf file is being read at all.

But the server still works. I'm running a Drupal site on it and it seems to operate fine. So mysql seems to be drawing default settings from... some mysterious secret location.

Any idea how I can make mysql see and use this my.cnf file?


Actually, wait - it looks like it may be being read, not sure. I checked the error.log and found this:

101128  4:28:52 [ERROR] Cannot find or open table databasename/cache_apachesolr from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

InnoDB: Error: auto-extending data file ./ibdata1 is of a different size
InnoDB: 640 pages (rounded down to MB) than specified in the .cnf file:
InnoDB: initial 32000 pages, max 0 (relevant if non-zero) pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
101128  4:28:52 [ERROR] Plugin 'InnoDB' init function returned error.
101128  4:28:52 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
101128  4:28:52 [ERROR] /usr/sbin/mysqld: unknown variable 'innodb_lock_wait_timout=50'
101128  4:28:52 [ERROR] Aborting

101128  4:28:52 [Note] /usr/sbin/mysqld: Shutdown complete
0

5 Answers 5

1

Similarly to how you cannot change the log file size without re-creating the innodb logs, you also cannot re-specify the ibdata file size without re-creating it. Besides, there is no need to resize it larger since it defaults to an auto-extending file. Here is the relevant bit of the error that tells you this is a problem:

    InnoDB: Error: auto-extending data file ./ibdata1 is of a different size
    InnoDB: 640 pages (rounded down to MB) than specified in the .cnf file

So I would remove that from my.cnf. Also you may have a typo somewhere in your config since the error log also says that "innodb_lock_wait_timout" is an invalid variable, but it does not appear to be invalid according to the manual.

It is mysterious though how mysql is running at all when it's clearly aborted during startup due to the invalid my.cnf.

0

you may want to check /var/lib/mysql, /etc/mysql/my.cnf (or your mysql directory) for the file

The server does not honor ~/.my.cnf file if there are /etc/mysql/my.cnf

The following locations are generally where my.cnf is normally located:

/etc/my.cnf Global options
/etc/mysql/my.cnf   Global options (as of MySQL 5.1.15)
SYSCONFDIR/my.cnf   Global options
$MYSQL_HOME/my.cnf  Server-specific options
defaults-extra-file The file specified with --defaults-extra-file=path, if any
~/.my.cnf   User-specific options

~ represents the current user's home directory (the value of $HOME).

SYSCONFDIR represents the directory specified with the --sysconfdir option to configure when MySQL was built. By default, this is the etc directory located under the compiled-in installation directory. This location is used as of MySQL 5.1.10. (From 5.1.10 to 5.1.22, it was read last, after ~/.my.cnf.)

MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides.

1
  • Thanks for the quick response. Like I said, there's only one my.cnf file on the server. For some reason. If I run find / -name 'my.cnf' - it just comes up with the /etc/mysql/my.cnf file. Doesn't seem to be anything else. Nov 28, 2010 at 9:37
0

If you are adding a new datapath into my.cnf you may need to edit your apparmour config to permit this change. Do you see any "audit" type errors in your log files?

When I changed the data_dir in my my.cnf it did not pick up on the change until I edited

 /etc/apparmor.d/usr.sbin.mysqld

And added the new path of the data directory with rw.

0

I guess this is the culprit:

innodb_buffer_pool_size=4G

I've had the same problem on Ubuntu 10.10 (64 bit). Apparently, mysql, at least the one compiled for for this Ubuntu, does not support buffer_pool_size bigger than 1000M. And ridiculously enough, it event does not complaint about it. So reduce the buffer size, restart the server and see what happens.

-2

you need to put innodb mysql variables under [mysql] block in my.cnf, otherwise it will not be read

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .