maria-developers team mailing list archive

[Lixun Peng <penglixun@xxxxxxxxx>]

Hi,

As we know, InnoDB has implicit primary key if a table hasn't defined a
primary key.
However MySQL server doesn't know this primary key, so this primary key
will not apear in binlog.

When we are using ROW format binlog for replication, if a table has no any
indexes, that's a disaster. If a table without indexes do a DML
(UPDATE/DELETE), of course it will run a long time in master, but in slave,
it still need a long time. It will cause serious slave replication delay.

For example:
binlog_format=ROW;

CREATE TABLE table1 (
  col1 varchar(32),
  col2 int
);

table1 hasn't any indexes, if table1 has 100 million rows, I do a query
"UPDATE table1 SET col1='xxx' WHERE col2=100", and this query will effect 1
million rows. Then it has 1 million UPDATE events in binlog.
After slave received these events, each events should scan all rows!!! It
will cause serious replication delay!
I saw:https://kb.askmonty.org/en/row-based-replication-with-no-primary-key/
But if no any indexes, it still a problem.

I think mysql server can do the same thing as InnoDB do, if user doesn't
define the primary key, mysql can add the primary key automatically.
For example:
If user create a table like "table1", we can do "ALTER TABLE table1 ADD
__id bigint auto_increment, ADD PRIMARY KEY(__id)", so in fact, the
"table1" structure is
CREATE TABLE table1 (
  col1 varchar(32),
  col2 int,
  __id bigint auto_increment primary key
);
But when user do "SELECT * FROM table xxx" or "INSERT INTO table
VALUES(xx)", we should ignore implicit primary key, "__id" will not apear
in the result set.

How do you think?

Thanks,
Lixun


-- 
Senior Database Engineer @ Taobao.com

Mobile Phone: +86 18658156856 (Hangzhou)
Gtalk: penglixun(at)gmail.com
Twitter: http://www.twitter.com/plinux
Blog: http://www.penglixun.com