Bug #18568 some index can't create

Submitted: 28 Mar 2006 7:41 Modified: 28 Apr 2006 11:29
Reporter: fu yang Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:mysql-mysql-5.1.7-beta-linux-i686 OS:Linux (linux)
Assigned to: CPU Architecture:Any

[28 Mar 2006 7:41] fu yang
Description:
When I use Mysql Cluster with mysql-mysql-5.1.7-beta-linux-i686,I find some index can't create ,for example a table have many rows,but
 0 rows affected.  
    This is my create table statement:
    CREATE TABLE address ( addr_id int not null, addr_street1 varchar(40), addr_street2 varchar(40), addr_city varchar(30), addr_state varchar(20), addr_zip varchar(10), addr_co_id int, PRIMARY KEY(addr_id))ENGINE=NDB;
    CREATE TABLE author ( a_id int not null, a_fname varchar(20), a_lname varchar(20), a_mname varchar(20), a_dob date, a_bio varchar(500), PRIMARY KEY(a_id)) ENGINE=NDB;
    CREATE TABLE cc_xacts ( cx_o_id int not null, cx_type varchar(10), cx_num varchar(16), cx_name varchar(31), cx_expire date, cx_auth_id char(15), cx_xact_amt fixed(17,2), cx_xact_date timestamp, cx_co_id int, PRIMARY KEY(cx_o_id))ENGINE=NDB;
    CREATE TABLE country ( co_id int not null, co_name varchar(50), co_exchange fixed(12,6), co_currency varchar(18), PRIMARY KEY(co_id)) ENGINE=NDB
    CREATE TABLE customer ( c_id int not null, c_uname varchar(20), c_passwd varchar(20), c_fname varchar(15), c_lname varchar(15), c_addr_id int, c_phone varchar(16), c_email varchar(50), c_since date, c_last_login date, c_login timestamp, c_expiration timestamp, c_discount fixed(5,2), c_balance fixed(17,2), c_ytd_pmt fixed(17,2), c_birthdate date, c_data varchar(500), PRIMARY KEY(c_id))ENGINE=NDB;
    CREATE TABLE item ( i_id int not null, i_title varchar(60), i_a_id int, i_pub_date date, i_publisher varchar(60), i_subject varchar(60), i_desc varchar(500), i_related1 int, i_related2 int, i_related3 int, i_related4 int, i_related5 int, i_thumbnail varchar(40), i_image varchar(40), i_srp fixed(17,2), i_cost fixed(17,2), i_avail date, i_stock int, i_isbn char(13), i_page int, i_backing varchar(15), i_dimensions varchar(25), PRIMARY KEY(i_id))ENGINE=NDB;
    CREATE TABLE order_line ( ol_id int not null, ol_o_id int not null, ol_i_id int, ol_qty int, ol_discount fixed(5,2), ol_comments varchar(100), PRIMARY KEY(ol_o_id, ol_id))ENGINE=NDB;
    CREATE TABLE orders ( o_id int not null, o_c_id int, o_date timestamp, o_sub_total fixed(17,2), o_tax fixed(17,2), o_total fixed(17,2), o_ship_type varchar(10), o_ship_date timestamp, o_bill_addr_id int, o_ship_addr_id int, o_status varchar(15), PRIMARY KEY(o_id))ENGINE=NDB;
    CREATE TABLE shopping_cart ( sc_id int not null, sc_time timestamp, PRIMARY KEY(sc_id))ENGINE=NDB;
    CREATE TABLE shopping_cart_line ( scl_sc_id int not null, scl_qty int, scl_i_id int not null, PRIMARY KEY(scl_sc_id, scl_i_id))ENGINE=NDB;
 

 In my test ,Only "author,item,customer" tables have many rows affected,other tables  0 rows affected
  This is my records:
 
  mysql> create index author_a_lname on author(a_lname);
   Query OK, 2500 rows affected (2.39 sec)
   Records: 2500  Duplicates: 0  Warnings: 0
 
  mysql> create index address_addr_co_id on address(addr_co_id);
   Query OK, 0 rows affected (4.48 sec)
   Records: 0  Duplicates: 0  Warnings: 0
 
  mysql> create index addr_zip on address(addr_zip);
  Query OK, 0 rows affected (5.30 sec)
   Records: 0  Duplicates: 0  Warnings: 0
 
  mysql> create index customer_c_addr_id on customer(c_addr_id);
  Query OK, 288000 rows affected (2 min 2.09 sec)
  Records: 288000  Duplicates: 0  Warnings: 0
 
  mysql> create index customer_c_uname on customer(c_uname);
  Query OK, 288000 rows affected (2 min 6.93 sec)
  Records: 288000  Duplicates: 0  Warnings: 0
 
  mysql> create index item_i_title on item(i_title);
  Query OK, 10000 rows affected (10.38 sec)
  Records: 10000  Duplicates: 0  Warnings: 0
 
  mysql> create index item_i_subject on item(i_subject);
   Query OK, 10000 rows affected (6.84 sec)
   Records: 10000  Duplicates: 0  Warnings: 0
 
  mysql> create index item_i_a_id on item(i_a_id);
  Query OK, 10000 rows affected (13.85 sec)
  Records: 10000  Duplicates: 0  Warnings: 0
 
  mysql> create index order_line_ol_i_id on order_line(ol_i_id);
  Query OK, 0 rows affected (10.63 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> create index order_line_ol_o_id on order_line(ol_o_id);
  Query OK, 0 rows affected (9.04 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> create index country_co_name on country(co_name);
  Query OK, 0 rows affected (2.97 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> create index orders_o_c_id on orders(o_c_id);
  Query OK, 0 rows affected (4.04 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> create index scl_i_id on shopping_cart_line(scl_i_id);
  Query OK, 0 rows affected (1.36 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> create index orders_union on orders(o_id,o_c_id);
  Query OK, 0 rows affected (3.05 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> create index order_line_union on order_line(ol_i_id,ol_o_id,ol_qty);
  Query OK, 0 rows affected (6.32 sec)
  Records: 0  Duplicates: 0  Warnings: 0
 
  I don't know why 0 rows affected ,In fact I have many rows in tables.
 

How to repeat:
Exactly as above.
[28 Mar 2006 11:29] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of the SHOW TABLE STATUS or, at least, SELECT COUNT(*) FROM ... for all the tables mentioned in your report, just to be sure.
[28 Apr 2006 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".

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.