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 09, 2017 10:38PM

Yes progress!

I split out the capture and aquaculture queries to calculate the moving average separately.

The SELECT query below returned the desired result for the aquaculture data
(85473 rows in 1 min 12.11 secs)

mysql> INSERT INTO `fao_average_a` (`global_id`, `species_code_a`, `year_a`, `cap_avg`) 
    -> 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;

EXPLAIN STATEMENT - SELECT QUERY - AQUACULTURE DATA
+----+--------------------+-------+------------+-------+---------------+----------------+---------+--------------------------+--------+----------+------------------------------------------+
| id | select_type        | table | partitions | type  | possible_keys | key            | key_len | ref                      | rows   | filtered | Extra                                    |
+----+--------------------+-------+------------+-------+---------------+----------------+---------+--------------------------+--------+----------+------------------------------------------+
|  1 | PRIMARY            | x     | NULL       | index | species_a     | species_year_a | 15      | NULL                     | 965656 |    18.66 | Using where; Using index; Using filesort |
|  2 | DEPENDENT SUBQUERY | y     | NULL       | ref   | species_a     | species_a      | 12      | possbil.x.species_code_a |   1882 |   100.00 | Using where                              |
+----+--------------------+-------+------------+-------+---------------+----------------+---------+--------------------------+--------+----------+------------------------------------------+

I tried the same SELECT structure for the much larger capture table data

EXPLAIN STATEMENT - SELECT query = CAPTURE DATA (904,401 rows):
+----+--------------------+-------+------------+-------+--------------------------+----------------+---------+--------------------------+--------+----------+------------------------------------------+
| id | select_type        | table | partitions | type  | possible_keys            | key            | key_len | ref                      | rows   | filtered | Extra                                    |
+----+--------------------+-------+------------+-------+--------------------------+----------------+---------+--------------------------+--------+----------+------------------------------------------+
|  1 | PRIMARY            | x     | NULL       | range | species_year_c,species_c | species_year_c | 12      | NULL                     | 482828 |   100.00 | Using where; Using index; Using filesort |
|  2 | DEPENDENT SUBQUERY | y     | NULL       | ref   | species_year_c,species_c | species_c      | 12      | possbil.x.species_code_c |    582 |   100.00 | Using where                              |
+----+--------------------+-------+------------+-------+--------------------------+----------------+---------+--------------------------+--------+----------+------------------------------------------+

I also tried a JOIN query on the capture table data:

mysql> SELECT
    -> x.species_code_c, x.year_c, y.cap_avg
    -> FROM fao_global AS x
    -> JOIN (
    -> SELECT x.species_code_c, sum(y.qty_taken) / count(y.qty_taken) AS cap_avg
    ->     FROM fao_global AS x
    -> JOIN fao_global AS y ON x.species_code_c = y.species_code_c
    ->     AND x.year_c-y.year_c BETWEEN 0 AND 4
    -> GROUP BY x.species_code_c
    ->     ) y USING (species_code_c)
    ->    WHERE x.species_code_c IS NOT NULL
    ->    ORDER BY x.year_c DESC, x.species_code_c ASC;

EXPLAIN STATEMENT - JOIN QUERY - CAPTURE DATA
+----+-------------+------------+------------+-------+--------------------------+----------------+---------+--------------------------+--------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys            | key            | key_len | ref                      | rows   | filtered | Extra                                        |
+----+-------------+------------+------------+-------+--------------------------+----------------+---------+--------------------------+--------+----------+----------------------------------------------+
|  1 | PRIMARY     | x          | NULL       | range | species_year_c,species_c | species_year_c | 12      | NULL                     | 482828 |   100.00 | Using where; Using index; Using filesort     |
|  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>              | <auto_key0>    | 12      | possbil.x.species_code_c |    742 |   100.00 | NULL                                         |
|  2 | DERIVED     | y          | NULL       | ALL   | species_year_c,species_c | NULL           | NULL    | NULL                     | 965656 |   100.00 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | x          | NULL       | ref   | species_year_c,species_c | species_year_c | 12      | possbil.y.species_code_c |    742 |   100.00 | Using where; Using index                     |
+----+-------------+------------+------------+-------+--------------------------+----------------+---------+--------------------------+--------+----------+----------------------------------------------+

No result returned was returned on either form (I killed each after letting run for about 15 mins).

I'm conscious that I have relied on the sample my.ini that was installed with the WAMP.

Reading today suggests there may be any number of settings that might be added and modified to help?

I may need to hunt down a better my.ini file structure...

I'm also getting a clear message that too much memory allocation can be as bad (or even worse) than not enough. It'not clear however (to me yet anyhow) how you work out which one is behind a query failing (memory during a session or at start-up)

Some of the key settings in the my.ini file are:

mysql> show variables like '%buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| 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        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 12884901888    |
+-------------------------------------+----------------+

The db size:

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 |
+-------+-------+-------+------------+---------+
| 2.06m | 0.13g | 0.06g | 0.18g      |    0.45 |
+-------+-------+-------+------------+---------+

again thanks in advance...

Options: ReplyQuote


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


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.