?
Solved

error 1146 trying to connect into mysql

Posted on 2014-07-15
6
Medium Priority
?
463 Views
Last Modified: 2014-07-23
i have a database i backed from another server using mysqldump

the database is called pbx

so, i created pbx database on the new server, then restored the backup file into pbx

this is where i gets confusing, the username an password are also pbx (not my idea).

so i  created the user 'pbx' then used

grant all privileges on pbx.* to pbx@'%' identified by 'pbx';

Open in new window


this i thought granted user rights for a specific user on a specific db.

then when i try

"Select * from pbx;"

ERROR 1146 (42S02): Table 'pbx.pbx' doesn't exist

Open in new window


any ideas?!

i just want to see if the backup is complete etc....

thanks
0
Comment
Question by:mudcow007
  • 3
  • 2
6 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40197140
Your query is trying to select records from a table called pbx, do you also have a table named pbx in your pbx database?
0
 

Author Comment

by:mudcow007
ID: 40197174
ah gotcha, all im trying to display is the whole contents of database.

did i use the wrong command?
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40197209
there really isn't a simple transact sql command for that. Why don't you open the database in MySQL Administrator program or QueryBrowser?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 21

Accepted Solution

by:
Randy Poole earned 2000 total points
ID: 40197211
or maybe there is :P
mysqlshow pbx

Open in new window


You can read up on this command at:
http://dev.mysql.com/doc/refman/5.0/en/mysqlshow.html
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40209574
Here is a teaching example of a PHP script that does it.  Uses the MySQLi extension.

<?php // demo/mysqli_list_dbs.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// THIS SCRIPT DEMONSTRATES HOW TO FIND DATABASES AND TABLES USING MySQLi


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// GET A LIST OF THE DATA BASES ON THIS CONNECTION
$sql = 'SHOW DATABASES';
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

while ($row = $res->fetch_object())
{
    $db_names[] = $row->Database ;
}
echo "LIST OF DB NAMES: ";
print_r($db_names);


// SELECT ONE MYSQL DATA BASE
$db_name     = $db_names[0];
if (!$db_sel = $mysqli->select_db($db_name))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE TABLES ON THIS DATA BASE
$sql = "SHOW TABLES FROM $db_name";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// SET THE NAME OF THE PROPERTY WITH THE DB NAME INCLUDED
$prop = "Tables_in_$db_name";
while ($row = $res->fetch_object())
{
    $db_tables[] = $row->$prop;
}

echo "LIST OF TABLES IN $db_name: ";
print_r($db_tables);

Open in new window

0
 

Author Closing Comment

by:mudcow007
ID: 40213611
awesome thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you step-by-step instructions to build your own NTP CentOS server.  The network diagram shows the best practice to setup the NTP server farm for redundancy.  This article also serves as your NTP server documentation.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Suggested Courses
Course of the Month14 days, 19 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question