Hi Wagner, 

Yes! Analyze solves the situation in a moment. 

(14:21:09) [borrame] > alter table creditLine discard tablespace;
Query OK, 0 rows affected (0.41 sec) 

(14:21:21) [borrame] > alter table creditLine import tablespace;
Query OK, 0 rows affected (23.48 sec) 

(14:24:55) [borrame] > analyze table creditLine;
+--------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+---------+----------+----------+
| borrame.creditLine | analyze | status | OK |
+--------------------+---------+----------+----------+
1 row in set (0.16 sec) 

(14:25:09) [borrame] > show index from creditLine;
+------------+------------+--------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Null | Index_type | Comment |
Index_comment |
+------------+------------+--------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| creditLine | 0 | PRIMARY | 1 | id | A | 32237680 | NULL | NULL | |
BTREE | | |
| creditLine | 1 | creditLine_idClient | 1 | idClient | A | 16118840 |
NULL | NULL | | BTREE | | |
| creditLine | 1 | creditLine_idCreditSubTypology | 1 | idCreditTypology
| A | 5050 | NULL | NULL | | BTREE | | |
| creditLine | 1 | creditLine_idCountry | 1 | idCountry | A | 8161 |
NULL | NULL | | BTREE | | |
| creditLine | 1 | creditLine_idAffiliate | 1 | idAffiliate | A | 1794 |
NULL | NULL | | BTREE | | |
| creditLine | 1 | endDate | 1 | endDate | A | 64995 | NULL | NULL | YES
| BTREE | | |
| creditLine | 1 | status | 1 | status | A | 4 | NULL | NULL | | BTREE |
| |
+------------+------------+--------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec) 

(14:25:14) [borrame] > 

Thanks, 

Rubén. 

El 2014-10-10 14:19, Wagner Bianchi escribió: 

> Did you check if an ANALYZE TABLE is enough in this case?
> 
> --
> WAGNER BIANCHI
> MOBILE: +55.31.8654. [2]9510 
> 
> Em 10/10/2014, às 09:06, Ruben Cardenal <my...@ruben.cn> escreveu:
> 
>> Hi, 
>> 
>> I have this problem among several different instaces of 5.6.20. I take
>> all the steps as stated in
>> http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html [1] and get
>> no errors neither warnings, neither in the cli or the mysql log. 
>> 
>> (13:23:02) [borrame] > alter table creditLine discard tablespace;
>> Query OK, 0 rows affected (0.30 sec) 
>> 
>> (copy operation of the .cfg and .ibd files from the origin server) 
>> 
>> (13:23:19) [borrame] > alter table creditLine import tablespace;
>> Query OK, 0 rows affected (44.35 sec) 
>> 
>> 2014-10-10 13:26:42 1657 [Note] InnoDB: Importing tablespace for table
>> 'letsbonus/creditLine' that was exported from host 'dualla'
>> 2014-10-10 13:26:42 1657 [Note] InnoDB: Phase I - Update all pages
>> 2014-10-10 13:27:04 1657 [Note] InnoDB: Sync to disk
>> 2014-10-10 13:27:25 1657 [Note] InnoDB: Sync to disk - done!
>> 2014-10-10 13:27:26 1657 [Note] InnoDB: Phase III - Flush changes to
>> disk
>> 2014-10-10 13:27:26 1657 [Note] InnoDB: Phase IV - Flush complete
>> 2014-10-10 13:27:26 1657 [Note] InnoDB: "borrame"."creditLine" autoinc
>> value set to 87313435 
>> 
>> After this, the indexes look empty: 
>> 
>> (13:27:26) [borrame] > show index from creditLine;
>> +------------+------------+--------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
>> | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
>> | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
>> Index_comment |
>> +------------+------------+--------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
>> | creditLine | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | |
>> |
>> | creditLine | 1 | creditLine_idClient | 1 | idClient | A | 0 | NULL |
>> NULL | | BTREE | | |
>> | creditLine | 1 | creditLine_idCreditSubTypology | 1 | idCreditTypology
>> | A | 0 | NULL | NULL | | BTREE | | |
>> | creditLine | 1 | creditLine_idCountry | 1 | idCountry | A | 0 | NULL |
>> NULL | | BTREE | | |
>> | creditLine | 1 | creditLine_idAffiliate | 1 | idAffiliate | A | 0 |
>> NULL | NULL | | BTREE | | |
>> | creditLine | 1 | endDate | 1 | endDate | A | 0 | NULL | NULL | YES |
>> BTREE | | |
>> | creditLine | 1 | status | 1 | status | A | 0 | NULL | NULL | | BTREE |
>> | |
>> +------------+------------+--------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
>> 7 rows in set (0.00 sec) 
>> 
>> I have to optimize or null-alter the table to get them working: 
>> 
>> (13:27:34) [borrame] > alter table creditLine engine = InnoDB;
>> Query OK, 0 rows affected (12 min 57.41 sec)
>> Records: 0 Duplicates: 0 Warnings: 0 
>> 
>> (13:51:17) [borrame] > show index from creditLine;
>> +------------+------------+--------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
>> | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
>> | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
>> Index_comment |
>> +------------+------------+--------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
>> | creditLine | 0 | PRIMARY | 1 | id | A | 32237680 | NULL | NULL | |
>> BTREE | | |
>> | creditLine | 1 | creditLine_idClient | 1 | idClient | A | 16118840 |
>> NULL | NULL | | BTREE | | |
>> | creditLine | 1 | creditLine_idCreditSubTypology | 1 | idCreditTypology
>> | A | 1792 | NULL | NULL | | BTREE | | |
>> | creditLine | 1 | creditLine_idCountry | 1 | idCountry | A | 8967 |
>> NULL | NULL | | BTREE | | |
>> | creditLine | 1 | creditLine_idAffiliate | 1 | idAffiliate | A | 2 |
>> NULL | NULL | | BTREE | | |
>> | creditLine | 1 | endDate | 1 | endDate | A | 293069 | NULL | NULL |
>> YES | BTREE | | |
>> | creditLine | 1 | status | 1 | status | A | 4 | NULL | NULL | | BTREE |
>> | |
>> +------------+------------+--------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
>> 7 rows in set (0.00 sec) 
>> 
>> Is this a know issue? Or something I'm missing? I've checked the doc and
>> saw nothing related to this. 
>> 
>> Thanks, 
>> 
>> Rubén.
 

Links:
------
[1] http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html
[2] tel:+55.31.8654.9510

Reply via email to