MySQL Forums
Forum List  »  Install & Repo

Re: Error Connecting to DataBase!: SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES)
Posted by: Amanda J
Date: October 08, 2017 12:56PM

hmmm, That might explain a couple a things...

To get this process clear I dropped the test user and started again

mysql>DROP USER 'test'@'localhost';
mysql> flush privileges;
mysql> CREATE USER 'test'@'localhost'IDENTIFIED BY 'test123';
mysql> SELECT USER from mysql.user;
 +---------------+
| USER          |
+---------------+
| mysql.session |
| mysql.sys     |
| root          |
| test          |
+---------------+

mysql>GRANT ALL ON possbil.* TO 'test'@'localhost' WITH GRANT OPTION;
mysql> SHOW GRANTS FOR 'test'@'localhost';

+-----------------------------------------------------------------------------+
| Grants for test@localhost                                                   |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost'                                    |
| GRANT ALL PRIVILEGES ON `possbil`.* TO 'test'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------+


http://localhost/test.php this time returned the desired result "Connected"

<?php 
$conn = mysqli_connect("localhost", "test", "test123", "possbil" ) or exit( mysqli_connect_error() ); 
echo "Connected"; 
?>

Amazing! - Thank you!!


On getting queries to run:

I've set up the local server on my laptop (only option).
    Domain: POSSBIL
    Logon Server: \\MYXPS

It's DELL XPS9560
    Windows 10
    16GB, DDR4, 2400MHz; (I can up to 32GB if I need to)
    400-APWK 512GB PCIe Solid State Drive (with access to 2TB via USB Type-C Port, or USB 3 Port with Power Share)
    490-BDMWNVIDIA(R) GeForce(R) GTX 1050 with 4GB GDDR5
    with an Intel i7-7700HQ Quad Core Processor (6M cache, up to 3.8 GHz)
    (Intel64 Family 6 Model 158 Stepping 9 GenuineIntel ~2801 Mhz)

So I have:
    Total Physical Memory: 16,236 MB
    Available Physical Memory: 10,535 MB
    Virtual Memory: Max Size: 18,668 MB
    Virtual Memory: Available: 11,839 MB
    Virtual Memory: In Use: 6,829 MB

On the basais of your earlier guidance the MySQL Configuration is currently:
    key_buffer_size = 64M
    max_allowed_packet = 1M
    Added to reduce memory used (minimum is 400)
    table_definition_cache = 600
    sort_buffer_size = 10G (I understand this to be the 70% of RAM)
    net_buffer_length = 8K
    read_buffer_size = 2M
    read_rnd_buffer_size = 2M
    myisam_sort_buffer_size = 32M
On those settings, logged in as user 'root'. I tried running the query:
SELECT 
x.global_id,
x.species_code_a,
x.year_a,
(SELECT SUM(y.qty_prod) / COUNT(y.qty_prod) 
FROM fao_global AS y
WHERE species_code_a = x.species_code_a
AND x.year_a - y.year_a BETWEEN 0 AND 4 
)AS 'cap_avg' 
FROM fao_global AS x 
WHERE x.species_code_a IS NOT NULL
ORDER BY x.year_a DESC, x.species_code_a ASC;

I got the fan going but has not yet returned a result.

mysql> show variables like '%buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 16777216       |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 16777216       |
+-------------------------------------+----------------+

mysql> use possbil;
Database changed
mysql> SELECT
-> concat(round(sum(table_rows)/1000000,2),'m') rows,
-> concat(round(sum(data_length)/(1024*1024*1024),2),'g') data,
-> concat(round(sum(index_length)/(1024*1024*1024),2),'g') idx,
-> concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'g') total_size,
-> round(sum(index_length)/sum(data_length),2) idxfrac
-> from information_schema.tables
-> where table_schema='possbil';
+-------+-------+-------+------------+---------+
| rows  | data  | idx   | total_size | idxfrac |
+-------+-------+-------+------------+---------+
| 1.92m | 0.12g | 0.00g | 0.12g      |    0.01 |
+-------+-------+-------+------------+---------+

Options: ReplyQuote


Subject
Written By
Posted
Re: Error Connecting to DataBase!: SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES)
October 08, 2017 12:56PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.