Mini Servers: MySQL problems
Compact but fully functional.
While designing the MySQL mini server series I found a number of inconsistencies. Running two version 4’s and two version 5’s sometimes they worked at other times would fail even worst databases created in one would appear in a different server.
I was pleased with my solution for version 5’s until the same architecture failed on version 4’s. Interestingly changing the password on a mini not only killed the mini it also took Uniform Server’s MySQL server with it. Running either as a service or program made little difference to the failures.
Server 13 - MySQL 4.1.22 Portable
The above problems started after I introduced version 4 series. It became obvious an incorrect configuration file was being picked-up by a server’s clients. Strange that since there is no configuration files on the system paths. In reality the clients were using defaults confirmed by this error message:
No other servers running:
L:\bin>mysqladmin --user=root --password=root status mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to MySQL server on 'localhost' (10061)' Check that mysqld is running on localhost and that the port is 3306. You can check this by doing 'telnet localhost 3306'
You can force a port as follows:
L:\bin>mysqladmin --port=3313 --user=root --password=root status Uptime: 816 Threads: 1 Questions: 2 Slow queries: 0 Opens: 11 Flush tables: 1 Open tables: 2 Queries per second a vg: 0.002
This clearly works like I said there is no configuration file on the search paths:
Default options are read from the following files in the given order: C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf
Interestingly it does not look in the folder from where the client is run (normally the default with most programs)
You can force a client to read a default file as follows:
--defaults-file=my.cnf (this file is contained in the folder with the execuitable folder bin)
Hence this will give:
L:\bin>mysqladmin --defaults-file=my.cnf --user=root --password=root status Uptime: 1628 Threads: 1 Questions: 3 Slow queries: 0 Opens: 11 Flush tables: 1 Open tables: 2 Queries per second avg: 0.002 L:\bin>
To find the configuration file MySQL (mini server 13) searches the same folders as above (C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf) so why does mini server 13 work or how does it find the correct file?
Open mysql_start.bat this line forces the server to use the correct configuration file:
start \bin\mysqld-opt.exe --defaults-file=/bin/my.cnf
Server 13 - MySQL 4.1.22 Service
Running server as a service (mini server 14) identical paths are searched again the configuration file will not be found. The server needs to be forced to read the configuration file however there is a little twist to this:
The following options may be given as the first argument: --defaults-file=# Only read default options from the given file #
From the above this would be a logical (Install.bat):
C:\mini_server_14\bin\mysqld-opt.exe --defaults-file=C:/mini_server_14/bin/my.cnf --install mini_server_14
However it produces this error:
Installing MySQL Service ... The filename, directory name, or volume label syntax is incorrect. 080813 20:24:45 [ERROR] C:\mini_server_14\bin\mysqld-opt.exe: unknown option '--install' The service name is invalid. More help is available by typing NET HELPMSG 2185. Press any key to continue . . .
Well that was a real pain I know the install option works on its own so perhaps it’s the order of commands. I tried the following with success:
C:\mini_server_14\bin\mysqld-opt.exe --install mini_server_14 --defaults-file=C:/mini_server_14/bin/my.cnf
Clients mysqladmin and mysql need to be forced to find the correct configuration file as above.
However the only information specific to a client is the port, hence it's slightly quicker to type the port for example:
mysqladmin --port=3314 --user=root --password=root status
Alteratively you can use the slightly longer method:
mysqladmin --defaults-file=my.cnf --user=root --password=root status
Mini Server 12 MySQL 5.0.51b Service
To obtain information about the server type the following at a command prompt:
mysqld-opt --verbose --help > info.txt
This creates a file where you can read the details at your own leisure. Of importance is this line:
Default options are read from the following files in the given order: C:\my.ini C:\my.cnf C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\mini_server_12\my.ini C:\mini_server_12\my.cnf
Both clients also produce a similar result (mysqladmin --help and mysql --help) for the search path. Placing the configuration file in folder C:\mini_server_12 allows it to be picked up by the three application.
Hence when typing commands there is no need to force a port or file for example this now works as expected:
mysqladmin --user=root --password=root status
I have included this page to explain why the differences in commands when using server versions 4 or 5. PhpMyAdmin has no problems you need to define the port used hence works as expected.