| Bug #58658 | Data truncation when joining I_S tables | ||
|---|---|---|---|
| Submitted: | 2 Dec 2010 13:55 | Modified: | 5 Jul 2011 18:24 |
| Reporter: | Andrew Hutchings | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 5.5.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[2 Dec 2010 14:05]
Valeriy Kravchuk
Verified on 32-bit Windows XP:
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -P3312 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.5.7-rc MySQL Community Server (GPL)
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 product (category INT NOT NULL, id INT NOT NULL,
-> price DECIMAL,
-> PRIMARY KEY(category, id)) ENGINE=INNODB;
Query OK, 0 rows affected (0.19 sec)
mysql>
mysql> CREATE TABLE customer (id INT NOT NULL,
-> PRIMARY KEY (id)) ENGINE=INNODB;
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
-> product_category INT NOT NULL,
-> product_id INT NOT NULL,
-> customer_id INT NOT NULL,
-> PRIMARY KEY(no),
-> INDEX (product_category, product_id),
-> FOREIGN KEY (product_category, product_id)
-> REFERENCES product(category, id)
-> ON UPDATE CASCADE ON DELETE RESTRICT,
-> INDEX (customer_id),
-> FOREIGN KEY (customer_id)
-> REFERENCES customer(id)) ENGINE=INNODB;
Query OK, 0 rows affected (0.13 sec)
mysql> select kc.constraint_name, kc.table_name, group_concat(distinct
-> concat('`',kc.column_name,'`')), kc.referenced_table_name, group_concat(
distinct
-> concat('`',kc.referenced_column_name,'`')), update_rule, delete_rule, mat
ch_option from
-> information_schema.key_column_usage kc join information_schema.referent
ial_constraints
-> rc on (rc.constraint_schema = kc.constraint_schema and rc.constraint_name
=
-> kc.constraint_name) where kc.constraint_schema='test' and kc.table_name=
'product_order'
-> and kc.referenced_table_name is not null group by rc.constraint_name\G
*************************** 1. row ***************************
constraint_name: product_order_
ibfk_1
table_name: product_order
group_concat(distinct
concat('`',kc.column_name,'`')): `product_id`,`product_category`
referenced_table_name: pro
group_concat(distinct
concat('`',kc.referenced_column_name,'`')): `id`,`category`
update_rule: CASCADE
delete_rule: RESTRICT
match_option: NONE
*************************** 2. row ***************************
constraint_name: product_order_
ibfk_2
table_name: product_order
group_concat(distinct
concat('`',kc.column_name,'`')): `customer_id`
referenced_table_name: cus
group_concat(distinct
concat('`',kc.referenced_column_name,'`')): `id`
update_rule: RESTRICT
delete_rule: RESTRICT
match_option: NONE
2 rows in set (0.83 sec)
Description: See How to repeat. How to repeat: 1. CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, price DECIMAL, PRIMARY KEY(category, id)) ENGINE=INNODB; 2. CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; 3. CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=INNODB; 4. select kc.constraint_name, kc.table_name, group_concat(distinct concat('`',kc.column_name,'`')), kc.referenced_table_name, group_concat(distinct concat('`',kc.referenced_column_name,'`')), update_rule, delete_rule, match_option from information_schema.key_column_usage kc join information_schema.referential_constraints rc on (rc.constraint_schema = kc.constraint_schema and rc.constraint_name = kc.constraint_name) where kc.constraint_schema='test' and kc.table_name='product_order' and kc.referenced_table_name is not null group by rc.constraint_name\G *************************** 1. row *************************** constraint_name: product_order_ibfk_1 table_name: product_order group_concat(distinct concat('`',kc.column_name,'`')): `product_id`,`product_category` referenced_table_name: product group_concat(distinct concat('`',kc.referenced_column_name,'`')): `id`,`category` update_rule: CASCADE delete_rule: RESTRICT match_option: NONE *************************** 2. row *************************** constraint_name: product_order_ibfk_2 table_name: product_order group_concat(distinct concat('`',kc.column_name,'`')): `customer_id` referenced_table_name: custome group_concat(distinct concat('`',kc.referenced_column_name,'`')): `id` update_rule: RESTRICT delete_rule: RESTRICT match_option: NONE 2 rows in set (0.01 sec) Note in row 2 "referenced_table_name: custome", looks like it is truncated to the length of the result in row 1?