internals

PostgreSQL locking, part 3: lightweight locks

LWLocks lightweight locks postgresPostgreSQL lightweight locks, or LWLocks, control memory access. PostgreSQL uses multi-process architecture and should allow only consistent reads and writes to shared memory structures. LWLocks have two levels of locking: shared and exclusive. It’s also possible to release all acquired LWLocks to simplify clean up. Other databases often call primitives similar to LWLocks “latches”. Because […]

PostgreSQL locking, part 2: heavyweight locks

Locking in PostgreSQLPostgreSQL locking visibility for application developers and DBAs is in most cases related to heavyweight locks. Complex database locking operations require full instrumentation using views from the system catalog. It should be clear which object is locked by a specific database “backend” process. An alternative name for any lock is “bottleneck”. In order to make […]

PostgreSQL locking, Part 1: Row Locks

PostgreSQL row level locksAn understanding of PostgreSQL locking is important to build scalable applications and avoid downtime. Modern computers and servers have many CPU cores and it’s possible to execute multiple queries in parallel. Databases containing many consistent structures with changes made by queries or background processes running in parallel could crash a database or even corrupt data. […]

Percona Live Europe Session Interviews with Yandex ClickHouse: A DBMS for Interactive Analytics at Scale and Quick Tour of ClickHouse Internals

Percona Live EuropePercona Live Europe 2017 keeps providing excellent sessions with useful information on great open source database technologies. Yandex’s Clickhouse was one of these technologies that was well covered at the conference this year. There were several talks that featured Clickhouse this year. I was able to attend two of them. The first was a discussion of […]

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

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569