Topic: MySQL settings randomly changing

[BerTreB]

Hi,

I use smeserver mainly for MySQL to serve our production system database (MSACCESS front end, MySQL backend).
The database isn't very big (backup file is only 200Mb)

I am not very knowledgable when it comes to MySQL and have kind of been thrown in at the deep end...

In order for me to get it to work, I have to edit some timeout settings every time I reboot:

  SET wait_timeout = 2592000;
  SET interactive_timeout = 2592000;
  SET GLOBAL wait_timeout = 2592000;
  SET GLOBAL interactive_timeout = 2592000;

If I do this, everything works great.

However, in the last few months I get a call randomly from workers saying the 'system is down' and when I log in to the server and check these settings, they have reverted back to what they are on boot. The server hasn't rebooted, and nothing else seems to be different. it's as if mysql has rebooted and restored its settings.

Can anyone help me narrow down why this might be happening, or help me get those settings to be default? From what i've read, i need to play with teplates but I could use a little push in the right direction.

Thanks

[Stefano]

hi, welcome here

you should read the dev's manual and learn something about how SME works behind the curtain..

you can't edit configuration files directly..

[BerTreB]

I realise that which is why I mention templates. I've tried to find a good tutorial but failed.

my main concern though is why these settings keep reverting even when the server isn't being rebooted. it's seemingly random.

[Stefano]

it happens every time the involved conf file are regenerated

what file do you need to edit?

[BerTreB]

I need the settings mentioned in my first post to be permanent, even after reboot. I'm not sure where they are mean to go, I always change them through command line.

The file shouldn't be being regenerated randomly though, which concerns me.

[Stefano]

BerTreB:

- files are not regenerated "randomly".. they are regenerated every time, for example, you call the "post-upgrade" event after yum update
- you are not answering me: what file/s are you editing?

[janet]

BerTreB

Other legitimate system events & actions can regenerate conf file eg mysql.conf & cause your manual setting changes to be overwritten.

You need to create custom templates probably for mysql
You need to tell us specifically what commands you are issuing at the command line to invoke the changes you want. These can then be translated into the appropriate custom template fragment.
See
http://wiki.contribs.org/Template_Tutorial
In particular the mysql section.

[BerTreB]

Sorry, We're having trouble explaining ourselves.

I understand that the config file shouldn't be regenerated randomly, but my settings are reverting back to default without me doing anything. The server is not rebooting, i'm not running any post-upgrade... server runs fine then 'randomly', i get problems, check those settings and they've reverted.

I'm not editing any files at all, I don't edit config files. I log into the server (ssh), type 'mysql' to log into the mysql command line interface, then issue the commands in my first post.

Doing this makes everything work. This worked flawlessly with no issue for over a year. No reboots, no issues.
Recently, every now and then ('randomly') these settings revert back with no interaction from me. To fix, I reboot server and reissue the commands.

I hope I've explained properly

Thanks

EDIT:
janet - the commands are in my first post. 4 commands are issued, all timeout related. I'm not sure I need all 4 but doing that makes everything work and i've not had problem before. thanks

[Stefano]

so you are not editing a conf file, you are changing "on the fly" some mysql's parameters... that's a bit different

I guess you need to create a custom fragment for /etc/my.cnf under [mysqld] section

Code: [Select]

mkdir -p /etc/e-smith/templates-custom/etc/my.cnf
cd /etc/e-smith/templates-custom/etc/my.cnf
nano 013myTimeoutVars

fill the text with
par_name=par_value

couples (search with google)

save and exit with Ctrl-X

then

Code: [Select]

expand-template /etc/my.cnf
service mysqld restart

that's all

[BerTreB]

thanks for that, really useful.

i tried what you said but dont understand what you mean by couples

when i have done what you said, i can no longer connect to mysql, it says "ERROR 2002 (HY000): Can't connect to local MySQL server through socket"

if i undo what you said, it works again. I must be missing something.

[Stefano]

try to put only

Code: [Select]

wait_timeout = 2592000
interactive_timeout = 2592000

inside the fragment and try again

you should search with google the way to put those variables/parameters into my.cnf file (it seems to me there are only 2 variables but with/without the "GLOBAL" flag )

in the meanwhile, read the devs' manual and search the wiki, thank you