ERROR 1069 (42000): Too many keys specified; max 64 keys allowed

A table can contain a maximum of 64 secondary indexes (https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html), exceed this limitation will hit error as below:

 mysql> alter table c add constraint fk64 foreign key (t64) references t64(id);

ERROR 1069 (42000): Too many keys specified; max 64 keys allowed

Confirmed the limitation still applies to MySQL 8.x. There is no such limitation in PostgreSQL and Oracle.

use mytest;
create table t1 (id int primary key);
create table t2 (id int primary key);
create table t3 (id int primary key);
create table t4 (id int primary key);
create table t5 (id int primary key);
create table t6 (id int primary key);
create table t7 (id int primary key);
create table t8 (id int primary key);
create table t9 (id int primary key);
create table t10 (id int primary key);
create table t11 (id int primary key);
create table t12 (id int primary key);
create table t13 (id int primary key);
create table t14 (id int primary key);
create table t15 (id int primary key);
create table t16 (id int primary key);
create table t17 (id int primary key);
create table t18 (id int primary key);
create table t19 (id int primary key);
create table t20 (id int primary key);
create table t21 (id int primary key);
create table t22 (id int primary key);
create table t23 (id int primary key);
create table t24 (id int primary key);
create table t25 (id int primary key);
create table t26 (id int primary key);
create table t27 (id int primary key);
create table t28 (id int primary key);
create table t29 (id int primary key);
create table t30 (id int primary key);
create table t31 (id int primary key);
create table t32 (id int primary key);
create table t33 (id int primary key);
create table t34 (id int primary key);
create table t35 (id int primary key);
create table t36 (id int primary key);
create table t37 (id int primary key);
create table t38 (id int primary key);
create table t39 (id int primary key);
create table t40 (id int primary key);
create table t41 (id int primary key);
create table t42 (id int primary key);
create table t43 (id int primary key);
create table t44 (id int primary key);
create table t45 (id int primary key);
create table t46 (id int primary key);
create table t47 (id int primary key);
create table t48 (id int primary key);
create table t49 (id int primary key);
create table t50 (id int primary key);
create table t51 (id int primary key);
create table t52 (id int primary key);
create table t53 (id int primary key);
create table t54 (id int primary key);
create table t55 (id int primary key);
create table t56 (id int primary key);
create table t57 (id int primary key);
create table t58 (id int primary key);
create table t59 (id int primary key);
create table t60 (id int primary key);
create table t61 (id int primary key);
create table t62 (id int primary key);
create table t63 (id int primary key);
create table t64 (id int primary key);
create table t65 (id int primary key);
create table t66 (id int primary key);
create table t67 (id int primary key);
create table t68 (id int primary key);
create table t69 (id int primary key);
create table t70 (id int primary key);
create table t71 (id int primary key);
create table t72 (id int primary key);
create table t73 (id int primary key);
create table t74 (id int primary key);
create table t75 (id int primary key);
create table t76 (id int primary key);
create table t77 (id int primary key);
create table t78 (id int primary key);
create table t79 (id int primary key);
create table t80 (id int primary key);
create table t81 (id int primary key);
create table t82 (id int primary key);
create table t83 (id int primary key);
create table t84 (id int primary key);
create table t85 (id int primary key);
create table t86 (id int primary key);
create table t87 (id int primary key);
create table t88 (id int primary key);
create table t89 (id int primary key);
create table t90 (id int primary key);
create table t91 (id int primary key);
create table t92 (id int primary key);
create table t93 (id int primary key);
create table t94 (id int primary key);
create table t95 (id int primary key);
create table t96 (id int primary key);
create table t97 (id int primary key);
create table t98 (id int primary key);
create table t99 (id int primary key);
create table t100 (id int primary key);

create table c (
c1 int primary key,
t1 int,
t2 int,
t3 int,
t4 int,
t5 int,
t6 int,
t7 int,
t8 int,
t9 int,
t10 int,
t11 int,
t12 int,
t13 int,
t14 int,
t15 int,
t16 int,
t17 int,
t18 int,
t19 int,
t20 int,
t21 int,
t22 int,
t23 int,
t24 int,
t25 int,
t26 int,
t27 int,
t28 int,
t29 int,
t30 int,
t31 int,
t32 int,
t33 int,
t34 int,
t35 int,
t36 int,
t37 int,
t38 int,
t39 int,
t40 int,
t41 int,
t42 int,
t43 int,
t44 int,
t45 int,
t46 int,
t47 int,
t48 int,
t49 int,
t50 int,
t51 int,
t52 int,
t53 int,
t54 int,
t55 int,
t56 int,
t57 int,
t58 int,
t59 int,
t60 int,
t61 int,
t62 int,
t63 int,
t64 int,
t65 int,
t66 int,
t67 int,
t68 int,
t69 int,
t70 int,
t71 int,
t72 int,
t73 int,
t74 int,
t75 int,
t76 int,
t77 int,
t78 int,
t79 int,
t80 int,
t81 int,
t82 int,
t83 int,
t84 int,
t85 int,
t86 int,
t87 int,
t88 int,
t89 int,
t90 int,
t91 int,
t92 int,
t93 int,
t94 int,
t95 int,
t96 int,
t97 int,
t98 int,
t99 int,
t100 int
);
alter table c add constraint fk1 foreign key (t1) references t1(id);
alter table c add constraint fk2 foreign key (t2) references t2(id);
alter table c add constraint fk3 foreign key (t3) references t3(id);
alter table c add constraint fk4 foreign key (t4) references t4(id);
alter table c add constraint fk5 foreign key (t5) references t5(id);
alter table c add constraint fk6 foreign key (t6) references t6(id);
alter table c add constraint fk7 foreign key (t7) references t7(id);
alter table c add constraint fk8 foreign key (t8) references t8(id);
alter table c add constraint fk9 foreign key (t9) references t9(id);
alter table c add constraint fk10 foreign key (t10) references t10(id);
alter table c add constraint fk11 foreign key (t11) references t11(id);
alter table c add constraint fk12 foreign key (t12) references t12(id);
alter table c add constraint fk13 foreign key (t13) references t13(id);
alter table c add constraint fk14 foreign key (t14) references t14(id);
alter table c add constraint fk15 foreign key (t15) references t15(id);
alter table c add constraint fk16 foreign key (t16) references t16(id);
alter table c add constraint fk17 foreign key (t17) references t17(id);
alter table c add constraint fk18 foreign key (t18) references t18(id);
alter table c add constraint fk19 foreign key (t19) references t19(id);
alter table c add constraint fk20 foreign key (t20) references t20(id);
alter table c add constraint fk21 foreign key (t21) references t21(id);
alter table c add constraint fk22 foreign key (t22) references t22(id);
alter table c add constraint fk23 foreign key (t23) references t23(id);
alter table c add constraint fk24 foreign key (t24) references t24(id);
alter table c add constraint fk25 foreign key (t25) references t25(id);
alter table c add constraint fk26 foreign key (t26) references t26(id);
alter table c add constraint fk27 foreign key (t27) references t27(id);
alter table c add constraint fk28 foreign key (t28) references t28(id);
alter table c add constraint fk29 foreign key (t29) references t29(id);
alter table c add constraint fk30 foreign key (t30) references t30(id);
alter table c add constraint fk31 foreign key (t31) references t31(id);
alter table c add constraint fk32 foreign key (t32) references t32(id);
alter table c add constraint fk33 foreign key (t33) references t33(id);
alter table c add constraint fk34 foreign key (t34) references t34(id);
alter table c add constraint fk35 foreign key (t35) references t35(id);
alter table c add constraint fk36 foreign key (t36) references t36(id);
alter table c add constraint fk37 foreign key (t37) references t37(id);
alter table c add constraint fk38 foreign key (t38) references t38(id);
alter table c add constraint fk39 foreign key (t39) references t39(id);
alter table c add constraint fk40 foreign key (t40) references t40(id);
alter table c add constraint fk41 foreign key (t41) references t41(id);
alter table c add constraint fk42 foreign key (t42) references t42(id);
alter table c add constraint fk43 foreign key (t43) references t43(id);
alter table c add constraint fk44 foreign key (t44) references t44(id);
alter table c add constraint fk45 foreign key (t45) references t45(id);
alter table c add constraint fk46 foreign key (t46) references t46(id);
alter table c add constraint fk47 foreign key (t47) references t47(id);
alter table c add constraint fk48 foreign key (t48) references t48(id);
alter table c add constraint fk49 foreign key (t49) references t49(id);
alter table c add constraint fk50 foreign key (t50) references t50(id);
alter table c add constraint fk51 foreign key (t51) references t51(id);
alter table c add constraint fk52 foreign key (t52) references t52(id);
alter table c add constraint fk53 foreign key (t53) references t53(id);
alter table c add constraint fk54 foreign key (t54) references t54(id);
alter table c add constraint fk55 foreign key (t55) references t55(id);
alter table c add constraint fk56 foreign key (t56) references t56(id);
alter table c add constraint fk57 foreign key (t57) references t57(id);
alter table c add constraint fk58 foreign key (t58) references t58(id);
alter table c add constraint fk59 foreign key (t59) references t59(id);
alter table c add constraint fk60 foreign key (t60) references t60(id);
alter table c add constraint fk61 foreign key (t61) references t61(id);
alter table c add constraint fk62 foreign key (t62) references t62(id);
alter table c add constraint fk63 foreign key (t63) references t63(id);
alter table c add constraint fk64 foreign key (t64) references t64(id);
alter table c add constraint fk65 foreign key (t65) references t65(id);
alter table c add constraint fk66 foreign key (t66) references t66(id);
alter table c add constraint fk67 foreign key (t67) references t67(id);
alter table c add constraint fk68 foreign key (t68) references t68(id);
alter table c add constraint fk69 foreign key (t69) references t69(id);
alter table c add constraint fk70 foreign key (t70) references t70(id);
alter table c add constraint fk71 foreign key (t71) references t71(id);
alter table c add constraint fk72 foreign key (t72) references t72(id);
alter table c add constraint fk73 foreign key (t73) references t73(id);
alter table c add constraint fk74 foreign key (t74) references t74(id);
alter table c add constraint fk75 foreign key (t75) references t75(id);
alter table c add constraint fk76 foreign key (t76) references t76(id);
alter table c add constraint fk77 foreign key (t77) references t77(id);
alter table c add constraint fk78 foreign key (t78) references t78(id);
alter table c add constraint fk79 foreign key (t79) references t79(id);
alter table c add constraint fk80 foreign key (t80) references t80(id);
alter table c add constraint fk81 foreign key (t81) references t81(id);
alter table c add constraint fk82 foreign key (t82) references t82(id);
alter table c add constraint fk83 foreign key (t83) references t83(id);
alter table c add constraint fk84 foreign key (t84) references t84(id);
alter table c add constraint fk85 foreign key (t85) references t85(id);
alter table c add constraint fk86 foreign key (t86) references t86(id);
alter table c add constraint fk87 foreign key (t87) references t87(id);
alter table c add constraint fk88 foreign key (t88) references t88(id);
alter table c add constraint fk89 foreign key (t89) references t89(id);
alter table c add constraint fk90 foreign key (t90) references t90(id);
alter table c add constraint fk91 foreign key (t91) references t91(id);
alter table c add constraint fk92 foreign key (t92) references t92(id);
alter table c add constraint fk93 foreign key (t93) references t93(id);
alter table c add constraint fk94 foreign key (t94) references t94(id);
alter table c add constraint fk95 foreign key (t95) references t95(id);
alter table c add constraint fk96 foreign key (t96) references t96(id);
alter table c add constraint fk97 foreign key (t97) references t97(id);
alter table c add constraint fk98 foreign key (t98) references t98(id);
alter table c add constraint fk99 foreign key (t99) references t99(id);
alter table c add constraint fk100 foreign key (t100) references t100(id);

Examples to generate large datasets in Oracle, PostgreSQL and MySQL

 In Oracle:

create table t_test (id integer generated as identity, name varchar2(20));

insert into t_test (name) select 'hans' from dual connect by level <= 2000000;

insert into t_test (name) select 'paul' from dual connect by level <= 2000000;

COMMIT;

CREATE INDEX idx_id ON t_test (id);

SQL> select segment_name,bytes from user_segments where segment_name in ('IDX_ID','T_TEST');

   SEGMENT_NAME       BYTES 

_______________ ___________ 

IDX_ID             83886080 

T_TEST             75497472 

In PostgreSQL:

CREATE TABLE t_test (id serial, name text);

INSERT INTO t_test (name) SELECT 'hans' FROM generate_series(1, 2000000);

INSERT INTO t_test (name) SELECT 'paul' FROM generate_series(1, 2000000);

CREATE INDEX idx_id ON t_test (id);

mytest=> SELECT pg_size_pretty(pg_relation_size('T_TEST')) AS "T_TEST", 

pg_size_pretty(pg_relation_size('IDX_ID')) AS "IDX_ID";

 T_TEST | IDX_ID 

--------+--------

 169 MB | 86 MB

(1 row)

In MySQL:

CREATE TABLE t_test (id int auto_increment, name varchar(10), primary key 

(id));

set @@cte_max_recursion_depth=100000000;

INSERT INTO t_test (name) 

WITH RECURSIVE cte (n) AS

(

  SELECT 1

  UNION ALL

  SELECT n + 1 FROM cte WHERE n < 2000000

)

SELECT 'hans' FROM cte;

INSERT INTO t_test (name) 

WITH RECURSIVE cte (n) AS

(

  SELECT 1

  UNION ALL

  SELECT n + 1 FROM cte WHERE n < 2000000

)

SELECT 'hans' FROM cte;

------ Redo the testing without depending on MySQL "Primary" index

CREATE TABLE t_test (id int, name varchar(10));

set @@cte_max_recursion_depth=100000000;

INSERT INTO t_test

WITH RECURSIVE cte (n) AS

(

  SELECT 1

  UNION ALL

  SELECT n + 1 FROM cte WHERE n < 2000000

)

SELECT *, 'hans' FROM cte;

INSERT INTO t_test

WITH RECURSIVE cte (n) AS

(

  SELECT 1

  UNION ALL

  SELECT n + 1 FROM cte WHERE n < 2000000

)

SELECT  *, 'paul' FROM cte;

CREATE INDEX idx_id ON t_test (id);

mysql> SELECT database_name, table_name, index_name, 

ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb FROM mysql.innodb_index_stats 

WHERE stat_name = 'size' AND index_name != 'PRIMARY' and table_name='t_test' ORDER BY size_in_mb DESC;

+---------------+------------+-----------------+------------+

| database_name | table_name | index_name      | size_in_mb |

+---------------+------------+-----------------+------------+

| test2         | t_test     | GEN_CLUST_INDEX |     142.67 |

| test2         | t_test     | idx_id          |      73.61 |

+---------------+------------+-----------------+------------+

2 rows in set (0.00 sec)

MySQL Locking Behaviour different from Oracle with FK invovled

 There is no locking observed to run following code in concurrent sessions in Oracle database: (S1 = first session, S2 = second session)

-- simulate FK locking in Oracle

create table p (id number, name varchar2(10), 

constraint p_pk primary key (id));

create table c (id number, name varchar2(10), pid number,

constraint c_pk primary key (id), 

constraint c_p_fk foreign key (pid) references p(id));

insert into p values (1,'a');

insert into p values (2,'b');

insert into c values (1,'a',1);

commit;

-- Test 1, update p while insert into c with same pid

(S1) update p set name='c' where id=2;

(S2) insert into c values (2,'b',2);

-- Test 2, insert into c while update p with same pid

(S1) insert into c values (2,'b',2);

(S2) update p set name='c' where id=2;

-- Test 3, update p1 while update p2 with same pid

(S1) update p set name='c' where id=;

(S2) update c set pid=2 where id=1;

(S2) update c set pid=1 where id=1;

Lock Error "ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction" observed in MySQL as child table update requires lock on parent table primary key.

-- simulate FK locking in MySQL

create table p (id int, name varchar(10), 

constraint p_pk primary key (id));

create table c (id int, name varchar(10), pid int,

constraint c_pk primary key (id), 

constraint c_p_fk foreign key (pid) references p(id));

insert into p values (1,'a');

insert into p values (2,'b');

insert into c values (1,'a',1);

commit;

SET autocommit=0;

-- Test 1, update p while insert into c with same pid

(S1) update p set name='c' where id=2;

(S2) insert into c values (2,'b',2);

MySQL [test]> insert into c values (2,'b',2);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

---TRANSACTION 2977, ACTIVE 48 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 50, OS thread handle 47659528083200, query id 16389 10.1.1.49 admin update

insert into c values (2,'b',2)

------- TRX HAS BEEN WAITING 48 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 9 page no 4 n bits 72 index PRIMARY of table `test`.`p` trx id 2977 lock mode S locks rec but not gap waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000002; asc     ;;

 1: len 6; hex 000000000ba0; asc       ;;

 2: len 7; hex 02000000ac0151; asc       Q;;

 3: len 1; hex 63; asc c;;

------------------

---TRANSACTION 2976, ACTIVE 132 sec

2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 51, OS thread handle 47659548722944, query id 16431 10.1.1.49 admin starting

show engine innodb status

-- Test 2, insert into c while update p with same pid

(S1) insert into c values (2,'b',2);

(S2) update p set name='c' where id=2;

MySQL [test]> update p set name='c' where id=2;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

---TRANSACTION 2994, ACTIVE 12 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 50, OS thread handle 47659528083200, query id 16777 10.1.1.49 admin updating

update p set name='c' where id=2

------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 9 page no 4 n bits 72 index PRIMARY of table `test`.`p` trx id 2994 lock_mode X locks rec but not gap waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000002; asc     ;;

 1: len 6; hex 000000000b9e; asc       ;;

 2: len 7; hex 010000013202d3; asc     2  ;;

 3: len 1; hex 62; asc b;;

------------------

---TRANSACTION 2989, ACTIVE 24 sec

3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 51, OS thread handle 47659548722944, query id 16762 10.1.1.49 admin

-- Test 3, update p1 while update p2 with same pid

(S1) update p set name='c' where id=1;

(S2) update c set pid=2 where id=1;

(S2) update c set pid=1 where id=1;

MySQL [test]> update c set pid=2 where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

MySQL [test]> update c set pid=1 where id=1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

---TRANSACTION 3001, ACTIVE 69 sec updating or deleting

mysql tables in use 1, locked 1

LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2

MySQL thread id 50, OS thread handle 47659528083200, query id 16964 10.1.1.49 admin updating

update c set pid=1 where id=1

------- TRX HAS BEEN WAITING 37 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 9 page no 4 n bits 72 index PRIMARY of table `test`.`p` trx id 3001 lock mode S locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000001; asc     ;;

 1: len 6; hex 000000000bb8; asc       ;;

 2: len 7; hex 02000002040151; asc       Q;;

 3: len 1; hex 63; asc c;;

------------------

---TRANSACTION 3000, ACTIVE 78 sec

2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 51, OS thread handle 47659548722944, query id 16942 10.1.1.49 admin

Number of MySQL table columns affects binlog size with row-based reolication

 binlog_row_image


Introduced
5.6.2
Command-Line Format
--binlog-row-image=image_type
System Variable
Name
binlog_row_image=image_type
Variable Scope
Global, Session
Dynamic Variable
Yes
Permitted Values
Type
enumeration
Default
full
Valid Values
full (Log all columns)
minimal (Log only changed columns, and columns needed to identify rows)
noblob (Log all columns, except for unneeded BLOB and TEXT columns)



In MySQL row-based replication, each row change event contains two images, a “before” image whose columns are matched against when searching for the row to be updated, and an “after” image containing the changes. Normally, MySQL logs full rows (that is, all columns) for both the before and after images. However, it is not strictly necessary to include every column in both images, and we can often save disk, memory, and network usage by logging only those columns which are actually required.


Note

When deleting a row, only the before image is logged, since there are no changed values to propagate following the deletion. When inserting a row, only the after image is logged, since there is no existing row to be matched. Only when updating a row are both the before and after images required, and both written to the binary log.


For the before image, it is necessary only that the minimum set of columns required to uniquely identify rows is logged. If the table containing the row has a primary key, then only the primary key column or columns are written to the binary log. Otherwise, if the table has a unique key all of whose columns are NOT NULL, then only the columns in the unique key need be logged. (If the table has neither a primary key nor a unique key without any NULL columns, then all columns must be used in the before image, and logged.) In the after image, it is necessary to log only the columns which have actually changed.

In MySQL 5.6, you can cause the server to log full or minimal rows using the binlog_row_image system variable. This variable actually takes one of three possible values, as shown in the following list:



full: Log all columns in both the before image and the after image.


minimal: Log only those columns in the before image that are required to identify the row to be changed; log only those columns in the after image that are actually changed.


noblob: Log all columns (same as full), except for BLOB and TEXT columns that are not required to identify rows, or that have not changed.





Note

This variable is not supported by MySQL Cluster; setting it has no effect on the logging of NDB tables. (Bug #16316828)


The default value is full. In MySQL 5.5 and earlier, full row images are always used for both before images and after images. If you need to replicate from a MySQL 5.6 (or later) master to a slave running a previous version of MySQL, the master should always use this value.

When using minimal or noblob, deletes and updates are guaranteed to work correctly for a given table if and only if the following conditions are true for both the source and destination tables:



All columns must be present and in the same order; each column must use the same data type as its counterpart in the other table.


The tables must have identical primary key definitions.




(In other words, the tables must be identical with the possible exception of indexes that are not part of the tables' primary keys.)

If these conditions are not met, it is possible that the primary key column values in the destination table may prove insufficient to provide a unique match for a delete or update. In this event, no warning or error is issued; the master and slave silently diverge, thus breaking consistency.

Setting this variable has no effect when the binary logging format is STATEMENT. When binlog_format is MIXED, the setting for binlog_row_image is applied to changes that are logged using row-based format, but this setting no effect on changes logged as statements.
Setting binlog_row_image on either the global or session level does not cause an implicit commit; this means that this variable can be changed while a transaction is in progress without affecting the transaction.   

================================================================

 Length Name
    ------ ----
  72251750 server1-bin.000001
   4381411 server1-bin.000002 <-- update t1 set last_modified=last_modified + interval 1 second;
    788494 server1-bin.000003 <-- update t2 set last_modified=last_modified + interval 1 second;
    229497 server1-bin.000004 <-- create table t3 (id integer auto_increment primary key, flag char(1));
    458175 server1-bin.000005 <-- update t3 set flag='y';
       810 server1-bin.000006 <-- alter table t3 add column c1 char(200) default 'a';alter table t3 add column c2 char(200) default 'b';alter table t3 add column c3 char(200) default 'c';
    850512 server1-bin.000007 <-- update t3 set c1='c',c2='b',c3='a';
    850512 server1-bin.000008 <-- update t3 set flag='z';
       194 server1-bin.000009
       297 server1-bin.index

mysql> create database test;
mysql> use test
mysql> create table t1 (id integer, c1 char(200), c2 char(200), c3 char(200), last_modified timestamp(6));
mysql> alter table t1 modify column id int auto_increment primary key;
mysql> create table t2 (id integer auto_increment primary key, last_modified timestamp(6));

mysql> flush logs;


mysql> update t1 set last_modified=last_modified + interval 1 second;
Query OK, 32550 rows affected (0.54 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;

mysql> update t2 set last_modified=last_modified + interval 1 second;
Query OK, 32689 rows affected (0.36 sec)
Rows matched: 32689  Changed: 32689  Warnings: 0
mysql> flush logs;


mysql> create table t3 (id integer auto_increment primary key, flag char(1));
mysql> insert into t3 (flag) select 'x' from t1;
Query OK, 32550 rows affected (0.33 sec)
Records: 32550  Duplicates: 0  Warnings: 0
mysql> flush logs;

mysql> update t3 set flag='y';
Query OK, 32550 rows affected (0.87 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;


mysql> alter table t3 add column c1 char(200) default 'a';
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t3 add column c2 char(200) default 'b';
Query OK, 0 rows affected (0.98 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t3 add column c3 char(200) default 'c';
Query OK, 0 rows affected (1.46 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> flush logs;


mysql> select * from t3 limit 1\G
*************************** 1. row ***************************
  id: 1flag: y
  c1: a
  c2: b
  c3: c
1 row in set (0.00 sec)

mysql> update t3 set c1='a',c2='b',c3='c';
Query OK, 0 rows affected (0.11 sec)
Rows matched: 32550  Changed: 0  Warnings: 0
mysql> update t3 set c1='c',c2='b',c3='a';
Query OK, 32550 rows affected (1.02 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;

mysql> update t3 set flag='z';
Query OK, 32550 rows affected (0.35 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

Quick Steps on how to setup MariaDB Enterprise Cluster (Galera Cluster)

Repeat below steps on servers “mysql01”, “mysql02” & “mysql03”

[root@mysql01 ~]# groupadd -g 500 mysql
[root@mysql01 ~]# useradd -g mysql -u 500 -m -d /home/mysql mysql
[mysql@mysql01 my]$ cd /my
[mysql@mysql01 my]$ tar zxvf repo/mariadb-enterprise-cluster-10.0.21-linux-x86_64.tar.gz
[mysql@mysql01 my]$ ln -s mariadb-enterprise-cluster-10.0.21-linux-x86_64 mysql
[mysql@mysql01 mysql]$ mkdir /my/data

Follow the steps based on server name. (Sample my.cnf referring to end of this post)

[mysql@mysql01 mysql]$ cp mariadb-enterprise.cnf /my/my.cnf
[mysql@mysql01 mysql]$ ./scripts/mysql_install_db --defaults-file=/my/my.cnf
[mysql@mysql01 my]$ scp my.cnf  mysql02:/my
[mysql@mysql01 my]$ scp my.cnf  mysql03:/my
[mysql@mysql01 my]$ scp -rp data mysql02:/my/
[mysql@mysql01 my]$ scp -rp data mysql03:/my/
[mysql@mysql01 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep-new-cluster &
[mysql@mysql02 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61 &
[mysql@mysql03 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61 &
[mysql@mysql01 my]$ /my/mysql/bin/mysqladmin -u root -S /tmp/mysql.sock shutdown
[mysql@mysql02 my]$ /my/mysql/bin/mysqladmin -u root -S /tmp/mysql.sock shutdown
[mysql@mysql03 my]$ /my/mysql/bin/mysqladmin -u root -S /tmp/mysql.sock shutdown
[mysql@mysql01 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm:// &
[mysql@mysql02 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61,192.168.6.63 &
[mysql@mysql03 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61,192.168.6.62 &

MariaDB [(none)]> show status like 'wsre%';
+------------------------------+-------------------------------------------------------+
| Variable_name                | Value                                                 |
+------------------------------+-------------------------------------------------------+
| wsrep_local_state_uuid       | ec8dda0f-86f0-11e5-945a-53d2400df46b                  |
| wsrep_protocol_version       | 7                                                     |
| wsrep_last_committed         | 0                                                     |
| wsrep_replicated             | 0                                                     |
| wsrep_replicated_bytes       | 0                                                     |
| wsrep_repl_keys              | 0                                                     |
| wsrep_repl_keys_bytes        | 0                                                     |
| wsrep_repl_data_bytes        | 0                                                     |
| wsrep_repl_other_bytes       | 0                                                     |
| wsrep_received               | 10                                                    |
| wsrep_received_bytes         | 832                                                   |
| wsrep_local_commits          | 0                                                     |
| wsrep_local_cert_failures    | 0                                                     |
| wsrep_local_replays          | 0                                                     |
| wsrep_local_send_queue       | 0                                                     |
| wsrep_local_send_queue_max   | 1                                                     |
| wsrep_local_send_queue_min   | 0                                                     |
| wsrep_local_send_queue_avg   | 0.000000                                              |
| wsrep_local_recv_queue       | 0                                                     |
| wsrep_local_recv_queue_max   | 1                                                     |
| wsrep_local_recv_queue_min   | 0                                                     |
| wsrep_local_recv_queue_avg   | 0.000000                                              |
| wsrep_local_cached_downto    | 18446744073709551615                                  |
| wsrep_flow_control_paused_ns | 0                                                     |
| wsrep_flow_control_paused    | 0.000000                                              |
| wsrep_flow_control_sent      | 0                                                     |
| wsrep_flow_control_recv      | 0                                                     |
| wsrep_cert_deps_distance     | 0.000000                                              |
| wsrep_apply_oooe             | 0.000000                                              |
| wsrep_apply_oool             | 0.000000                                              |
| wsrep_apply_window           | 0.000000                                              |
| wsrep_commit_oooe            | 0.000000                                              |
| wsrep_commit_oool            | 0.000000                                              |
| wsrep_commit_window          | 0.000000                                              |
| wsrep_local_state            | 4                                                     |
| wsrep_local_state_comment    | Synced                                                |
| wsrep_cert_index_size        | 0                                                     |
| wsrep_causal_reads           | 0                                                     |
| wsrep_cert_interval          | 0.000000                                              |
| wsrep_incoming_addresses     | 192.168.6.61:3306,192.168.6.62:3306,192.168.6.63:3306 |
| wsrep_evs_delayed            |                                                       |
| wsrep_evs_evict_list         |                                                       |
| wsrep_evs_repl_latency       | 0/0/0/0/0                                             |
| wsrep_evs_state              | OPERATIONAL                                           |
| wsrep_gcomm_uuid             | ec8ccb15-86f0-11e5-9d6e-0632737bbcb8                  |
| wsrep_cluster_conf_id        | 3                                                     |
| wsrep_cluster_size           | 3                                                     |
| wsrep_cluster_state_uuid     | ec8dda0f-86f0-11e5-945a-53d2400df46b                  |
| wsrep_cluster_status         | Primary                                               |
| wsrep_connected              | ON                                                    |
| wsrep_local_bf_aborts        | 0                                                     |
| wsrep_local_index            | 0                                                     |
| wsrep_provider_name          | Galera                                                |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>                     |
| wsrep_provider_version       | 25.3.9(r3387)                                         |
| wsrep_ready                  | ON                                                    |
| wsrep_thread_count           | 2                                                     |
+------------------------------+-------------------------------------------------------+
57 rows in set (0.00 sec)

Sample “/my/my.cnf” on mysql01.

[mariadb] plugin-maturity=gamma feedback-user-info='enterprise' ## -- Remove the following line to enable feedback reporting to mariadb.org feedback-url='' [mysqld] # -- Disable for applications using old auth protocol enable-secure-auth # -- Auditing - pre-load Plugin plugin-load=server_audit ########################################### [mysqld] # General datadir=/my/data basedir=/my/mysql # bind_address = 127.0.0.1 skip-name-resolve max-allowed-packet                       = 64M open-files-limit                         = 65535 max-connect-errors                       = 100000 max-connections                          = 1000 thread-cache-size                        = 200 back-log                                 = 128 #net_buffer_length                       = 16K #connect_timeout                         = 10 #net_read_timeout                        = 30 #net_write_timeout                       = 60 #interactive_timeout                     = 28800 #wait_timeout                            = 28800 #net_retry_count                         = 10 character-set-server                     = 'utf8' collation-server                         = 'utf8_general_ci' # -- Do not change these defaults, unless you really know. #read_rnd_buffer_size                    = 256K #sort_buffer_size                        = 2M #read_buffer_size                        = 128K #max_tmp_tables                          = 32 tmp-table-size                           = 32M max-heap-table-size                      = 32M # -- This will enable a stricter SQL mode to avoid bad data sql-mode                                 = STRICT_TRANS_TABLES optimizer_search_depth                   = 0 optimizer_switch                         = 'index_merge=on' optimizer_switch                         = 'index_merge_union=on' optimizer_switch                         = 'index_merge_sort_union=on' optimizer_switch                         = 'index_merge_intersection=on' optimizer_switch                         = 'index_merge_sort_intersection=off' optimizer_switch                         = 'index_condition_pushdown=on' optimizer_switch                         = 'derived_merge=on' optimizer_switch                         = 'derived_with_keys=on' optimizer_switch                         = 'firstmatch=on' optimizer_switch                         = 'loosescan=on' optimizer_switch                         = 'materialization=on' optimizer_switch                         = 'in_to_exists=on' optimizer_switch                         = 'semijoin=on' optimizer_switch                         = 'partial_match_rowid_merge=on' optimizer_switch                         = 'partial_match_table_scan=on' optimizer_switch                         = 'subquery_cache=on,mrr=on' optimizer_switch                         = 'mrr_cost_based=on' optimizer_switch                         = 'mrr_sort_keys=off' optimizer_switch                         = 'outer_join_with_cache=on' optimizer_switch                         = 'semijoin_with_cache=on' optimizer_switch                         = 'join_cache_incremental=on' optimizer_switch                         = 'join_cache_hashed=on' optimizer_switch                         = 'join_cache_bka=on' optimizer_switch                         = 'optimize_join_buffer_size=on' optimizer_switch                         = 'table_elimination=on' optimizer_switch                         = 'extended_keys=on' join-buffer-space-limit                  = 4M join-cache-level                         = 6 join-buffer-size                         = 4M # Security symbolic-links                           = 0 local-infile                             = 0 # Logging log-warnings                             = 2 slow-query-log                           = 1 long-query-time                          = 5 log-slow-verbosity                       = 'query_plan,innodb' #log_slow_rate_limit                     = 1000 # Replication / Binary logs # -- Set to unique number on each server participating in replication server-id                                 = 1 slave_net_timeout                         = 60 #max_prepared_stmt_count                   = 16382 # -- Annotate queries as comments in binary log, for row based events (Breaks replication to MySQL) #If you plan to downgrade to MySQL don’t set binlog_annotate_row_events to ON. binlog-annotate-row-events               = ON log-bin=mariadb-bin # BIN.2  Binlog File binlog-format                            = ROW expire-logs-days                         = 7 max-binlog-size                          = 1024M # -- Recommended for binary log durability. Expect 60% performance hit without write cache on controller sync-binlog                              = 1 binlog-stmt-cache-size                   = 128K binlog-cache-size                        = 256K #slave_compressed_protocol                = ON slave-transaction-retries                = 10 #relay_log_recovery                       = ON #sync_master_info                         = 1 #sync_relay_log                           = 0 #sync_relay_log_info                      = 1 # InnoDB default-storage-engine                   = 'InnoDB' innodb-stats-on-metadata                 = 0 innodb-stats-sample-pages                = 32 table-definition-cache                   = 2048 table-open-cache                         = 2048 transaction-isolation                    = READ-COMMITTED # To be set to 0 if not multi transactional storage engine innodb-support-xa                        = ON # Query Cache query-cache-size                         = 0 query-cache-type                         = 0 innodb-buffer-pool-instances             = 8 # -- Set the following to maximum 60/70% of physical RAM. innodb-buffer-pool-size                  = 2G # innodb_max_dirty_pages_pct should be compute with redo log size in mind: It’s recommended to set the dirty pages smaller than redo log space. The more you have dirty page, the less io will be produced on disk. innodb-max-dirty-pages-pct               = 50 innodb-file-per-table                    = 1 innodb-file-format                       = Barracuda innodb-flush-log-at-trx-commit           = 1 #Save and restore buffer pool to be transparent for user #innodb_flush_method                      = O_DIRECT innodb-log-buffer-size                   = 64M innodb-log-files-in-group                = 2 #innodb-log-file-size                     = 1024M #innodb_purge_threads                     = 1 # -- Raid1: 200. Raid10: 200 x write arrays. SSD: 5000. FusionIO: 20000. #innodb_io_capacity                      = 200 # -- SSD & FusionIO can flush pages in random disk order, keep default for spinning disks #innodb_flush_neighbors                   = 0 # -- Increase these numbers to increase global throughput on SAN (16,32,64) innodb-read-io-threads                   = 8 innodb-write-io-threads                  = 8 # -- Set this to 2-4 times # of Cpus, maximum recommended is 64. # -- iostat report io service time: io_capacity / service_time is a good starting point for innodb_write_io_threads # -- innodb_read_io_threads starting point monitoring status # read ahead per second #innodb-thread-concurrency                = 64 innodb-open-files                       = 2048 # MyISAM key-buffer-size                          = 64M flush                                    = OFF myisam-recover-options                   = BACKUP,FORCE myisam-sort-buffer-size                  = 64M # Other # -- Enable INFORMATION_SCHEMA.%_STATISTICS tables userstat                                 = ON archive                                  = OFF blackhole                                = OFF #federated                               = OFF #innodb                                  = FORCE [mysql] default-character-set                    = 'utf8' auto-rehash                              = FALSE local-infile                             = 1 max-allowed-packet                       = 64M secure-auth                              = TRUE [mysqldump] max-allowed-packet                       = 1G default-character-set                    = 'utf8' [myisamchk] key-buffer-size                          = 1G sort-buffer-size                         = 1G read-buffer-size                         = 8M write-buffer-size                        = 8M # # * Galera-related settings # [galera] # Mandatory settings: # -- The path to the galea library, usually its # -- /usr/lib(64)/galera/libgalera_smm.so wsrep_provider=/my/mysql/lib/libgalera_smm.so # -- Address of any single member node of the cluster. You can also specify # -- addresses of multiple nodes in the cluster (recommended). It takes the # -- address(es) in the following format : gcomm://ip[:port][,ip[:port]]. # -- An empty address (gcomm://) would cause the node to bootstrap. # below Bootstrap for first time initialization on mysql01 #wsrep_cluster_address=gcomm:// #After this single-node cluster is started, #variable wsrep_cluster_address should be updated to the list of all nodes in the cluster wsrep_cluster_address=gcomm://192.168.6.61,192.168.6.62,192.168.6.63 # -- Binary log format for write sets. binlog_format=row # -- Default storage engine to create tables. default_storage_engine=InnoDB # -- interleaved lock-mode innodb_autoinc_lock_mode=2 # -- Accept remote connections (for mysqldump state-transfer method). bind-address=0.0.0.0 # # Optional settings: # -- Number of worker threads to apply replicated transactions in parallel. #wsrep_slave_threads=1 # -- Do not write log buffer to log file at transaction commit, but once per # -- second. innodb_flush_log_at_trx_commit=0 # -- Do not allow to create table without primary key. innodb_force_primary_key=1