Bug #41079 information_schema.schema_privileges is limited to 7680 records.
Submitted: 27 Nov 2008 18:32 Modified: 28 Jan 2009 21:37
Reporter: ws lee Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.0(5.0.51a), 5.1(5.1.30), 5.0, 5.1, 6.0 bzr OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any

[27 Nov 2008 18:32] ws lee
Description:
information_schema.schema_privileges is limited to 7680 records.

although mysql.db has priv info, information_schema has not privileges info.
priv info of mysql.db is not matched priv info of information_schema.

How to repeat:
mysql> select count(*) from information_schema.schema_privileges;
+----------+
| count(*) |
+----------+
|     7680 | 
+----------+
1 row in set (0.06 sec)

mysql> 
mysql> GRANT ALL PRIVILEGES ON test2.* TO test2@localhost identified by 'test2';
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from information_schema.schema_privileges;
+----------+
| count(*) |
+----------+
|     7680 | 
+----------+
1 row in set (0.05 sec);

Information_schema.schema_privileges records not increase over 7680.
It seems to delete old priv and new user priv was overwritted or new user prive not added in schema_privileges table.
[27 Nov 2008 23:17] Sveta Smirnova
Thank you for the report.

Verified as described:

mysql> show table status like 'schema_privileges'\G
*************************** 1. row ***************************
           Name: SCHEMA_PRIVILEGES
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: NULL
 Avg_row_length: 2179
    Data_length: 0
Max_data_length: 16767405
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: max_rows=7699
        Comment: 
1 row in set (0.01 sec)

Additionally MEMORY storage engine does not allow to store much more than max_rows values, although user manual contains word about this is not hard limit:

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TEMPORARY TABLE `SCHEMA_PRIVILEGES` (
    ->   `GRANTEE` varchar(81) NOT NULL DEFAULT '',
    ->   `TABLE_CATALOG` varchar(512) DEFAULT NULL,
    ->   `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
    ->   `PRIVILEGE_TYPE` varchar(64) NOT NULL DEFAULT '',
    ->   `IS_GRANTABLE` varchar(3) NOT NULL DEFAULT ''
    -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8 max_rows=5;
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> insert into SCHEMA_PRIVILEGES values('test2@localhost', NULL, 'test2', 'ALTER', 'NO');
Query OK, 1 row affected (0.46 sec)

mysql> insert into SCHEMA_PRIVILEGES values('test2@localhost', NULL, 'test2', 'ALTER', 'NO');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SCHEMA_PRIVILEGES values('test2@localhost', NULL, 'test2', 'ALTER', 'NO');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SCHEMA_PRIVILEGES values('test2@localhost', NULL, 'test2', 'ALTER', 'NO');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SCHEMA_PRIVILEGES values('test2@localhost', NULL, 'test2', 'ALTER', 'NO');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SCHEMA_PRIVILEGES values('test2@localhost', NULL, 'test2', 'ALTER', 'NO');
Query OK, 1 row affected (0.31 sec)

mysql> insert into SCHEMA_PRIVILEGES values('test2@localhost', NULL, 'test2', 'ALTER', 'NO');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SCHEMA_PRIVILEGES values('test2@localhost', NULL, 'test2', 'ALTER', 'NO');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SCHEMA_PRIVILEGES values('test2@localhost', NULL, 'test2', 'ALTER', 'NO');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SCHEMA_PRIVILEGES values('test2@localhost', NULL, 'test2', 'ALTER', 'NO');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SCHEMA_PRIVILEGES values('test2@localhost', NULL, 'test2', 'ALTER', 'NO');
ERROR 1114 (HY000): The table 'schema_privileges' is full
[28 Nov 2008 8:30] ws lee
[Create_options: max_rows=] option of memory is not good.
Because of this reason, schema_privileges not same mysql.db.

schema_privileges have not correct info.
what does meaning of schema_privileges's existing?
this table that incorect info has, not nessary.
[28 Nov 2008 15:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/60189

2731 Sergey Glukhov	2008-11-28
      Bug#41079 information_schema.schema_privileges is limited to 7680 records.
      The problem is that we cannot insert new record into memory table
      when table size exceeds max memory table size.
      The fix is to use schema_table_store_record() function which
      converts memory table into MyISAM in case of table size exceeding.
[8 Dec 2008 13:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/60912

2731 Sergey Glukhov	2008-12-08
      Bug#41079 information_schema.schema_privileges is limited to 7680 records.
      The problem is that we cannot insert new record into memory table
      when table size exceeds max memory table size.
      The fix is to use schema_table_store_record() function which
      converts memory table into MyISAM in case of table size exceeding.
      
      Note:
      There is no test case for this bug, the reason is that
      1. The code  that was added already checks(i.e. works) with existing tests
      2. Correct work of schema_table_store_record() is checked with other test cases
         (information_schema tests)
      So new code is fully covered with existing test cases.
[23 Dec 2008 14:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/62262

2720 Sergey Glukhov	2008-12-23
      Bug#41079 information_schema.schema_privileges is limited to 7680 records.
      The problem is that we cannot insert new record into memory table
      when table size exceeds max memory table size.
      The fix is to use schema_table_store_record() function which
      converts memory table into MyISAM in case of table size exceeding.
      
      Note:
      There is no test case for this bug, the reason is that
      1. The code  that was added already is checked(i.e. works) with existing tests
      2. Correct work of schema_table_store_record() is checked with other test cases
         (information_schema tests)
      So new code is fully covered with existing test cases.
[6 Jan 2009 13:56] Bugs System
Pushed into 5.0.76 (revid:joro@sun.com-20090105160414-8q9j4bi1klkfwiup) (version source revid:azundris@mysql.com-20081230114734-nmsc37ak330zlygn) (merge vers: 5.0.76) (pib:6)
[7 Jan 2009 20:33] Paul DuBois
Noted in 5.0.76 changelog.

The INFORMATION_SCHEMA.SCHEMA_PRIVILEGES table was limited to 7680
rows.   

Setting report to NDI pending push into 5.1.x/6.0.x.
[15 Jan 2009 6:33] Bugs System
Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:azundris@mysql.com-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)
[15 Jan 2009 16:36] Paul DuBois
Noted in 5.1.31 changelog.

Setting report to NDI pending push into 6.0.x.
[19 Jan 2009 11:30] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:07] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 15:08] Jon Stephens
Setting status back to NDI pending merge to 6.0 tree.
[19 Jan 2009 16:13] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[20 Jan 2009 18:59] Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)
[28 Jan 2009 21:37] Paul DuBois
Noted in 6.0.10 changelog.