Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: struggle with some MySQL USE-statements that throw back errors all the time

  1. #1

    Default struggle with some MySQL USE-statements that throw back errors all the time

    Hello dear all - good day dear opensuse-experts,


    first of all - i am still wondering if this is the best place to put this question to. Since this is also a server-issue - on a opensuse-based server - i guess that this "programming-"superforum is one of the best plcaces ever to ask this qustion, well i struggle with some MySQLstatements that throw back errors all the time. Hope you can help me here.

    Code:
    SHOW GRANTS FOR CURRENT_USER;



    i have tried out various things in order to run this statement correctly

    Code:
    USE jo ;
    SHOW GRANTS FOR CURRENT_USER;
    Code:
    USE jo ;
    SHOW GLOBAL VARIABLES LIKE 'PORT';
    Failed to execute SQL : SQL USE jo; SHOW GRANTS FOR CURRENT_USER; failed : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'jo; SHOW GRANTS FOR CURRENT_USER' at line 1

    note: my db-name; jo


    see what i i get back:


    Code:
    Failed to execute SQL : SQL USE jo; SHOW GRANTS FOR CURRENT_USER; failed : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'jo; SHOW GRANTS FOR CURRENT_USER' at line 1

    the question is: do i need any quotations here to run the SQL-Startement. According to the manual i do not have to do that..


    see the manual: https://beginner-sql-tutorial.com/sql-use-database.htm


    USE yourdatabase;
    SHOW GRANTS FOR CURRENT_USER;

    SQL USE Statement
    The USE Statement is used to select a database and perform SQL operations into that database.
    The database remains default until end of session or execution of another USE statement with some other database.
    SQL USE DATABASE Statement:
    The Syntax for the USE Statement is:
    USE database_name;
    database_name - is the name of the database to be selected


    Background: during the installation of wordpress on a rootserver where i can configure and administer via webmin
    i get the feedback: Error establishing a database connection




    i have gathered some more infos and insights due to some more tests.


    see the following data: – the outcome of a testscript which i have put on the server

    Warning: mysqli_connect(): (HY000/2002): No such file or directory in /sites/www.mysite.de/tests.php on line 3
    Warning: mysqli_error() expects parameter 1 to be mysqli, boolean given in /sites/www.mysite.de/tests.php on line 4
    could not connect:

    see the script:


    on a sidenote,. …


    Code:
    <?ph
    if(function_expists('mysqli_connect')){
    if(!($link = mysqli_connect('localhost','user','passwd','my_db'))){
    die('could not connect: ' . mysqli_error($link));
    }
    } else {
    die("don't have mysqli");
    }
    echo 'connect successfully';
    mysqli_close($link);
    well i guess that this is saying that 1 parameter was required, 0 were provided. and besides this we surely can say that there absolutly no error checking is being done before calling the function, it’s blindly passing a variable that was collected.
    interesting: This actually makes sense, given i am probably failing to connect to mysql. What we don’t see is validation that the credentials work, & i can connect. one option would be to ssh to the php server, & then connect over the cli to mysql. Otherwise, we´re just guessing. Above all – we can say that there probably is no pathing issue. honestly – there are serious doubts that wordpress has a pathing issue, or this is anyway code related.



    background: i have gotten the Error establishing a database connection


    This either means that the username and password information in your wp-config.php file is incorrect or we can’t contact the database server at localhost. This could mean your host’s database server is down.
    Are you sure you have the correct username and password?
    Are you sure that you have typed the correct hostname?
    Are you sure that the database server is running?
    If you’re unsure what these terms mean you should probably contact your host. If you still need help you can always visit the WordPress Support Forums.

    guess that i have to edit all the stuff manually now..


    i want to edit the file now and i will afterwards store it as wp_config.php on the server ab.
    Btw: i am still wondering why this was not happening so far


    additionally – i will do the following


    First thing you should do is to make sure that you are getting the same error on both the front-end of the site, and the back-end of the site (wp-admin). If the error message is the same on both pages “Error establishing a database connection”, then proceed onto the next step. If you are getting a different error on the wp-admin for instance something like “One or more database tables are unavailable. The database may need to be repaired”, then you need to repair your database.

    gem. https://www.wpbeginner.com/wp-tutori...-in-wordpress/


    Well at the moment i do not know how to proceed..


    Love to hear from you
    dilbert ;-)
    Wordpress-development - a Toolset: wpgear.org

  2. #2
    Join Date
    Jun 2008
    Location
    San Diego, Ca, USA
    Posts
    10,823
    Blog Entries
    1

    Default Re: struggle with some MySQL USE-statements that throw back errors all the time

    When you run into a problem, I recommend you prioritize official documentation,

    So,
    For instance in the following MySQL 8 documentation,
    - There are 3 possible commands which might return what you want
    - A description of what may be required to run such a command.

    https://dev.mysql.com/doc/refman/8.0...ow-grants.html

    I've personally never had reason to run this command for "current user," but have run the command in the context of the MySQL Administrator displaying for a specific user... And has always worked. This is likely because when I'm "designing" the particular User role, I'm logged in as the MySQL Administrator, I don't usually find myself logged in as a specific User needing to display own rights.

    TSU
    Beginner Wiki Quickstart - https://en.opensuse.org/User:Tsu2/Quickstart_Wiki
    Solved a problem recently? Create a wiki page for future personal reference!
    Learn something new?
    Attended a computing event?
    Post and Share!

  3. #3

    Default Re: struggle with some MySQL USE-statements that throw back errors all the time

    good evening dear Tsu

    many thanks for the quick reply. Your ideas were food for thoughts. I am musing about the ideas since i have big big troubles i establishing a db-connection on the server (with the installed Wordpress).
    This is a root-server where my friend administrates the backend and i am workin on the the Webadmin-panel -

    creating, establishing and configuring vhosts
    creating and configuring MySQL-DBs and things alike

    -having had no issues in the past years with all that work i now run into issues - and keep doing this for more than one week now.



    Quote Originally Posted by tsu2 View Post
    When you run into a problem, I recommend you prioritize official documentation,

    So,
    For instance in the following MySQL 8 documentation,
    - There are 3 possible commands which might return what you want
    - A description of what may be required to run such a command.

    https://dev.mysql.com/doc/refman/8.0...ow-grants.html

    I've personally never had reason to run this command for "current user," but have run the command in the context of the MySQL Administrator displaying for a specific user... And has always worked. This is likely because when I'm "designing" the particular User role, I'm logged in as the MySQL Administrator, I don't usually find myself logged in as a specific User needing to display own rights.

    TSU

    perhaps i have made some mistakes in the basic concepts and design ... probably the user does not "fit" or does not have full permission.

    see the full coverage of the story...https://wordpress.org/support/topic/...#post-11624186


    as for the SQL-Code:

    a wordress-expert just wanted me to prove this SQL... after i have tried out many many other things. - on the OpenSuse.-Server

    Code:
    USE yourdatabase;
    SHOW GLOBAL VARIABLES LIKE 'PORT';
    SHOW GRANTS FOR CURRENT_USER;
    Well - i still wonder why i cannot run



    Code:
    USE yourdatabase;
    SHOW GLOBAL VARIABLES LIKE 'PORT';
    SHOW GRANTS FOR CURRENT_USER;

    when i enter for yourdatabase - the databasename that i have - jo
    to spell it out clearly:


    - do i need to type the db-name into double-quotes!? or is it okay to just type the name of the db - eg. jo if the db name is just jo!?

    more questions that arise
    - is there a problem with the user - ie - is there probably a user not set up right
    ie sensu the manual of Webmin https://doxfer.webmin.com/Webmin/MySQL_Database_Server

    Your MySQL database server requires all clients to authenticate themselves with a username and password before they can execute SQL commands. It has its own tables of users, passwords and permissions that are consulted when a client tries to login, rather than the Unix user files /etc/passwd and /etc/shadow. Detailed permissions can be defined for each user, in order to limit the kinds of SQL statements that he can use, the client hosts he can connect from, and the databases, tables and fields that he can modify.
    Typically after MySQL has been first installed, only the root user is able to login. This user will have permissions to access all databases and tables and perform all actions, and so is generally used for administration purposes only. If you want to write an application that uses a database, it is a good idea to create another user for that purpose and set up the application to login as that user.

    The standard MySQL install also creates an Anonymous user with no password and access to databases starting with test. This special user is used for any login attempt for which no other matching user is found. Anonymous users are explained in more detail below.
    hmm - probably i have mixed / messed up here something!?

    Note: the sequences:

    1. i first have setup a DB-user and then
    2. create the db
    3. give the user permission on the db
    4. give the user permission on the db-host


    but probably i have messed up something.

    there are fields in the WEBMIN-Frontend like the following.


    the options below configure syncronistation between unix- users created through Webmin and MySQL-users

    when to synchronisize

    [ ] add a new MySQL user when a Unix-User is added;
    [ ] update a Mysql.-user when the matching of a Unix-user is modified;
    [ ] delete a MysqL.-user when the matching Unix-user is deleted

    hmmm - i am not sure what i should do next... But i will try out some thing. And i come back to report all the findings.


    meanwhile - have a great day
    dilbert ;-)
    Wordpress-development - a Toolset: wpgear.org

  4. #4
    Join Date
    Jun 2008
    Location
    San Diego, Ca, USA
    Posts
    10,823
    Blog Entries
    1

    Default Re: struggle with some MySQL USE-statements that throw back errors all the time

    Thinking about your situation a bit further, I remember on another different but similar RDBMS that permission to access the GRANT tables was critical but sometimes overlooked to read the GRANT tables... makes sense, right?

    Not sure why this detail isn't mentioned in the MySQL documentation I referenced, and don't know but assume that basic security should be similar even if the RDBMS are different...

    So again,
    It probably starts with viewing and setting up the ordinary MySQL User with access to all necessary tables including the GRANT tables when you're logged in as the MySQL Administrator. With sufficient permissions, then the ordinary MySQL User should be able to "self-administer" accessing even the "system level" GRANT tables.

    TSU
    Beginner Wiki Quickstart - https://en.opensuse.org/User:Tsu2/Quickstart_Wiki
    Solved a problem recently? Create a wiki page for future personal reference!
    Learn something new?
    Attended a computing event?
    Post and Share!

  5. #5

    Default Re: struggle with some MySQL USE-statements that throw back errors all the time

    hello dear TSU


    many many thanks for the quick answer with all the ideas, that are very interesting.


    here a little update and more findings:

    by the way: if i do not use USE <db-name> .... then it works propperly


    conclusio: if i run

    Code:
    SHOW GLOBAL VARIABLES LIKE 'PORT';
    i get a concrete output from SQL command SHOW GLOBAL VARIABLES LIKE 'PORT'; ..

    or if i run
    Code:
        SHOW GRANTS FOR CURRENT_USER;
    i get outputs like this one

    Code:
        Output from SQL command SHOW GRANTS FOR CURRENT_USER; ..
        Grants for root@localhost
        GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*4444444rrrrrrwwwww222222444443660283C379783ED8EF54B6EC01DAF8374444444rrrrrrrrrrrreeeeewwwww2CeC3C474F4' WITH GRANT OPTION
        GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
    and now i have to rethink what this does want to say in the context of a - error in trying to establish a db-connection - while installing a wordpress on the frontend


    look forward to hear from you

    greetings
    dilbert ;-)
    Wordpress-development - a Toolset: wpgear.org

  6. #6
    Join Date
    Jun 2008
    Location
    San Diego, Ca, USA
    Posts
    10,823
    Blog Entries
    1

    Default Re: struggle with some MySQL USE-statements that throw back errors all the time

    Quote Originally Posted by dilbertone View Post
    hello dear TSU


    many many thanks for the quick answer with all the ideas, that are very interesting.


    here a little update and more findings:

    by the way: if i do not use USE <db-name> .... then it works propperly


    conclusio: if i run

    Code:
    SHOW GLOBAL VARIABLES LIKE 'PORT';
    i get a concrete output from SQL command SHOW GLOBAL VARIABLES LIKE 'PORT'; ..

    or if i run
    Code:
        SHOW GRANTS FOR CURRENT_USER;
    i get outputs like this one

    Code:
        Output from SQL command SHOW GRANTS FOR CURRENT_USER; ..
        Grants for root@localhost
        GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*4444444rrrrrrwwwww222222444443660283C379783ED8EF54B6EC01DAF8374444444rrrrrrrrrrrreeeeewwwww2CeC3C474F4' WITH GRANT OPTION
        GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
    and now i have to rethink what this does want to say in the context of a - error in trying to establish a db-connection - while installing a wordpress on the frontend


    look forward to hear from you

    greetings
    SHOW GRANTS FOR CURRENT_USER works when not specifying a User context first because you're logged in a the MySQL Administrator (Superuser).

    The following should work, which would further validate my suspicion That the problem is simply a permissions problem accessing the GRANTS table
    Code:
    SHOW GRANTS FOR 'jo'@'localhost';
    TSU
    Beginner Wiki Quickstart - https://en.opensuse.org/User:Tsu2/Quickstart_Wiki
    Solved a problem recently? Create a wiki page for future personal reference!
    Learn something new?
    Attended a computing event?
    Post and Share!

  7. #7

    Default Re: struggle with some MySQL USE-statements that throw back errors all the time

    hello dear TSU

    first of all - many thanks for the reply and your continued support:


    Output from SQL command SHOW GRANTS FOR 'jo'@'localhost'; ..
    Code:
    Grants for jo@localhost
    GRANT USAGE ON *.* TO 'jo'@'localhost' IDENTIFIED BY PASSWORD '*93C1B40FA5F0FED10E5A0CE946A5E71D59B00860'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `jo`.* TO 'jo'@'localhost'

    hmmm - running the SQL-Request works now - without any issue. but for the results : i try to figure out what these results mean. Guess that i can verify the issues.


    Love to hear from you
    Regards dibert
    dilbert ;-)
    Wordpress-development - a Toolset: wpgear.org

  8. #8

    Default Re: struggle with some MySQL USE-statements that throw back errors all the time

    dear TSU2,

    first of all many many thanks for the continued help and support. I am trying to figure out what has happened. on a sidenote: a big big sorry - for the bit of confusing thread-posting with the issues. I am in need of help and i try to sort out things here.

    i am tryin to understand what goes on...in order to do that i recapitulate what i have done..: Tsu - i runned two differnt versions of scripts to test the connection
    a. one with mysql and
    b. one with mysqli

    i had several results - and now i have to interpret these results correcly:
    i had very very interesting results and i need to think about them - for the sake of a better understanding:

    regarding the issues i have - some discussion of the errors that have been encoutered


    The "no such file or directory" message is also related to trying to connect to MySQL, that is related to how the mysql functions try to connect.
    i runned the MySQLi-script and afterward i runned the MySQL-script as a "Test-The-Connection-Script"
    Well it is probably better to use mysqli or PDO. The regular mysql extension is no longer part of PHP.
    regarding the trust of the error messages. i have one, single problem - it is that PHP cannot connect to MySQL, but the one big big question is, why i cannot connect?

    conclusio: i have to read and understand the error messages


    i have to run the testscript that is written below.


    https://www.php.net/manual/en/mysqli.connect-error.php


    well - i guess that i need to run the following command: mysqli_connect_error (PHP 5, PHP 7)

    mysqli::$connect_error -- mysqli_connect_error
    — Returns a string description of the last connect error




    Object oriented style

    Code:
    string $mysqli->connect_error;
    Procedural style
    mysqli_connect_error ( void ) : string
    Returns the last error message string from the last call to mysqli_connect().
    Return Values:

    A string that describes the error. NULL is returned if no error occurred.

    Examples:
    Example #1 $mysqli->connect_error example

    Object oriented style
    Code:
    <?php
    
    $mysqli = @new mysqli('localhost', 'fake_user', 'my_password', 'my_db');
    
    // Works as of PHP 5.2.9 and 5.3.0.
    if ($mysqli->connect_error) {
        die('Connect Error: ' . $mysqli->connect_error);
    }
    ?>
    Procedural style

    Code:
    <?php
    $link = @mysqli_connect('localhost', 'fake_user', 'my_password', 'my_db');
    
    
    if (!$link) {
        die('Connect Error: ' . mysqli_connect_error());
    }
    ?>
    The above examples will output:


    Code:
    Connect Error: Access denied for user 'fake_user'@'localhost' (using password: YES)

    i do that... dear TSU2- i am happy that you help me to figure out the issues.


    many many thanks


    i will run the script and come back and report all the findings.


    untill later...
    dilbert ;-)
    Wordpress-development - a Toolset: wpgear.org

  9. #9

    Default Re: struggle with some MySQL USE-statements that throw back errors all the time

    Hello dear TSU,




    first of all: note i have plenty of issues - a whole bunch of issues on a Server - i need to digg deeper into all things regarding creating a healthy db-connection. Therefore i need to learn all about the script - mysql_connect_error... see below.


    TSU, i have gathered some information - in order to think about it - and to share the ideas ... so i hopefully will learn and solve my issues. It is my belive that i need to learn about the techniques that help me to get more infos about the server.


    well the mysql_connect_error-script is a great help: It returns a string description of the last connect error ( see more infos here: https://www.php.net/manual/en/mysqli.connect-error.php ). The mysqli_connect_error() function returns the error description from the last connection error, if there is any error-note.

    the return value are the following ones:
    a. A string that describes the error.
    b. an empty string if no error occurred.
    at least this goes for the Version: PHP 5, PHP 7

    well - if we run the code below we can get the info bout the option to connect to the db. What if we run this as a mysql-test-script, and what if we will want to convert it to use mysqli? Can this be done by changing mysql _query($sql); to mysqli _query($sql); ?

    Code:
    <?PHP
    
    // the test-script that we are running.
    $DB["dbName"] = "emails";
    $DB["host"] = "localhost";
    $DB["user"] = "root";
    $DB["pass"] = "";
    $link = mysql_connect($DB['host'], $DB['user'], $DB['pass']) or die("<center>Howdy - be aware; There a thing happenede - 
    An Internal Error has Occured. Please report following error to the webmaster shot him a mail now.<br><br>".mysql_error()."'</center>");
    mysql_select_db($DB['dbName']);
    // end header connection part
    
    // function from a functions file that I run a mysql query through in any page.
    function executeQuery($sql) {
        $result = mysql_query($sql);
        if (mysql_error()) {
            $error = '<BR><center><font size="+1" face="arial" color="red">An Internal Error has Occured.<BR> The error has been recorded for review</font></center><br>';
            if ($_SESSION['auto_id'] == 1) {
                $sql_formatted = highlight_string(stripslashes($sql), true);
                $error .= '<b>The MySQL Syntax Used</b><br>' . $sql_formatted . '<br><br><b>The MySQL Error Returned</b><br>' . mysql_error();
            }
            die($error);
        }
        return $result;
    }
    
    // example query ran on anypage of the site using executeQuery function
    $sql='SELECT auto_id FROM friend_reg_user WHERE auto_id=' .$info['auto_id'];
    $result_member=executequery($sql);
    if($line_member=mysql_fetch_array($result_member)){
        extract($line_member);
    } else {
        header("location: index.php");
        exit;
    }
    ?> 
    If we do replace mysql_* with mysqli_* then we will have to bear in mind that a whole load of mysqli_* functions need the database link to be passed.

    E.g.: the following ones.
    mysql_query($query)
    becomes
    mysqli_query($link, $query)
    I.e., lots of checking required.

    on the other hand side:
    is it suffice if we replace every mysql_* function call with its equivalent mysqli_*, when we will use the procedural API
    (note: there is some code based on the MySQL API, which is a procedural one - at least afaik), To help with that, the The MySQLi Extension Function Summary-manual is definitely something that will prove helpful. We can do the following:


    we have the following options to do that:


    - mysql_connect will be replaced by mysqli_connect
    - mysql_error will be replaced by mysqli_error and/or mysqli_connect_error, depending on the context
    - mysql_query will be replaced by mysqli_query ,,,, and so on and so forth.


    Note: For some functions, we may need to check the parameters very very carefully: Maybe there are
    some differences here and there -- but not that many differences. Belive me. Both mysql and mysqli-codes are based on the same library ( the great and powerful libmysql ; at least for PHP-version <= 5.2)

    Usage - for instance:
    with mysql, we have to use the mysql_select_db once connected, to indicate on which database we want to do our queries mysqli, on the other side, allows us to specify that database name as the fourth parameter to mysqli_connect.


    what do you think bout this..


    love to hear from you
    dilbert ;-)
    Wordpress-development - a Toolset: wpgear.org

  10. #10
    Join Date
    Jun 2008
    Location
    San Diego, Ca, USA
    Posts
    10,823
    Blog Entries
    1

    Default Re: struggle with some MySQL USE-statements that throw back errors all the time

    Sounds like you are making progress and are now working out the correct SQL syntax for your queries.

    Depending on what you're working on,
    I'm a bit surprised that you have to get down into debugging your sql connection and handcrafting your sql statements.

    Personally,
    I admit I'm a bit spoiled that I've used "drop in" data connection code blocks (aka modules, aka middleware) that set up with minimal configuration, and there are good IDE tools that can assist in designing your database schema and constructing your SQL statements. Whatever your skill level and experience, good tools can speed your progress.

    TSU
    Beginner Wiki Quickstart - https://en.opensuse.org/User:Tsu2/Quickstart_Wiki
    Solved a problem recently? Create a wiki page for future personal reference!
    Learn something new?
    Attended a computing event?
    Post and Share!

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •