| Bug #9896 | SHOW TABLE STATUS shows wrong information | ||
|---|---|---|---|
| Submitted: | 14 Apr 2005 10:52 | Modified: | 18 Oct 2005 23:27 |
| Reporter: | Max Mether | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.0.3-max-beta | OS: | Linux (SuSE) |
| Assigned to: | Martin Skold | CPU Architecture: | Any |
[14 Apr 2005 11:05]
Max Mether
added version
[14 Apr 2005 14:08]
Jorge del Conde
Verified w/5.0.4 max from bk
[6 May 2005 12:34]
Martin Skold
Not high priority compared to other more urgent bugs
[17 Jun 2005 8:19]
Martin Skold
Please attach SQL statements to reproduce the problem
[17 Jul 2005 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[14 Sep 2005 14:32]
Max Mether
1) Create the following table: CREATE TABLE `citycopy` ( `Id` int(11) NOT NULL auto_increment, `Name` char(35) NOT NULL default '', `Country` char(3) NOT NULL default '', `District` char(20) NOT NULL default '', `Population` int(11) NOT NULL default '0', PRIMARY KEY (`Id`), KEY `Name` (`Name`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 | 1) Insert some rows into the table, I inserted 209264 rows. 2) Check the table meta data: select table_name, table_rows, avg_row_length, data_length, data_length/table_rows from information_schema.tables where table_name = 'citycopy'; +------------+------------+----------------+-------------+------------------------+ | table_name | table_rows | avg_row_length | data_length | data_length/table_rows | +------------+------------+----------------+-------------+------------------------+ | citycopy | 209264 | 80 | 9895936 | 47.2892 | +------------+------------+----------------+-------------+------------------------+ 1 row in set (0.09 sec) The data length doesn't look right. It should be around 80bytes / row, not 47.
[20 Sep 2005 8:36]
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/internals/30090
[13 Oct 2005 18:21]
Tomas Ulin
Martin, this has been pushed. Please provide version it was pushed to. T
[14 Oct 2005 10:00]
Martin Skold
Pushed to 5.0.13
[18 Oct 2005 23:27]
Jon Stephens
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:
Documented fix in 5.0.13 changelog.
[16 Nov 2005 19:28]
Matt McParland
This bug showed up again in 5.0.15. I used the same `citycopy` table as the previous comment and the numbers still don't match up:
Rows: 132091
Avg_row_length: 80
Data_length: 11534336
Data_length / Rows shows a discrepency of 7 bytes per row. Not that huge.
On a table from my development database the discrepency is higher:
Rows: 570751
Avg_row_length: 836
Data_length: 587857920
A discrepency of 193 bytes per row, or about 23%. That's too much.
My table looks like:
CREATE TABLE `transaction` (
`trans_id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL,
`user_promotion_id` int(11) NOT NULL,
`game_id` int(11) default NULL,
`processor_id` int(11) default NULL,
`description` varchar(255) default NULL,
`type` enum('D','C','R','P','M','W','N','S','I','A','B','F','L') default NULL,
`status` enum('PENDING','COMPLETE','CANCELLED','DENIED','PROCESSING','VOID') default NULL,
`amount` decimal(7,2) NOT NULL,
`wagered` decimal(7,2) default NULL,
`ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`pay_table_id` int(11) default NULL,
`session_id` int(11) default NULL,
`parent_trans_id` int(11) default NULL,
PRIMARY KEY (`trans_id`),
KEY `user_id` (`user_id`),
KEY `session_id` (`session_id`),
KEY `game_id` (`game_id`),
KEY `processor_id` (`processor_id`),
KEY `user_id_game_id` (`user_id`,`game_id`),
KEY `parent_trans_id` (`parent_trans_id`),
KEY `user_id_game_id_status` (`user_id`,`game_id`,`status`,`trans_id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
Description: SHOW TABLE STATUS shows erraneous information. The Data_length in the below examples is incosistent. In the second table Row_count x Avg_row_length > Data_length 13:02 ndb> desc test; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | a | int(11) | NO | PRI | | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.04 sec) 13:02 ndb> show table status like 'test' \G *************************** 1. row *************************** Name: test Engine: ndbcluster Version: 9 Row_format: Fixed Rows: 4 Avg_row_length: 16 Data_length: 131072 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: number_of_replicas: 2 1 row in set (0.02 sec) 13:02 ndb> desc t2; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | a | int(11) | NO | PRI | NULL | auto_increment | | b | int(11) | YES | | NULL | | | c | int(11) | YES | | NULL | | | d | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) 13:03 ndb> show table status like 't2' \G *************************** 1. row *************************** Name: t2 Engine: ndbcluster Version: 9 Row_format: Fixed Rows: 11200 Avg_row_length: 32 Data_length: 262144 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 112441 Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: number_of_replicas: 2 1 row in set (0.05 sec) How to repeat: Create similar tables with bogus data and see what you get. Can provide SQL file with table creation statements and inserts if needed