<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="performance-schema-table-characteristics">
      </a>
      29.11 Performance Schema General Table Characteristics
     </h2>
    </div>
   </div>
  </div>
  <p>
   The name of the
   <code class="literal">
    performance_schema
   </code>
   database is
      lowercase, as are the names of tables within it. Queries should
      specify the names in lowercase.
  </p>
  <a class="indexterm" name="idm46045074401328">
  </a>
  <a class="indexterm" name="idm46045074399824">
  </a>
  <p>
   Many tables in the
   <code class="literal">
    performance_schema
   </code>
   database
      are read only and cannot be modified:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa43342474"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">TRUNCATE</span> <span class="token keyword">TABLE</span> performance_schema<span class="token punctuation">.</span>setup_instruments<span class="token punctuation">;</span>
<span class="token output">ERROR 1683 (HY000)<span class="token punctuation">:</span> Invalid performance_schema usage.</span></code></pre>
  </div>
  <p>
   Some of the setup tables have columns that can be modified to
      affect Performance Schema operation; some also permit rows to be
      inserted or deleted. Truncation is permitted to clear collected
      events, so
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE TABLE
    </code>
   </a>
   can be
      used on tables containing those kinds of information, such as
      tables named with a prefix of
   <code class="literal">
    events_waits_
   </code>
   .
  </p>
  <p>
   Summary tables can be truncated with
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE
      TABLE
    </code>
   </a>
   . Generally, the effect is to reset the summary
      columns to 0 or
   <code class="literal">
    NULL
   </code>
   , not to remove rows. This
      enables you to clear collected values and restart aggregation.
      That might be useful, for example, after you have made a runtime
      configuration change. Exceptions to this truncation behavior are
      noted in individual summary table sections.
  </p>
  <p>
   Privileges are as for other databases and tables:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      To retrieve from
      <code class="literal">
       performance_schema
      </code>
      tables,
          you must have the
      <a class="link" href="privileges-provided.html#priv_select">
       <code class="literal">
        SELECT
       </code>
      </a>
      privilege.
     </p>
    </li>
    <li class="listitem">
     <p>
      To change those columns that can be modified, you must have
          the
      <a class="link" href="privileges-provided.html#priv_update">
       <code class="literal">
        UPDATE
       </code>
      </a>
      privilege.
     </p>
    </li>
    <li class="listitem">
     <p>
      To truncate tables that can be truncated, you must have the
      <a class="link" href="privileges-provided.html#priv_drop">
       <code class="literal">
        DROP
       </code>
      </a>
      privilege.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Because only a limited set of privileges apply to Performance
      Schema tables, attempts to use
   <code class="literal">
    GRANT ALL
   </code>
   as
      shorthand for granting privileges at the database or table level
      fail with an error:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa73405343"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> performance_schema<span class="token punctuation">.</span><span class="token operator">*</span>
       <span class="token keyword">TO</span> <span class="token string">'u1'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1044 (42000)<span class="token punctuation">:</span> Access denied for user 'root'@'localhost'
to database 'performance_schema'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> performance_schema<span class="token punctuation">.</span>setup_instruments
       <span class="token keyword">TO</span> <span class="token string">'u2'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1044 (42000)<span class="token punctuation">:</span> Access denied for user 'root'@'localhost'
to database 'performance_schema'</span></code></pre>
  </div>
  <p>
   Instead, grant exactly the desired privileges:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3700637"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span> <span class="token keyword">ON</span> performance_schema<span class="token punctuation">.</span><span class="token operator">*</span>
       <span class="token keyword">TO</span> <span class="token string">'u1'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.03 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span><span class="token punctuation">,</span> <span class="token keyword">UPDATE</span> <span class="token keyword">ON</span> performance_schema<span class="token punctuation">.</span>setup_instruments
       <span class="token keyword">TO</span> <span class="token string">'u2'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.02 sec)</span></code></pre>
  </div>
 </div>
 <br/>
</div>
