my.ini setting not working

Problems with the Windows version of XAMPP, questions, comments, and anything related.

my.ini setting not working

Postby pearlliang » 23. September 2011 00:09

Hi

My website's MySQL setting under xampp/mysql/bin/my.ini file in the section of [mysqld] don't take effect. I am using XAMPP 1.7.1 Window version with mysql 5.1.33 installed as a service.

I added these variables:

slow_query_log = 1
slow_query_log_file = "C:/xampp/mysql/data/mysql-slow.log"
query_cache_size=64M

I stop and restarted MySQL (installed as a service).
And, I query MySQL using these comments:

show variables like 'query_cache%';

But, the results still shows:

Variable_name Value
query_cache_size 0

Also, there is no mysql-slow.log file created in C:xampp/mysql/data/

Anyone konws why my setting not working?

Thanks for your help.
pearlliang
 
Posts: 6
Joined: 22. September 2011 23:43

Re: my.ini setting not working

Postby Sharley » 23. September 2011 00:22

Where did you add your entries?

Perhaps paste your my.ini in between forum code tags so it can be checked over.
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: my.ini setting not working

Postby pearlliang » 23. September 2011 00:58

All fo my setting in my.ini (under my local drive/xampp/mysql/bin/my.ini) are as below :

I incrested packet size to a bigh number because my website (using Drupal 6.22) shows a lot of "MySQL has gone away warning" for what ever I do on the top of the screen when I log in as admin, but other people whithout admin role don't see the warning. I searched Drupal forum and they said it is caused my MySQL setting. Is there another location for my.ini under XAMPP for window setting? Thanks for your help.

[client]
port = 3306
socket = "my local drive:/xampp/mysql/mysql.sock"

[mysqld]
port= 3306
socket= "my local drive:/xampp/mysql/mysql.sock"
basedir="my local drive:/xampp/mysql"
tmpdir="my local drive:/xampp/tmp"
datadir="my local drive:/xampp/mysql/data"
skip-locking
key_buffer = 384M
max_allowed_packet = 896M
thread_cache_size = 8
query_cache_size = 64M
table_cache = 4096
sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 64M
myisam_sort_buffer_size = 64M
wait_timeout = 1200
connect_timeout = 1200
interactive_timeout = 120
log_query_time = 0
slow_query_log = 1
slow_query_log_file = "my local drive:/xampp/mysql/data/mysql-slow.log"
log_error="mysql_error.log"

skip-federated
server-id = 1
innodb_data_home_dir = "my local drive:/xampp/mysql/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "my local drive:/xampp/mysql/data"
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 10M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 180

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
max_allowed_packet = 896M
connect_timeout=1200

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

** end of my.ini setting **

By the way, when I click SCM button, look for mysql on the screen popped up, right click and choose properties, the "path to exectuable" shows:

my local drive:\xampp\mysql\bin\mysqld.exe --defaults-file=my local drive:\xampp\mysql\bin\my.cnf mysql

However, the file name in \xampp\mysql\bin\ is my.ini, not my.cnf
Is it a problem?
I tried changing my.ini to my.cnf and the file name becomes my.cnf.ini, and the file type description is still "configuration file"
It seems like I can never change the file type of my.ini
pearlliang
 
Posts: 6
Joined: 22. September 2011 23:43

Re: my.ini setting not working

Postby Sharley » 23. September 2011 01:11

No my.ini is fine.

There looks to be no obvious issues with your my.ini as far as I can tell.

I take it you replace my local drive with the actual drive letter, for example C:/

Do search of your PC especially Windows folder for any other instances of my.ini files.

I have seen that to start the ball rolling you may need to set slow_query_log = 5 then bring it down to 3 then to 1 as a fix for some issues.

You may have another my.ini file in the system folder of Windows but a search of your PC will help and if you find any other my.ini files then rename them so they can't be called by MySQL in XAMPP.

You are running MySQL as a service.

Try uninstalling MySQL service from the Control Panel by stopping MySQL then removing the tick in the Svc check box.

Then restart MySQL manually and see if it helps.

I am currently extracting 1.7.1 to test the service my.* path issue and will edit this post with my results.

EDIT
Yes, you have an older MySQL service installed with the old my.cnf file as a clean install of 1.7.1 looks for my.ini file.

So this has to be sorted and your issue should then be resolved - let me know if the instructions above don't work and I may be able to offer another solution that requires I know your Windows version.

Good luck.
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: my.ini setting not working

Postby pearlliang » 24. September 2011 03:19

Hi Sharley,

I know what the problem is. Maybe this is a bug for XAMPP 1.7.1 and I hope the same bug has been solved for the newer version of XAMPP.

My co-worker installed a clean XAMPP 1.7.1 on his Window 2003 computer. He also found that the 'Patch Executable" by clicking "SCM" is /XAMPP/mysql/bin/my.cnf while the my file in /XAMPP/mysql/bin/ has a .ini file extension. He changed the mysqld variable value in my.ini, but the mysql query still shows the default variable value without rewrite the new value that he set. He changed the my.ini to my.cnf in /XAMPP/mysql/bin/ folder and then it works. The new variable value is reflected when doing mysql query "show variables like" comment.

The same experience repeats with XAMPP 1.7.1 on Window XP machine.

However, my acutal website sit on a Window 2008 machine with XAMPP 1.7.1. The 'Patch Executable" by clicking "SCM" is /XAMPP/mysql/bin/my.cnf while the actual file name is /XAMPP/mysql/bin/my.ini . I tried to change the actaul file name from my.ini to my.cnf, but when I restart MySQL, open phpmyadmin, MySQL won't start. I tried a few times by deleting my.ini (leaving my.cnf become the only one file name with my), MySQL still don't start when my.cnf there.
I changed my.cnf back to my.ini and MySQL can be started. my.cnf in Window 2008 becomes a "CNF" type of file, while my.ini is "Configuration FIle" type of file.

I am still trying to find ways to make my.ini become my.cnf so it can sync with "path executable".

Is it OK to change the "path executable" file name from my.cnf to my.ini ? Will it work?
pearlliang
 
Posts: 6
Joined: 22. September 2011 23:43

Re: my.ini setting not working

Postby Sharley » 24. September 2011 04:37

Win XP SP3

As I pointed out above I installed 1.7.1 using a 7zip self extracting archive xampp-win32-1.7.1.exe and opened it with WinRAR and extracted the xampp folder to the root folder of my drive G: so I had G:\xampp171 then ran the setup_xampp.bat file.

It installed without issues.

I ticked the Svc box and installed MySQL as a service and then started MySQL.

Absolutely no problems.

SCM revealed MySQL as having the Path to executable as:
"G:\xampp171\mysql\bin\mysqld.exe" --defaults-file="G:\xampp171\mysql\bin\my.ini" MySQL

No mention of a my.cnf at all.

Next I renamed my.ini to my.cnf which created my that looks like a shortcut and dragged it into my text editor and sure enough it was called my.cnf.

To have the file read again I Stop MySQL and uninstalled the service and reinstalled the service then Start MySQL and the Path to exe again showed my.ini in the SCM.

So I can't replicate your issue with the my.cnf in the path to exe in the SCM which incidentally is located in the registry and will always remain the same every time you install and uninstall the MySQL service. So it must be compiled that way, who knows.

Registry value can be found here using regedit:
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MySQL

Tip
leave regedit open and then uninstall the service and then click on View in regedit and the Refresh and make sure the above key is deleted - repeat after installing the MySQL service again to check that the key is again created - this way you will know if the registry key is actually coming from XAMPP 1.7.1 and not from an old installation of XAMPP.

Time in my opinion for you to back up your files and uninstall 1.7.1 (which if I recall was a bit of a beastly release) and install 1.7.7 unless of course you files need older XAMPP components, in which case 1.7.3 was quite stable and relatively bug free.

Older versions can be found here:
SourceForge XAMPP File Repository
There is no upgrade from 1.7.1 to 1.7.3 only 1.7.1 to 1.7.2 which might be an a;alternative if your not concerned about versions.

Sorry best I can do for your issue also I know nothing about Windows Servers File Systems or how they interact with mostly unproblematic applications in other Windows Operating Systems.

Good luck :)
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: my.ini setting not working

Postby pearlliang » 02. November 2011 01:42

Hi Sharley,

It has been long time. I was busy on other problems so now I come back to look at this issue again.

Fortunatly, I changed my.ini in xampp/mysql/bin to my.cnf in order to match the the Path to executable as:
"G:\xampp171\mysql\bin\mysqld.exe" --defaults-file="G:\xampp171\mysql\bin\my.cnf" MySQL

And it works.

However, the setting were not right so I start to wondering how can my website ever worked before when the path to executable point to my.cnf while the actual file name is my.ini. There was no my.cnf file there before. How can my website ever running well? My website was doing OK until recently. Where do Apache and Mysql get their setting direction from? Does Mysql has a set of default value enforced even when there is no my.cnf file?

Before I changed my.ini to my.cnf, I tried to delete my.ini, and the Mysql server won't start. So, I guess my.ini's presence is important. But, the setting in my.ini doesn't take effect until I changed it to my.cnf to match my path to executable.

I downloaded a new XAMPP 1.7.1 and indeed the default is my.ini and path to executable is my.ini. How come my website's path to executable is my.cnf? The developer who build the website left the company so I didn't know how he set XAMPP up. Earlier you mentioned that my.cnf existed in older version of XAMPP. So, if the previous developer installed an older version of XAMPP and upgrade to 1.7.1, why didn't the path to executable be updated from my.cnf to my.ini? Is it because the previous developer didn't follow the proper procedure of XAMPP upgrade?
pearlliang
 
Posts: 6
Joined: 22. September 2011 23:43

Re: my.ini setting not working

Postby Sharley » 02. November 2011 01:54

Pearl wrote:Fortunatly, I changed my.ini in xampp/mysql/bin to my.cnf in order to match the the Path to executable as:
"G:\xampp171\mysql\bin\mysqld.exe" --defaults-file="G:\xampp171\mysql\bin\my.cnf" MySQL
In 1.7.1 the MySQL configuration file in the bin folder is simply named my and looks like a shortcut - please do not rename this file.

Drag this file into an open text editor to edit and read - you will see in your text editor the file is actually named my.cnf

Do not change add or rename files unless you are absolutely sure you know what you are doing - ask here first.

Good luck. :)
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: my.ini setting not working

Postby pearlliang » 03. November 2011 02:39

Hi Sharley,

I found the cause of the problem and this maybe a bug with XAMPP1.7.1.

When I install XAMPP 1.7.1 (with Installation Wizard) on my Windows Server 2008 64-biz Operating System and choose to install mysql as a “service” in the Installation Wizard. My Path to be executable become my.cnf. But the file name in bin folder is my.ini. This caused the setting in my.ini not working.

When I install XAMPP 1.7.1 (with Installation Wizard) on my Windows Server 2008 64-biz Operating System and “Don’t” choose to install mysql as a service in the Installation Wizard. Then, My Path to be executable become my.ini. And the file name in bin folder is my.ini.

Thanks.

Pearl Liang
Web Developer
Supermicro
408-546-8244
pearlliang
 
Posts: 6
Joined: 22. September 2011 23:43

Re: my.ini setting not working

Postby Sharley » 03. November 2011 03:00

This maybe an issue with the installer not with a bug in XAMPP as there have been many issues with using the installer version and may be replaced in the next release:
viewtopic.php?f=16&t=48373

Try to uninstall 1.7.1 using these guidelines...
viewtopic.php?p=179699#p179699

...and then download and extract the xampp folder using the zip or 7zip version:
SourceForge XAMPP File Repository

You will need to run the setup_xampp.bat file to set the paths then you can install the services by ticking the Svc boxes in the Control Panel.

Next check your mysql entry by using the SCM... button in the CP and you should see that the 'Path to executable' contains the correct my.ini file location - I have just tested this using a fresh 1.7.1 install using the 7z version.

The installer included in most versions of XAMPP has been known to be buggy on some Windows systems and is the main reason I never use it.

Also in the archive version the my.ini file is there in the mysql\bin folder not a my shortcut file as I incorrectly pointed out above.
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: my.ini setting not working

Postby pearlliang » 10. November 2011 21:34

Thanks. Good to know this issue is related to the Installer.
pearlliang
 
Posts: 6
Joined: 22. September 2011 23:43

Re: my.ini setting not working

Postby Sharley » 11. November 2011 00:41

Pearl wrote:Thanks. Good to know this issue is related to the Installer.
Pearl. is everything working as expected now?

Also, for future support requests so that we can provide best version support, could you please add your XAMPP version and your current Windows OS to your profile:
viewtopic.php?f=16&t=48626

Thanks.
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
Operating System: Win 7 Pro 32bit/XP Pro SP3


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 52 guests