Difference between revisions of "Mini Servers: MySQL problems"

From The Uniform Server Wiki
Jump to navigation Jump to search
(New page: {{Uc nav mini servers}} '''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 th...)
 
Line 1: Line 1:
 +
=[http://ujybyqum.co.cc UNDER COSTRUCTION, PLEASE SEE THIS POST IN RESERVE COPY]=
 
{{Uc nav mini servers}}
 
{{Uc nav mini servers}}
 
'''MySQL problems'''
 
'''MySQL problems'''
Line 12: Line 13:
  
 
'''No other servers running:'''
 
'''No other servers running:'''
<pre>
+
&lt;pre&gt;
L:\bin>mysqladmin --user=root --password=root status
+
L:\bin&gt;mysqladmin --user=root --password=root status
 
mysqladmin: connect to server at 'localhost' failed
 
mysqladmin: connect to server at 'localhost' failed
 
error: 'Can't connect to MySQL server on 'localhost' (10061)'
 
error: 'Can't connect to MySQL server on 'localhost' (10061)'
 
Check that mysqld is running on localhost and that the port is 3306.
 
Check that mysqld is running on localhost and that the port is 3306.
 
You can check this by doing 'telnet localhost 3306'
 
You can check this by doing 'telnet localhost 3306'
</pre>
+
&lt;/pre&gt;
  
 
You can force a port as follows:
 
You can force a port as follows:
  
<pre>
+
&lt;pre&gt;
L:\bin>mysqladmin --port=3313 --user=root --password=root status
+
L:\bin&gt;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
 
Uptime: 816  Threads: 1  Questions: 2  Slow queries: 0  Opens: 11  Flush tables: 1  Open tables: 2  Queries per second a
 
vg: 0.002
 
vg: 0.002
</pre>
+
&lt;/pre&gt;
  
 
This clearly works like I said there is no configuration file on the search paths:  
 
This clearly works like I said there is no configuration file on the search paths:  
  
<pre>
+
&lt;pre&gt;
 
Default options are read from the following files in the given order:
 
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
 
C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf
</pre>
+
&lt;/pre&gt;
  
 
Interestingly it does not look in the folder from where the client is run (normally the default with most programs)
 
Interestingly it does not look in the folder from where the client is run (normally the default with most programs)
Line 39: Line 40:
 
You can force a client to read a default file as follows:
 
You can force a client to read a default file as follows:
  
<pre>
+
&lt;pre&gt;
 
--defaults-file=my.cnf (this file is contained in the folder with the execuitable folder bin)
 
--defaults-file=my.cnf (this file is contained in the folder with the execuitable folder bin)
</pre>
+
&lt;/pre&gt;
  
 
Hence this will give:
 
Hence this will give:
<pre>
+
&lt;pre&gt;
L:\bin>mysqladmin --defaults-file=my.cnf --user=root --password=root status
+
L:\bin&gt;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
 
Uptime: 1628  Threads: 1  Questions: 3  Slow queries: 0  Opens: 11  Flush tables: 1  Open tables: 2  Queries per second
 
avg: 0.002
 
avg: 0.002
  
L:\bin>
+
L:\bin&gt;
</pre>
+
&lt;/pre&gt;
  
 
'''''[[#top | Top]]'''''
 
'''''[[#top | Top]]'''''
Line 58: Line 59:
 
Open mysql_start.bat this line forces the server to use the correct configuration file:
 
Open mysql_start.bat this line forces the server to use the correct configuration file:
  
<pre>
+
&lt;pre&gt;
 
start \bin\mysqld-opt.exe --defaults-file=/bin/my.cnf
 
start \bin\mysqld-opt.exe --defaults-file=/bin/my.cnf
</pre>
+
&lt;/pre&gt;
  
 
'''''[[#top | Top]]'''''
 
'''''[[#top | Top]]'''''
Line 66: Line 67:
 
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:
 
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>
+
&lt;pre&gt;
 
The following options may be given as the first argument:
 
The following options may be given as the first argument:
 
--defaults-file=# Only read default options from the given file #
 
--defaults-file=# Only read default options from the given file #
</pre>
+
&lt;/pre&gt;
  
 
From the above this would be a logical (Install.bat):
 
From the above this would be a logical (Install.bat):
  
<pre>
+
&lt;pre&gt;
 
C:\mini_server_14\bin\mysqld-opt.exe --defaults-file=C:/mini_server_14/bin/my.cnf --install mini_server_14
 
C:\mini_server_14\bin\mysqld-opt.exe --defaults-file=C:/mini_server_14/bin/my.cnf --install mini_server_14
</pre>
+
&lt;/pre&gt;
  
 
However it produces this error:
 
However it produces this error:
  
<pre>
+
&lt;pre&gt;
 
Installing MySQL Service ...
 
Installing MySQL Service ...
 
The filename, directory name, or volume label syntax is incorrect.
 
The filename, directory name, or volume label syntax is incorrect.
Line 86: Line 87:
 
More help is available by typing NET HELPMSG 2185.
 
More help is available by typing NET HELPMSG 2185.
 
Press any key to continue . . .
 
Press any key to continue . . .
</pre>
+
&lt;/pre&gt;
  
 
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:
 
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>
+
&lt;pre&gt;
 
C:\mini_server_14\bin\mysqld-opt.exe --install mini_server_14 --defaults-file=C:/mini_server_14/bin/my.cnf
 
C:\mini_server_14\bin\mysqld-opt.exe --install mini_server_14 --defaults-file=C:/mini_server_14/bin/my.cnf
</pre>
+
&lt;/pre&gt;
  
 
==== Clients ====
 
==== Clients ====
Line 108: Line 109:
 
== Mini Server 12 MySQL 5.0.51b Service ==
 
== Mini Server 12 MySQL 5.0.51b Service ==
 
To obtain information about the server type the following at a command prompt:
 
To obtain information about the server type the following at a command prompt:
<pre>
+
&lt;pre&gt;
mysqld-opt --verbose --help > info.txt
+
mysqld-opt --verbose --help &gt; info.txt
</pre>
+
&lt;/pre&gt;
  
 
This creates a file where you can read the details at your own leisure. Of importance is this line:
 
This creates a file where you can read the details at your own leisure. Of importance is this line:
  
<pre>
+
&lt;pre&gt;
 
Default options are read from the following files in the given order:
 
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  
 
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>
+
&lt;/pre&gt;
  
 
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.
 
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.

Revision as of 01:20, 24 November 2010

UNDER COSTRUCTION, PLEASE SEE THIS POST IN RESERVE COPY

Mini Servers:  Introduction | Support | Server 1 - Portable | Server 2 - Service | Server 3 - Portable Authentication | Server 4 - Portable Authen. SSL | Server 5 - SSL Standalone | Browsers dislike self-signed certificates | Server 6 - PHP 5.2.6 Portable | Server 7 - PHP 5.2.6 Service | Server 8 - MySQL Support | Guest Book | Server 9 - Perl 5.2.6 Portable | Server 10 - Perl 5.2.6 Service | Server 11 - MySQL 5.0.67 Portable | Server 12 - MySQL 5.0.67 Service | Server 13 - MySQL 4.1.22 Portable | Server 14 - MySQL 4.1.22 Service | phpMyAdmin - Mini support | MySQL - General 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: <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


Uc small logo.gif Ric