Duplicate Key when alter table

• When running an online ALTER TABLE operation, the thread that runs the ALTER TABLE operation will apply an “online log” of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the “online log”. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.
  
  
  
  Take the following limitations into account when running online DDL operations:
  
  • An online ALTER TABLE operation that copies the table can cause an error if the operation uses all of the available disk space on the file system where the data directory (datadir) resides. To avoid this problem, ensure that there is enough disk space to accommodate online ALTER TABLE operations that copy the table. During these operations, MySQL writes temporary sort files to the MySQL temporary directory ($TMPDIR on Unix, %TEMP% on Windows, or the directory specified by the --tmpdir configuration variable). Each temporary file is large enough to hold one column in the new table or index, and each one is removed as soon as it is merged into the final table or index. Such operations may require temporary space equal to the amount of data in the table plus indexes.
    As of MySQL 5.6.29, you can define a separate temporary directory for InnoDB online ALTER TABLE operations using the innodb_tmpdir configuration option. The innodb_tmpdir option was introduced to help avoid temporary directory overflows that could occur as a result of large temporary sort files created during online ALTER TABLE operations that rebuild the table.
  • The table is copied, rather than using Fast Index Creation when you create an index on a TEMPORARY TABLE. This has been reported as MySQL Bug #39833.
  • InnoDB handles error cases when users attempt to drop indexes needed for foreign keys. See Section B.3, “Server Error Codes and Messages” for information related to error 1553.
  • The ALTER TABLE clause LOCK=NONE is not allowed if there are ON...CASCADE or ON...SET NULL constraints on the table.
  • During each online DDL ALTER TABLE statement, regardless of the LOCK clause, there are brief periods at the beginning and end requiring an exclusive lock on the table (the same kind of lock specified by the LOCK=EXCLUSIVE clause). Thus, an online DDL operation might wait before starting if there is a long-running transaction performing inserts, updates, deletes, or SELECT ... FOR UPDATE on that table; and an online DDL operation might wait before finishing if a similar long-running transaction was started while the ALTER TABLE was in progress.
  • When running an online ALTER TABLE operation, the thread that runs the ALTER TABLE operation will apply an “online log” of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the “online log”. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.
  • OPTIMIZE TABLE for an InnoDB table is mapped to an ALTER TABLE operation to rebuild the table and update index statistics and free unused space in the clustered index. Prior to 5.6.17, there is no online DDL support for this operation. Secondary indexes are not created as efficiently because keys are inserted in the order they appeared in the primary key. As of 5.6.17, OPTIMIZE TABLE is supported with the addition of online DDL support for rebuilding regular and partitioned InnoDB tables. For additional information, see Section 14.11.1, “Overview of Online DDL”.
  • InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY. In this case, an ALTER TABLE ... ALGORITHM=INPLACE operation returns the following error:
    

    ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. 
    Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
    

  • These limitations are generally applicable to online DDL operations on large tables where table copying is involved:
    
    • There is no mechanism to pause an online DDL operation or to throttle I/O or CPU usage for an online DDL operation.
    • Progress monitoring capability for online DDL operations is limited until MySQL 5.7.6, which introduces Performance Schema stage events for monitoring ALTER TABLE progress. See Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema.
    • Rollback of an online DDL operation can be expensive should the operation fail.
    • Long running online DDL operations can cause replication lag. An online DDL operation must finish running on the master before it is run on the slave. Also, DML that was processed concurrently on the master is only processed on the slave after the DDL operation on the slave is completed (Bug #73196).
    For additional information related to running DDL operations on large tables, see Section 14.11.2, “Performance and Concurrency Considerations for Online DDL”.
  
  

Comparison of B-Tree and Hash Indexes

Sumary:
- Hash Index:  faster, not support range comparision, only: =, <=>
- B-Tree Index: slower, support more comparison operator =, >, >=, <, <=, or BETWEEN



 Understanding the B-tree and hash data structures can help predict how different queries perform on different storage engines that use these data structures in their indexes, particularly for the MEMORY storage engine that lets you choose B-tree or hash indexes.

B-Tree Index Characteristics

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character. For example, the following SELECT statements use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

In the first statement, only rows with 'Patrick' <= key_col < 'Patricl' are considered. In the second statement, only rows with 'Pat' <= key_col < 'Pau' are considered.
The following SELECT statements do not use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

In the first statement, the LIKE value begins with a wildcard character. In the second statement, the LIKE value is not a constant.
If you use ... LIKE '%string%' and string is longer than three characters, MySQL uses the Turbo Boyer-Moore algorithm to initialize the pattern for the string and then uses this pattern to perform the search more quickly.
A search using col_name IS NULL employs indexes if col_name is indexed.
Any index that does not span all AND levels in the WHERE clause is not used to optimize the query. In other words, to be able to use an index, a prefix of the index must be used in every AND group.
The following WHERE clauses use indexes:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

    /* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2

    /* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5

    /* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

These WHERE clauses do not use indexes:

    /* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2

    /*  Index is not used in both parts of the WHERE clause  */
... WHERE index=1 OR A=10

    /* No index spans all rows  */
... WHERE index_part1=1 OR index_part2=10

Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.) However, if such a query uses LIMIT to retrieve only some of the rows, MySQL uses an index anyway, because it can much more quickly find the few rows to return in the result.

Hash Index Characteristics

Hash indexes have somewhat different characteristics from those just discussed:

• They are used only for equality comparisons that use the = or <=> operators (but are very fast). They are not used for comparison operators such as < that find a range of values. Systems that rely on this type of single-value lookup are known as “key-value stores”; to use MySQL for such applications, use hash indexes wherever possible.
• The optimizer cannot use a hash index to speed up ORDER BY operations. (This type of index cannot be used to search for the next entry in order.)
• MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM or InnoDB table to a hash-indexed MEMORY table.
• Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)
  
  
  Source: https://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

Mounted File Systems or Logical Volumes

Mounted File Systems or Logical Volumes

There are two ways to configure a new disk drive into a CentOS 6 system. One very simple method is to create one or more Linux partitions on the new drive, create Linux file systems on those partitions and then mount them at specific mount points so that they can be accessed. This approach will be covered in this chapter.
Another approach is to add the new space to an existing volume group or create a new volume group. When CentOS 6 is installed a volume group is created and named vg_hostname, where hostname is the host name of the system. Within this volume group are two logical volumes named lv_root and lv_swap that are used to store the / file system and swap partition respectively. By configuring the new disk as part of a volume group we are able to increase the disk space available to the existing logical volumes. Using this approach we are able, therefore, to increase the size of the / file system by allocating some or all of the space on the new disk to lv_root. This topic will be discussed in detail in Adding a New Disk to a CentOS 6 Volume Group and Logical Volume.

Getting Started

This tutorial assumes that the new physical hard drive has been installed on the system and is visible to the operating system. The best way to do this is to enter the system BIOS during the boot process and ensuring that the BIOS sees the disk drive. Sometimes the BIOS will provide a menu option to scan for new drives. If the BIOS does not see the disk drive double check the connectors and jumper settings (if any) on the drive.

Finding the New Hard Drive in CentOS 6

Assuming the drive is visible to the BIOS it should automatically be detected by the operating system. Typically, the disk drives in a system are assigned device names beginning hd or sd followed by a letter to indicate the device number. For example, the first device might be /dev/sda, the second /dev/sdb and so on.
The following is output from a system with only one physical disk drive:

# ls /dev/sd*
/dev/sda  /dev/sda1  /dev/sda2

This shows that the disk drive represented by /dev/sda is itself divided into 2 partitions, represented by /dev/sda1 and /dev/sda2.
The following output is from the same system after a second hard disk drive has been installed:

# ls /dev/sd*
/dev/sda /dev/sda1 /dev/sda2 /dev/sdb

As shown above, the new hard drive has been assigned to the device file /dev/sdb. Currently the drive has no partitions shown (because we have yet to create any). At this point we have a choice of creating partitions and file systems on the new drive and mounting them for access or adding the disk as a physical volume as part of a volume group. To perform the former continue with this chapter, otherwise read Adding a New Disk to a CentOS 6 Volume Group and Logical Volume for details on configuring Logical Volumes.

Creating Linux Partitions

The next step is to create one or more Linux partitions on the new disk drive. This is achieved using the fdisk utility which takes as a command-line argument the device to be partitioned:

# su -
# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xd1082b01.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help):

As instructed, switch off DOS compatible mode and change the units to sectors by entering the c and u commands:

Command (m for help): c
DOS Compatibility flag is not set
Command (m for help): u
Changing display/entry units to sectors

In order to view the current partitions on the disk enter the p command:

Command (m for help): p

Disk /dev/sdb: 34.4 GB, 34359738368 bytes
255 heads, 63 sectors/track, 4177 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xd1082b01

   Device Boot      Start         End      Blocks   Id  System

As we can see from the above fdisk output, the disk currently has no partitions because it is a previously unused disk. The next step is to create a new partition on the disk, a task which is performed by entering n (for new partition) and p (for primary partition):

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4):

In this example we only plan to create one partition which will be partition 1. Next we need to specify where the partition will begin and end. Since this is the first partition we need it to start at the first available sector and since we want to use the entire disk we specify the last sector as the end. Note that if you wish to create multiple partitions you can specify the size of each partition by sectors, bytes, kilobytes or megabytes.

 
Partition number (1-4): 1
First sector (2048-67108863, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-67108863, default 67108863):
Using default value 67108863

Now that we have specified the partition we need to write it to the disk using the w command:

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

If we now look at the devices again we will see that the new partition is visible as /dev/sdb1:

# ls /dev/sd*
/dev/sda /dev/sda1 /dev/sda2 /dev/sdb /dev/sdb1

The next step is to create a file system on our new partition.

Creating a File System on a CentOS 6 Disk Partition

We now have a new disk installed, it is visible to CentOS 6 and we have configured a Linux partition on the disk. The next step is to create a Linux file system on the partition so that the operating system can use it to store files and data. The easiest way to create a file system on a partition is to use the mkfs.ext4 utility which takes as arguments the label and the partition device:

# /sbin/mkfs.ext4 -L /backup /dev/sdb1
mke2fs 1.41.12 (17-May-2010)
Filesystem label=/backup
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
2097152 inodes, 8388352 blocks
419417 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
256 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000, 7962624

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 36 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.

Mounting a File System

Now that we have created a new file system on the Linux partition of our new disk drive we need to mount it so that it is accessible. In order to do this we need to create a mount point. A mount point is simply a directory or folder into which the file system will be mounted. For the purposes of this example we will create a /backup directory to match our file system label (although it is not necessary that these values match):

# mkdir /backup

The file system may then be manually mounted using the mount command:

# mount /dev/sdb1 /backup

Running the mount command with no arguments shows us all currently mounted file systems (including our new file system):

# mount
/dev/mapper/vg_CentOS6-lv_root on / type ext4 (rw)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
tmpfs on /dev/shm type tmpfs (rw,rootcontext="system_u:object_r:tmpfs_t:s0")
/dev/sda1 on /boot type ext4 (rw)
none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)
/dev/sr0 on /media/CentOS_6.0 x86_64 Disc 1 type iso9660 (ro,nosuid,nodev,uhelper=udisks,uid=500,gid=500,
iocharset=utf8,mode=0400,dmode=0500)
/dev/sdb1 on /backup type ext4 (rw)

Configuring CentOS 6 to Automatically Mount a File System

In order to set up the system so that the new file system is automatically mounted at boot time an entry needs to be added to the /etc/fstab file.
The following example shows an fstab file configured to automount our /backup partition:

/dev/mapper/vg_centos6-lv_root /            ext4    defaults        1 1
UUID=0d06ebad-ea73-48ad-a50a-1b3b8ef24491 /boot  ext4    defaults        1 2
/dev/mapper/vg_centos6-lv_swap swap         swap    defaults        0 0
tmpfs                   /dev/shm            tmpfs   defaults        0 0
devpts                  /dev/pts            devpts  gid=5,mode=620  0 0
sysfs                   /sys                sysfs   defaults        0 0
proc                    /proc               proc    defaults        0 0
LABEL=/backup /backup      ext4    defaults        1 2

Source: http://www.techotopia.com/index.php/Adding_a_New_Disk_Drive_to_a_CentOS_6_System 

zzz

https://www.elastic.co/guide/en/elasticsearch/guide/current/_significant_terms_demo.html#_recommending_based_on_statistics

http://stackoverflow.com/questions/18199135/is-it-possible-to-left-join-nested-documents-in-elasticsearch-queries

http://stackoverflow.com/questions/21833092/elasticsearch-join-filter-using-subquery-results-as-filter-input-possible

https://www.elastic.co/guide/en/elasticsearch/guide/current/relations.html

https://www.elastic.co/guide/en/elasticsearch/guide/current/parent-child.html

https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-has-parent-query.html#query-dsl-has-parent-query

https://www.elastic.co/guide/en/elasticsearch/reference/2.1/query-dsl-filtered-query.html

https://www.elastic.co/guide/en/elasticsearch/guide/current/application-joins.html

Restarting iptables restores sysctl setting net.netfilter.nf_conntrack_max to default in RHEL6

# cat /proc/sys/net/netfilter/nf_conntrack_max
65536
# sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
vm.overcommit_memory = 2
vm.overcommit_ratio = 80
net.netfilter.nf_conntrack_max = 524288
# cat /proc/sys/net/netfilter/nf_conntrack_max
524288
[root@rhel6-5 ~]# service iptables restart
iptables: Setting chains to policy ACCEPT: filter [  OK  ]
iptables: Flushing firewall rules: [  OK  ]
iptables: Unloading modules: [  OK  ]
iptables: Applying firewall rules: [  OK  ]
# cat /proc/sys/net/netfilter/nf_conntrack_max
65536

Solution: not yet 

Config load sysctl after restart iptables

vi /etc/sysctl.conf

IPTABLES_SYSCTL_LOAD_LIST=".ip_conntrack .bridge-nf"
IPTABLES_SYSCTL_LOAD_ON_START="yes"




Source: https://bugzilla.redhat.com/show_bug.cgi?id=552522

Sysctl setting - ip conntract

You must login as the root user to use any one of the following command on a Linux operating systems.

Display Linux kernel parameters

The syntax is:
# sysctl variable
####################################
### Display all sysctl variables ###
####################################
# sysctl -a
# sysctl -a | more
# sysctl -a | grep 'something'
# sysctl -a | grep memory
Sample outputs:

vm.memory_failure_early_kill = 0
vm.memory_failure_recovery = 1
vm.overcommit_memory = 0

To see value for kernel variable called kernel.hostname, enter:
# sysctl -n kernel.hostname

How do I set new values?

There are three methods to set new values for given kernel parameters as follows:

Method # 1: Setting value via procfs

You can use standard echo command to write data to variables (this temporary change):
# echo "value" > /proc/sys/location/variable

Method # 2: Temporary on the command line

Use sysctl command with -w option when you want to change a sysctl setting:
sysctl -w variable=value

Method # 3: Configuration file /etc/sysctl.conf

This is recommended way. First open /etc/sysctl.conf file, enter:
# vi /etc/sysctl.conf
Now add value:
variable = value
Close and save the changes. Type the following command to load sysctl settings from the file /etc/sysctl.conf file:
# sysctl -p
OR
# sysctl -p /etc/sysctl.conf
The last method will load settings permanently at boot time from /etc/sysctl.conf file. Read man page of sysctl for information:
$ man sysctl