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".