MySQL Bugs: #41079: information_schema.schema_privileges is limited to 7680 records.

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

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.

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

[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.

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.

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.

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.

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)

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.

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)

Noted in 5.1.31 changelog.

Setting report to NDI pending push into 6.0.x.

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)

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)

Setting status back to NDI pending merge to 6.0 tree.

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)

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)

Noted in 6.0.10 changelog.