From: Date: July 1 2009 4:13pm
Subject: AW: why different users get different count(*) from same table?
List-Archive: http://lists.mysql.com/mysql/218017
Message-Id: <007401c9fa56$28c04330$7a40c990$@de>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Check your rights ;)

-----Urspr=FCngliche Nachricht-----
Von: jinava [mailto:jinava@stripped]=20
Gesendet: Mittwoch, 1. Juli 2009 14:52
An: mysql@stripped
Betreff: Re: why different users get different count(*) from same table?

Hi experts,

Something strange here, use root and mysql (root and mysql are user
accounts) to select count(*) from same table, but the result is =
different.

login as root:

mysql -uroot -p information_schema

mysql> select * from tables where table_name=3D'tb_staff'\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
  TABLE_SCHEMA: dbsg
     TABLE_NAME: tb_staff
    TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
       VERSION: 10
     ROW_FORMAT: Dynamic
    *TABLE_ROWS: 0*
 AVG_ROW_LENGTH: 0
   DATA_LENGTH: 0
MAX_DATA_LENGTH: 281474976710655
  INDEX_LENGTH: 1024
      DATA_FREE: 0
AUTO_INCREMENT: 1
    CREATE_TIME: 2009-05-31 16:13:07
   UPDATE_TIME: 2009-05-31 16:13:07
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.00 sec)

then,


login as mysql:

mysql -umysql -p information_schema

mysql> select * from tables where table_name=3D'tb_staff'\G
*************************** 1. row ***************************
 TABLE_CATALOG: NULL
   TABLE_SCHEMA: dbsg
    TABLE_NAME: tb_staff
     TABLE_TYPE: BASE TABLE
        ENGINE: MyISAM
        VERSION: 10
    ROW_FORMAT: Dynamic
     *TABLE_ROWS: 7519*
AVG_ROW_LENGTH: 223
    DATA_LENGTH: 1680120
MAX_DATA_LENGTH: 281474976710655
   INDEX_LENGTH: 79872
     DATA_FREE: 0
 AUTO_INCREMENT: 99022
   CREATE_TIME: 2009-06-29 16:57:50
    UPDATE_TIME: 2009-06-30 13:41:07
    CHECK_TIME: 2009-06-30 11:15:48
TABLE_COLLATION: latin1_swedish_ci
      CHECKSUM: NULL
 CREATE_OPTIONS:
 TABLE_COMMENT:
1 row in set (0.00 sec)

mysql>


Cheers
jinava