maria-developers team mailing list archive

[Witold Filipczyk <gglater62@xxxxxxxxx>]

On Tue, Feb 21, 2017 at 09:41:07PM +0100, Sergei Golubchik wrote:
> Hi, Witold!
> 
> On Feb 21, Witold Filipczyk wrote:
> > Hi,
> > I'm newbie, I have problem with INFORMATION_SCHEMA.
> > There is mariadb-10.0 with around 100000 databases. Disks are slow,
> > but most users have one database,
> > a few have more than one, but it is not a big number. Queries like
> > SHOW DATABASES or SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
> > bring to knees whole server.
> > The idea to resolve it is:
> > in find_files(...) instead of the for loop execute query:
> > SELECT REPLACE(TABLE_SCHEMA, '\\', '') FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE PRIVILEGE_TYPE = 'SELECT';
> > There are databases with names with _ in them and \_ in GRANTS.
> 
> That's a very interesting idea.
> 
> Basically, you're saying that if a user has no probal privileges and
> database privileges on only one database and no wildcards in the
> database name (*), then SELECT * FROM INFORMATION_SCHEMA.SCHEMATA essentially
> has an implicit WHERE schema_name="FOO" clause. Yes, that's certainly
> doable. It's even not very difficult to do, I'd think.
> 
> (*) this can be extended to privileges on few (more than one) databases
> and on wildcards in the database names, but let's start from something simple.
> 
> > This is theory, but I don't know how to do it. That's mean how to
> > freeze the current query, how to run new query, read results, and fill
> > files in the find_files function and resume the current query.
> 
> Kind of. Privileges are stored in memory in lists and hashes. You'd need
> to traverse them to see what databases a user has grants for.

I guess, I did not understand.
I did something like this:

/* sql_show.cc */
static find_files_result
find_files(THD *thd, Dynamic_array<LEX_STRING*> *files, LEX_STRING *db,
           const char *path, const LEX_STRING *wild)
{
  MY_DIR *dirp;
  Discovered_table_list tl(thd, files, wild);
  DBUG_ENTER("find_files");

  if (!(dirp = my_dir(path, MY_THREAD_SPECIFIC | (db ? 0 : MY_WANT_STAT))))
  {
    if (my_errno == ENOENT)
      my_error(ER_BAD_DB_ERROR, MYF(ME_BELL | ME_WAITTANG), db->str);
    else
      my_error(ER_CANT_READ_DIR, MYF(ME_BELL | ME_WAITTANG), path, my_errno);
    DBUG_RETURN(FIND_FILES_DIR);
  }

  if (!db)                                           /* Return databases */
  {
#ifndef NO_EMBEDDED_ACCESS_CHECKS
    char bufor[201];
    char *current;
    uint counter, i, n;
    ACL_DB *acl_db;
    char *curr_host= thd->security_ctx->priv_host_name();

    mysql_mutex_lock(&acl_cache->lock);

    for (counter=0 ; counter < acl_dbs.elements ; counter++)
    {
      const char *user, *host;

      acl_db=dynamic_element(&acl_dbs,counter,ACL_DB*);
      user= safe_str(acl_db->user);
      host= safe_str(acl_db->host.hostname);

      if ((strcmp(thd->security_ctx->priv_user, user) ||
         my_strcasecmp(system_charset_info, curr_host, host)))
        continue;

      if (is_in_ignore_db_dirs_list(acl_db->db))
        continue;

      for (i = 0, current = bufor; i < 200; ++i)
      {
        if (acl_db->db[i] == '\\') continue;
        if (acl_db->db[i] == '\0') break;
        *current++ = acl_db->db[i];
      }
      *current = '\0';

      if (tl.add_file(bufor))
        goto err;
    }
    mysql_mutex_unlock(&acl_cache->lock);
    tl.sort();
#endif
  }
  else
  {
    if (ha_discover_table_names(thd, db, dirp, &tl, false))
      goto err2;
  }
  DBUG_PRINT("info",("found: %zu files", files->elements()));
  my_dirend(dirp);

  DBUG_RETURN(FIND_FILES_OK);

err:
  mysql_mutex_unlock(&acl_cache->lock);
err2:
  my_dirend(dirp);
  DBUG_RETURN(FIND_FILES_OOM);
}

And turned on INFORMATION_SCHEMA in phpmyadmin and it is slow, very slow.
What I'm doing wrong here?
How to speed up?

diff -Nru mariadb-10.0-10.0.29.info/sql/sql_acl.cc mariadb-10.0-10.0.29/sql/sql_acl.cc
--- mariadb-10.0-10.0.29.info/sql/sql_acl.cc	2017-03-05 10:36:10.603234750 +0100
+++ mariadb-10.0-10.0.29/sql/sql_acl.cc	2017-03-05 10:38:17.155530562 +0100
@@ -213,11 +213,6 @@
 
 /* Classes */
 
-struct acl_host_and_ip
-{
-  char *hostname;
-  long ip, ip_mask;                      // Used with masked ip:s
-};
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
 static bool compare_hostname(const acl_host_and_ip *, const char *, const char *);
@@ -225,12 +220,6 @@
 #define compare_hostname(X,Y,Z) 0
 #endif
 
-class ACL_ACCESS {
-public:
-  ulong sort;
-  ulong access;
-};
-
 /* ACL_HOST is used if no host is specified */
 
 class ACL_HOST :public ACL_ACCESS
@@ -335,14 +324,6 @@
 
 };
 
-class ACL_DB :public ACL_ACCESS
-{
-public:
-  acl_host_and_ip host;
-  char *user,*db;
-  ulong initial_access; /* access bits present in the table */
-};
-
 #ifndef DBUG_OFF
 /* status variables, only visible in SHOW STATUS after -#d,role_merge_stats */
 ulong role_global_merges= 0, role_db_merges= 0, role_table_merges= 0,
@@ -588,15 +569,6 @@
 
 #define FIRST_NON_YN_FIELD 26
 
-class acl_entry :public hash_filo_element
-{
-public:
-  ulong access;
-  uint16 length;
-  char key[1];					// Key will be stored here
-};
-
-
 static uchar* acl_entry_get_key(acl_entry *entry, size_t *length,
                                 my_bool not_used __attribute__((unused)))
 {
@@ -714,7 +686,8 @@
 /* Flag to mark that on_node was already called for this role */
 #define ROLE_OPENED             (1L << 3)
 
-static DYNAMIC_ARRAY acl_hosts, acl_users, acl_dbs, acl_proxy_users;
+DYNAMIC_ARRAY acl_dbs;
+static DYNAMIC_ARRAY acl_hosts, acl_users, acl_proxy_users;
 static HASH acl_roles;
 /*
   An hash containing mappings user <--> role
@@ -728,7 +701,7 @@
 static bool allow_all_hosts=1;
 static HASH acl_check_hosts, column_priv_hash, proc_priv_hash, func_priv_hash;
 static DYNAMIC_ARRAY acl_wild_hosts;
-static Hash_filo<acl_entry> *acl_cache;
+Hash_filo<acl_entry> *acl_cache;
 static uint grant_version=0; /* Version of priv tables. incremented by acl_load */
 static ulong get_access(TABLE *form,uint fieldnr, uint *next_field=0);
 static bool check_is_role(TABLE *form);
diff -Nru mariadb-10.0-10.0.29.info/sql/sql_acl.h mariadb-10.0-10.0.29/sql/sql_acl.h
--- mariadb-10.0-10.0.29.info/sql/sql_acl.h	2017-03-05 10:35:42.863169909 +0100
+++ mariadb-10.0-10.0.29/sql/sql_acl.h	2017-03-05 10:38:21.203540024 +0100
@@ -19,6 +19,7 @@
 #include "my_global.h"                          /* NO_EMBEDDED_ACCESS_CHECKS */
 #include "violite.h"                            /* SSL_type */
 #include "sql_class.h"                          /* LEX_COLUMN */
+#include "hash_filo.h"
 
 #define SELECT_ACL      (1L << 0)
 #define INSERT_ACL      (1L << 1)
@@ -193,6 +194,36 @@
                           : ER_ACCESS_DENIED_ERROR;
 }
 
+class acl_entry :public hash_filo_element
+{
+public:
+  ulong access;
+  uint16 length;
+  char key[1];					// Key will be stored here
+};
+
+class ACL_ACCESS {
+public:
+  ulong sort;
+  ulong access;
+};
+
+struct acl_host_and_ip
+{
+  char *hostname;
+  long ip, ip_mask;                      // Used with masked ip:s
+};
+
+class ACL_DB :public ACL_ACCESS
+{
+public:
+  acl_host_and_ip host;
+  char *user,*db;
+  ulong initial_access; /* access bits present in the table */
+};
+
+extern DYNAMIC_ARRAY acl_dbs;
+extern Hash_filo<acl_entry> *acl_cache;
 
 /* prototypes */
 
diff -Nru mariadb-10.0-10.0.29.info/sql/sql_show.cc mariadb-10.0-10.0.29/sql/sql_show.cc
--- mariadb-10.0-10.0.29.info/sql/sql_show.cc	2017-03-05 10:36:32.415285735 +0100
+++ mariadb-10.0-10.0.29/sql/sql_show.cc	2017-03-05 10:38:25.451549954 +0100
@@ -59,6 +59,8 @@
 #include "debug_sync.h"
 #include "keycaches.h"
 
+#include "hash_filo.h"
+
 #ifdef WITH_PARTITION_STORAGE_ENGINE
 #include "ha_partition.h"
 #endif
@@ -811,6 +813,14 @@
   FIND_FILES_DIR
 };
 
+static char *safe_str(char *str)
+{ return str ? str : const_cast<char*>(""); }
+
+static const char *safe_str(const char *str)
+{ return str ? str : ""; }
+
+
+
 /*
   find_files() - find files in a given directory.
 
@@ -849,53 +859,62 @@
 
   if (!db)                                           /* Return databases */
   {
-    for (uint i=0; i < (uint) dirp->number_of_files; i++)
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+    char bufor[201];
+    char *current;
+    uint counter, i, n;
+    ACL_DB *acl_db;
+    char *curr_host= thd->security_ctx->priv_host_name();
+
+    mysql_mutex_lock(&acl_cache->lock);
+
+    for (counter=0 ; counter < acl_dbs.elements ; counter++)
     {
-      FILEINFO *file= dirp->dir_entry+i;
-#ifdef USE_SYMDIR
-      char *ext;
-      char buff[FN_REFLEN];
-      if (my_use_symdir && !strcmp(ext=fn_ext(file->name), ".sym"))
-      {
-        /* Only show the sym file if it points to a directory */
-        char *end;
-        *ext=0;                                 /* Remove extension */
-        unpack_dirname(buff, file->name);
-        end= strend(buff);
-        if (end != buff && end[-1] == FN_LIBCHAR)
-          end[-1]= 0;				// Remove end FN_LIBCHAR
-        if (!mysql_file_stat(key_file_misc, buff, file->mystat, MYF(0)))
-               continue;
-       }
-#endif
-      if (!MY_S_ISDIR(file->mystat->st_mode))
+      const char *user, *host;
+
+      acl_db=dynamic_element(&acl_dbs,counter,ACL_DB*);
+      user= safe_str(acl_db->user);
+      host= safe_str(acl_db->host.hostname);
+
+      if ((strcmp(thd->security_ctx->priv_user, user) ||
+         my_strcasecmp(system_charset_info, curr_host, host)))
         continue;
 
-      if (is_in_ignore_db_dirs_list(file->name))
+      if (is_in_ignore_db_dirs_list(acl_db->db))
         continue;
 
-      if (tl.add_file(file->name))
+      for (i = 0, current = bufor; i < 200; ++i)
+      {
+        if (acl_db->db[i] == '\\') continue;
+        if (acl_db->db[i] == '\0') break;
+        *current++ = acl_db->db[i];
+      }
+      *current = '\0';
+
+      if (tl.add_file(bufor))
         goto err;
     }
+    mysql_mutex_unlock(&acl_cache->lock);
     tl.sort();
+#endif
   }
   else
   {
     if (ha_discover_table_names(thd, db, dirp, &tl, false))
-      goto err;
+      goto err2;
   }
-
   DBUG_PRINT("info",("found: %zu files", files->elements()));
   my_dirend(dirp);
 
   DBUG_RETURN(FIND_FILES_OK);
 
 err:
+  mysql_mutex_unlock(&acl_cache->lock);
+err2:
   my_dirend(dirp);
   DBUG_RETURN(FIND_FILES_OOM);
 }
 
-
 /**
    An Internal_error_handler that suppresses errors regarding views'
    underlying tables that occur during privilege checking within SHOW CREATE
diff -Nru mariadb-10.0-10.0.29.info/sql/sql_show.h mariadb-10.0-10.0.29/sql/sql_show.h
--- mariadb-10.0-10.0.29.info/sql/sql_show.h	2017-03-05 10:36:00.199210431 +0100
+++ mariadb-10.0-10.0.29/sql/sql_show.h	2017-03-05 10:38:07.759508600 +0100
@@ -35,6 +35,8 @@
 struct TABLE;
 typedef struct system_status_var STATUS_VAR;
 
+class ACL_DB;
+
 /* Used by handlers to store things in schema tables */
 #define IS_FILES_FILE_ID              0
 #define IS_FILES_FILE_NAME            1