Bug #2874 Grant table bugs
Submitted: 18 Feb 2004 12:22 Modified: 11 Mar 2004 2:13
Reporter: Paul DuBois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Installing Severity:S3 (Non-critical)
Version:4.0 and up OS:
Assigned to: Bugs System CPU Architecture:Any

[18 Feb 2004 12:22] Paul DuBois
Description:
(I asked about this on dev-public@, but no one explained
it as "not a bug", so I assume it is)

An odd discrepancy?

Why is the Table_name column char(60) in tables_priv, but
char(64) in columns_priv?  It can't be shorter in tables_priv
due to key length problems, because columns_priv has a longer
key, but also a longer Table_name length.

From mysql_install_db.sh (4.0) or create_system_tables.sh (4.1):

if test ! -f $mdata/tables_priv.frm
then
  if test "$1" = "verbose" ; then
    echo "Preparing tables_priv table" 1>&2;
  fi

  c_t="$c_t CREATE TABLE tables_priv ("
  c_t="$c_t   Host char(60) binary DEFAULT '' NOT NULL,"
  c_t="$c_t   Db char(64) binary DEFAULT '' NOT NULL,"
  c_t="$c_t   User char(16) binary DEFAULT '' NOT NULL,"
  c_t="$c_t   Table_name char(60) binary DEFAULT '' NOT NULL,"
  c_t="$c_t   Grantor char(77) DEFAULT '' NOT NULL,"
  c_t="$c_t   Timestamp timestamp(14),"
[...]
if test ! -f $mdata/columns_priv.frm
then
  if test "$1" = "verbose" ; then
    echo "Preparing columns_priv table" 1>&2;
  fi

  c_c="$c_c CREATE TABLE columns_priv ("
  c_c="$c_c   Host char(60) binary DEFAULT '' NOT NULL,"
  c_c="$c_c   Db char(64) binary DEFAULT '' NOT NULL,"
  c_c="$c_c   User char(16) binary DEFAULT '' NOT NULL,"
  c_c="$c_c   Table_name char(64) binary DEFAULT '' NOT NULL,"
  c_c="$c_c   Column_name char(64) binary DEFAULT '' NOT NULL,"
  c_c="$c_c   Timestamp timestamp(14),"

Not a big deal, perhaps, except that the grant tables use a length of 64
for database, column, and table names everywhere with the exception of
the single instance tables_priv.Table_name.  I'm curious whether this is
deliberate or an oversight...

By the way, TIMESTAMP no longer supports a width specification, so both
those timestamp(14) declarations should be just timestamp. :-)

How to repeat:
See above.

Suggested fix:
Make tables_priv.Table_name a CHAR(64) column, not CHAR(60).
(affects 4.0/4.1)

Omit the (14) from the TIMESTAMP column declarations.
(4.1 only)
[18 Feb 2004 16:00] Dean Ellis
With the maximum allowed table name length being 64... verified.
[3 Mar 2004 8:36] Victor Vagin
bk commit - 4.0 tree (vva:1.1731)
ChangeSet
  1.1731 04/03/04 00:23:23 vva@eagle.mysql.r18.ru +16 -0
  fixed Bug #2874 "Grant table bugs"
  and added all tests to avoid such bugs in the future
[11 Mar 2004 2:13] Victor Vagin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be in mysql-4.0.19 and mysql-4.1.2