maria-developers team mailing list archive

[Jeremy Cole <jeremycole@xxxxxxxxxx>]

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@xxxxxxxxx> 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@xxxxxxxxxxx>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)
> Gtalk: penglixun(at)gmail.com
> Twitter: http://www.twitter.com/plinux
> Blog: http://www.penglixun.com
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-developers
> Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp
>
>