Checking DB size may fail, for example if the DB has Views or Functions the user Drupal uses is not allowed to access (such as when the definer in the View is another or non-existing user).

In this case, the query will fail, yet the dashboard will report that the database size is 0 bytes instead of reporting that the check failed.

I spent a few days wondering why the DB size was not reported before realizing there was an error in the DB.

CommentFileSizeAuthor
#6 db_size_check_catch_failure-2394635-6.patch1.7 KBmdupont
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

FluxSauce’s picture

FluxSauce CreditAttribution: FluxSauce commented
Assigned: Unassigned » FluxSauce

Interesting, hadn't run into that one, but I can see how this would be a problem.

I'll try to replicate this locally and check for a database error condition and return info if there's a security problem.

Thanks for reporting this!

mdupont’s picture

mdupont
Primary language French
Location Switzerland
CreditAttribution: mdupont commented

To help you replicate the issue, you can try creating a MySQL View this way:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `nonexisting`@`%` 
    SQL SECURITY DEFINER
VIEW `buggy_view` as
  SELECT 1 FROM node
    LIMIT 0,1;
FluxSauce’s picture

FluxSauce CreditAttribution: FluxSauce commented
Status: Active » Postponed (maintainer needs more info)

Hey, sorry for the delay in response. I can't replicate this as described. As root, I created the view, then ensured that the Drupal MySQL user couldn't access it.

drush sqlc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.5.38 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM buggy_view;
ERROR 1045 (28000): Access denied for user 'broken'@'localhost' (using password: YES)

So far, so good. However, I ran the specific check you specified:

drush ad --detail --skip=DatabaseCollation,DatabaseEngine,DatabaseRowCount --verbose --debug

Here's the result:

Bootstrap to phase 0. [0.01 sec, 1.72 MB]                            [bootstrap]
Drush bootstrap phase : _drush_bootstrap_drush() [0.01 sec, 1.83 MB] [bootstrap]
Cache HIT cid: 5.10.0-commandfiles-0-6b8b014af817f090cb4cba8110e45f42 [0.02 sec, 1.85 MB]                                [debug]
Bootstrap to phase 0. [0.06 sec, 5.61 MB]                                                                            [bootstrap]
Bootstrap to phase 5. [0.08 sec, 5.62 MB]                                                                            [bootstrap]
Drush bootstrap phase : _drush_bootstrap_drupal_root() [0.08 sec, 5.62 MB]                                           [bootstrap]
Initialized Drupal 7.25 root directory at /Users/jpeck/Projects/broken [0.1 sec, 6.55 MB]                               [notice]
Drush bootstrap phase : _drush_bootstrap_drupal_site() [0.1 sec, 6.56 MB]                                            [bootstrap]
Initialized Drupal site default at sites/default [0.1 sec, 6.56 MB]                                                     [notice]
Cache HIT cid: 5.10.0-commandfiles-2-c32acd775c18a90c5b41d0fc9accf5ab [0.11 sec, 6.56 MB]                                [debug]
Drush bootstrap phase : _drush_bootstrap_drupal_configuration() [0.11 sec, 6.78 MB]                                  [bootstrap]
Drush bootstrap phase : _drush_bootstrap_drupal_database() [0.11 sec, 6.79 MB]                                       [bootstrap]
Successfully connected to the Drupal database. [0.11 sec, 6.8 MB]                                                    [bootstrap]
Drush bootstrap phase : _drush_bootstrap_drupal_full() [0.12 sec, 7.3 MB]                                            [bootstrap]
Cache HIT cid: 5.10.0-commandfiles-5-59ba1c38e044bc75ada4837a9f1fca9e [0.35 sec, 18.94 MB]                               [debug]
Found command: audit_database (commandfile=site_audit) [0.35 sec, 19.2 MB]                                           [bootstrap]
Database: Info
  Total size: Determine the size of the database.
    Total size: 19.00MB
Command dispatch complete [0.5 sec, 19.58 MB]                                                                           [notice]
 Timer  Cum (sec)  Count  Avg (msec)
 page   0.39       1      390

Peak memory usage was 21.56 MB [0.5 sec, 19.58 MB]

No error. This is the raw SQL:

mysql> SELECT SUM(TABLES.data_length + TABLES.index_length) FROM information_schema.TABLES WHERE TABLES.table_schema = "broken" GROUP BY TABLES.table_schema;
+-----------------------------------------------+
| SUM(TABLES.data_length + TABLES.index_length) |
+-----------------------------------------------+
|                                      19921380 |
+-----------------------------------------------+
1 row in set, 1 warning (0.02 sec)

I believe you're having a problem, but it's not with a view. Are you certain it's with the size check, and are you certain it's within Site Audit? What's the result of that specific check for you?

mdupont’s picture

mdupont
Primary language French
Location Switzerland
CreditAttribution: mdupont commented

Indeed, with the view I gave as an example, I still can run the raw SQL query for DB size.

Here is another View that triggers the bug in my DB (note this is a site hosted at Pantheon):

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `nonexisting`@`%` 
    SQL SECURITY DEFINER
VIEW `nodeView` AS
    SELECT 
        `d`.`uid` AS `uid`, `d`.`nid` AS `nid`
    FROM
        `node` `d`
    WHERE
        (`d`.`status` = 1)
    GROUP BY `d`.`uid` , `d`.`nid`

The error that occurs is this one: http://stackoverflow.com/questions/6527599/mysql-forgets-who-is-logged-i...

Now here's the tricky thing. To reproduce the bug in my context:

  • the view should select at least 2 columns from the same table (no bug when 1 column)
  • the query should have a GROUP BY on at least 2 columns (no bug without GROUP BY)
  • the name of the view has to be in camelCase (no bug with snake_case)
  • the name of the view can't start with a letter lower than "c" (no bug if the name starts with "a" or "b")

Color me puzzled.

mdupont’s picture

mdupont
Primary language French
Location Switzerland
CreditAttribution: mdupont commented
Status: Postponed (maintainer needs more info) » Active

I made some further research and to sum up I don't think it's possible to calculate the DB size with a buggy View.

The right thing to do is to enclose the query in calculateScore() inside a try...catch block, so we can catch the PDOException and warn the user there's something wrong.

It appears that the cause is that the value for the *_length columns in information_schema.TABLES are calculated, and the bug occurs when trying to calculate a *_length for the buggy view. Adding WHERE clauses to the query doesn't help. The only fix is for the site owner to go and fix the DEFINER in the View.

mdupont’s picture

mdupont
Primary language French
Location Switzerland
CreditAttribution: mdupont commented
Status: Active » Needs review
FileSize
db_size_check_catch_failure-2394635-6.patch1.7 KB

Patch attached to catch an error executing the query and aborting cleanly if it's the case.

FluxSauce’s picture

FluxSauce CreditAttribution: FluxSauce commented
Status: Needs review » Fixed

Great, replicated the bug locally with nodeView!

Thanks for the patch, much appreciated! The patch supplied included a syntax error (always test!); I fixed it and clarified the error message (empty isn't always accurate now).

drush ad --detail --skip=DatabaseCollation,DatabaseEngine,DatabaseRowCount
Database: 0%
  Total size: Determine the size of the database.
    Empty or unable to determine the size due to a permission error.

Committed. I'm going to do some cleanup and roll 1.13 this afternoon.

  • FluxSauce committed 5c772ef on 7.x-1.x authored by mdupont 
    Issue #2394635 by mdupont: Database size check shows "0 bytes" instead...
FluxSauce’s picture

FluxSauce CreditAttribution: FluxSauce commented
Status: Fixed » Closed (fixed)