Category Archives: mysql

MySQL lost “AUTO_INCREMENT” after a long time

Description:

I create a empty table and then alter this table's "AUTO_INCREMENT" starts from 10000000.After a long time ,MySQL lost "AUTO_INCREMENT" definition when I show this table again. MariaDB has the same problem but Percona Server is fine.

$mysql06

Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 141028725
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DROP TABLE IF EXISTS `test_bug` ;
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> CREATE TABLE test_bug (
    ->   `id` BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    ->   `consult_order_id` BIGINT ,
    ->   `pay_channel` INT COMMENT ,
    ->   `price` BIGINT ,
    ->   `trade_no` VARCHAR (64) ,
    ->   `right_no` VARCHAR (64) ,
    ->   `item_id` BIGINT NOT NULL,
    ->   `source_type` INT NOT NULL ,
    ->   `gmt_create` DATETIME DEFAULT CURRENT_TIMESTAMP ,
    ->   `gmt_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
    -> ) ENGINE = INNODB DEFAULT CHARSET = utf8 ;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> ALTER TABLE test_bug 
    ->   ADD INDEX index_consult_order (consult_order_id) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> ALTER TABLE test_bug 
    ->   ADD INDEX index_right_no (right_no) ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> ALTER TABLE test_bug 
    ->   AUTO_INCREMENT = 100000000 ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> show create table  test_bug;
  
   CREATE TABLE `test_bug` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `consult_order_id` bigint(20) DEFAULT NULL ,
  `pay_channel` int(11) DEFAULT NULL COMMENT ,
  `price` bigint(20) DEFAULT NULL COMMENT ,
  `trade_no` varchar(64) DEFAULT NULL COMMENT ,
  `right_no` varchar(64) DEFAULT NULL COMMENT ,
  `item_id` bigint(20) NOT NULL COMMENT ,
  `source_type` int(11) NOT NULL COMMENT ,
  `gmt_create` datetime DEFAULT CURRENT_TIMESTAMP ,
  `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  PRIMARY KEY (`id`),
  KEY `index_consult_order` (`consult_order_id`),
  KEY `index_right_no` (`right_no`)
) ENGINE=InnoDB AUTO_INCREMENT=100000000 DEFAULT CHARSET=utf8  


1 row in set (0.00 sec)


wait about 24 hours , then show create table :


mysql> show create table test_bug; 

   CREATE TABLE `test_bug` (
  `id` BIGINT (20) NOT NULL AUTO_INCREMENT,
  `consult_order_id` BIGINT (20) DEFAULT NULL,
  `pay_channel` INT (11) DEFAULT NULL,
  `price` BIGINT (20) DEFAULT NULL,
  `trade_no` VARCHAR (64) DEFAULT NULL,
  `right_no` VARCHAR (64) DEFAULT NULL,
  `item_id` BIGINT (20) NOT NULL,
  `source_type` INT (11) NOT NULL,
  `gmt_create` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `gmt_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `index_consult_order` (`consult_order_id`),
  KEY `index_right_no` (`right_no`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 

1 row in set (0.00 sec)  

AUTO_INCREMENT disappears .

Bug has been reported :https://bugs.mysql.com/bug.php?id=77743

使用MyDumper迁移InnoDB到TokuDB

最近几台微信买家相关数据库数据量增长迅猛,空间紧张,因而考虑迁移到压缩率超高的TokuDB,一个比较流行的迁移方法是用mysqldump导出同时修改InnoDB字符串到TokuDB:
mysqldump ...... | sed -e 's/^) ENGINE=MyISAM/) ENGINE=InnoDB/' > dump.sql

但这样导出的单一文件恢复时太慢,虽然有各种第三方方法能使mysqldump导出多个文件,但复杂了些;而mydumper因为并行特性是一个更理想的迁移工具,但0.6之前因为锁时间太长导致实际不可用,而0.6后大大降低了锁时间,因而这次我尝试使用MyDumper来迁移InnoDB到TokuDB,我的思路是:

因为MyDumper导出的文件事schema和data分开的,因而可以先导入schema,修改成TokuDB引擎后再将数据导入

以下是步骤:
1.导出文件
mydumper ...... -o /data/dump

2.将schema和data文件分离

<span class="hljs-title" style="font-weight: bold; color: #880000;">mkdir</span><span style="color: #000000;"> /</span><span class="hljs-typedef" style="color: #000000;"><span class="hljs-keyword" style="font-weight: bold;">data</span>/dump_schema 
</span><span class="hljs-title" style="font-weight: bold; color: #880000;">mv</span><span style="color: #000000;"> /</span><span class="hljs-typedef" style="color: #000000;"><span class="hljs-keyword" style="font-weight: bold;">data</span>/dump/*schema* /<span class="hljs-keyword" style="font-weight: bold;">data</span>/dump_schema 
</span><span class="hljs-title" style="font-weight: bold; color: #880000;">mv</span><span style="color: #000000;"> /</span><span class="hljs-typedef" style="color: #000000;"><span class="hljs-keyword" style="font-weight: bold;">data</span>/dump /<span class="hljs-keyword" style="font-weight: bold;">data</span>/dump_data 
</span><span class="hljs-title" style="font-weight: bold; color: #880000;">cp</span><span style="color: #000000;"> /</span><span class="hljs-typedef" style="color: #000000;"><span class="hljs-keyword" style="font-weight: bold;">data</span>/dump/metadata /<span class="hljs-keyword" style="font-weight: bold;">data</span>/dump_schema </span>

3.导入schema
myloader ...... -d /data/dump_schema

4.转换InnoDB到TokuDB
pt-find ...... --engine InnoDB --exec "ALTER TABLE %D.%N ENGINE=TokuDB"

5.导入data
myloader ...... -d /data/dump_data

MariaDB : Bug when add index on Partition table

Version: 5.5.5-10.0.10-MariaDB-log MariaDB Server

When I use mariadb as multi-source slave DB I met a strange problem . Creating index on a vary large partition table , Mariadb do — copy to tmp table on the slave side. It takes a long time and still not finish over 9 hours.

Primary : MySQL 5.6.16 —

add index on a partition table:

CREATE TABLE `track_raw_wap211_log` (
 `table_id` varchar(100) DEFAULT NULL,
 `page_id` varchar(100) DEFAULT NULL,
 `banner_id` varchar(100) DEFAULT NULL,
 `button_id` varchar(100) DEFAULT NULL,
 `test_id` varchar(100) DEFAULT NULL,
 `classfication` varchar(100) DEFAULT NULL,
 `request_refer` varchar(100) DEFAULT NULL,
 `request_url` text,
 `title` varchar(100) DEFAULT NULL,
 `user_id` varchar(100) DEFAULT NULL,
 `language` varchar(100) DEFAULT NULL,
 `event` varchar(100) DEFAULT NULL,
 `event_desc` varchar(100) DEFAULT NULL,
 `event_type` varchar(100) DEFAULT NULL,
 `log_version` varchar(100) DEFAULT NULL,
 `project` varchar(100) DEFAULT NULL,
 `log_time` varchar(100) DEFAULT NULL,
 `unicookie` varchar(100) DEFAULT NULL,
 `session_id` varchar(100) DEFAULT NULL,
 `session_duration` varchar(100) DEFAULT NULL,
 `resolution` varchar(100) DEFAULT NULL,
 `channel` varchar(100) DEFAULT NULL,
 `refer` text,
 `user_agent` text,
 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `httplogtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `httpforwardip` varchar(100) DEFAULT NULL,
 `country` varchar(100) DEFAULT NULL,
 `province` varchar(100) DEFAULT NULL,
 `city` varchar(100) DEFAULT NULL,
 `area` varchar(100) DEFAULT NULL,
 `mapweidu` varchar(100) DEFAULT NULL,
 `mapjingdu` varchar(100) DEFAULT NULL,
 `sync_mysqlid` bigint(20) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`sync_mysqlid`,`create_time`),
 KEY `ix_create_time` (`create_time`),
 KEY `ix_httplogtime` (`httplogtime`),
 KEY `ix_httplogtime_userid` (`httplogtime`,`user_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=23217118 DEFAULT CHARSET=utf8
 /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(`create_time`))
 (PARTITION p20150318 VALUES LESS THAN (1426694400) ENGINE = InnoDB,
 PARTITION p20150319 VALUES LESS THAN (1426780800) ENGINE = InnoDB,
 PARTITION p20150320 VALUES LESS THAN (1426867200) ENGINE = InnoDB,
 PARTITION p20150321 VALUES LESS THAN (1426953600) ENGINE = InnoDB,
 PARTITION p20150322 VALUES LESS THAN (1427040000) ENGINE = InnoDB,
 PARTITION p20150323 VALUES LESS THAN (1427126400) ENGINE = InnoDB,
 PARTITION p20150324 VALUES LESS THAN (1427212800) ENGINE = InnoDB,
 PARTITION p20150325 VALUES LESS THAN (1427299200) ENGINE = InnoDB,
 PARTITION p20150326 VALUES LESS THAN (1427385600) ENGINE = InnoDB,
 PARTITION p20150327 VALUES LESS THAN (1427472000) ENGINE = InnoDB,
 PARTITION p20150328 VALUES LESS THAN (1427558400) ENGINE = InnoDB,
 PARTITION p20150329 VALUES LESS THAN (1427644800) ENGINE = InnoDB,
 PARTITION p20150330 VALUES LESS THAN (1427731200) ENGINE = InnoDB,
 PARTITION p20150331 VALUES LESS THAN (1427817600) ENGINE = InnoDB,
 PARTITION p20150401 VALUES LESS THAN (1427904000) ENGINE = InnoDB,
 PARTITION p20150402 VALUES LESS THAN (1427990400) ENGINE = InnoDB,
 PARTITION p20150403 VALUES LESS THAN (1428076800) ENGINE = InnoDB,
 PARTITION p20150404 VALUES LESS THAN (1428163200) ENGINE = InnoDB,
 PARTITION p20150405 VALUES LESS THAN (1428249600) ENGINE = InnoDB,
 PARTITION p20150406 VALUES LESS THAN (1428336000) ENGINE = InnoDB,
 PARTITION p20150407 VALUES LESS THAN (1428422400) ENGINE = InnoDB,
 PARTITION p20150408 VALUES LESS THAN (1428508800) ENGINE = InnoDB,
 PARTITION p20150409 VALUES LESS THAN (1428595200) ENGINE = InnoDB,
 PARTITION p20150410 VALUES LESS THAN (1428681600) ENGINE = InnoDB,
 PARTITION p20150411 VALUES LESS THAN (1428768000) ENGINE = InnoDB,
 PARTITION p20150412 VALUES LESS THAN (1428854400) ENGINE = InnoDB,
 PARTITION p20150413 VALUES LESS THAN (1428940800) ENGINE = InnoDB,
 PARTITION p20150414 VALUES LESS THAN (1429027200) ENGINE = InnoDB,
 PARTITION p20150415 VALUES LESS THAN (1429113600) ENGINE = InnoDB,
 PARTITION p20150416 VALUES LESS THAN (1429200000) ENGINE = InnoDB,
 PARTITION p20150417 VALUES LESS THAN (1429286400) ENGINE = InnoDB,
 PARTITION p20150418 VALUES LESS THAN (1429372800) ENGINE = InnoDB,
 PARTITION p20150419 VALUES LESS THAN (1429459200) ENGINE = InnoDB,
 PARTITION p20150420 VALUES LESS THAN (1429545600) ENGINE = InnoDB,
 PARTITION p20150421 VALUES LESS THAN (1429632000) ENGINE = InnoDB,
 PARTITION p20150422 VALUES LESS THAN (1429718400) ENGINE = InnoDB,
 PARTITION p20150423 VALUES LESS THAN (1429804800) ENGINE = InnoDB,
 PARTITION p20150424 VALUES LESS THAN (1429891200) ENGINE = InnoDB,
 PARTITION p20150425 VALUES LESS THAN (1429977600) ENGINE = InnoDB,
 PARTITION p20150426 VALUES LESS THAN (1430064000) ENGINE = InnoDB,
 PARTITION p20150427 VALUES LESS THAN (1430150400) ENGINE = InnoDB,
 PARTITION p20150428 VALUES LESS THAN (1430236800) ENGINE = InnoDB,
 PARTITION p20150429 VALUES LESS THAN (1430323200) ENGINE = InnoDB,
 PARTITION p20150430 VALUES LESS THAN (1430409600) ENGINE = InnoDB,
 PARTITION p20150501 VALUES LESS THAN (1430496000) ENGINE = InnoDB,
 PARTITION p20150502 VALUES LESS THAN (1430582400) ENGINE = InnoDB,
 PARTITION p20150503 VALUES LESS THAN (1430668800) ENGINE = InnoDB,
 PARTITION p20150504 VALUES LESS THAN (1430755200) ENGINE = InnoDB,
 PARTITION p20150505 VALUES LESS THAN (1430841600) ENGINE = InnoDB,
 PARTITION p20150506 VALUES LESS THAN (1430928000) ENGINE = InnoDB,
 PARTITION p20150507 VALUES LESS THAN (1431014400) ENGINE = InnoDB,
 PARTITION p20150508 VALUES LESS THAN (1431100800) ENGINE = InnoDB,
 PARTITION p20150509 VALUES LESS THAN (1431187200) ENGINE = InnoDB,
 PARTITION p20150510 VALUES LESS THAN (1431273600) ENGINE = InnoDB,
 PARTITION p20150511 VALUES LESS THAN (1431360000) ENGINE = InnoDB) */ 

create index idx_tmp on track_raw_wap211_log (log_time);

Slave: Mariadb – 5.5.5-10.0.15-MariaDB-log MariaDB Server

21407 | root | localhost | jkgj_log | Query | 34 | copy to tmp table | create index idx_tmp on track_raw_wap211_log (log_time) | 0.000 |  

drop index command still has this problem.

——————————————————————

When I repeat these steps on MariaDB – 10.0.16 everything is OK.

This bug has been reported to MariaDB:

https://mariadb.atlassian.net/browse/MDEV-8038