Re: Indexes issue importing tablespaces
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 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)
Re: Indexes issue importing tablespaces
Did you check if an ANALYZE TABLE is enough in this case? -- Wagner Bianchi Mobile: +55.31.8654.9510 > Em 10/10/2014, às 09:06, Ruben Cardenal 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 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.
Indexes issue importing tablespaces
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 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.
