Database accessing error [SOLVED]

[opersai]

I'm getting Exception null error when I try to submit form. And I get the stack trace error below. Can someone tell me where is the problem? Thanks a million

Load LOCAL DB infomation:jdbc:mysql://localhost:3306/ssheidae
java.sql.SQLException: Invalid authorization specification, message from server: "Access denied for user 'teap'@'localhost' (using password: YES)"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1977)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1900)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:2471)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:813)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:1771)
at com.mysql.jdbc.Connection.<init>(Connection.java:440)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:400)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at sfu.db.DBConnector.createConnection(DBConnector.java:228)
at sfu.db.DBConnector.getConnection(DBConnector.java:184)
at sfu.db.DBConnector.getTables(DBConnector.java:291)
at sfu.db.DBConnector.createTablesIfNotExist(DBConnector.java:119)
at iat.ConfigServlet.checkTables(ConfigServlet.java:124)
at iat.ConfigServlet.init(ConfigServlet.java:74)
at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1173)
at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:993)
at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4149)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4458)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:526)
at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:987)
at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:909)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:495)
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1206)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:314)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
at org.apache.catalina.core.StandardHost.start(StandardHost.java:722)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
at org.apache.catalina.core.StandardService.start(StandardService.java:516)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
at org.apache.catalina.startup.Catalina.start(Catalina.java:583)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:288)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413)
java.lang.NullPointerException
at sfu.db.DBConnector.getTables(DBConnector.java:302)
at sfu.db.DBConnector.createTablesIfNotExist(DBConnector.java:119)
at iat.ConfigServlet.checkTables(ConfigServlet.java:124)
at iat.ConfigServlet.init(ConfigServlet.java:74)
at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1173)
at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:993)
at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4149)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4458)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:526)
at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:987)
at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:909)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:495)
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1206)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:314)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
at org.apache.catalina.core.StandardHost.start(StandardHost.java:722)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
at org.apache.catalina.core.StandardService.start(StandardService.java:516)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
at org.apache.catalina.startup.Catalina.start(Catalina.java:583)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:288)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413)

[ckeyrouz]

Access denied for user 'teap'@'localhost' (using password: YES)"

change the access user another username and password.

The user you are using does not have enough privilege to access the database you are using in this transaction.

[opersai]

Hey,

thanks. I'll try to grant all access to the users. I'm also the admin. Do you know what's the code to do that in mysql? thanks.

[ckeyrouz]

GRANT ALL PRIVILEGES ON [dbname].* to '[user]'@'[hostname]'
identified by '[password]'

This is the syntax in general.
In your case:

GRANT ALL PRIVILEGES ON ssheidae.* to 'teap'@'localhost'
identified by 'YES'

[opersai]

thanks. =D

I did just about that, and changed a user too, but it still doesn't seem to work:

grant all privileges on *.* to 'hwm'@'localhost' identified by 'thepassword' with grant option;

=(

[ckeyrouz]

Is it still the same error?

If this is the case try accessing using another user but if it is another error please post the exception

[opersai]

Yes, unfortunately. I have tried to reset all the password on the server. And re-granted all privileges for user 'hwm'. But no use. I'll try a different user as you have suggested.

ps. I read somewhere else, the part message "(using password: YES)" doesn't mean the password used is "YES", but YES, a password is used.

=p twisted error message isn't it. =p

[ckeyrouz]

Try executing the command I gave you and then after it execute this command:

FLUSH PRIVILEGES

[ckeyrouz]

Can you please post the code you are using to connect to the database?

[opersai]

Thanks a lot for helping!

I'm not sure if these are the codes in charge - I'm changing a template to work. I apologize if it's too long or too messy.


package iat.db;

import sfu.db.DBInfo;

/**
* <p>Title: DBInfoImpl </p>
*
* <p>Description: DBInfoImpl implements DBInfo to supply the application
* database connection information</p>
*
* @author Ty Mey Eap
* @version 1.0
*/
public class DBInfoImpl implements DBInfo{

/**
* Default constructor
*/
public DBInfoImpl() {
}
/**
* return database URL
*/
public String getUrl(){
return "jdbc:mysql://localhost:3306/";
}
/**
* return database access username
*/
public String dbUsername(){
return "hwm";
}
/**
* return database access password
*/
public String dbPassword(){
return "password";
}
}


package iat;

import iat.db.DBUtil;
import java.io.File;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletResponse;
import org.w3c.dom.Element;
import sfu.db.DBConnector;
import sfu.util.InetUtil;
import sfu.util.XmlApi;

/**
* <p>Title: ConfigServlet</p>
*
* <p>Description: This is a servlet utility that will be loaded when Tomcat start to
* initailize all the required variables.</p>
*
* @author Ty Mey Eap
* @version 1.0
*/
public class ConfigServlet extends HttpServlet {
final public static String NS = "http://www.sfu.ca/IAT352/";
public static DBConnector DB = null;
public static String BASE_URL = "http://localhost:8080";
public static String HOME = "";
public static String TMP_DIR = "tmp";
public static String DATA_DIR = "WEB-INF/data";
public static HashMap<String, ArrayList> TEABLE_DEFS = null;
public static String CONTEXT = "";
/**
* Implements init servlet interface, whihc will be invoked by Tomcat
* to configure the servlet.
* @param config ServletConfig
* @throws ServletException
*/
public void init(ServletConfig config) throws ServletException {
HOME = config.getServletContext().getRealPath("");
String h = HOME.replace('\\','/');
CONTEXT = h.substring(h.lastIndexOf("/"));
TMP_DIR = HOME + "/tmp";
DATA_DIR = HOME + "/WEB-INF/data";
File f = new File(TMP_DIR);
f.mkdir();
try{
DB = new DBConnector(config);
checkTables();
}catch(Exception e){
e.printStackTrace();
}
}

/**
* You can use this method to initialize the DBConnector when you want to test
* your implementation.
*
* @param home String This application home directory. It should be under Tomcat webapps.
* @param username String Your student username
* @param dbInfoClassname The name of your DBInfo implementation class
*/
public static void initialize(String home, String username, String dbInfoClassname){
HOME = home;
BASE_URL = BASE_URL + "/" + username;
TMP_DIR = HOME + "/tmp";
DATA_DIR = HOME + "/WEB-INF/data";
try{
DB = new DBConnector(username, dbInfoClassname);
checkTables();
}catch(Exception e){
e.printStackTrace();
}
}

/**
* Helper table for checking and creating tables for the web application.
* The helper method also contains all the table definitions, and it is
* a good place to add more tables.
*/
public static void checkTables()throws Exception{
HashMap<String, String> tables = new HashMap<String, String>();
tables.put("metadata", "CREATE TABLE metadata (mid INT NOT NULL AUTO_INCREMENT, lang"+
" VARCHAR(5), media_type VARCHAR(16), url TEXT, authors VARCHAR(255),"+
"publisher VARCHAR(64), format VARCHAR(64), title TEXT, description TEXT, "+
"PRIMARY KEY (mid))");
tables.put("hits", "CREATE TABLE hits (mid INT, count INT, "+
"PRIMARY KEY (mid))");
tables.put("reviews", "CREATE TABLE reviews (rid INT NOT NULL AUTO_INCREMENT, "+
"mid INT, userid VARCHAR(64), reviewdate BIGINT, rating INT, "+
"comment TEXT, PRIMARY KEY (rid))");
tables.put("forum_topics", "CREATE TABLE forum_topics (fid INT NOT NULL "+
"AUTO_INCREMENT, title TEXT, parentid INT, PRIMARY KEY (fid))");
tables.put("forum_posts", "CREATE TABLE forum_posts (threadid INT NOT "+
"NULL AUTO_INCREMENT, fid INT, title TEXT, userid VARCHAR(64), "+
"replyto INT, postdate BIGINT, description TEXT, PRIMARY KEY (threadid))");
tables.put("users", "CREATE TABLE users (userid VARCHAR(64), fname VARCHAR(64), lname VARCHAR(64), PRIMARY KEY (userid))");
tables.put("favorites", "CREATE TABLE favorites (userid VARCHAR(64), mid INT, PRIMARY KEY (userid, mid))");
DB.createTablesIfNotExist(tables);
getTableDefs(tables);
}

/**
* helper method for extracting table definitions
*/
private static void getTableDefs(HashMap<String, String> tables){
TEABLE_DEFS = new HashMap<String, ArrayList>();
Iterator it = tables.keySet().iterator();
while(it.hasNext()){
String key = (String)it.next();
String def = tables.get(key);
def = def.substring(def.indexOf("(")+1);
String[] defs = def.split(",");
ArrayList<String> tableDef = new ArrayList<String>();
for (int i = 0; i < defs.length; i++){
if (!getTableDef(defs[i].trim(), tableDef)){
i = defs.length;
}
}
TEABLE_DEFS.put(key, tableDef);
}
}
/**
* helper method for extracting table definitions
*/
private static boolean getTableDef(String tdef, ArrayList<String> tableDef){
String colname = null;
int index = tdef.indexOf(" ");
if (index > 0){
colname = tdef.substring(0, index);
if (colname.equalsIgnoreCase("PRIMARY")||colname.equalsIgnoreCase("UNIQUE")){
return false;
}
tdef = tdef.substring(index +1).trim().toUpperCase();
}else{
return false;
}
if (tdef.startsWith("VARCHAR")){
tableDef.add(colname+ ":VARCHAR");
}else if (tdef.startsWith("CHAR")){
tableDef.add(colname+ ":CHAR");
}else{
if (tdef.indexOf(" ") > 0){
tdef = tdef.substring(0, tdef.indexOf(" "));
}
tableDef.add(colname+ ":"+tdef);
}
return true;
}

/**
* loadData delete all the rows in the table and loads new data to data
* from an XML file onto the table.
*
* @param tableFilename String - the XML file name the contain table data.
* The XML must follow the naming convention.
* @return String - null if successful, otherwise error message
*/
public static String loadData(String tableFilename, boolean isDeleteRows){
try{
Element data = XmlApi.readXmlElement(DATA_DIR +"/"+tableFilename);
if (data == null){
return "FILE NOT FOUND ERROR";
}
List tablesData = XmlApi.getChildren(data);
for (int i = 0; i < tablesData.size(); i++){
loadTableData((Element)tablesData.get(i), isDeleteRows);
}
return "SUCCESS: ";
}catch(Exception e){
e.printStackTrace();
return "GENERAL ERROR: "+e.getMessage();
}
}
/**
* loadData delete all the rows in the table and loads new data to data
* from an XML Element onto the table.
*
* @param tableData Element - the XML element the contain table data.
* The XML Element must follow the naming convention.
* @param isDeleteRows boolean Delete all rows in the table before
* insertion if set to true, and do nothing otherwise
*/
public static void loadTableData(Element tableData, boolean isDeleteRows)throws Exception{
String tableName = XmlApi.getAttributeValue(tableData, "name");
System.out.println("Loading table "+tableName +" data.");
HashMap<String, String> tdefs = getTableDef(tableName);
List rows = XmlApi.getChildren(tableData);
if (rows.size() > 0){
if (isDeleteRows){
DB.executeUpdate("DELETE FROM "+tableName);
}
for (int i = 0; i < rows.size(); i++){
Element rowData = (Element)rows.get(i);
List cols = XmlApi.getChildren(rowData);
if (cols.size() > 0){
String colNames = null;
String colValues = null;
for (int j = 0; j < cols.size(); j++){
Element colData = (Element)cols.get(j);
String colName = XmlApi.getAttributeValue(colData, "name");
String colValue = encodeStr(tdefs, colName, XmlApi.getText(colData));
if (colValue != null){
if (colNames == null){
colNames = colName;
colValues = colValue;
}else{
colNames += ","+colName;
colValues += ","+colValue;
}
}
}
if (colNames != null){
DB.executeUpdate("INSERT INTO "+tableName+" ("+colNames+") VALUES ("+colValues+")");
}
}
}
}
}
/**
* getTableDef returns a HashMap of a table's column definitions
*/
public static HashMap<String, String> getTableDef(String tableName){
ArrayList cols = TEABLE_DEFS.get(tableName);
HashMap<String, String> tdefs = new HashMap<String, String>();
if (cols != null){
for (int i = 0; i < cols.size(); i++){
String def = (String)cols.get(i);
String[] defs = def.split(":");
tdefs.put(defs[0], defs[1]);
}
}
return tdefs;
}

private static String encodeStr(HashMap<String, String> tdefs, String colName, String value)throws Exception{
String def = tdefs.get(colName);
if (def != null){
if (def.startsWith("TEXT") || def.startsWith("VARCHAR")|| def.startsWith("CHAR")){
return "'" +DBUtil.encode(value)+"'";
}else if (def.startsWith("INT") || def.startsWith("BIGINT")){
return value;

}else{
return "'"+value+"'";
}
}
return null;
}

private static String decodeStr(HashMap<String, String> tdefs, String colName, String value)throws Exception{
String def = tdefs.get(colName);
if (def != null){
if (def.startsWith("TEXT") || def.startsWith("VARCHAR")|| def.startsWith("CHAR")){
return DBUtil.decode(value);
}
}
return value;
}
/**
* This method would export tables from database into an XML document and save
* it under the name of WEB-INF/data/tables.xml
*/
public static void exportDatabase(HttpServletResponse response)throws Exception{
Element tables = XmlApi.createNewElement(NS, "tables");
Iterator it = TEABLE_DEFS.keySet().iterator();
while (it.hasNext()){
String tablename = (String)it.next();
ArrayList tdefs = (ArrayList)TEABLE_DEFS.get(tablename);
exportTable(tdefs, NS, tablename, tables);
}
InetUtil.outputFile(response, "tables.xml", InetUtil.OCTET,
XmlApi.getXmlString(tables, true).getBytes());
}

private static void exportTable(ArrayList tdefs, String ns, String tname, Element tables)throws Exception{
ResultSet rs = DB.executeQuery("SELECT * FROM "+tname);
if (rs != null){
try{
System.out.println("Exporting table " + tname);
Element table = XmlApi.createChildElement(tables, NS, "table", null);
table.setAttribute("name", tname);
while(rs.next()){
Element row = XmlApi.createChildElement(table, ns, "row", null);
for (int i = 0; i < tdefs.size(); i++){
String[] defs = ((String)tdefs.get(i)).split(":");
String value = rs.getString(defs[0]);
if (value != null){
if (defs[1].startsWith("TEXT") ||
defs[1].startsWith("VARCHAR")|| defs[1].startsWith("CHAR")){
value = DBUtil.decode(value);
}
Element col = XmlApi.createChildElement(row, ns, "col", value);
col.setAttribute("name", defs[0]);
}
}
}
}finally{
DB.closeResultSet(rs);
}
}
}

/**
* destroy is called when Tomcat is shuting down. Here we close
* our database connection
*/
public void destroy() {
DB.close();
}

/**
* Test loading a data in an XML file onto the database.
*/
public static void test(String dataFileName){
System.out.println(ConfigServlet.loadData(dataFileName, true));
}

}

[opersai]

Thanks to ckeyrouz for helping out. I have solved my problem. This is indeed a problem with connecting to database. In my case, it was the problem with my database itself. I had make couple installations of mySQL originally, and that somehow confused the system, and insisted to connect to the old service, or use password of old service or something. Anyway, the problem is solved when I re-installed mySQL after I completely deleted old mySQL installation - including manually deleting any registry with keyword mySQL from the regEdit, and god bless I didn't destroy my operating system while doing this. =p