MySQL Troubleshooting FAQ for Metawerx Customers - v1.1



This document describes the use of MySQL remotely through SSH, using the PLINK client.

This information is intended for use by hosting customers of Metawerx Developer Hosting,
however, it also serves as a useful guide for getting started with MySQL, either
through a secure, encrypted remote connection, or locally for new developers.



CONTENTS






INTRODUCTION



Remote connections to MySQL are made by first setting up an SSH connection
using SSH (Linux) or PuTTY/PLink (Windows).  Please read the PLINK section
of the following FAQ before attempting to connect to MySQL.

	SSH (RSync and PuTTY) Info/Tips
	Section 2
	"Starting a connection with PLINK"
	
If you are unable to connect, then the troubleshooting section below
contains some common problems and solutions.

After going through the Troubleshooting section, if you are still having
problems connecting, please contact support for assistance.

For faster turn-around, please include a screen shot of the problem you
are having if possible, including the MySQL command window and the PLINK
connection window.




REMOTE CONNECTION TROUBLESHOOTING FAQ



1.	I am prompted for a password each time I try to connect.

2.	I get the following error, what is wrong?
	ERROR 1045: Access denied for user: 'mydatabase@localhost' (Using password: YES)

3.	I get the following error, what is wrong?
	Enter password: ***
	ERROR 1045: Access denied for user: 'mydatabase@localhost' (Using password: YES)

4.	I get the following error, what is wrong?
	ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)

5.	I get the following error, what is wrong?
	Enter password: **********
	ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)

6.  Other problems.


1.	I am prompted for a password each time I try to connect.

A.	MySQL is attempting to connect to the wrong database, and also prompting for
	a password.
	This will happen if you use a space after the "-p" parameter in the MySQL
	command.  -p should be specified without any space after it.
	For example, this will cause a problem: mysql -hlocalhost -u user -p pass
	The correct command is as follows: mysql -hlocalhost -uuser -ppass


2.	I get the following error, what is wrong?
	ERROR 1045: Access denied for user: 'mydatabase@localhost' (Using password: YES)

A.	If you are also being prompted for a password, see the next FAQ question.
	(Make sure you are not using a space between "-p" and your password).
	If not, then either the password is incorrect or the database has been set up
	incorrectly.  Please contact support and attach a screenshot of this problem.

	Another problem that can happen here, is if you are already running MySQL
	on your own PC.  In this case, you *must* use port 3307 (or another port)
	instead of 3306, because your own MySQL instance (on your PC) will already
	be listening on port 3306, and it will be giving you the access denied error,
	probably because you are using the metawerx username/password, but your the
	MySQL instance running on your PC doesn't recognise this username/password.
	
3.	I get the following error, what is wrong?
	Enter password: ***
	ERROR 1045: Access denied for user: 'mydatabase@localhost' (Using password: YES)

A.	MySQL is attempting to connect to the wrong database, and also prompting for
	a password.
	This will happen if you use a space after the "-p" parameter in the MySQL
	command.  -p should be specified without any space after it.
	For example, this will cause a problem: mysql -hlocalhost -u user -p pass
	The correct command is as follows: mysql -hlocalhost -uuser -ppass


4.	I get the following error, what is wrong?
	ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)

A.	No connection can be made to the database.  This means the PLINK connection
	may not be correctly set up.
	If you are running MySQL on your machine:
		Check your PLINK command includes the following: "-L 3307:mysql.metawerx.net:3306"
		Check your MySQL command includes the following: "-hlocalhost -P3307"
		Check your PLINK window is active (it should be showing a prompt)
	If you are not running MySQL on your machine, and really want to use port 3306:
		Check your PLINK command includes the following: "-L 3306:mysql.metawerx.net:3306"
		Check your MySQL command includes the following: "-hlocalhost"
		Check your PLINK window is active (it should be showing a prompt)

5.	I get the following error, what is wrong?
	Enter password: **********
	ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)

A.	There are two problems here.
	You will be prompted for a password, and the MySQL command will attempt to
	connect to the wrong database if you use a space after the "-p" parameter
	in the MySQL command.  -p should be specified without any space after it.
	For example, this will cause a problem: mysql -hlocalhost -u user -p pass
	The correct command is as follows: mysql -hlocalhost -uuser -ppass
	Secondly, no connection can be made to the database.  This means the PLINK
	connection may not be correctly set up.
	If you are running MySQL on your machine:
		Check your PLINK command includes the following: "-L 3307:mysql.metawerx.net:3306"
		Check your MySQL command includes the following: "-hlocalhost -P3307"
		Check your PLINK window is active (it should be showing a prompt)
	If you are not running MySQL on your machine, and really want to use port 3306:
		Check your PLINK command includes the following: "-L 3306:mysql.metawerx.net:3306"
		Check your MySQL command includes the following: "-hlocalhost"
		Check your PLINK window is active (it should be showing a prompt)

6.  Other problems

	- Please ensure you are using the latest version of MySQL on your local machine,
	  including the latest version of the client.
	  For most Linux users familiar with MySQL, this is usually the problem.

	- To test if your SSH/PLINK connection is working, and tunnelling through to our
	  server, see if you can telnet to localhost, port 3307.
	  If you can, you should get some garbage showing on the screen, because the server
	  thinks you a MySQL client.  This means you can connect correctly.  Next, check your
	  client is pointing to localhost, and port 3307 correctly.




CREATING A SIMPLE DATABASE WITH MYSQL



This tutorial explains how to create a simple database, with two rows
and how to query it remotely using the MySQL command.  The table will
then be removed.

1. Connect to MySQL on the server

	In this example, we will assume your username is "testa", your password
	is "mypass", your database is "mydatabase" and you have created an
	SSH tunnel from port 3307 on your machine, to our MySQL server on port
	3306 using the following tunnel:
	
		-L 3307:mysql.metawerx.net:3306
		
	CD to your MySQL\bin folder and run the following command:

		mysql --port=3307 -hlocalhost -utesta -pmypass
	
	You should be presented with a prompt as follows:
	
		mysql>
	
2. In MySQL, switch to your database

	You can connect to your database using two methods.
	
	The first is to specify the database on the command line as follows:

		mysql --port=3307 -hlocalhost -utesta -pmypass mydatabase
	
	The second way is to connect first, then issue a USE command as
	follows:
	
		mysql> USE mydatabase
		
	The system should respond:
	
		Database changed
		mysql> 
		
3.	Now that you are in the right database, list all your tables using
	show "show tables" command.
	
		mysql> show tables;
		
	Remember to use a semicolon at the end of the command, or you will
	be prompted for additional lines, like in the following example:
	
		mysql> show tables
		    -> ;
		    
	The system will respond with a list of your tables, or "Empty set"
	if you have not created any tables yet.
	
4.	Create a table using the following syntax (just copy and paste this
	example if you like)
		
		CREATE TABLE `contact` (
			`id` int(11) NOT NULL default '0',
			`name` varchar(200) NOT NULL default '',
			PRIMARY KEY  (`id`)
		) TYPE=MyISAM; 

	This creates a table with an ID column and a NAME column.
	You can use the "show tables" command to show the table to
	ensure it has been created.
	
5.	Insert two rows using the following commands:

		INSERT INTO contact (id, name) VALUES (1, 'Test1');
		INSERT INTO contact (id, name) VALUES (2, 'Test2');

	Your database now contains two rows.
	
6.	Run a SELECT statement to select and display all rows from the table.

		SELECT * FROM contact;

	This will show:

		+----+-------+
		| id | name  |
		+----+-------+
		|  1 | Test1 |
		|  2 | Test2 |
		+----+-------+
		2 rows in set (0.00 sec)

7.	We will now delete the table, because we only created it for this tutorial.

		DROP TABLE contact;

8.	You may now exit the MySQL system by using the QUIT command.

		QUIT
		


COPYRIGHT NOTICE



All material is (c)Copyright Neale Rudd, Metawerx Pty Ltd, 1997-2014.

If you found this information helpful, additional guides can be found at
http://www.metawerx.net/admin under the 'Resources' link.



END OF DOCUMENT