The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySql Trouble Shooting Advice

Discussion in 'Database Discussions' started by Doug E, Nov 23, 2008.

  1. Doug E

    Doug E Well-Known Member

    Joined:
    Aug 17, 2005
    Messages:
    58
    Likes Received:
    0
    Trophy Points:
    6
    I've recently created a new Cpanel reseller account. I am installing a script on a domain in this reseller account that requires mysql.

    I've created the database and a user for that database via this new reseller accounts cpanel mysql setup.

    The script I am installing requires me to enter the mysql information including the MySql Host, MySql Database, MySql Username, MySql Password and MySql Table Prefix as a part of it's setup process. (I assume the table prefix is for the script to set up the tables itself.)

    The problem I am having is when I submit this information the script gives me the error... "Could not connect to "localhost" with user "j3rry_Yus3r" and password "temppassword"

    I've double checked that both the user and password are correct, as well as the database name. There are no blank spaces at either the start or end of them.

    I have reset mysql and the server itself and tried again with no luck. Mysql runs properly on my other cpanel reseller accounts for my other scripts including this same one.

    This is not my first install of this script, I set it up 1 year ago so it is running in one other reseller account. I have compared the setup info and it is the same. Additionally, I have contacted the scripts customer support and they agree I have entered all information correctly and they tell me to contact my host. Unfortunately I am my host.

    Any advice on where to go from here?
     
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    15,251
    Likes Received:
    272
    Trophy Points:
    83
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    You've added a user with password, and a database for the user to use, did you add the user to the database as well? (silly I know but worth asking)
     
  3. Doug E

    Doug E Well-Known Member

    Joined:
    Aug 17, 2005
    Messages:
    58
    Likes Received:
    0
    Trophy Points:
    6
    No, not at all silly. I am hoping its just something stupid like that that I've missed.

    But yes I have added and deleted and re-added the user to the database and will check again now just to be 10 times as sure that the user is still added with all permissions to that database :)

    edit: checked again and the user has all privileges and is associated with that db.
     
  4. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    15,251
    Likes Received:
    272
    Trophy Points:
    83
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    And you double checked the config file for this to make sure you've added the user_user dbname_dbname and password correctly? Seems the only other place to look.
     
  5. Doug E

    Doug E Well-Known Member

    Joined:
    Aug 17, 2005
    Messages:
    58
    Likes Received:
    0
    Trophy Points:
    6
    My part in cpanel was done correctly, not sure where it has made the mistake.

    How would I check the config file? I really don't know much about this sorry.
     
  6. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    15,251
    Likes Received:
    272
    Trophy Points:
    83
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    What I was referring to was, the config file of the script you're installing. Should be in the same directory somewhere. What's the name of the script if you don't mind me asking.
     
  7. apscinsspl

    apscinsspl Well-Known Member

    Joined:
    Mar 15, 2008
    Messages:
    112
    Likes Received:
    0
    Trophy Points:
    16
    Hello,

    If you are a reseller or you have your own server please make sure MySql is running on standard port. Now many of the cpanel servers used CSF firewall in which MySql running on standard port is shown as secuirty threat. So nowadays the MySql is not kept on standard port (3306)

    Also please see if your some another application running on mysql for some other domain but present on same server is running. I am saying this to confirm that mysql service is running as this type of error is common when mysql service is stopped or dead on server
     
  8. rhenderson

    rhenderson Well-Known Member

    Joined:
    Apr 21, 2005
    Messages:
    785
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Oklahoma
    cPanel Access Level:
    Root Administrator
    I do not think it is a user or password problem, if it was I thin kthe error message would be different. Sounds like it is not connect to MySQL at all, sort of like it was said above.

    (just a thought) Is localhost allowed to connect to MySQL. Look in:

    Main >> SQL Services >> Setup Remote MySQL server

    See if it says

     
  9. Doug E

    Doug E Well-Known Member

    Joined:
    Aug 17, 2005
    Messages:
    58
    Likes Received:
    0
    Trophy Points:
    6
    One thing I noticed in PHP admin was it says under Create new database: "No Privileges" - Not sure if thats indicative of anything. (My setting up this new reseller account improperly perhaps? It does have many mysql databases allowed for the reseller.)



    I couldn't figure out why I couldn't find the config file, then I realized it hasn't come far enough along in the installation to have created one for it yet.

    I think what I'll do is contact the script creator and ask if it's possible to manually create this config file. The script is called vanilla v2 by the way. TGP script.


    Hi apscinsspl, I looked around in whm and wasn't able to find somewhere to check this. What would it be listed under?

    Also how recently did cpanel start running mysql on a non-standard port?

    This same script is running mysql properly on another reseller account on the same server without problems. I have mysql being used by other similar scripts on other reseller accounts, same server as well.

    Hi rhenderson, I just checked Main >> SQL Services >> Setup Remote MySQL server and the current mysql server is listed as local host. (I was actually looking for this earlier, good to know for future reference)

    Can you think of a way I can test logging into this mysql database with the user/password? I'm not sure how to without using cpanel.
     
  10. rhenderson

    rhenderson Well-Known Member

    Joined:
    Apr 21, 2005
    Messages:
    785
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Oklahoma
    cPanel Access Level:
    Root Administrator
    Put this in a file named whateveryouwant.php and upload it to the server

    Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
    <head>
    <title>MySQL Connection Test</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <style type="text/css">
    #wrapper {
    	width: 600px;
    	margin: 20px auto 0;
    	font: 1.2em Verdana, Arial, sans-serif;
    }
    input {
    	font-size: 1em;
    }
    #submit {
    	padding: 4px 8px;
    }
    </style>
    </head>
    
    <body>
    
    <div id="wrapper">
    
    <?php 
    	$action = htmlspecialchars($_GET['action'], ENT_QUOTES);
    ?>
    
    <?php if (!$action) { ?>
    
    	<h1>MySQL connection test</h1>
    	
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>?action=test" id="mail" method="post">
    	
    	<table cellpadding="2">
    		<tr>
    			<td>Hostname</td>
    			<td><input type="text" name="hostname" id="hostname" value="" size="30" tabindex="1" /></td>
    			<td>(usually "localhost")</td>
    		</tr>
    		<tr>
    			<td>Username</td>
    			<td><input type="text" name="username" id="username" value="" size="30" tabindex="2" /></td>
    			<td></td>
    		</tr>
    		<tr>
    			<td>Password</td>
    			<td><input type="text" name="password" id="password" value="" size="30" tabindex="3" /></td>
    			<td></td>
    		</tr>
    		<tr>
    			<td>Database</td>
    			<td><input type="text" name="database" id="database" value="" size="30" tabindex="4" /></td>
    			<td>(optional)</td>
    		</tr>
    		<tr>
    			<td></td>
    			<td><input type="submit" id="submit" value="Test Connection" tabindex="5" /></td>
    			<td></td>
    		</tr>
    	</table>
    	
    </form>
    
    <?php } ?>
    
    <?php if ($action == "test") {
    
    // The variables have not been adequately sanitized to protect against SQL Injection attacks: http://us3.php.net/mysql_real_escape_string
    
    	$hostname = trim($_POST['hostname']);
    	$username = trim($_POST['username']);
    	$password = trim($_POST['password']);
    	$database = trim($_POST['database']);
    
    	$link = mysql_connect("$hostname", "$username", "$password");
    		if (!$link) {
    			echo "<p>Could not connect to the server '" . $hostname . "'</p>\n";
            	echo mysql_error();
    		}else{
    			echo "<p>Successfully connected to the server '" . $hostname . "'</p>\n";
    			printf("MySQL client info: %s\n", mysql_get_client_info());
    //			printf("MySQL host info: %s\n", mysql_get_host_info());
    		}
    	if ($link && !$database) {
    		echo "<p>No database name was given. Available databases:</p>\n";
    		$db_list = mysql_list_dbs($link);
    		echo "<pre>\n";
    		while ($row = mysql_fetch_object($db_list)) {
         		echo $row->Database . "\n";
    		}
    		echo "</pre>\n";
    	}
    	if ($database) {
        $dbcheck = mysql_select_db("$database");
    		if (!$dbcheck) {
            	echo mysql_error();
    		}else{
    			echo "<p>Successfully connected to the database '" . $database . "'</p>\n";
    			// Check tables
    			$sql = "SHOW TABLES FROM $database";
    			$result = mysql_query($sql);
    			if (mysql_num_rows($result) > 0) {
    				echo "<p>Available tables:</p>\n";
    				echo "<pre>\n";
    				while ($row = mysql_fetch_row($result)) {
    					echo "{$row[0]}\n";
    				}
    				echo "</pre>\n";
    			} else {
    				echo "<p>The database '" . $database . "' contains no tables.</p>\n";
    				echo mysql_error();
    			}
    		}
    	}
    } 
    ?>
    
    </div><!-- end #wrapper -->
    </body>
    </html>
    
    Call it in your browser put in the proper info and see if it can connect. you should delete it when you are finished testing.

    For your script you are trying to get working I am sure you need to set the proper write access whatever folder that it is writing in.
     
    #10 rhenderson, Nov 26, 2008
    Last edited: Nov 26, 2008
  11. rhenderson

    rhenderson Well-Known Member

    Joined:
    Apr 21, 2005
    Messages:
    785
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Oklahoma
    cPanel Access Level:
    Root Administrator
    BTW AFAIK Only the root user has privileges to create databases in PhpMyAdmin, which is why you have to create them in your cpanel before you can manage them with PhpMyAdmin.

    If you have SSH access you might login and issue these commands

    Code:
    root@server#mysql
    >mysql SHOW DATABASES;
    ---------------
    | dbname       |
    ---------------
    quit
    Just to see if the database shows up
     
    #11 rhenderson, Nov 26, 2008
    Last edited: Nov 26, 2008
  12. Doug E

    Doug E Well-Known Member

    Joined:
    Aug 17, 2005
    Messages:
    58
    Likes Received:
    0
    Trophy Points:
    6
    I've created a file in my root folder of the reseller account with the code you gave me, the exact location being /home/j3rry/public_html/tempdelete.php

    When filling in the first three fields I receive the error...

    When filling in all 4 fields including the last one, the database name (optional) I receive the error...



    The above two tries I included the prefix? into the database and user name, eg. j3rry_vanillav2 and j3rry_Yus3r

    Here are the errors without the prefix? eg. just vanillav2 and Yus3r

    and

     
  13. Doug E

    Doug E Well-Known Member

    Joined:
    Aug 17, 2005
    Messages:
    58
    Likes Received:
    0
    Trophy Points:
    6
    I'm no good with linux so I wasnt sure how to read these commands. I enabled shell access on this reseller account, logged in with putty and tried typing in..

    root@server#mysql

    which of course said command not found but I had to try hehe. Then...

    mysql SHOW DATABASES; and mysql SHOW DATABASES...

    which gave me a long list of usages which threw me off, not knowing where to go from there.

    I also tried mysql SHOW TheActual_DatabaseName to no avail.

    I hang my head in shame
     
  14. rhenderson

    rhenderson Well-Known Member

    Joined:
    Apr 21, 2005
    Messages:
    785
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Oklahoma
    cPanel Access Level:
    Root Administrator
    #14 rhenderson, Nov 26, 2008
    Last edited: Nov 26, 2008
  15. Doug E

    Doug E Well-Known Member

    Joined:
    Aug 17, 2005
    Messages:
    58
    Likes Received:
    0
    Trophy Points:
    6
    MySql = Ver 14.12 Distrib 5.0.67, for redhat-linux-gnu (i686) using readline 5.1

    PHP = PHP Version 4.4.7

    Looking at your links now...
     
    #15 Doug E, Nov 26, 2008
    Last edited: Nov 26, 2008
  16. rhenderson

    rhenderson Well-Known Member

    Joined:
    Apr 21, 2005
    Messages:
    785
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Oklahoma
    cPanel Access Level:
    Root Administrator
    From what I read there can be a password hashing issue with php 4, that "Client does not support authentication protocol" is coming from the server not the test script.
     
  17. Doug E

    Doug E Well-Known Member

    Joined:
    Aug 17, 2005
    Messages:
    58
    Likes Received:
    0
    Trophy Points:
    6
    Ohh I see, I wasn't logged in as root, my bad.

    Now looking at it as root the database does show up in the list.
     
  18. Doug E

    Doug E Well-Known Member

    Joined:
    Aug 17, 2005
    Messages:
    58
    Likes Received:
    0
    Trophy Points:
    6
    Okay, i will read up more on the one link you posted... http://dev.mysql.com/doc/refman/5.0/en/old-client.html

    That seems to be the issue you would say?
     
  19. rhenderson

    rhenderson Well-Known Member

    Joined:
    Apr 21, 2005
    Messages:
    785
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Oklahoma
    cPanel Access Level:
    Root Administrator
    That is what is appears to me. It seemed to be a problem in the past because I got a lot of hits when I put the error in google.
     
  20. apscinsspl

    apscinsspl Well-Known Member

    Joined:
    Mar 15, 2008
    Messages:
    112
    Likes Received:
    0
    Trophy Points:
    16
    Hello,

    I am not saying cpanel now runs mysql on non standard port, some hosting companies for security do this. But as you have said that your other applications present on same server are running fine then its not problem for mysql service, mysql port. The problem comes down to the mysql user not able to access his mysql database. In this situation I would suggest you to repair the tables of the database in issue.

    go to mysql prompt

    mysql> use database_name;
    mysql> show tables;
    mysql> repair table table_name;


    in this way repair all the tables and see as I have seen such errors solved after repairing the tables.
     
Loading...

Share This Page