| Bug #40955 | SHOW TABLE STATUS truncates information | ||
|---|---|---|---|
| Submitted: | 23 Nov 2008 14:04 | Modified: | 8 Oct 2010 17:42 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.67, 5.0.70 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | qc | ||
[23 Nov 2008 15:54]
Valeriy Kravchuk
Verified just as described with 5.0.70:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.70-enterprise-gpl-nt MySQL Enterprise Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `parent` (
-> `id` int(11) NOT NULL auto_increment,
-> `parenttext` varchar(50) default NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.22 sec)
mysql> CREATE TABLE `child` (
-> `id` int(11) NOT NULL auto_increment,
-> `fkfield` int(11) default NULL,
-> PRIMARY KEY (`id`),
-> KEY `FK_child` (`fkfield`),
-> CONSTRAINT `FK_child` FOREIGN KEY (`fkfield`) REFERENCES `parent` (`id`
) ON DELETE SET NULL ON UPDATE CASCADE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)
mysql> show create table child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`id` int(11) NOT NULL auto_increment,
`fkfield` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `FK_child` (`fkfield`),
CONSTRAINT `FK_child` FOREIGN KEY (`fkfield`) REFERENCES `parent` (`id`) ON DE
LETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.02 sec)
mysql> show table status like 'child'\G
*************************** 1. row ***************************
Name: child
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 1
Create_time: 2008-11-23 17:50:29
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 1239040 kB; (`fkfield`) REFER `test/parent`(`id`)
ON UPDATE CASCADE
1 row in set (0.00 sec)
mysql> show table status like 'child';
+-------+--------+---------+------------+------+----------------+-------------+-
----------------+--------------+-----------+----------------+-------------------
--+-------------+------------+-----------------+----------+----------------+----
------------------------------------------------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time
| Update_time | Check_time | Collation | Checksum | Create_options | Com
ment |
+-------+--------+---------+------------+------+----------------+-------------+-
----------------+--------------+-----------+----------------+-------------------
--+-------------+------------+-----------------+----------+----------------+----
------------------------------------------------------------------------------+
| child | InnoDB | 10 | Compact | 0 | 0 | 16384 |
0 | 16384 | 0 | 1 | 2008-11-23 17:50:2
9 | NULL | NULL | utf8_general_ci | NULL | | Inn
oDB free: 1239040 kB; (`fkfield`) REFER `test/parent`(`id`) ON UPDATE CASCADE |
+-------+--------+---------+------------+------+----------------+-------------+-
----------------+--------------+-----------+----------------+-------------------
--+-------------+------------+-----------------+----------+----------------+----
------------------------------------------------------------------------------+
1 row in set (0.00 sec)
I think SHOW CREATE TABLE can be used to get correct/complete foreign key information at least in some cases.
[23 Nov 2008 15:57]
Valeriy Kravchuk
One should NOT reply on comment of SHOW TABLE STATUS for this information anyway, as in 5.1.30 or 6.0.7, for example, it is just empty:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `parent` (
-> `id` int(11) NOT NULL auto_increment,
-> `parenttext` varchar(50) default NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.30 sec)
mysql> CREATE TABLE `child` (
-> `id` int(11) NOT NULL auto_increment,
-> `fkfield` int(11) default NULL,
-> PRIMARY KEY (`id`),
-> KEY `FK_child` (`fkfield`),
-> CONSTRAINT `FK_child` FOREIGN KEY (`fkfield`) REFERENCES `parent` (`id`
) ON DELETE SET NULL ON UPDATE CASCADE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)
mysql> show create table child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fkfield` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_child` (`fkfield`),
CONSTRAINT `FK_child` FOREIGN KEY (`fkfield`) REFERENCES `parent` (`id`) ON DE
LETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.02 sec)
mysql> show table status like 'child'\G
*************************** 1. row ***************************
Name: child
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 233832448
Auto_increment: 1
Create_time: 2008-11-23 17:54:47
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.08 sec)
[2 Oct 2010 8:10]
Peter Laursen
Any idea about status of this in 5.1 and 5.5 servers?
[2 Oct 2010 8:23]
Valeriy Kravchuk
With 5.1.x situation is better, it does NOT show this information at all in SHOW TABLE STATUS :)
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.52-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `parent` (
-> `id` int(11) NOT NULL auto_increment,
-> `parenttext` varchar(50) default NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.11 sec)
mysql> CREATE TABLE `child` ( `id` int(11) NOT NULL auto_increment, `fkfield` int(11) default NULL, PRIMARY KEY (`id`), KEY `FK_child` (`fkfield`), CONSTRAINT `FK_child` FOREIGN KEY (`fkfield`) REFERENCES `parent` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.47 sec)
mysql> show table status like 'child'\G
*************************** 1. row ***************************
Name: child
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 17825792
Auto_increment: 1
Create_time: 2010-10-02 11:20:32
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
So, just use perfect workaround:
mysql> show create table child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fkfield` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_child` (`fkfield`),
CONSTRAINT `FK_child` FOREIGN KEY (`fkfield`) REFERENCES `parent` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
[8 Oct 2010 17:42]
Peter Laursen
so, that is better, you think ? :-) anyway we don't depend on SHOW TABLE STATUS in this context any more ...
Description: SHOW TABLE STATUS truncates information. A table with a FK constraint defined "KEY `FK_child` (`fkfield`), CONSTRAINT `FK_child` FOREIGN KEY (`fkfield`) REFERENCES `parent` (`id`) ON DELETE SET NULL ON UPDATE CASCADE" becomes " (`fkfield`) REFER `fktest/parent`(`id`) ON UPDATE CASCA " only in SHOW TABLE STATUS Verified in both command line client and libmysql(SQLyog). So I think it is a server and not a client issue! How to repeat: Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.0.67-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use fktest; Database changed mysql> show create table parent; +--------+---------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------+ | Table | Create Table | +--------+---------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------+ | parent | CREATE TABLE `parent` ( `id` int(11) NOT NULL auto_increment, `parenttext` varchar(50) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+---------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------+ 1 row in set (0.00 sec) mysql> show create table child; +-------+----------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------------+ | child | CREATE TABLE `child` ( `id` int(11) NOT NULL auto_increment, `fkfield` int(11) default NULL, PRIMARY KEY (`id`), KEY `FK_child` (`fkfield`), CONSTRAINT `FK_child` FOREIGN KEY (`fkfield`) REFERENCES `parent` (`id`) ON DE LETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+----------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show table status; +--------+--------+---------+------------+------+----------------+-------------+ -----------------+--------------+-----------+----------------+------------------ ---+-------------+------------+-----------------+----------+----------------+--- -------------------------------------------------------------------------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Co mment | +--------+--------+---------+------------+------+----------------+-------------+ -----------------+--------------+-----------+----------------+------------------ ---+-------------+------------+-----------------+----------+----------------+--- -------------------------------------------------------------------------------+ | child | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2008-11-20 15:57: 12 | NULL | NULL | utf8_general_ci | NULL | | In noDB free: 1789952 kB; (`fkfield`) REFER `fktest/parent`(`id`) ON UPDATE CASCA | | parent | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2008-11-20 15:56: 26 | NULL | NULL | utf8_general_ci | NULL | | In noDB free: 1789952 kB | +--------+--------+---------+------------+------+----------------+-------------+ -----------------+--------------+-----------+----------------+------------------ ---+-------------+------------+-----------------+----------+----------------+--- -------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> Suggested fix: It should not truncate! And ON DELETE and ON UPDATE should be returned in same order with every statement. One more annoying example of lack of attention to detail and uniform standards in MySQL!