MYSQL server problem after upgrading

running a perfect debian wheezy server.

what I noticed first was that when restarting mysql I got a message about the number of open files:

so I edited /etc/mysql/my.cnf and upped

then restarted mysql and got the same problem.

eventually I noticed when starting mysql I get this message:

and then I did a check:

and there I found:

so clearly for some weird reason the open_files_limit I set up in /etc/mysql/my.cnf is being ignored.

Any ideas please?

 

partially making progress. found this in syslog:

Went and increased the limits:

inside /etc/security/limits.conf

now I only see:

any more suggestions looking at above logs?
any ideas what limits the table cache?

 

sorry, replied to the wrong thread, my question still stand.

 

Still seeing this line too when manually restarting MYSQL:

 

The question about "open_files_limit" can be resolved in this way.

As far I understand, /etc/security/limits.conf gets loaded AFTER mysql starts at boot - that's why the correct limits for mysql are not set.

(You can check this by restarting the mysql service and checking the open_files_limit value after that - it will be OK).

So, we need to apply the limits at the time when mysql starts, and the way to do this is (in Debian Wheezy, at least) is to add this line:

Code:

session required pam_limits.so

to this file:
/etc/pam.d/common-auth

You'll see that now the "open_files_limit" value will be OK right after reboot.

 

As for you second question, please check your /etc/passwd file

If you see something like:

Code:

mysql:x:106:110:MySQL Server,,,:/nonexistent:/bin/false

change "/nonexistent" to "/var/lib/mysql" like this:

Code:

mysql:x:106:110:MySQL Server,,,:/var/lib/mysql:/bin/false

And upon

Code:

service mysql restart

you'll see that the message is gone!

 

thx Dimas, that solved it!

Let me throw some more questions at you as I still see these lines in the logs but no pressure I'll be googling the errors myself to see if I can find some solutions, but if you have any solutions handy, you're more than welcome to reply.

 

hm, still having these errors:

Code:

Nov 12 17:37:00 alfred mysqld: 2015-11-12 17:37:00 10891 [ERROR] Function 'innodb' already exists
Nov 12 17:37:00 alfred mysqld: 2015-11-12 17:37:00 10891 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
Nov 12 17:37:00 alfred mysqld: 2015-11-12 17:37:00 10891 [ERROR] Function 'federated' already exists
Nov 12 17:37:00 alfred mysqld: 2015-11-12 17:37:00 10891 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
Nov 12 17:37:00 alfred mysqld: 2015-11-12 17:37:00 10891 [ERROR] Function 'blackhole' already exists
Nov 12 17:37:00 alfred mysqld: 2015-11-12 17:37:00 10891 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
Nov 12 17:37:00 alfred mysqld: 2015-11-12 17:37:00 10891 [ERROR] Function 'archive' already exists
Nov 12 17:37:00 alfred mysqld: 2015-11-12 17:37:00 10891 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.

I read somewhere to do a mysql -u root -p

Code:

mysql> select * from mysql.plugin;
+-----------+-----------------+
| name      | dl              |
+-----------+-----------------+
| innodb    | ha_innodb.so    |
| federated | ha_federated.so |
| blackhole | ha_blackhole.so |
| archive   | ha_archive.so   |
+-----------+-----------------+
4 rows in set (0.00 sec)

apparently one can delete these plugins as its built in with mysql 5.6? Anyway I don't know how to delete them. Any advice?

 

Wow, u are totally profesional!