Lixun,

I've thought about this a bit and I'm not sure this will be very simple to do (or rather it's more complicated than it might seem). While I think it is not that hard to expose the __id field to replication internals, I think in order for this to really work it would need to be exposed to other tools, such as mysqldump. It is also unclear how to decide when it is safe to use this __id field (how to determine if it is in sync between master and slave).

As an alternate suggestion, what about ignoring the current implicit PK behavior, and instead automatically adding a field using auto_increment when the user doesn't provide a PK:

__id BIGINT UNSIGNED NOT NULL auto_increment,
PRIMARY KEY(__id)

Regards,

Jeremy


On Wed, Jun 19, 2013 at 2:40 AM, Lixun Peng <penglixun@gmail.com> wrote:
Hi Sergei,

You are right, let users add primary key is best.
But I can't let users who don't want to create primary key can't use our MySQL service.
Amazon RDS also allow users to create the tables without primary key, just change binlog_format to MIXED to solve replication problem.

I think this feature is very useful for any MySQL cloud service providers, all of them will face this problem in one day.
I will try to do some analysis/research in this feature implement first, I will need your help :-)

I will notice any new updates in this email.


Thanks,
Lixun
 



On Wed, Jun 19, 2013 at 5:14 PM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Lixun!


On Jun 18, Lixun Peng wrote:
> 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.
...

> 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.
>
> How do you think?

Well, that's doable. A much easier solution would be to require a user
to create a primary key. It's a one-line change:

-  Table_flags ha_table_flags() const { return cached_table_flags; }
+  Table_flags ha_table_flags() const { return cached_table_flags | HA_REQUIRE_PRIMARY_KEY; }

But what you suggest is possible too, I believe.

Regards,
Sergei



--
Senior Database Engineer @ Taobao.com

Mobile Phone: +86 18658156856 (Hangzhou)

_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to     : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp