Mini Servers: MySQL problems

UNDER COSTRUCTION, PLEASE SEE THIS POST IN RESERVE COPY

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: <pre> 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' </pre>

You can force a port as follows:

<pre> 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 </pre>

This clearly works like I said there is no configuration file on the search paths:

<pre> 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 </pre>

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:

<pre> --defaults-file=my.cnf (this file is contained in the folder with the execuitable folder bin) </pre>

Hence this will give: <pre> 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> </pre>

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:

<pre> start \bin\mysqld-opt.exe --defaults-file=/bin/my.cnf </pre>

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:

<pre> The following options may be given as the first argument: --defaults-file=# Only read default options from the given file # </pre>

From the above this would be a logical (Install.bat):

<pre> C:\mini_server_14\bin\mysqld-opt.exe --defaults-file=C:/mini_server_14/bin/my.cnf --install mini_server_14 </pre>

However it produces this error:

<pre> 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 . . . </pre>

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:

<pre> C:\mini_server_14\bin\mysqld-opt.exe --install mini_server_14 --defaults-file=C:/mini_server_14/bin/my.cnf </pre>

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: <pre> mysqld-opt --verbose --help > info.txt </pre>

This creates a file where you can read the details at your own leisure. Of importance is this line:

<pre> 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 </pre>

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