Mini Servers: MySQL problems

Mini Servers: Compact but fully functional.

MySQL problems

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

Problems

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>

Top

mysql_start.bat

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

Top

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

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

Top

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

Conclusion

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.

Top

---

Ric